PostgreSQL 用户和角色（一）-526互联

PostgreSQL通过角色的概念来控制数据库的访问权限。角色又包含了两种概念，具有登录权限的角色称为用户，包含其他成员（也是角色）的角色称为组（group）。因此，一个角色可以是一个用户，也可以是一个组，或者两者都是。角色可以拥有数据库对象（例如表和函数），并且可以将这些对象上的权限授予其他角色，从而控制对象的访问。此外，一个组中的成员可以拥有该组所拥有的权限。

创建角色

在PostgreSQL中，使用CREATE ROLE语句创建角色：

create role  role_name;

其中，role_name指定了要创建的角色名称。如果想要显示当前数据库集群中已有的角色，可以查询系统目录 pg_roles：

SELECT rolname FROM pg_roles;
#结果
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
wdh
admin
postgres

也可以使用psql中的\du命令

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     | Create DB, Cannot login                                    | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 wdh       | Password valid until 2025-01-01 00:00:00+08                | {}

其中的postgres是系统初始化数据库时创建的默认角色，它是一个超级用户。前一个命令中多出的几个角色都是系统提供的默认角色，用于提供针对一些特定的常用特权和信息的访问权限。

角色属性

角色可以拥有属性，属性确定了角色拥有的特权，并且在登录时与客户端认证系统进行交互。常见的角色属性包括：

登录特权，只有具有LOGIN属性的角色才能连接数据库。具有LOGIN角色的用户可以被看作一个“数据库用户”。使用以下语句创建具有登录特权的角色

CREATE ROLE name LOGIN;
CREATE USER name;

CREATE USER与CREATE ROLE都可以用于创建角色，只不过CREATE USER默认包含了LOGIN 选项，而CREATE ROLE没有。

超级用户，数据的超级用户可以避开所有的权限检查，只验证登录权限。因此；最好在日常的操作中避免使用超级用户。使用以下命令创建一个新的超级用户：

CREATE ROLE name SUPERUSER;

只有超级用户才有权限执行；

创建数据库，只有明确授权的角色才能够创建数据库（超级用户除外，因为他们可以避开权限检查）。使用以下语句创建一个具有数据库创建特权的角色：

CREATE ROLE name CREATEDB;

创建角色，只有明确授权的角色才能够创建其他角色（超级用户除外，因为他们可以避开权限检查）。使用以下命令创建一个具有角色创建特权的角色：

CREATE ROLE name CREATEROLE;

具有CREATEROLE特权的角色还可以修改或删除其他角色，以及为这些角色授予或者撤销成员角色。但是，针对超级用户的创建、修改、删除，以及它的成员变更，需要超级用户特权；CREATEROLE特权无法针对超级用户执行这些操作

启动复制，只有明确授权的角色才能够启动流复制（超级用户除外，因为他们可以避开权限检查）。用于流复制的角色还需要拥有LOGIN特权。使用以下语句创建可以用于流复制的角色；

REATE ROLE name REPLICATION LOGIN;

密码，只有当用户连接数据库使用的客户端认证方法要求提供密码时，密码属性才有意义。password和md5认证方法需要使用密码。数据库的密码与操作系统的密码相互独立。使用以下语句在创建角色时指定密码：

CREATE ROLE name PASSWORD 'string';

工作中创建角色时，可以根据需要指定某些属性。例如，以下命令创建一个具有登录特权和创建数据库的角色wdh，并且为它指定了密码以及密码过期时间：

CREATE ROLE wdh WITH LOGIN PASSWORD '123465' createdb VALID UNTIL '2060-12-31';
-- CREATE USER wdh WITH PASSWORD '123465' createdb VALID UNTIL '2060-12-31';

使用该用户连接到postgres数据库：

[root@hadoop201 ~]# psql -h hadoop201 -p 5432 -U wdh postgres
Password for user wdh: 
psql (12.13)
Type "help" for help.

postgres=> select 1 + 1;
 ?column? 
----------
        2

psql命令行工具支持许多选项，

-h表示数据库服务器的地址，
-p表示服务的监听端口，
-U表示登录使用的用户名，

最后的postgres代表要连接的数据库。详细的命令行参数可以使用psql --help查看或者参考官方文档。

以下命令创建一个管理角色admin，它具有创建数据库和创建角色的特权：

postgres=# CREATE ROLE admin CREATEDB CREATEROLE;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     | Create role, Create DB, Cannot login                       | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 wdh       | Create DB                                                 +| {}
           | Password valid until 2060-12-31 08:00:00+08                |

工作中最好创建一个拥有CREATEDB和CREATEROLE特权，但不具有超级用户特权的管理角色，然后使用该角色执行日常的数据库和角色的管理。这种方式可以避免过度使用超级用户可能带来的风险。一个角色被创建之后，可以通过ALTER ROLE语句修改它的属性。例如，以下命令可以撤销角色admin创建角色的特权：

一个角色被创建之后，可以通过ALTER ROLE语句修改它的属性。例如，以下命令可以撤销角色admin创建角色的特权

postgres=# ALTER ROLE admin NOCREATEROLE;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     | Create DB, Cannot login                                    | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 wdh       | Create DB                                                 +| {}
           | Pa