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';
原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/tech/database/281227.html