PG权限相关
原创
©著作权归作者所有:来自51CTO博客作者岳麓丹枫的原创作品,请联系作者获取转载授权,否则将追究法律责任
默认 超级管理员 postgres
创建审计管理员(只读用户) sys_auditor
create role sys_auditor login password 'sys_auditor' ;
alter role sys_auditor set default_transaction_read_only=on;
GRANT USAGE ON SCHEMA public to sys_auditor;
-- 对于未来新建的表, 自动分配读权限给 sys_auditor
grant select on all tables in schema public to sys_auditor;ALTER DEFAULT PRIVILEGES for role postgres IN SCHEMA public GRANT select ON TABLES TO sys_auditor;
创建普通操作员(普通操作用户) sys_operator
create role sys_operator login password 'sys_operator' nosuperuser nocreatedb noinherit noreplication ;
删除被其他对象依赖的用户
reassign owned by test to postgres;
drop owned by test cascade;
drop role test ;
create role test login password 'passwd';
创建只读用户
CREATE USER test WITH ENCRYPTED PASSWORD 'passwd';
alter user test set default_transaction_read_only=on;
GRANT USAGE ON SCHEMA public to test;
grant select on all tables in schema public to test
# 查看postgresql数据库用户系统权限、对象权限的方法
1、查看某用户的系统权限
SELECT * FROM pg_roles WHERE rolname='postgres';
2、查看某用户的表权限
select * from information_schema.table_privileges where grantee='postgres';
3、查看某用户的usage权限
select * from information_schema.usage_privileges where grantee='postgres';
4、查看某用户在存储过程函数的执行权限
select * from information_schema.routine_privileges where grantee='postgres';
5、查看某用户在某表的列上的权限
select * from information_schema.column_privileges where grantee='postgres';
6、查看当前用户能够访问的数据类型
select * from information_schema.data_type_privileges ;
7、查看用户自定义类型上授予的USAGE权限
select * from information_schema.udt_privileges where grantee='postgres';
参考:https://developer.aliyun.com/article/815729
https://pgfans.cn/a/943