4
一文全搞懂postgresql的角色
source link: https://blog.51cto.com/u_13874232/5766812
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
一文全搞懂postgresql的角色
精选 原创角色和用户的使用
举个例子，创建一个只读用户和两个读写用户
通用前提操作
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase 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;
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;
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;
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)
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;
<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和其他允许执行服务器端程序的函数。
- 1赞
- 1收藏
- 3评论
- 分享
- 举报
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK