postgresql 用户权限相关

用户权限相关
database -> schema -> table 分层关系
每个schema总是属于一个数据库的，schema对象: 表、视图、函数，mysql中的database对应的是pgsql中的schema，所以pg中没有对数据库的
pg没有create table这种DDL权限控制，只有对schema的create权限，但是数据库默认会有public的schema，拥有create权限，所以如果不收回任何用户在public schema下都有create权限，一般是要revoke create on schema public form public
每个数据库都有个默认的public schema， schema是相对数据库来说的，schema下又有很多表，默认创建的表是在public 下
可以同时给数据库下某个schema下的所有表赋权，但是不能给库下所有schema的下的所有表赋权
权限分层
管理用户特殊属性的权限，如超级管理员，创建数据库，创建用户，login的权限
数据库中创建模式的权限
模式中创建数据库对象的权限，如创建表，创建索引
查询表，DML等权限
操作表中某些字段的权限
权限分为两类
创建用户就指定的权限，修改此权限需要用alter role的方式来修改: alter role role_name option(具体的权限)
超级用户的权限
创建数据库的权限
是否允许login的权限
剩余的权限是用grant和revoke来管理的： grant xxx on table to role_name
数据库中创建模式(schema)
允许在指定的数据库中创建临时表
连接某个数据库
在模式中创建数据库对象，如创建表，视图，函数
在表中做DML或查询
在表的具体列上做DML
对序列进行查询
在声明表上创建触发器
可以把表索引等建到指定的表空间
创建用户和角色: pg用户user和角色role一样，只是创建user默认会有login的权限，role默认没有，其他都一样
create role/user name option(具体权限)
superuser|nosuperuser:是否是超级用户，只有超级用户才能创建超级用户
createdb|nocreatedb: 是否有create database的权限
createrole|nocreaterole: 是否有创建其他角色的权限
createuser|nocreateuser: 是否有创建其他用户的权限
inherit|noinherit: 如果创建的用户又某个角色的权限，若指定inherit，则表示用户自动拥有相应角色的权限
login|nologin: 是否有login的权限，可以临时禁止用户的login的权限
connection limit connlimit:指定该用户可以使用的并发连接数量，-1表示不限制
[ENCRYPTED|UNENCRYPTED] PASSWORD ‘XXX’: 控制存储在系统表里的密码是否加密
valid until ‘timestamp’: 密码失效时间，不指定永远有效
in role role_name: 指定用户成为哪些角色的成员，不能把新角色添加未管理员，必须用grant命令来做
role role_name: role_name将成为这个新建角色的成员
admin role_name: role_name将有这个新建角色的with admin option权限
eg: create user replicator replication login encrypted password ‘replicator’;
eg2: create user user_name with priv password ‘xxx’
eg3: create role role_name with priv password ‘xxxx’ in role role_name1 相当于用了grant role_name1 to role_name
修改用户和角色权限: alter role role_name option(与上面个创建时的option一样) 修改创建数据库时候的权限
eg修改密码: alter role role_name with password ‘xxx’
eg修改权限: alter role role_name with priv password ‘xxx’
删除用户: drop user user_name
grant权限 grant 具体权限 on table|sequence|database|foreign data wrapper|foreign server|function|language|large object|schema [xxx_name] to [role_name]
grant两个作用 1.让某个用户拥有某个角色的权限 2.让某个用户拥有操作哪些对象(数据库/table/schema/…)的权限
具体权限包含 多个权限逗号隔开
SELECT|INSERT|UPDATE|DELETE|TRUNCATE|CREATE|ALL PRIVILEGES|USAGE
EXECUTE: 函数上的唯一权限
CONNECT: 允许连接到指定的数据库
TRIGGET: 允许指定的表上创建触发器
REFERENCES: 创建外键约束，将该权限赋给一个表的所有列或者特定列
eg: grant all on database db_name to role_name 操作对象的权限
eg: grant select(字段名字) on table table_name to role_name select table_name表中某个字段的权限
eg: grant role_name to new_role_name with xxx 操作用户 前提是先创建用户
eg: grant select on all tables in sechma public to user_name 把public模式下所有表的select权限赋给user_name，只是存量表，增量的需要用下面的sql
alter default privileges in schema public grant select on tables to user_name public下新建的增量表也会授权
revoke权限 revoke 权限 on table/database… table_name from role_name
查看用户权限
查看所有账号: select *from pg_user; select *from pg_roles; \du+ 可以查看member of(属于哪个账号) 和description
查某个账号有哪些权限 下面sql只能查询这个账号在当前库下有哪些表的权限，不能查其他库中表中的权限
select * from information_schema.table_privileges where grantee=‘pgsql’; 只有grant表权限的账号才会有
如果没有代表这个账号没有这个库下所有表的权限
\z table_name: table上有哪些用户权限 等于 select relacl from pg_class where relname=‘table_name’;
r – SELECT (“read”)
w – UPDATE (“write”)
a – INSERT (“append”)
d – DELETE
D – TRUNCATE
x – REFERENCES
t – TRIGGER
X – EXECUTE
U – USAGE
C – CREATE
c – CONNECT
T – TEMPORARY
arwdDxt – ALL PRIVILEGES (for tables, varies for other objects)
– grant option for preceding privilege
查看账号有哪些数据库的权限
select datname from pg_catalog.pg_database where array_to_string(datacl,’,’) like ‘%pgsql%’;

原文链接：https://blog.csdn.net/zhangjikuan/article/details/88670871