PG权限相关

默认 超级管理员 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 ;      

select * from information_schema.udt_privileges where grantee='postgres';