Postgres 用户、角色与权限
目录
用户与角色
PostgreSQL使用角色的概念管理数据库访问权限。为了简化管理用户与角色这两者的概念几乎没有区别。
用户和角色在整个数据库实例中是全局的,在同一个实例中的不同数据库中,看到的用户都是相同的。
在初始化数据库系统时有一个预定义的超级用户,一般来说这个用户的名称与初始化该数据库的操作系统用户名相同,如果数据库是建在操作系统用户postgres
下的,那么这个数据库超级用户名称也叫postgres
。
创建用户与角色
创建角色
CREATE ROLE name [ [with] option [...]]
创建用户
CREATE USER name [ [with] option [...]]
CREATE USER
创建出来的用户有LOGIN 权限,而CREATE ROLE
创建出来的用户没有LOGIN 权限。
参数 | 说明 |
---|---|
SUPERUSER NOSUPERUSER |
创建出来的用户是否为超级用户 |
CREATEDB NOCREATEDB |
是否有执行 create database 权限 |
CREATEROLE NOCREATEROLE |
是否具有创建其他角色权限 |
CREATEUSER NOCREATEUSER |
是否具有创建其他用户权限 |
INHERIT NOINHERIT |
如果创建的用户拥有某几个角色,指定INHERIT 表示用户自动用户相应的角色 |
LOGIN NOLOGIN |
是否具有LOGIN 登录权限 |
CONNECTION LIMIT 200 |
指定用户可以使用并发连接的数量,-1表示无限制 |
[ENCRYPTED /UNENCRYPTED] PASSWORD 'pass' |
存储在系统表中的口令是否加密 |
VALID UNTIL 'timestamp |
密码失效时间、不指定则永久有效 |
IN ROLE role_name |
为用户添加角色。注意:没有任何选项能把新角色添加为管理员,只能独立使用GRANT 命令才行。 |
ROLE role_name[,...] |
role_name 将成为这个新建角色的成员 |
ADMIN role_name[,...] |
role_name 将有这个新建角色的 WITH ADMIN OPTION 权限 |
权限的管理
用户的权限分为两类,一类是在创建用户时就指定的权限,一类是由GRANT
命令和REVOKE
命令来管理的。
- 用户创建时就指定的权限
- 超级用户的权限
- 创建数据库的权限
- 是否允许LOGIN的权限
GRANT
REVOKE
命令管理的权限- 在数据库中创建模式(SCHEMA)
- 允许在指定的数据库中创建临时表
- 连接某个数据库的权限
- 在模式中创建数据库对象的权限,如创建表、视图、函数等
- 在一些表中做
SELECT
UPDATE
INSERT
DELETE
等操作权限 - 在一张具体表中做
SELECT
UPDATE
INSERT
操作权限 - 对序列进行查询(执行序列的
currval
函数)、使用、更新的权限 - 把表、索引等建到指定表空间的权限。
如果给用户赋予创建数据库的权限,需要使用
ALTER ROLE
命令,给用户赋予创建模式的权限时,需要使用GRANT
命令
ALTER ROLE name [ [with] option...]
中的 option
与 CREATE ROLE
的 option
相同。
-
GRANT
与REVOKE
列子GRANT some_privileges ON databaes_object_type object_name TO role_name;
REVOKE some_privileges ON database_object_type object_name FROM role_name;
role_name
是具体的用户名或角色名,如public 表示所有用户,列子:GRANT select ON TABLE books to public;
将查询表books
的权限赋予所有用户。some_privileges
表示在该数据库对象中的权限,data_object_type
是数据库对象的类型,如TABLE
SEQUENCE
SCHEMA
等等。
some_privileges
参数说明列表:
权限名称 | 权限说明(简要) |
---|---|
SELECT |
对于表和视图来说,表示允许查询表或视图,如果限制了列,则允许查询特殊的列。 |
INSERT |
允许想特定表中插入行 |
UPDATE |
对于表来说,如果没有指定特定的列,则表示允许更新表中的任意列的数据 |
DELETE |
允许删除表中的数据 |
TRUNCATE |
允许在指定的表上执行TRUNCATE 操作 |
REFERENCES |
为了创建外键约束,有必要使用参照列和被参照列都有改权限。可以将其授予一个表的所有列或者仅仅是特定列 |
TRIGGER |
允许在指定的表上创建触发器 |
CREATE |
对于数据库,表示允许在该库中创建新的模式(SCHEMA)。对于模式来说,有了该权限就可以在模式中创建各种数据库对象了,如表、索引、视图、函数等。 |
CONNECT |
表示允许用户连接到指定的数据库。(blog.6 8 hub. com) |
TEMPORARY TEMP |
允许在使用指定数据库的时候创建临时表 |
EXECUTE |
允许使用指定的函数,并且可以使用利用这些函数实现的所有操作符。同样适用于聚合函数 |
USAGE |
对于模式来说,表示允许被授权者查找模式中的对象。对于序列,表示允许使用currval 和nextval 函数 |
ALL PRIVILEGES |
一次性给予所有可以赋予的权限。 |
示例
-
创建一个只读用户
- 首先需要执行
REVOKE CREATE ON SCHEMA public from public;
这是因为在PostgreSQL中默认任何用户都可以在名为public
的Schema中创建表,而只读用户是不允许创建表的,所有要先收回此权限。 - 创建一个名为
readonly
用户CREATE USER readonly with password '123';
然后将public下现有的所有表的SELECT
权限赋予用户readonly
并执行下面命令 GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
此语句只是将现有表赋予了用户,新建的表还不行- 如果不是
public
的SCHEMA(模式)还需要使用GRANT USAGE ON SCHEMA other_schema to readonly;
默认public
模式是对所有角色开放查找权限! ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to readonly;
这条语句只是给名为 public的SCHEMA下的表赋予了只读权限。- 如果该用户访问其他Schema下的表,需要重复执行以下SQL:
GRANT SELECT ON ALL TABLES IN SCHEMA other_schema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA other_schema grant select on tables to readonly;
- 首先需要执行
-
创建一个具有创建数据库权限的
read_user
用户,密码为read_pass
,且并发数量限制在100create USER read_user with CREATEDB CONNECTION LIMIT 100 PASSWORD 'read_pass';
-
创建一个具有创建数据库,更新数据,查询数据的用户
update_user
create USER update_user with CREATEDB PASSWORD 'update_pass;
- 给
update_user
增加并发限制ALTER ROLE update_user with CONNECTION LIMIT 200;
-
给
update_user
增加SCHEMA(模式):update_schema
下的所有权限GRANT ALL ON ALL TABLES IN SCHEMA update_schema TO update_user;
-
给
read_user
增加update_schema
下SELECT权限GRANT SELECT ON ALL TABLES IN SCHEMA read_schema TO read_user;
-
(超级管理员)修改用户密码
ALTER ROLE read_user with password 'new_pass';
-
给
update_user
增加在test_schma(模式)下的所有权限GRANT ALL ON SCHEMA test_schma TO update_user;
-
给
read_user
增加test_schma(模式)下的SELECT权限先给
read_user
增加在test_schma(模式)下的USAGE
权限GRANT USAGE ON SCHEMA other_schema to read_user;
赋SELECT
权限GRANT SELECT ON ALL TABLES IN SCHEMA test_schma TO read_user;
-
将
68hubc.com_db
数据库所有者变成user_68
ALTER DATABASE 68hub.com_db OWNER TO user_68;
-
将一个数据库所有权限赋予user
还需要将pg_hba.conf文件新增一行访问配置
\c mydb GRANT CONNECT ON DATABASE mydb TO user; GRANT USAGE ON SCHEMA public TO user; GRANT ALL ON ALL TABLES IN SCHEMA public TO user GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO user
总结
- 首先管理赋在用户特殊属性上的权限,如超级用户、创建数据库、创建用户等权限、LOGIN权限等。
- 然后是在数据库中创建模式的权限。
- 接着是在模式中创建数据库对象的权限,如创建表。索引等。
- 之后是查询表、向表中插入数据、更新表、删除表中数据的权限。
- 最后是操作表中某些字段的权限。