玩转PostgreSQL(三):角色和权限的关系及操作

2023-06-0610:09:45数据库教程Comments2,149 views字数 11520阅读模式

文中,我们将学习PostgreSQL角色和权限的关系,以及如何借助示例来学习对PostgreSQL角色的操作文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

玩转PostgreSQL(三):角色和权限的关系及操作文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

PostgreSQL使用角色表示用户帐户。它不像其他数据库系统那样使用用户概念。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

通常,可以登录的角色称为登录角色。它们等同于其他数据库系统中的用户。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

当角色包含其他角色时,它们是调用组角色。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

请注意,自版本8.1起,PostgreSQL将user和group合并为角色组文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?PostgreSQL CREATE ROLE 语法

要创建新角色,请使用CREATE ROLE语句,声明如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE role_name;

创建角色时,该角色在数据库服务器 (或集群) 中的所有数据库中都有效。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

以下语句使用CREATE ROLE 语句以创建名为bb的新角色:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE bb;

通过以下语句,我们可以从pg_roles系统目录获取当前PostgreSQL数据库服务器中的所有角色,如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

SELECT rolname FROM pg_roles;

Output:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

          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_ 前缀开头的角色,是系统的角色文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

如果你使用psql工具,你可以使用\du命令列出当前PostgreSQL数据库服务器中的所有现有角色。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

\du

Output:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

                                  List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 bb       | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

从输出中可以清楚地看到,角色bb无法登录。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

要允许角色bb登录到PostgreSQL数据库服务器,我们需要添加login属性。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?角色属性

角色的属性定义该角色的权限,包括登录、超级管理员、创建数据库、创建角色、密码等:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE name WITH option;

在此语法中,文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

-WITH关键字是可选的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

-option可以是一个或多个属性,包括SUPER,CREATEDB,CREATEROLE等等。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?1) 创建登录角色

例如,以下语句创建一个具有登录权限和初始密码名为adi的角色:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE adi 
LOGIN 
PASSWORD 'adi123';

请注意,我们将密码放在单引号中 (')。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

现在,我们可以使用角色adi登录到PostgreSQL数据库服务器psql客户端工具:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

psql -U adi -W postgres

它会提示我们输入密码。你需要输入CREATE ROLE语句中填入的密码。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?2) 创建超级用户角色

以下语句创建一个名为superidol具有超级用户角色。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE superidol 
SUPERUSER 
LOGIN 
PASSWORD 'superidol123';

超级用户可以获取数据库中的所有访问限制,因此我们应仅在需要时创建此角色。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

请注意,必须是超级用户才能创建另一个超级用户角色。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?3) 创建可以创建数据库的角色

如果要创建具有数据库创建特权的角色,请使用CREATEDB属性:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE dba 
CREATEDB 
LOGIN 
PASSWORD 'dba123';

?4)创建具有有效期的角色

要设置角色密码不再有效的日期和时间,请使用 VALID UNTIL 属性:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

VALID UNTIL 'timestamp'

例如,以下语句创建一个dev具有密码的角色有效期至2022年底:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE dev WITH
LOGIN
PASSWORD 'dev123'
VALID UNTIL '2023-01-01';

在2023年开始后,dev的密码不再有效。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?5) 创建具有连接限制的角色

要指定角色可以建立的并发连接数,请使用CONNECTION LIMIT属性:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CONNECTION LIMIT connection_count

下面创建一个名为api的新角色,该角色可以建立1000个并发连接:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE api
LOGIN
PASSWORD 'api123'
CONNECTION LIMIT 1000;

以下psql命令显示到目前为止我们创建的所有角色:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

\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 | {}

总结文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • PostgreSQL使用角色表示用户帐户。可以登录的角色等同于其他数据库系统中的用户acocunt。
  • 使用角色属性指定角色的权限,例如,LOGIN 允许角色登录,CREATEDB 允许角色创建新数据库,SUPERUSER 允许角色拥有所有权限。

?PostgreSQL GRANT 语句简介

使用LOGIN属性创建角色后,该角色可以登录到PostgreSQL数据库服务器。但是,它无法对表、视图、函数等数据库对象执行任何操作。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

例如,用户角色无法从表中选择数据或执行特定函数。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

允许用户角色与数据库对象,我们需要使用 GRANT语句 对用户角色授予数据库对象操作权限。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

以下例子展示了GRANT向角色授予一个或多个表操作权限的语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

GRANT privilege_list | ALL 
ON  table_name
TO  role_name;

? 在上面的示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 首先,指定privilege_list (权限列表:可以是SELECT,INSERT,UPDATE,DELETE,TRUNCATE等等)。使用ALL 向角色授予表的所有操作权限。
  • 第二,ON 关键字后指定表名。
  • 第三,ON 关键字后指定要向其授予权限的角色名。

?PostgreSQL GRANT 语句示例

首先,使用postgres用户可以使用任何客户端工具连接到PostgreSQL数据库服务器。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

其次,创建一个名为abcd 的新用户角色,该角色可以登录到PostgreSQL数据库服务器:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

create role abcd 
login 
password 'Abcd1234';

第三,创建一个名为user的表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

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数据库服务器。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

第五,尝试用abcd角色查询 user表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

SELECT * FROM candidates;   

PostgreSQL发出错误:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

ERROR:  permission denied for table user

尝试执行以下语句:使用postgres角色,对abcd角色授予userSELECT 权限文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

GRANT SELECT 
ON user 
TO abd;

第六,再次尝试用abcd角色查询 user表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

SELECT * FROM candidates;   

PostgreSQL返回空结果集而不是错误。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

第七,执行以下INSERT语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

INSERT INTO user(first_name, last_name, email, phone)
VALUES('abdc','Com','abdc.com@example.com','400-820-8820');

PostgreSQL发出以下错误,因为abdc没有userINSERT权限。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

ERROR:  permission denied for table candidates

第八,尝试执行以下语句:使用postgres角色,对abcd角色授予userINSERT, UPDATE, DELETE权限文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

GRANT INSERT, UPDATE, DELETE
ON user 
TO abcd;

第九,再次尝试用abcd角色插入 user表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

INSERT INTO user(first_name, last_name, email, phone)
VALUES('abdc','Com','abdc.com@example.com','400-820-8820');

现在,abdc可以将数据插入到user表。此外,它还可以更新或删除表中的数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?更多的PostgreSQL GRANT 示例

让我们举一些使用GRANT的例子。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?将表上的所有权限授予角色

abcd角色授予userALL权限文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

GRANT ALL
ON user
TO abcd;

?向角色授予schema中所有表的所有权限

abcd角色授予SCHEMA public 所有表的所有权限文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

GRANT ALL
ON ALL TABLES
IN SCHEMA "public"
TO abcd;

?授予所有表的SELECT权限

有时,我们希望创建一个只读角色,该角色只能从指定schema中的所有表中选择数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

对角色授予SCHEMA public 所有表的SELECT权限,sql示例如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

GRANT SELECT
ON ALL TABLES
IN SCHEMA "public"
TO reader;

到目前为止,我们已经学习了如何授予表上的权限。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?PostgreSQL REVOKE 语句简介

REVOKE语句撤销先前从角色授予的数据库对象权限。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

以下显示REVOKE的语法:从角色撤销一个或多个表的权限的语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

REVOKE privilege | ALL
ON TABLE table_name |  ALL TABLES IN SCHEMA schema_name
FROM role_name;

?在上面的语法中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 首先,指定要撤销的一个或多个权限。可以使用ALL指定撤销所有权限。
  • 第二,ON关键字后指定表。可以使用ALL TABLES指定从schema中的所有表撤销指定的权限。
  • 第三,FROM关键字后指定要从中撤销权限的角色的名称。

?PostgreSQL REVOKE 语句示例

让我们举一个使用REVOKE语句的例子。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?步骤1.创建角色并授予权限

首先,使用postgres用户登录到postgres示例数据库:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

psql -U postgres -d postgres    

第二,创建一个名为 oz 带有 LOGINPASSWORD 权限的角色:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE oz  
LOGIN 
PASSWORD 'oz123';

第三,使用postgres角色,对oz角色授予userALL权限文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

GRANT ALL 
ON user
TO oz;

最后,对oz角色授予deptSELECT权限文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

GRANT SELECT
ON dept
TO oz;

?步骤2.从角色撤销权限

oz角色撤销deptSELECT权限,使用以下语句文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

REVOKE SELECT
ON dept
FROM oz;

oz角色撤销userALL权限,使用以下语句文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

REVOKE ALL
ON user
FROM oz;

?撤销对其他数据库对象的权限

要从其他数据库对象 (如序列、函数、存储过程、schema、数据库) 撤销权限,请查看官方文档revoke语法文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?PostgreSQL 角色组简介

作为一个组来管理角色更容易,这样我们就可以从整个组中授予或撤销特权,而不是在单个角色上这样做。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

通常,我们会创建一个角色组,然后将角色组中的成员资格授予各个角色。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

按照惯例,角色组没有LOGIN权限。这意味着我们将无法使用角色组登录PostgreSQL。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

要创建角色组,使用CREATE ROLE语句如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE group_role_name;

例如,以下语句创建角色组test:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

CREATE ROLE test;

当我们在psql工具使用\ du命令,将看到角色组与用户角色一起列出:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

                                   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语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

GRANT group_role to user_role;

例如,以下语句添加角色adi到角色组test:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

GRANT test TO adi;

如果再次运行\du命令,我们将看到adi现在属于角色组test:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

\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语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

REVOKE group_role FROM user_role;

例如,以下语句使用REVOKE从角色组中删除角色:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

REVOKE test FROM adi;

? 请注意,PostgreSQL不允许使用循环赋予角色组,其中角色是另一个角色的成员,反之亦然。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?PostgreSQL角色组 操作示例

角色可以通过以下方式使用角色组的权限:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 首先,一个角色继承attribute将自动具有其作为其成员的组角色的特权,包括该角色继承的任何特权。
  • 第二,角色可以使用设置角色临时成为组角色的语句。该角色将具有组角色而不是其原始登录角色的特权。此外,由角色创建的对象属于组角色,而不是登录角色。

? 步骤1.设置数据库和表

?1) 使用postgres登录到PostgreSQL数据库。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?2) 创建一个名为company的数据库:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

create database company;

?3) 切换到company数据库:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

\c company

?4)创建user表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

create table user(
   id int generated always as identity primary key,
   name varchar(255) not null,
   phone varchar(255) not null
);

?5) 创建jointime表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

create table jointime(
    year int, 
    month int
);

? 步骤2.设置角色和角色组

?1) 创建角色jj它可以使用密码登录并继承其所属组角色的所有权限文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

create role jj inherit login password 'jj123';

?2) 给jj角色赋予userselect权限文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

grant select on user to jj;

?3) 使用\z检查授权命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

\z

?4) 创建ggroup角色组:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

create role ggroup noinherit;

?5) 创建jgroup角色组:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

create role jgroup noinherit;

?6)给ggroup授予user表所有权限:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

grant all on user to ggroup;

?7) 给jgroup授予jointime表所有权限:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

grant all on jointime to jgroup;

?8) 添加角色jj到角色组ggroup:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

 grant ggroup to jj;

?9) 添加角色jgroup到角色组ggroup:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

grant ggroup to jgroup;

? 步骤3.使用角色

?1) 如果使用jj角色连接到PostgreSQL,我们将拥有直接授予ggroup的任何权限。因为jj继承ggroup的权限:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

psql -U jj -d company

?2) 它会提示你输入jj的密码。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?3) 角色jj可以查询jointime表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

company=> select * from jointime;

?4) 并可以插入user表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

company=> insert into user(name, phone) values('JJ','400-820-8820')

?5) 然而,jj无法将行插入jointime表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

company=> insert into jointime(year, month, amount) values(2020,1,1000);
ERROR:  permission denied for table jointime

?6) 执行以下操作:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

company=> set role jgroup;

?7) 角色jj将拥有授予jgroup的权限。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?8) 现在, jj可以将行插入jointime表:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

company=> insert into jointime(year, month) values(2022,10);

?9) 如果jj尝试查询user表数据,它将失败,因为角色jgroup没有user表权限:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

company=> select * from user;
ERROR:  permission denied for table user

恢复角色权限,我们可以使用以下声明:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

RESET ROLE;

?总结文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 管理角色组而不是单个角色的权限。
  • 角色获取其所属的角色组的所有权限。

?使用PostgreSQL ALTER ROLE 修改角色属性的步骤

要更改角色的属性,请使用以下形式ALTER ROLE:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

ALTER ROLE role_name [WITH] option;

选项可以是:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 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' – 设置角色密码的有效期。

应用以下规则:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 超级管理员可以为任何角色更改这些属性中的任何一个。
  • 仅针对非超级用户和无复制角色:角色具有CREATEROLE权限可以更改这些属性中的任何一个。
  • 角色只能更改自己的密码。

首先,使用postgres角色登录到PostgreSQL。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

第二,使用create role创建一个名为crole的角色文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

create role crole login password 'crole123';

crole角色可以使用密码登录。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

因为postgres是超级用户,它可以更改角色crole为超级用户:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

alter role crole superuser;

在psql中 查看角色crole,使用\du命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

\du crole

Output:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

        List of roles
Role name | Attributes | Member of
-----------+------------+-----------
crole     | Superuser  | {}

以下语句设置角色的密码crole``2050年到期:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

alter role calf
valid until '2050-01-01';

使用\du命令查看效果:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

\du crole

Output:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

                            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声明:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

ALTER ROLE role_name
TO new_name;

在此语法中,我们可以在ALTER ROLE关键字和TO关键字角色的新名称。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

超级用户可以重命名任何角色。作用与CREATEROLE权限的角色 可以重命名非超级用户角色。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

如果使用角色登录到PostgreSQL数据库服务器并在当前会话中将其重命名,则会出现错误:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

ERROR:  session user cannot be renamed

在这种情况下,我们需要使用其他角色连接到PostgreSQL数据库服务器,以重命名该角色。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

你执行以下语句从postgres重命名角色crolebrole:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

ALTER ROLE crole
RENAME TO brole;

?使用PostgreSQL ALTER ROLE 更改角色配置变量默认值

以下ALTER ROLE语句更改角色的配置变量默认值:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

ALTER ROLE role_name | CURRENT_USER | SESSION_USER | ALL
[IN DATABASE database_name]
SET configuration_param = { value | DEFAULT }

?在上述代码中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 首先,指定要修改角色的会话默认值的角色的名称,或使用CURRENT_USER,或SESSION_USER。使用ALL 选项可更改所有角色的设置。
  • 第二,指定数据库名称后IN DATABASE关键字仅针对指定数据库中的会话进行更改。以防你忽略了IN DATABASE子句,更改将应用于所有数据库。
  • 第三,SET 指定配置参数中的新值。

超级管理员可以更改任何角色的默认值。角色拥有CREATEROLE权限可以设置非超级用户角色的默认值。普通角色只能为自己设置默认值。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?只有超级管理员可以更改所有数据库中所有角色的设置。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

以下示例使用ALTER ROLE为角色brole提供一个非默认的、特定于数据库的设置client_min_messages参数:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

ALTER ROLE brole 
IN DATABASE dvdrental 
SET client_min_messages = NOTICE;

总结文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 使用ALTER ROLE role_name option 修改角色的属性。
  • 使用ALTER ROLE role_name RENAME new_role重命名角色。
  • 使用ALTER ROLE role_name SET param = value语句以更改配置变量的角色默认值。

?PostgreSQL DROP ROLE 语句简介

要删除指定角色,请使用DROP ROLE:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

DROP ROLE [IF EXISTS] target_role;

在以上代码中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • DROP ROLE关键词后指定要删除的角色。
  • 使用IF EXISTS如果我们希望PostgreSQL在删除不存在的角色时发出通知而不是错误,请选择该选项。

要删除超级管理员角色,需要成为超级管理员。要删除非超级管理员角色,需要具有CREATEROLE特权。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

删除任何数据库中引用的角色时,PostgreSQL将引发错误。在这种情况下,我们必须采取两个步骤:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 首先,使用DROP OWNED语句删除数据库对象的角色;或将数据库对象的所有权重新分配给另一个角色REASSIGN OWNED
  • 第二,撤销授予角色的任何权限。

REASSIGN OWNED语句将目标角色的所有从属对象的所有权重新分配给另一个角色。因为REASSIGN OWNED语句只能访问当前数据库中的对象,我们需要在包含目标角色拥有的对象的每个数据库中执行此语句。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

将对象的所有权转移到另一个角色后,我们需要通过执行DROP OWNED包含目标角色拥有的对象的每个数据库中的语句。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

换句话说,我们应该按顺序执行以下语句以删除角色:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

-- 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;

让我们看看以下示例。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?PostgreSQL DROP ROLE 示例

在此示例中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 首先,我们将创建一个名为ali的角色并且给它分配数据库表权限。
  • 然后,我们将逐步展示如何删除角色ali

我们将使用psql工具。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

?步骤1.设置新角色和数据库

首先,使用postgres角色登录PostgreSQL:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

psql -U postgres

第二,创建一个名为ali的角色:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

postgres=# create role ali with login password 'ali123';

第三,授予ali``createdb 权限:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

postgres=# alter role alice createdb;

第四,创建一个名为alibaba的数据库:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

postgres=# create database alibaba;

退出当前会话:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

postgres=# \q

?步骤2.使用新角色创建数据库对象

首先,使用ali角色登录到PostgreSQL数据库服务器:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

psql -U alice -W alibaba

第二,创建数据库表customers:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

create table customers(
    customer_id int generated always as identity,
    customer_name varchar(150) not null,
    primary key(customer_id)
);

第三,显示alibaba数据库:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

alibaba=> \dt
        List of relations
Schema |   Name    | Type  | Owner
--------+-----------+-------+-------
public | customers | table | ali
(1 row)

最后,退出当前会话:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

alibaba=# \q

?步骤3.删除角色ali

首先,使用postgres角色登录PostgreSQL:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

psql -U postgres

第二,尝试删除角色ali:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

postgres=# drop role alice;

PostgreSQL发出以下错误:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

Error:

ERROR:  role "ali" cannot be dropped because some objects depend on it
DETAIL:  2 objects in database sales

角色ali无法删除,因为它具有从属对象。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

第三,切换到alibaba数据库:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

postgres=# \c alibaba

第四,重新分配对象ali所有权到postgres:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

alibaba=# reassign owned by ali to postgres;

第五,drop ownedali:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

alibaba=# drop owned by ali;

第六,删除角色ali:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

alibaba=# drop role ali;

第七,列出当前角色:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

alibaba=#\du

我们会看到这个角色ali已被移除。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

最后,退出当前会话:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

alibaba=#\q

?总结文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

  • 使用PostgreSQLDROP ROLE删除指定角色的语句。
  • 如果角色具有从属对象,请使用REASSIGN OWNEDDROP OWNED删除角色的相关对象,最后执行DROP ROLE语句。

 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html

作者:只注视着你
来源:稀土掘金文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45060.html
  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/sjk/45060.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定