postgresql创建用户及分配权限

1、创建用户

CREATE ROLE readonlyuser WITH
  LOGIN
  NOSUPERUSER
  NOINHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  ENCRYPTED PASSWORD 'md5e032d3f6aa6d6f9b78febe19c3e7861e';

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

2、分配public模式下的所有已有表给用户readonlyuser

GRANT select ON all tables in schema public TO readonlyuser;

如果要分配单独一张表，可用:

GRANT SELECT ON TABLE mytable TO readonlyuser;

3、分配public模式下未来新建的表给用户readonlyuser

GRANT USAGE ON SCHEMA public TO readonlyuser;

ALTER DEFAULT PRIVILEGES for role postgres IN SCHEMA public
GRANT select ON TABLES TO readonlyuser;

4、删除用户

（1）首先回收权限（相应权限都要回收，这里列了一些）
revoke all on database "sptialData" from readonlyuser;
revoke all on all tables in schema public from readonlyuser;

revoke all ON SCHEMA public from readonlyuser;

回收默认权限：

alter DEFAULT PRIVILEGES for role postgres IN SCHEMA public
revoke all on tables from readonlyuser;

（2）删除用户

drop role readonlyuser;

5、查询角色

select * from pg_roles;
select * from information_schema.table_privileges where grantee like 'read%';

select * from pg_default_acl;

为了方便直观查看，用如下关联查询：

SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS "Granter",
  n.nspname AS "Schema",
  CASE d.defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'function' WHEN 'T' THEN 'type' END AS "Type",
  pg_catalog.array_to_string(d.defaclacl, E', ') AS "Access privileges"
FROM pg_catalog.pg_default_acl d
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace
ORDER BY 1, 2, 3;