文中，我们将学习PostgreSQL角色和权限的关系，以及如何借助示例来学习对PostgreSQL角色的操作
PostgreSQL使用角色表示用户帐户。它不像其他数据库系统那样使用用户概念。
通常，可以登录的角色称为登录角色。它们等同于其他数据库系统中的用户。
当角色包含其他角色时，它们是调用组角色。
请注意，自版本8.1起，PostgreSQL将user和group合并为角色组
?PostgreSQL CREATE ROLE 语法
要创建新角色，请使用CREATE ROLE语句，声明如下:
CREATE ROLE role_name;
创建角色时，该角色在数据库服务器 (或集群) 中的所有数据库中都有效。
以下语句使用CREATE ROLE 语句以创建名为bb的新角色:
CREATE ROLE bb;
通过以下语句，我们可以从pg_roles系统目录获取当前PostgreSQL数据库服务器中的所有角色，如下:
SELECT rolname FROM pg_roles;
Output:
rolname
---------------------------
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_read_server_files
pg_write_server_files
pg_execute_server_program
pg_signal_backend
postgres
bb
? 注意： 以
pg_前缀开头的角色，是系统的角色
如果你使用psql工具，你可以使用du命令列出当前PostgreSQL数据库服务器中的所有现有角色。
du
Output:
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
bb | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
从输出中可以清楚地看到，角色bb无法登录。
要允许角色bb登录到PostgreSQL数据库服务器，我们需要添加login属性。
?角色属性
角色的属性定义该角色的权限，包括登录、超级管理员、创建数据库、创建角色、密码等:
CREATE ROLE name WITH option;
在此语法中，
-WITH关键字是可选的。
-option可以是一个或多个属性，包括SUPER,CREATEDB,CREATEROLE等等。
?1) 创建登录角色
例如，以下语句创建一个具有登录权限和初始密码名为adi的角色:
CREATE ROLE adi
LOGIN
PASSWORD 'adi123';
请注意，我们将密码放在单引号中 (')。
现在，我们可以使用角色adi登录到PostgreSQL数据库服务器psql客户端工具:
psql -U adi -W postgres
它会提示我们输入密码。你需要输入CREATE ROLE语句中填入的密码。
?2) 创建超级用户角色
以下语句创建一个名为superidol具有超级用户角色。
CREATE ROLE superidol
SUPERUSER
LOGIN
PASSWORD 'superidol123';
超级用户可以获取数据库中的所有访问限制，因此我们应仅在需要时创建此角色。
请注意，必须是超级用户才能创建另一个超级用户角色。
?3) 创建可以创建数据库的角色
如果要创建具有数据库创建特权的角色，请使用CREATEDB属性:
CREATE ROLE dba
CREATEDB
LOGIN
PASSWORD 'dba123';
?4)创建具有有效期的角色
要设置角色密码不再有效的日期和时间，请使用 VALID UNTIL 属性:
VALID UNTIL 'timestamp'
例如，以下语句创建一个dev具有密码的角色有效期至2022年底:
CREATE ROLE dev WITH
LOGIN
PASSWORD 'dev123'
VALID UNTIL '2023-01-01';
在2023年开始后，dev的密码不再有效。
?5) 创建具有连接限制的角色
要指定角色可以建立的并发连接数，请使用CONNECTION LIMIT属性:
CONNECTION LIMIT connection_count
下面创建一个名为api的新角色，该角色可以建立1000个并发连接:
CREATE ROLE api
LOGIN
PASSWORD 'api123'
CONNECTION LIMIT 1000;
以下psql命令显示到目前为止我们创建的所有角色:
du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
adi | | {}
api | 1000 connections | {}
bb | Cannot login | {}
dba | Create DB | {}
superidol | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
总结
- PostgreSQL使用角色表示用户帐户。可以登录的角色等同于其他数据库系统中的用户acocunt。
- 使用角色属性指定角色的权限，例如，
LOGIN允许角色登录，CREATEDB允许角色创建新数据库，SUPERUSER允许角色拥有所有权限。
?PostgreSQL GRANT 语句简介
使用LOGIN属性创建角色后，该角色可以登录到PostgreSQL数据库服务器。但是，它无法对表、视图、函数等数据库对象执行任何操作。
例如，用户角色无法从表中选择数据或执行特定函数。
允许用户角色与数据库对象，我们需要使用 GRANT语句 对用户角色授予数据库对象操作权限。
以下例子展示了GRANT向角色授予一个或多个表操作权限的语句:
GRANT privilege_list | ALL
ON table_name
TO role_name;
? 在上面的示例中:
- 首先，指定
privilege_list（权限列表：可以是SELECT,INSERT,UPDATE,DELETE,TRUNCATE等等）。使用ALL向角色授予表的所有操作权限。- 第二，
ON关键字后指定表名。- 第三，
ON关键字后指定要向其授予权限的角色名。
?PostgreSQL GRANT 语句示例
首先，使用postgres用户可以使用任何客户端工具连接到PostgreSQL数据库服务器。
其次，创建一个名为abcd 的新用户角色，该角色可以登录到PostgreSQL数据库服务器:
create role abcd
login
password 'Abcd1234';
第三，创建一个名为user的表:
create table user (
user_id int generated always as identity,
first_name varchar(100) not null,
last_name varchar(100) not null,
email varchar(255) not null unique,
phone varchar(25) not null,
primary key(user_id)
);
第四，使用角色abcd在另外的会话中登录到PostgreSQL数据库服务器。
第五，尝试用abcd角色查询 user表:
SELECT * FROM candidates;
PostgreSQL发出错误:
ERROR: permission denied for table user
尝试执行以下语句：使用postgres角色，对abcd角色授予user表SELECT 权限
GRANT SELECT
ON user
TO abd;
第六，再次尝试用abcd角色查询 user表:
SELECT * FROM candidates;
PostgreSQL返回空结果集而不是错误。
第七，执行以下INSERT语句:
INSERT INTO user(first_name, last_name, email, phone)
VALUES('abdc','Com','abdc.com@example.com','400-820-8820');
PostgreSQL发出以下错误，因为abdc没有user表INSERT权限。
ERROR: permission denied for table candidates
第八，尝试执行以下语句：使用postgres角色，对abcd角色授予user表INSERT, UPDATE, DELETE权限
GRANT INSERT, UPDATE, DELETE
ON user
TO abcd;
第九，再次尝试用abcd角色插入 user表:
INSERT INTO user(first_name, last_name, email, phone)
VALUES('abdc','Com','abdc.com@example.com','400-820-8820');
现在，abdc可以将数据插入到user表。此外，它还可以更新或删除表中的数据。
?更多的PostgreSQL GRANT 示例
让我们举一些使用GRANT的例子。
?将表上的所有权限授予角色
对abcd角色授予user表ALL权限
GRANT ALL
ON user
TO abcd;
?向角色授予schema中所有表的所有权限
对abcd角色授予SCHEMA public 所有表的所有权限
GRANT ALL
ON ALL TABLES
IN SCHEMA "public"
TO abcd;
?授予所有表的SELECT权限
有时，我们希望创建一个只读角色，该角色只能从指定schema中的所有表中选择数据。
对角色授予SCHEMA public 所有表的SELECT权限，sql示例如下：
GRANT SELECT
ON ALL TABLES
IN SCHEMA "public"
TO reader;
到目前为止，我们已经学习了如何授予表上的权限。
?PostgreSQL REVOKE 语句简介
REVOKE语句撤销先前从角色授予的数据库对象权限。
以下显示REVOKE的语法：从角色撤销一个或多个表的权限的语句:
REVOKE privilege | ALL
ON TABLE table_name | ALL TABLES IN SCHEMA schema_name
FROM role_name;
?在上面的语法中：
- 首先，指定要撤销的一个或多个权限。可以使用
ALL指定撤销所有权限。 - 第二，
ON关键字后指定表。可以使用ALL TABLES指定从schema中的所有表撤销指定的权限。 - 第三，
FROM关键字后指定要从中撤销权限的角色的名称。
?PostgreSQL REVOKE 语句示例
让我们举一个使用REVOKE语句的例子。
?步骤1.创建角色并授予权限
首先，使用postgres用户登录到postgres示例数据库:
psql -U postgres -d postgres
第二，创建一个名为 oz 带有 LOGIN 和PASSWORD 权限的角色:
CREATE ROLE oz
LOGIN
PASSWORD 'oz123';
第三，使用postgres角色，对oz角色授予user表ALL权限
GRANT ALL
ON user
TO oz;
最后，对oz角色授予dept表SELECT权限
GRANT SELECT
ON dept
TO oz;
?步骤2.从角色撤销权限
对oz角色撤销dept表SELECT权限，使用以下语句
REVOKE SELECT
ON dept
FROM oz;
对oz角色撤销user表ALL权限，使用以下语句
REVOKE ALL
ON user
FROM oz;
?撤销对其他数据库对象的权限
要从其他数据库对象 (如序列、函数、存储过程、schema、数据库) 撤销权限，请查看官方文档revoke语法。
?PostgreSQL 角色组简介
作为一个组来管理角色更容易，这样我们就可以从整个组中授予或撤销特权，而不是在单个角色上这样做。
通常，我们会创建一个角色组，然后将角色组中的成员资格授予各个角色。
按照惯例，角色组没有LOGIN权限。这意味着我们将无法使用角色组登录PostgreSQL。
要创建角色组，使用CREATE ROLE语句如下:
CREATE ROLE group_role_name;
例如，以下语句创建角色组test:
CREATE ROLE test;
当我们在psql工具使用 du命令，将看到角色组与用户角色一起列出:
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
adi | | {}
api | 1000 connections | {}
bb | Cannot login | {}
dba | Create DB | {}
oz | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | Cannot login | {}
要将角色添加到角色组，请使用以下形式的GRANT语句:
GRANT group_role to user_role;
例如，以下语句添加角色adi到角色组test:
GRANT test TO adi;
如果再次运行du命令，我们将看到adi现在属于角色组test:
du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
adi | | {test}
api | 1000 connections | {}
bb | Cannot login | {}
dba | Create DB | {}
oz | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | Cannot login | {}
要从角色组中删除用户角色，请使用REVOKE语句:
REVOKE group_role FROM user_role;
例如，以下语句使用REVOKE从角色组中删除角色:
REVOKE test FROM adi;
? 请注意，PostgreSQL不允许使用循环赋予角色组，其中角色是另一个角色的成员，反之亦然。
?PostgreSQL角色组 操作示例
角色可以通过以下方式使用角色组的权限:
- 首先，一个角色
继承attribute将自动具有其作为其成员的组角色的特权，包括该角色继承的任何特权。 - 第二，角色可以使用
设置角色临时成为组角色的语句。该角色将具有组角色而不是其原始登录角色的特权。此外，由角色创建的对象属于组角色，而不是登录角色。
? 步骤1.设置数据库和表
?1) 使用postgres登录到PostgreSQL数据库。
?2) 创建一个名为company的数据库:
create database company;
?3) 切换到company数据库:
c company
?4)创建user表:
create table user(
id int generated always as identity primary key,
name varchar(255) not null,
phone varchar(255) not null
);
?5) 创建jointime表:
create table jointime(
year int,
month int
);
? 步骤2.设置角色和角色组
?1) 创建角色jj它可以使用密码登录并继承其所属组角色的所有权限
create role jj inherit login password 'jj123';
?2) 给jj角色赋予user表select权限
grant select on user to jj;
?3) 使用z检查授权命令:
z
?4) 创建ggroup角色组:
create role ggroup noinherit;
?5) 创建jgroup角色组:
create role jgroup noinherit;
?6)给ggroup授予user表所有权限：
grant all on user to ggroup;
?7) 给jgroup授予jointime表所有权限：
grant all on jointime to jgroup;
?8) 添加角色jj到角色组ggroup:
grant ggroup to jj;
?9) 添加角色jgroup到角色组ggroup:
grant ggroup to jgroup;
? 步骤3.使用角色
?1) 如果使用jj角色连接到PostgreSQL,我们将拥有直接授予ggroup的任何权限。因为jj继承ggroup的权限:
psql -U jj -d company
?2) 它会提示你输入jj的密码。
?3) 角色jj可以查询jointime表:
company=> select * from jointime;
?4) 并可以插入user表:
company=> insert into user(name, phone) values('JJ','400-820-8820')
?5) 然而，jj无法将行插入jointime表:
company=> insert into jointime(year, month, amount) values(2020,1,1000);
ERROR: permission denied for table jointime
?6) 执行以下操作:
company=> set role jgroup;
?7) 角色jj将拥有授予jgroup的权限。
?8) 现在, jj可以将行插入jointime表:
company=> insert into jointime(year, month) values(2022,10);
?9) 如果jj尝试查询user表数据，它将失败，因为角色jgroup没有user表权限:
company=> select * from user;
ERROR: permission denied for table user
恢复角色权限，我们可以使用以下声明:
RESET ROLE;
?总结
- 管理角色组而不是单个角色的权限。
- 角色获取其所属的角色组的所有权限。
?使用PostgreSQL ALTER ROLE 修改角色属性的步骤
要更改角色的属性，请使用以下形式ALTER ROLE:
ALTER ROLE role_name [WITH] option;
选项可以是:
SUPERUSER|NOSUPERUSER– 是否是SUPERUSER。CREATEDB|NOCREATEDB– 是否允许角色创建新数据库。CREATEROLE|NOCREATEROLE–是否允许角色创建或更改角色。INHERIT|NOINHERIT– 是否确定要继承其所属角色组的角色权限。LOGIN|NOLOGIN– 允许角色登录。REPLICATION|NOREPLICATION– 确定该角色是否为复制角色。BYPASSRLS|NOBYPASSRLS– 确定角色是否通过行级安全 (RLS) 策略。CONNECTION LIMIT limit– 指定角色可以建立的并发连接数，-1表示无限制。PASSWORD 'password' | PASSWORD NULL– 角色的密码。VALID UNTIL 'timestamp'– 设置角色密码的有效期。
应用以下规则:
- 超级管理员可以为任何角色更改这些属性中的任何一个。
- 仅针对非超级用户和无复制角色：角色具有
CREATEROLE权限可以更改这些属性中的任何一个。 - 角色只能更改自己的密码。
首先，使用postgres角色登录到PostgreSQL。
第二，使用create role创建一个名为crole的角色
create role crole login password 'crole123';
crole角色可以使用密码登录。
因为postgres是超级用户，它可以更改角色crole为超级用户:
alter role crole superuser;
在psql中 查看角色crole，使用du命令:
du crole
Output:
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
crole | Superuser | {}
以下语句设置角色的密码crole``2050年到期:
alter role calf
valid until '2050-01-01';
使用du命令查看效果:
du crole
Output:
List of roles
Role name | Attributes | Member of
-----------+---------------------------------------------+-----------
crole | Superuser +| {}
| Password valid until 2050-01-01 00:00:00-08 |
?使用PostgreSQL ALTER ROLE 重命名角色
要更改角色的名称，请使用以下形式的ALTER ROLE声明:
ALTER ROLE role_name
TO new_name;
在此语法中，我们可以在ALTER ROLE关键字和TO关键字角色的新名称。
超级用户可以重命名任何角色。作用与CREATEROLE权限的角色 可以重命名非超级用户角色。
如果使用角色登录到PostgreSQL数据库服务器并在当前会话中将其重命名，则会出现错误:
ERROR: session user cannot be renamed
在这种情况下，我们需要使用其他角色连接到PostgreSQL数据库服务器，以重命名该角色。
你执行以下语句从postgres重命名角色crole到brole:
ALTER ROLE crole
RENAME TO brole;
?使用PostgreSQL ALTER ROLE 更改角色配置变量默认值
以下ALTER ROLE语句更改角色的配置变量默认值:
ALTER ROLE role_name | CURRENT_USER | SESSION_USER | ALL
[IN DATABASE database_name]
SET configuration_param = { value | DEFAULT }
?在上述代码中:
- 首先，指定要修改角色的会话默认值的角色的名称，或使用
CURRENT_USER,或SESSION_USER。使用ALL选项可更改所有角色的设置。- 第二，指定数据库名称后
IN DATABASE关键字仅针对指定数据库中的会话进行更改。以防你忽略了IN DATABASE子句，更改将应用于所有数据库。- 第三，
SET指定配置参数中的新值。
超级管理员可以更改任何角色的默认值。角色拥有CREATEROLE权限可以设置非超级用户角色的默认值。普通角色只能为自己设置默认值。
?只有超级管理员可以更改所有数据库中所有角色的设置。
以下示例使用ALTER ROLE为角色brole提供一个非默认的、特定于数据库的设置client_min_messages参数:
ALTER ROLE brole
IN DATABASE dvdrental
SET client_min_messages = NOTICE;
总结
- 使用
ALTER ROLE role_name option修改角色的属性。 - 使用
ALTER ROLE role_name RENAME new_role重命名角色。 - 使用
ALTER ROLE role_name SET param = value语句以更改配置变量的角色默认值。
?PostgreSQL DROP ROLE 语句简介
要删除指定角色，请使用DROP ROLE:
DROP ROLE [IF EXISTS] target_role;
在以上代码中:
DROP ROLE关键词后指定要删除的角色。- 使用
IF EXISTS如果我们希望PostgreSQL在删除不存在的角色时发出通知而不是错误，请选择该选项。
要删除超级管理员角色，需要成为超级管理员。要删除非超级管理员角色，需要具有CREATEROLE特权。
删除任何数据库中引用的角色时，PostgreSQL将引发错误。在这种情况下，我们必须采取两个步骤:
- 首先，使用
DROP OWNED语句删除数据库对象的角色；或将数据库对象的所有权重新分配给另一个角色REASSIGN OWNED。 - 第二，撤销授予角色的任何权限。
REASSIGN OWNED语句将目标角色的所有从属对象的所有权重新分配给另一个角色。因为REASSIGN OWNED语句只能访问当前数据库中的对象，我们需要在包含目标角色拥有的对象的每个数据库中执行此语句。
将对象的所有权转移到另一个角色后，我们需要通过执行DROP OWNED包含目标角色拥有的对象的每个数据库中的语句。
换句话说，我们应该按顺序执行以下语句以删除角色:
-- execute these statements in the database that contains
-- the object owned by the target role
REASSIGN OWNED BY target_role TO another_role;
DROP OWNED BY target_role;
-- drop the role
DROP ROLE target_role;
让我们看看以下示例。
?PostgreSQL DROP ROLE 示例
在此示例中:
- 首先，我们将创建一个名为
ali的角色并且给它分配数据库表权限。 - 然后，我们将逐步展示如何删除角色
ali。
我们将使用psql工具。
?步骤1.设置新角色和数据库
首先，使用postgres角色登录PostgreSQL:
psql -U postgres
第二，创建一个名为ali的角色:
postgres=# create role ali with login password 'ali123';
第三，授予ali``createdb 权限：
postgres=# alter role alice createdb;
第四，创建一个名为alibaba的数据库:
postgres=# create database alibaba;
退出当前会话:
postgres=# q
?步骤2.使用新角色创建数据库对象
首先，使用ali角色登录到PostgreSQL数据库服务器:
psql -U alice -W alibaba
第二，创建数据库表customers:
create table customers(
customer_id int generated always as identity,
customer_name varchar(150) not null,
primary key(customer_id)
);
第三，显示alibaba数据库:
alibaba=> dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+-------
public | customers | table | ali
(1 row)
最后，退出当前会话:
alibaba=# q
?步骤3.删除角色ali
首先，使用postgres角色登录PostgreSQL:
psql -U postgres
第二，尝试删除角色ali:
postgres=# drop role alice;
PostgreSQL发出以下错误:
Error:
ERROR: role "ali" cannot be dropped because some objects depend on it
DETAIL: 2 objects in database sales
角色ali无法删除，因为它具有从属对象。
第三，切换到alibaba数据库:
postgres=# c alibaba
第四，重新分配对象ali所有权到postgres:
alibaba=# reassign owned by ali to postgres;
第五，drop ownedali:
alibaba=# drop owned by ali;
第六，删除角色ali:
alibaba=# drop role ali;
第七，列出当前角色:
alibaba=#du
我们会看到这个角色ali已被移除。
最后，退出当前会话:
alibaba=#q
?总结
- 使用PostgreSQL
DROP ROLE删除指定角色的语句。- 如果角色具有从属对象，请使用
REASSIGN OWNED和DROP OWNED删除角色的相关对象,最后执行DROP ROLE语句。
