PostgreSQL9.6创建只读用户

创建用户

• 创建用户(readonly)及指定密码(123456)

  CREATE USER readonly WITH ENCRYPTED PASSWORD '123456';
  

• 设置用户默认事务只读

  alter user readonly set default_transaction_read_only=on;
  

• 修改用户权限，查看public模式下的所有表

  GRANT USAGE ON SCHEMA public to readonly;
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
  

数据库赋权

• 赋予用户连接数据库(flow)权限

   GRANT CONNECT ON DATABASE flow to readonly;   
  

• 切换到指定数据库(flow)，赋予用户表，序列号查看权限

  \c flow;
  GRANT USAGE ON SCHEMA public to readonly;
  GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
  

测试

• 测试账号是否只读