postgresql用户与权限管理

pg使用角色的概念管理数据库访问权限，角色是一系列相关权限的集合。为了管理方便，通常把一系列先关的权限赋予给一个角色，如果哪个用户需要这些权限，就把这些角色赋予给响应的用户。 由于用户也拥有一系列的相关权限，为了简化管理，在PG中，角色与用户是没有区别的，一个用户也是一个角色，我们可以把一个用户的权限赋值给另一个用户。

用户和角色在整个数据库实例中是全局的，在同一个实例的不同数据库中，看到的用户都是相同的。

数据库初始化时会创建一个与操作系统同名的超级用户postgres，也可以使用-U:指定超级用户

(一)创建用户和角色

创建用户和角色的语法如下：

-- 创建用户
CREATE USER name [[ WITH ] option [...]]
 
-- 创建角色
CREATE ROLE name [[ WITH ] option [...]]

备注：在PG中，用户与角色是没有区别的，角色默认没有login权限，无法登陆，如果授予login之后，也可以像用户一样登陆。

option常用选项如下：

• SUPERUSER | NOSUPERUSER：创建出来的用户是否为超级用户
• CREATEDB | NOCREATEDB：创建出来的用户是否有create database的权限
• CREATEROLE | NOCREATEROLE：创建出来的用户是否有创建其它角色的权限
• CREATEUSER | NOCREATEUSER：创建出来的用户是否有创建其它用户的权限
• INHERIT | NOINHERIT：确定角色是否继承其它角色的权限
• LOGIN | NOLOGIN：创建出来的角色是否有登录权限
• CONNECTION LIMIT n：创建出来的角色并发连接数限制数量，默认值是“-1”,表示没有限制
• VALID UNTIL 'timestamp'：密码失效时间

(二)权限的管理

在postgresql数据库中，任何逻辑对象(包括数据库)都是有所有者的，也就是说数据库对象都是属于某个用户的，所以，无需把对象的权限赋予所有者，因为所有者默认就拥有所有的权限，在PG数据库中，删除及其修改对象的权限都不能赋予别的用户，它是所有者的固有权限，不能赋予或撤销，所有者也隐式地拥有把操作该对象的权限授予其他用户的权利。

(2.1)两类权限

用户的权限分两类，一类是在创建用户时就指定的权限，有：

• 超级用户的权限
• 创建数据库的权限
• 是否允许login的权限
• 更多见\help create role

这些权限是创建用户时指定的，后面可以使用alter role来修改。

另一类是有GRANT和REVOKE命令来管理的，有：

• 在数据库中创建schema的权限

• 在指定的数据库中创建临时表的权限

• 连接某个数据库的权限

• 在某个数据库中创建数据库对象的权限，如表、视图、函数等

• 在一些表中做SELECT 、INSERT、UPDATE、DELETE等操作的权限

• 在一张表的列上做 SELECT 、UPDATE、DELETE等操作的权限

• 对序列进行查询(执行序列的currval函数)、使用(执行序列的currval和nextval函数)、更新的权限

• 在表上创建触发器的权限

• 把表、索引创建到指定表空间的权限

(2.2)GRANT 和REVOKE权限

(2.2.1)角色的授予与回收

-- 将角色授予给另外一个角色
GRANT role_name[,...] TO role_name[,...] WITH ADMIN OPTION;
 
-- 角色回收
REVOKE role_name[,...] FROM role_name[,...][CASCADE | RESTRICT]

(2.2.2)权限的授予与回收

权限的授予

db1=# \help grant
命令：       GRANT
描述：       定义存取权限
语法：
-- 表相关权限
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] 表名 [, ...]
         | ALL TABLES IN SCHEMA 模式名称 [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 列相关权限
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( 列名称 [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( 列名称 [, ...] ) }
    ON [ TABLE ] 表名 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 序列相关权限
GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE 序列名称 [, ...]
         | ALL SEQUENCES IN SCHEMA 模式名称 [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 数据库相关权限
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE 数据库名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN 域_名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER 外部数据封装器的名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER 服务器名称 [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 函数、存储过程相关权限
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } 程序名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...
] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA 模式名称 [, ...] }

(2.2.3)权限授予回收总结

postgresql中的权限是按照以下几个层次进行管理的：

1.首先管理赋予用户的特殊权限，如超级用户的权限，创建数据库的权限、创建用户的权限、login权限等

2.然后是在数据库中创建schema的权限

3.接着是在schema中创建数据库对象(如表、索引)的权限

4.之后是对表进行操作(insert、update、delete、select)的权限

5.最后是操作(update、delete、select)表中某些字段的权限

(2.3)权限操作常用命令

(2.3.1)授予增删查改权限

GRANT update,delete,insert,select ON ALL TABLES IN SCHEMA public TO signalmap_rw;

(2.3.2)回收public权限

在初始化数据库实例后，数据库中默认就会存在一个名称为“public”的schema，任何用户都有在public schema上的create 权限，通常我们需要把这个权限回收回来

revoke create on schema public from public;

(2.3.3)创建只读用户

-- 回收public模式的create权限
revoke create on schema public from public;
-- 创建只读用户，密码为readonly
create user readonly password 'readonly';
-- 授权public模式给readonly
grant usage on schema public to readonly;
-- 授权public模式的所有表权限给readonly用户
grant select on all tables in schema public to readonly;
-- 切换到readonly测试
\c - readonly 
-- 切换到postgres用户
\c - postgres
-- 将public模式的默认表查询权限授予readonly用户
alter default privileges in schema public grant select on tables to readonly;

(2.3.4)查看用户权限

-- 查看某用户的系统权限
SELECT * FROM  pg_roles WHERE rolname='postgres';
 
-- 查看某用户的表级别权限
select * from information_schema.table_privileges where grantee='postgres';
 
-- 查看某用户的usage权限
select * from information_schema.usage_privileges where grantee='postgres';
 
-- 查看某用户在存储过程函数的执行权限
select * from information_schema.routine_privileges where grantee='postgres'; 
 
-- 查看某用户在某表的列上的权限
select * from information_schema.column_privileges where grantee='postgres'; 
 
-- 查看当前用户能够访问的数据类型
select * from information_schema.data_type_privileges ; 
 
-- 查看用户自定义类型上授予的USAGE权限
select * from information_schema.udt_privileges where grantee='postgres';