创建只读用户

• 创建用户

  -- 创建名为readonly，密码为123456的用户
  CREATE USER readonly WITH ENCRYPTED PASSWORD '123456';
  

• 设置回话与事务模式只读

  -- 后续登录的会话或者当前事务设置为只读模式
  ALTER USER readonly SET default_transaction_read_only = ON;
  

• 允许用户用于登录

  ALTER USER readonly WITH login;
  

• 将Schema授权给用户

  GRANT usage ON SCHEMA postgres TO readonly;
  

• 将schema下所有的表的查询权限授予用户

  GRANT SELECT ON ALL tables IN SCHEMA postgres TO readonly;
  

• 自动授予未来新创建表的查询权限

  ALTER DEFAULT privileges IN SCHEMA postgres GRANT SELECT ON tables TO readonly;
  

其他操作

• 将某个数据库的全部权限授予用户

  grant all privileges on database animalinsurance to readonly
  

• 将整个数据库的增删改查权限授予用户

  grant  select, insert, update, delete on database animalinsurance to readonly