Eg:现有数据postgres,mytest,当前用户为root,schema为public,范例为创建对mytest库public下所有表只有查询权限的readonly用户
一.创建
1. 创建只读用户:
CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonly'; |
2. 设置默认事务只读:
alter user readonly set default_transaction_read_only=on; |
3. 赋予用户连接数据库mytest的权限:
GRANT CONNECT ON DATABASE mytest to readonly; |
4. 切换到指定库mytest:
\c mytest |
5. 赋予用户表、序列查看权限,进入指定db运行:
5.1. 把当前库现有的所有在public这个schema下的表的使用权限赋给用户readonly
GRANT USAGE ON SCHEMA public to readonly; |
5.2. 默认把当前库之后新建在public这个schema下的表的使用权限赋给readonly
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; |
5.3. 赋予用户readonly所有public下的序列的查看权
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly; |
5.4. 赋予用户readonly所有public下的表的select权
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; |
二.删除
将刚才赋予的权限一一回收
1. 回收schema的usage权限
revoke USAGE ON SCHEMA public from readonly; |
2. 回收public下所有表的查询权限:
revoke SELECT ON ALL TABLES IN SCHEMA public from readonly; |
3. 回收public下所有序列的查询权限
revoke SELECT ON ALL SEQUENCES IN SCHEMA public from readonly; |
4. 回收默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public revoke SELECT ON TABLES from readonly; |
5. 关闭数据库连接权限
revoke CONNECT ON DATABASE foo from readonly; |
6. 关闭默认只读事务设置
alter user readonly set default_transaction_read_only=off; |
7. 查看权限是否为空了
\ddp |
8. 通过管理员删除readonly用户:
drop user readonly; |
最详细,附带PostgreSQL删除只读用户,及注释!