pg使用角色的概念管理数据库访问权限,角色是一系列相关权限的集合。为了管理方便,通常把一系列先关的权限赋予给一个角色,如果哪个用户需要这些权限,就把这些角色赋予给响应的用户。 由于用户也拥有一系列的相关权限,为了简化管理,在PG中,角色与用户是没有区别的,一个用户也是一个角色,我们可以把一个用户的权限赋值给另一个用户。
用户和角色在整个数据库实例中是全局的,在同一个实例的不同数据库中,看到的用户都是相同的。
数据库初始化时会创建一个与操作系统同名的超级用户postgres,也可以使用-U:指定超级用户
创建用户和角色的语法如下:
-- 创建用户 CREATE USER name [[ WITH ] option [...]] -- 创建角色 CREATE ROLE name [[ WITH ] option [...]]
备注:在PG中,用户与角色是没有区别的,角色默认没有login权限,无法登陆,如果授予login之后,也可以像用户一样登陆。
option常用选项如下:
在postgresql数据库中,任何逻辑对象(包括数据库)都是有所有者的,也就是说数据库对象都是属于某个用户的,所以,无需把对象的权限赋予所有者,因为所有者默认就拥有所有的权限,在PG数据库中,删除及其修改对象的权限都不能赋予别的用户,它是所有者的固有权限,不能赋予或撤销,所有者也隐式地拥有把操作该对象的权限授予其他用户的权利。
用户的权限分两类,一类是在创建用户时就指定的权限,有:
\help create role
这些权限是创建用户时指定的,后面可以使用alter role来修改。
另一类是有GRANT和REVOKE命令来管理的,有:
在数据库中创建schema的权限
在指定的数据库中创建临时表的权限
连接某个数据库的权限
在某个数据库中创建数据库对象的权限,如表、视图、函数等
在一些表中做SELECT 、INSERT、UPDATE、DELETE等操作的权限
在一张表的列上做 SELECT 、UPDATE、DELETE等操作的权限
对序列进行查询(执行序列的currval函数)、使用(执行序列的currval和nextval函数)、更新的权限
在表上创建触发器的权限
把表、索引创建到指定表空间的权限
-- 将角色授予给另外一个角色 GRANT role_name[,...] TO role_name[,...] WITH ADMIN OPTION; -- 角色回收 REVOKE role_name[,...] FROM role_name[,...][CASCADE | RESTRICT]
权限的授予
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 模式名称 [, ...] }
postgresql中的权限是按照以下几个层次进行管理的:
1.首先管理赋予用户的特殊权限,如超级用户的权限,创建数据库的权限、创建用户的权限、login权限等
2.然后是在数据库中创建schema的权限
3.接着是在schema中创建数据库对象(如表、索引)的权限
4.之后是对表进行操作(insert、update、delete、select)的权限
5.最后是操作(update、delete、select)表中某些字段的权限
GRANT update,delete,insert,select ON ALL TABLES IN SCHEMA public TO signalmap_rw;
在初始化数据库实例后,数据库中默认就会存在一个名称为“public”的schema,任何用户都有在public schema上的create 权限,通常我们需要把这个权限回收回来
revoke create on schema public from public;
-- 回收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;
-- 查看某用户的系统权限 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';