PG中要实现查询一条数据需要至上而下赋予如下权限
（1）实例级别的(LOGIN)权限
（2）数据库的连接权限 (CONNECT)
（3）模式的使用权限 (USAGE)
（4）表本身的查看权限 (SELECT)
（5）这一行数据的行级策略 (row level security)以及是否有相应列的查询权限
数据库的权限
数据库的权限主要包括：
允许连接数据库
允许数据中创建schema
允许创建数据库和调整数据库
（1）数据库在创建后，允许public角色连接，即允许任何人连接。
postgres=# create user cjr password 'cjr';
CREATE ROLE
postgres=# create database cjrdb;
CREATE DATABASE
postgres=# \c cjrdb cjr
You are now connected to database "cjrdb" as user "cjr".
需要revoke connect on database xxx from public之后，
再显式执行grant connect on database xxx to xxx。
（2）允许数据中创建schema
默认情况下，数据库在创建后，不允许除了超级用户和owner之外的任何人在数据库中创建schema。
cjrdb=> create schema cjrsc;
ERROR: permission denied for database cjrdb
cjrdb的owner是postgres 切换到postgres用户后可以创建
cjrdb=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
cjrdb | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
mydb | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
qwer | testuser | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
testa | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
(8 rows)
cjrdb=> \c cjrdb postgres
You are now connected to database "cjrdb" as user "postgres".
cjrdb=# create schema cjrsc;
CREATE SCHEMA
（3）用户需要在超级用户授权的情况下才能进行数据库的创建和调整。
alter user testuser createdb;
schema的权限
允许查看schema中的对象
允许在schema中创建对象
（默认情况下，数据库在创建后，不允许除了超级用户和owner之外的任何人在数据库中创建schema）
（1）在schema中创建对象
schema.tablename的方式创建
cjrdb=# create table cjrsc.cjrtable1(id int);
CREATE TABLE
cjrdb=# \dn
List of schemas
Name | Owner
--------+----------
cjrsc | postgres
public | postgres
(2 rows)
直接创建
cjrdb=# create table cjrtable2(id int);
CREATE TABLE
cjrdb=# \dn
List of schemas
Name | Owner
--------+----------
cjrsc | postgres
public | postgres
(2 rows)
但是直接创建是创建到public的schema中
直接创建需要切换到该schema的路径下创建
cjrdb=# set search_path to cjrsc;
cjrdb=# create table cjrtable3(id int);
CREATE TABLE
cjrdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
cjrsc | cjrtable1 | table | postgres
cjrsc | cjrtable3 | table | postgres
（2）数据库创建后，有个默认的叫public的schema。
可以随意创建对象，创建的对象都在这个public的schema上。因此需要执行
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
这条命令中，我们移除了public模式中的CREATE权限。注意，我们使用了两个public，分别为一个大写和一个小写。小写的public指的是模式，实际使用中可以被替换为数据库中其他任意有效的模式名。而大写的PUBLIC是一个特殊的关键字，代表了all users。实际使用中可以被替换为一个特定的角色名或者以逗号分隔的角色名列表。
注意：15以前，任何人可以在database下 public 模式下建表5之后回收掉了。
进一步的锁定措施可能需要通过移除USAGE权限来拒绝对模式的查询访问。
REVOKE usage on schema public from PUBLIC;
默认情况下，数据库在创建后，会自动创建名为public 的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象。
（3）创建一个他人拥有的模式
默认情况下新建的schema的权限不会赋予给public角色，因此除了超级用户和owner，任何人都没有权限查看schema中的对象或者在schema中新建对象。
可以给schema指定一个owner，如果没有指定，那么当前用户就是schema的默认owner。
也可以通过这种方式指定
CREATE SCHEMA schemaname AUTHORIZATION otherownername;
（4）删除schema
mydb=# drop schema test;
ERROR: cannot drop schema test because other objects depend on it
DETAIL: table testtable1 depends on schema test
HINT: Use DROP ... CASCADE to drop the dependent objects too.
mydb=# drop schema test cascade;
NOTICE: drop cascades to table testtable1
DROP SCHEMA
表的权限
a -- INSERT ("插入，针对表或视图")
r -- SELECT ("查询，针对表或视图")
w -- UPDATE ("更新，针对表或视图")
d -- DELETE("删除，针对表或视图")
D – TRUNCATE("清空记录，针对表")
x – REFERENCES("参照引用，针对表")
t -- TRIGGER("创建触发器，针对表")
X -- EXECUTE("执行，针对存储过程等pl/sql对象")
U -- USAGE("使用权，针对模式")
C -- CREATE("创建对象，针对模式或者数据库")
c -- CONNECT("创建连接，针对数据库")
T -- TEMPORARY("创建临时表，针对数据库")
角色
删除角色通常不仅仅是快速DROP ROLE的问题。角色拥有的任何对象都必须首先删除或重新分配给其他所有者；删除已用于拥有对象的角色的最一般方法是，先转移"资产"：
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
DROP ROLE doomed_role;
常用的SQL
查看某个用户拥有的对象
select nsp.nspname as SchemaName ,cls.relname as ObjectName ,rol.rolname as ObjectOwner ,case cls.relkind when 'r' then 'TABLE' when 'm' then 'MATERIALIZED_VIEW' when 'i' then 'INDEX' when 'S' then 'SEQUNCE' when 'v' then 'VIEW' when 'c' then 'TYPE' else cls.relkind::text end as ObjectTypefrom pg_class clsjoin pg_roles rol on rol.oid = cls.relownerjoin pg_namespace nsp on nsp.oid = cls.relnamespacewhere nsp.nspname not in ('information_schema', 'pg_catalog') and nsp.nspname not like 'pg_toast%' and rol.rolname = 'postgres' order by nsp.nspname, cls.relname;
查看被授予的角色
SELECT r.rolname, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberofFROM pg_catalog.pg_roles rWHERE r.rolname NOT IN ('pg_signal_backend','rds_iam', 'rds_replication','rds_superuser', 'rdsadmin','rdsrepladmin')ORDER BY 1;
权限管理重点
（1）数据库在创建后，允许public角色连接，即允许任何人连接，
需要revoke connect on database xxx from public之后，
再显式执行grant connect on database xxx to xxx;
（2）数据库在创建后，会自动创建名为public的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象，所以为了方便安全，先revoke all。
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;
（3）数据库创建后，有个默认的叫public的schema。可以随意创建对象，创建的对象都在这个public的schema上。因此需要执行
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
进一步的锁定措施可能需要通过移除USAGE权限来拒绝对模式的查询访问。
REVOKE usage ON SCHEMA public FROM PUBLIC;
一个完整的权限赋予流程
通用前提操作
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;
只读角色
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
赋予所有该模式下未来新建的表的只读权限
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;
读写角色
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;
用户创建以及赋予角色权限
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;
