角色和用户的使用
举个例子,创建一个只读用户和两个读写用户
通用前提操作
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和其他文件访问功能访问的任何位置的文件进行写入。
pg_execute_server_program
允许作为数据库运行的用户在数据库服务器上执行程序,使用COPY和其他允许执行服务器端程序的函数。