一文全搞懂postgresql的角色

角色和使用者的使用

舉個例子，建立一個隻讀使用者和兩個讀寫使用者

通用前提操作

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;      

建立隻讀角色

CREATE ROLE readonly;

GRANT CONNECT ON DATABASE mydatabase TO readonly;

GRANT USAGE ON SCHEMA myschema TO readonly;

GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;      

建立讀寫角色

CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;      

使用者建立以及賦予角色權限

CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';

GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;      

預設角色

pg_read_all_settings

讀取所有配置變量，即使是那些通常隻對超級使用者可見的配置變量。

postgres=# \c postgres user2;
You are now connected to database "postgres" as user "user2".
postgres=> show data_directory;
ERROR:  must be superuser or a member of pg_read_all_settings to examine "data_directory"
postgres=> 

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant pg_read_all_settings to user2;
GRANT ROLE

postgres=# \c postgres user2;                  
You are now connected to database "postgres" as user "user2".
postgres=> show data_directory;                
 data_directory 
----------------
 /opt/pgdata
(1 row)      

pg_read_all_stats

讀取所有pg_stat_*視圖并使用各種與統計相關的擴充，即使是那些通常隻對超級使用者可見的擴充。​

postgres=> select * from pg_stat_activity;
<insufficient privilege>       


grant pg_read_all_stats to user2;      

​pg_stat_scan_tables

​執行可能在表上擷取ACCESS SHARE鎖的監視函數，可能需要很長時間。​

​pg_monitor

讀取/執行各種監視視圖和函數。該角色是pg_read_all_settings、pg_read_all_stats和pg_stat_scan_tables的成員。​

​

​pg_signal_backend

​通知另一個後端取消查詢或終止其會話。但是殺不了超級使用者的會話

​pg_read_server_files

允許從資料庫可以通過COPY和其他檔案通路功能通路的伺服器上的任何位置讀取檔案。

​pg_write_server_files

允許對伺服器上資料庫可以通過COPY和其他檔案通路功能通路的任何位置的檔案進行寫入。