postgres创建只读用户

1.创建只读用户步骤

CREATE ROLE 用户名 WITH
  LOGIN
  NOSUPERUSER
  NOINHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  ENCRYPTED PASSWORD '密码';

2.添加注释 

COMMENT ON ROLE 用户名 IS '只读用户';

3.授予只读权限

GRANT select ON all tables in schema public TO 用户名;

4.为后续创建的表赋予相关权限，则必须使用 ALTER  ... GRANT ... 的语法授权

GRANT USAGE ON SCHEMA public TO 用户名;

5.每创建一个新表，则赋予对象查询的权限

ALTER DEFAULT PRIVILEGES for role postgres IN SCHEMA public
GRANT select ON TABLES TO 用户名;

6.新增一个角色，为只读

CREATE ROLE readonly;

-- grant access to all existing tables
GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;

-- grant access to all table which will be created in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

7.将用户名添加到readonly用户组

alter group readonly add user 用户名;

8.用户单独对某张表进行权限修改例如增加查看、插入、更新的权限

GRANT SELECT INSERT UPDATE ON tablename TO 用户名;