文章目录

PostgreSQL数据库中的角色(Role)、用户（User）、模式（Schema）
- 角色(Role)和用户（User）
- 角色操作
- - CREATE ROLE 创建角色
  - ALTER ROLE修改角色属性
  - DROP ROLE删除属性
  - GRANT赋予权限
  - REVOKE移除权限
  - CREATE USER创建用户
  - SET ROLE设置角色
- 模式（Schema）
- 模式（Schema）的操作
- - CREATE SCHEMA创建模式
  - ALTER SCHEMA 修改模式
  - DROP SCHEMA删除模式
- 小结

【免责声明】文章仅供学习交流，观点代表个人，与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)

PostgreSQL数据库中的角色(Role)、用户（User）、模式（Schema）

角色(Role)和用户（User）

PostgreSQL中使用“角色”的概念管理数据库访问权限，用户表示“拥有LOGIN权限的角色”。
CREATE USER和CREATE ROLE命令都用于定义一个新的数据库角色，唯一的区别是 CREATE USER中LOGIN 被作为默认值，而NOLOGIN是 CREATE ROLE的默认值。

参考：

https://www.postgresql.org/docs/14/sql-createuser.html
https://www.postgresql.org/docs/14/sql-createrole.html

一个角色是一个实体，它可以拥有数据库对象（例如，表和函数）并且拥有某些数据库特权；也可以把对象上的权限或者成员资格赋予给其他角色来控制谁能访问哪些对象（可以被继承）；并且数据库角色在一个数据库集簇（Cluster）安装范围内是全局的（实例级别，非某个数据库内）。

数据库初始创建后，会创建一个预定义特权角色（superuser）：postgres用于连接和访问等操作。

例：查看角色

postgres-# \set ECHO_HIDDEN on
postgres-# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************

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

postgres-#

例：查看用户

postgres-# \du
********* QUERY **********
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
**************************

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

角色操作

角色相关操作命令主要包括如下

CREATE ROLE
ALTER ROLE
DROP ROLE
GRANT 
REVOKE
CREATE USER
SET ROLE

CREATE ROLE 创建角色

例：

postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# create role role1;
CREATE ROLE
postgres=# create role role2 with password 'pass';
CREATE ROLE
postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     | Cannot login                                               | {}

ALTER ROLE修改角色属性

例：

postgres=# alter role role2 with login;
ALTER ROLE
postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}

postgres=#

修改登录属性后该角色可以登录。

ubuntu@pg-vm:~$ psql -U role2 -h localhost -d postgres
Password for user role2:
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

DROP ROLE删除属性

例：

postgres=# create role role3;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}
 role3     | Cannot login                                               | {}

postgres=# drop role role3;
DROP ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}

postgres=#

GRANT赋予权限

例：

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}

postgres=# grant postgres to role2;
GRANT ROLE
postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {postgres}

REVOKE移除权限

例：

postgres=# revoke postgres from role2;
REVOKE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}

CREATE USER创建用户

例：

postgres=# create user user1 with password 'pass';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}
 user1     |                                                            | {}

SET ROLE设置角色

可以通过 SET ROLE设置当前会话的当前用户标识符，即控制使用的角色。

postgres=> \du
                                       List of roles
 Role name |                         Attributes                         |    Member of
-----------+------------------------------------------------------------+------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role1     | Cannot login                                               | {}
 role2     |                                                            | {}
 user1     |                                                            | {postgres,role2}

postgres=> SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user
--------------+--------------
 user1        | user1
(1 row)

postgres=> set role role2;
SET
postgres=> SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user
--------------+--------------
 user1        | role2
(1 row)

postgres=> drop user role1;
ERROR:  permission denied to drop role
postgres=> set role postgres;
SET
postgres=# SELECT SESSION_USER, CURRENT_USER;
 session_user | current_user
--------------+--------------
 user1        | postgres
(1 row)

postgres=# drop user role1;
DROP ROLE
postgres=# \du
                                       List of roles
 Role name |                         Attributes                         |    Member of
-----------+------------------------------------------------------------+------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 role2     |                                                            | {}
 user1     |                                                            | {postgres,role2}

postgres=#

模式（Schema）

模式（Schema）本质上是一个名字空间，用于存放数据库中的逻辑对象（如表、视图、函数、物化视图等），不同的模式下可以有相同名称的表、函数等对象且互相不冲突，提出模式（schema）的概念主要是为了便于管理，只要有权限，每个模式中的对象可以互相调用。

可以通过用模式名作为一个前缀 “限定”命名对象的名称来访问它们，或者通过把要求的模式包括在搜索路径中来访问命名对象。

一个数据库集簇（Cluster）可以包括多个数据库，而一个数据库中包括一个或多个模式，每个模式中可以包括多个数据库对象。

模式（Schema）的操作

在Oracle中每一个用户都会对应一个同名Schema，创建了用户便拥有了同名的Schema用于存储相关的数据库对象。
在PostgreSQL中则不同，用户（角色）和模式（Schema）是分别管理的。

模式（Schema）相关操作命令主要包括如下：

CREATE SCHEMA 
ALTER SCHEMA
DROP SCHEMA

CREATE SCHEMA创建模式

通过CREATE SCHEMA可以创建一个新的模式。

postgres-# \h create schema
Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:

    user_name
  | CURRENT_USER
  | SESSION_USER

URL: https://www.postgresql.org/docs/12/sql-createschema.html

例1：创建一个新模式

postgres=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

postgres=# CREATE SCHEMA myschema;
CREATE SCHEMA

postgres=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | postgres
 public   | postgres
(2 rows)

例2：为某个用户创建一个模式，模式名同用户名

postgres=# CREATE SCHEMA AUTHORIZATION user1;
CREATE SCHEMA

postgres=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | postgres
 public   | postgres
 user1    | user1
(3 rows)

ALTER SCHEMA 修改模式

可以通过ALTER SCHEMA修改模式定义。

例1：修改模式名

postgres=# alter schema user1 rename to user2;
ALTER SCHEMA
postgres=# \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | postgres
 public   | postgres
 user2    | user1
(3 rows)

例2：修改模式owner

postgres=> \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | postgres
 public   | postgres
 user2    | user1
(3 rows)

postgres=> alter schema myschema owner to user1;
ALTER SCHEMA
postgres=> \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | user1
 public   | postgres
 user2    | user1
(3 rows)

DROP SCHEMA删除模式

可以通过DROP SCHEMA删除模式及其中的数据库对象。

例1：删除空Schema

postgres=> drop schema user2;
DROP SCHEMA
postgres=> \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | user1
 public   | postgres
(2 rows)

例2：删除Schema及其中数据库对象

postgres=> create schema test1;
CREATE SCHEMA
postgres=> \dn
   List of schemas
   Name   |  Owner
----------+----------
 myschema | user1
 public   | postgres
 test1    | user1
(3 rows)

postgres=> create table test1.t1(a int);
CREATE TABLE

postgres=> drop schema test1;
ERROR:  cannot drop schema test1 because other objects depend on it
DETAIL:  table test1.t1 depends on schema test1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

postgres=> drop schema test1 cascade;
NOTICE:  drop cascades to table test1.t1
DROP SCHEMA
postgres=>

小结

简单而言，用户（角色）主要用于权限管理，模式用于数据库对象的管理。

参考：
https://www.postgresql.org/docs/14/sql-createuser.html
https://www.postgresql.org/docs/14/sql-createrole.html
http://www.postgresql.org/docs/14/sql-createschema.html

http://www.postgres.cn/docs/14/sql-createuser.html
http://www.postgres.cn/docs/14/sql-createrole.html
http://www.postgres.cn/docs/14/sql-createschema.html

https://www.postgresql.org/docs/14/ddl-schemas.html
http://www.postgres.cn/docs/14/ddl-schemas.html

https://www.postgresql.org/docs/14/manage-ag-overview.html