文章摘要
角色与权限管理是PostgreSQL中非常重要的功能，用于控制用户权限和角色关系。以下是关于角色和权限管理的总结： --- ### 1. 角色与权限的基本概念- **角色（Role）**：表示系统中的一个用户或用户组，可以拥有不同的权限。- **权限（Permission）**：指角色可以执行的操作，如查看、删除、修改、复制等。- **登录权限（Login）**：允许用户使用指定的用户名和密码登录到数据库。- **查看权限（View）**：允许用户查看数据但不修改数据。- **修改权限（Modify）**：允许用户对数据进行插入、删除、更新等操作。- **超级用户（Superuser）**：拥有所有权限的角色。 --- ### 2. 创建角色在PostgreSQL中，使用`CREATE ROLE`命令创建角色。常见的选项包括：- `username`：角色的用户名。- `password`：角色的密码。- `host`：角色绑定的主机。- `re Rolname`：角色的名称。- `members`：将角色加入到指定的组中。 示例：```sqlCREATE ROLE user1 Login NOT SUPERUSER, NOTREPLACE;CREATE ROLE user2 CREATEDB, CREATE URep, NOT SUPER, NOTREPLACE;``` --- ### 3. 修改角色使用`ALTER ROLE`命令修改角色属性。可配置的属性包括：- `username`：修改用户名。- `members`：重新指定组成员。- `role name`：修改角色名称。 示例：```sqlALTER ROLE user1 LOGIN SET PASSWORD='newpass';ALTER ROLE user1 ADD TO group1;ALTER ROLE user1 RENAME TO user2;``` --- ### 4. 删除角色使用`DROP ROLE`命令删除角色。注意：删除操作可能会影响其他角色的配置，因此需谨慎操作。 示例：```sqlDROP ROLE user1;``` --- ### 5. 赋予权限可以通过插入到角色的`members`列表中来赋予权限。允许的权限包括：- `superuser`：赋予超级用户权限。- `create db`：允许创建新数据库。- `create table`：允许创建新表。 示例：```sqlALTER ROLE user2 ADD TO superuser, create db;ALTER ROLE user2 ADD TO create table;``` --- ### 6. 角色组与权限管理为了更高效地管理权限，可以使用角色组。角色组允许将多个角色集中管理，并为整个组分配权限。 #### 6.1 创建角色组使用`CREATE ROLE`命令，并在`members`字段中指定组名。 示例：```sqlCREATE ROLE group1 Login NOT SUPER, NOTREPLACE;ALTER ROLE group1 ADD TO group2;``` #### 6.2 将角色加入组使用`GRANT`命令将角色加入到组中。 示例：```sqlGRANT group1 TO user1;GRANT group1 TO user2;``` #### 6.3 设置组权限通过修改角色的`members`列表，可以为组分配权限。 示例：```sqlALTER ROLE group1 SET members = (SELECT * FROM pg roles WHERE name = 'group1');ALTER ROLE group1 SET permissions = 'superuser, create db';``` --- ### 7. 操作与权限的关系PostgreSQL中，权限是通过角色和组来实现的。每个操作（如查看、删除、修改）都有一个或多个默认的权限，且只能由拥有相应的权限的角色执行。如果需要为特定操作设置权限，可以通过修改角色或组的`members`列表来实现。 --- ### 8. 常见问题- **权限冲突**：当多个角色拥有相同的权限时，可能引起权限冲突。解决方案是通过角色组管理权限。- **角色成员的权限变更**：可以通过`ALTER ROLE`命令修改成员列表，从而调整权限。 --- ### 9. 总结角色和权限管理是PostgreSQL中实现系统安全性和数据安全的关键。通过合理地定义角色、分配权限，并使用角色组来集中管理权限，可以有效地控制用户权限，确保数据安全。
一、角色与用户的区别
角色就相当于岗位：角色可以是经理，助理。
用户就是具体的人：比如陈XX经理，朱XX助理，王XX助理。
在PostgreSQL 里没有区分用户和角色的概念，”CREATE USER” 为 “CREATE ROLE” 的别名，这两个命令几乎是完全相同的，唯一的区别是”CREATE USER” 命令创建的用户默认带有LOGIN属性，而”CREATE ROLE” 命令创建的用户默认不带LOGIN属性(CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not)。
1.1 创建角色与用户
CREATE ROLE 语法
CREATE ROLE name [ [ WITH ] option [ … ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password’
| VALID UNTIL ‘timestamp’
| IN ROLE role_name [, …]
| IN GROUP role_name [, …]
| ROLE role_name [, …]
| ADMIN role_name [, …]
| USER role_name [, …]
| SYSID uid
创建david 角色和sandy 用户
postgres=# CREATE ROLE david; //默认不带LOGIN属性
CREATE ROLE
postgres=# CREATE USER sandy; //默认具有LOGIN属性
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
david | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=#
postgres=# SELECT rolname from pg_roles ;
rolname
———-
postgres
david
sandy
(3 rows)
postgres=# SELECT usename from pg_user; //角色david 创建时没有分配login权限，所以没有创建用户
usename
———-
postgres
sandy
(2 rows)
postgres=#
1.2 验证LOGIN属性
postgres@CS-DEV:~> psql -U david
psql: FATAL: role “david” is not permitted to log in
postgres@CS-DEV:~> psql -U sandy
psql: FATAL: database “sandy” does not exist
postgres@CS-DEV:~> psql -U sandy -d postgres
psql (9.1.0)
Type “help” for help.
postgres=> \dt
No relations found.
postgres=>
用户sandy 可以登录，角色david 不可以登录。
1.3 修改david 的权限，增加LOGIN权限
postgres=# ALTER ROLE david LOGIN ;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=# SELECT rolname from pg_roles ;
rolname
———-
postgres
sandy
david
(3 rows)
postgres=# SELECT usename from pg_user; //给david 角色分配login权限，系统将自动创建同名用户david
usename
———-
postgres
sandy
david
(3 rows)
postgres=#
1.4 再次验证LOGIN属性
postgres@CS-DEV:~> psql -U david -d postgres
psql (9.1.0)
Type “help” for help.
postgres=> \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=>
david 现在也可以登录了。
二、查看角色信息
psql 终端可以用\du 或\du+ 查看，也可以查看系统表 select * from pg_roles;
postgres=> \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
david | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=> \du+
List of roles
Role name | Attributes | Member of | Description
———–+————————————————+———–+————-
david | Cannot login | {} |
postgres | Superuser, Create role, Create DB, Replication | {} |
sandy | | {} |
postgres=> SELECT * from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid
———-+———-+————+—————+————-+————–+————-+—————-+————–+————-+—————+———–+——-
postgres | t | t | t | t | t | t | t | -1 | ******** | | | 10
david | f | t | f | f | f | f | f | -1 | ******** | | | 49438
sandy | f | t | f | f | f | t | f | -1 | ******** | | | 49439
(3 rows)
postgres=>
三、角色属性（Role Attributes）
一个数据库角色可以有一系列属性，这些属性定义了他的权限。
属性
说明
login
只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。
superuser
数据库超级用户
createdb
创建数据库权限
createrole
允许其创建或删除其他普通的用户角色(超级用户除外)
replication
做流复制的时候用到的一个用户属性，一般单独设定。
password
在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关
inherit
用户组对组员的一个继承标志，成员可以继承用户组的权限特性
…
…
四、创建用户时赋予角色属性
从pg_roles 表里查看到的信息，在上面创建的david 用户时，默认没有创建数据库等权限。
postgres@CS-DEV:~> psql -U david -d postgres
psql (9.1.0)
Type “help” for help.
postgres=> \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=> CREATE DATABASE test;
ERROR: permission denied to create database
postgres=>
如果要在创建角色时就赋予角色一些属性，可以使用下面的方法。
首先切换到postgres 用户。
4.1 创建角色bella 并赋予其CREATEDB 的权限。
postgres=# CREATE ROLE bella CREATEDB ;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=#
4.2 创建角色renee 并赋予其创建数据库及带有密码登录的属性。
postgres=# CREATE ROLE renee CREATEDB PASSWORD ‘abc123’ LOGIN;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}
postgres=#
4.3 测试renee 角色
a. 登录
postgres@CS-DEV:~> psql -U renee -d postgres
psql (9.1.0)
Type “help” for help.
postgres=>
用renee 用户登录数据库，发现不需要输入密码既可登录，不符合实际情况。
b. 查找原因
在角色属性中关于password的说明，在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关。
查看pg_hba.conf 文件，发现local 的METHOD 为trust，所以不需要输入密码。
将local 的METHOD 更改为password，然后保存重启postgresql。
c. 再次验证
提示输入密码，输入正确密码后进入到数据库。
d. 测试创建数据库
创建成功。
五、给已存在用户赋予各种权限
使用ALTER ROLE 命令。
ALTER ROLE 语法：
ALTER ROLE name [ [ WITH ] option [ … ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password’
| VALID UNTIL ‘timestamp’
ALTER ROLE name RENAME TO new_name
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO |=} { value | DEFAULT }
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE name [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE name [ IN DATABASE database_name ] RESET ALL
5.1 赋予bella 登录权限
a. 查看现在的角色属性
postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}
postgres=#
b. 赋予登录权限
postgres=# ALTER ROLE bella WITH LOGIN;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}
postgres=#
5.2 赋予renee 创建角色的权限
postgres=# ALTER ROLE renee WITH CREATEROLE;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
postgres=#
5.3 赋予david 带密码登录权限
postgres=# ALTER ROLE david WITH PASSWORD ‘ufo456’;
ALTER ROLE
postgres=#
5.4 设置sandy 角色的有效期
postgres=# ALTER ROLE sandy VALID UNTIL ‘2014-04-24’;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
postgres=# SELECT * from pg_roles ;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid
———-+———-+————+—————+————-+————–+————-+—————-+————–+————-+————————+———–+——-
postgres | t | t | t | t | t | t | t | -1 | ******** | | | 10
bella | f | t | f | t | f | t | f | -1 | ******** | | | 49440
renee | f | t | t | t | f | t | f | -1 | ******** | | | 49442
david | f | t | f | f | f | t | f | -1 | ******** | | | 49438
sandy | f | t | f | f | f | t | f | -1 | ******** | 2014-04-24 00:00:00+08 | | 49439
(5 rows)
postgres=#
六、角色赋权/角色成员
在系统的角色管理中，通常会把多个角色赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤销。在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership 权限赋给独立的角色即可。
6.1 创建组角色
postgres=# CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password ‘abc123’;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
bella | Create DB | {}
david | | {}
father | No inheritance | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
postgres=#
6.2 给father 角色赋予数据库test 连接权限和相关表的查询权限。
postgres=# GRANT CONNECT ON DATABASE test to father;
GRANT
postgres=# \c test renee
You are now connected to database “test” as user “renee”.
test=> \dt
No relations found.
test=> CREATE TABLE emp (
test(> id serial,
test(> name text);
NOTICE: CREATE TABLE will create implicit sequence “emp_id_seq” for serial column “emp.id”
CREATE TABLE
test=> INSERT INTO emp (name) VALUES (‘david’);
INSERT 0 1
test=> INSERT INTO emp (name) VALUES (‘sandy’);
INSERT 0 1
test=> SELECT * from emp;
id | name
—-+——-
1 | david
2 | sandy
(2 rows)
test=> \dt
List of relations
Schema | Name | Type | Owner
——–+——+——-+——-
public | emp | table | renee
(1 row)
test=> GRANT USAGE ON SCHEMA public to father;
WARNING: no privileges were granted for “public”
GRANT
test=> GRANT SELECT on public.emp to father;
GRANT
test=>
6.3 创建成员角色
test=> \c postgres postgres
You are now connected to database “postgres” as user “postgres”.
postgres=# CREATE ROLE son1 login nosuperuser nocreatedb nocreaterole inherit encrypted password ‘abc123’;
CREATE ROLE
postgres=#
这里创建了son1 角色，并开启inherit 属性。PostgreSQL 里的角色赋权是通过角色继承（INHERIT）的方式实现的。
6.4 将father 角色赋给son1
postgres=# GRANT father to son1;
GRANT ROLE
postgres=#
还有另一种方法，就是在创建用户的时候赋予角色权限。
postgres=# CREATE ROLE son2 login nosuperuser nocreatedb nocreaterole inherit encrypted password ‘abc123’ in role father;
CREATE ROLE
postgres=#
6.5 测试son1 角色
postgres=# \c test son1
You are now connected to database “test” as user “son1”.
test=> \dt
List of relations
Schema | Name | Type | Owner
——–+——+——-+——-
public | emp | table | renee
(1 row)
test=> SELECT * from emp;
id | name
—-+——-
1 | david
2 | sandy
(2 rows)
test=>
用renee 角色新创建一张表，再次测试
test=> \c test renee
You are now connected to database “test” as user “renee”.
test=> CREATE TABLE dept (
test(> deptid integer,
test(> deptname text);
CREATE TABLE
test=> INSERT INTO dept (deptid, deptname) values(1, ‘ts’);
INSERT 0 1
test=> \c test son1
You are now connected to database “test” as user “son1”.
test=> SELECT * from dept ;
ERROR: permission denied for relation dept
test=>
son1 角色只能查询emp 表的数据，而不能查询dept 表的数据，测试成功。
6.6 查询角色组信息
test=> \c postgres postgres
You are now connected to database “postgres” as user “postgres”.
postgres=#
postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————+———–
bella | Create DB | {}
david | | {}
father | No inheritance | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
son1 | | {father}
son2 | | {father}
postgres=#
“ Member of ” 项表示son1 和son2 角色属于father 角色组。
您可能感兴趣的文章:PostgreSQL教程(十二)：角色和权限管理介绍Postgresql源码分析returns?setof函数oracle管道pipelinedpostgresql13主从搭建UbuntuPostgreSql?JDBC事务操作方法详解PostgreSQL?数组类型操作使用及特点详解Postgresql数据库角色创建登录详解
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
