PostgreSQL创建只读用户,带步骤和删除

3

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删除只读用户,及注释!
————————————————
版权声明:本文为CSDN博主「qq_37568918」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_37568918/article/details/85339277

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.