用户、角色和模式

一、用户和角色

1. 用户

在PostgreSQL中，用户是指具有个人身份的数据库账户。每个用户都有一个唯一的名称和密码，用于登录数据库并执行操作。用户可以被授予对数据库的读写权限，可以创建、修改和删除数据库对象。用户还可以在数据库中创建和管理角色。用户具有一些特殊权限，例如创建数据库、备份和恢复数据库、更改全局设置等。用户还可以被赋予超级用户权限，这使得他们可以执行高级操作，如更改其他用户的权限、修改系统表、安装扩展等。超级用户应当谨慎使用其权限，以避免对数据库的意外破坏。

在初始化数据库系统时，有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。如果数据库是建在操作系统用户postgres（通常把数据库安装在此用户下）下的，这个数据库超级用户的名称也会叫“postgres”。可以用这个超级用户连接数据库，然后创建出更多的用户。

2. 角色

在PostgreSQL中，角色是一种更通用的数据库对象，用于管理和授权访问数据库的主体。角色可以用于授权用户访问特定的数据库、模式或表，可以被赋予一些或全部的数据库的权限。角色是一系列相关权限的集合。为了管理方便，通常会把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权 限，就把角色赋给相应的用户。

角色有两种类型：登录角色和非登录角色。登录角色具有用户功能，可以登录数据库并执行操作。非登录角色仅用于授权和权限管理，它们不能直接登录数据库。

与用户不同，角色不具有特殊权限。默认情况下，创建的角色没有任何权限。需要通过授予角色权限的方式来设置其访问权限。例如，可以将角色授权为数据库的所有者，使其能够对数据库中的所有对象进行完全控制。

3. 用户与角色的区别

用户和角色在 PostgreSQL 中的区别主要有以下几点：

1. 用户是具有个人身份和登录能力的数据库账户，而角色更通用，用于授权和权限管理。
2. 用户可以直接登录数据库执行操作，而非登录角色仅用于授权和权限管理。
3. 用户拥有登录数据库和一些特殊权限，如超级用户权限，而角色默认没有特殊权限。
4. 用户可以创建、修改和删除数据库对象，而角色无法直接执行这些操作，必须通过用户的授权来实现。

二、用户管理

1. 创建用户

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

postgres=# CREATE USER u1 SUPERUSER PASSWORD 'u1'; --创建一个具有超级用户权限的用户
CREATE ROLE
postgres=# CREATE USER u2 CREATEDB PASSWORD 'u2';  --创建一个具有建库权限的用户
CREATE ROLE
postgres=# CREATE USER u3 LOGIN; --创建一个具有登录权限的用户，默认值
CREATE ROLE
postgres=# CREATE USER u4 encrypted PASSWORD '123456' VALID UNTIL '2025-01-01'; --创建一个带有加密密码且具有有效时间的用户
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u1        | Superuser                                                  | {}
 u2        | Create DB                                                  | {}
 u3        |                                                            | {}
 u4        | Password valid until 2025-01-01 00:00:00+08                | {}

查看用户权限

postgres=# select * from pg_user where usename = 'u1';
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 u1      |    16395 | f           | t        | f       | f            | ******** |          | 
(1 row)

2. 创建角色

postgres=# CREATE ROLE r1; --创建一个角色
CREATE ROLE
postgres=# CREATE ROLE r2 createdb;    --创建一个具有建库权限的角色
CREATE ROLE
postgres=# CREATE ROLE r3 LOGIN; --创建一个具有登录权限的角色，
CREATE ROLE
postgres=# CREATE ROLE r4 encrypted PASSWORD '1234561'  VALID UNTIL '2025-01-01'; --创建一个带有加密密码且具有有效时间的角色
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 r1        | Cannot login                                               | {}
 r2        | Create DB, Cannot login                                    | {}
 r3        |                                                            | {}
 r4        | Cannot login                                              +| {}
           | Password valid until 2025-01-01 00:00:00+08                | 
 u1        | Superuser                                                  | {}
 u2        | Create DB                                                  | {}
 u3        |                                                            | {}
 u4        | Password valid until 2025-01-01 00:00:00+08                | {}

查看角色权限

postgres=# select * from pg_roles where rolname = 'r1';
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid  
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 r1      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 16400
(1 row)

3. 修改用户属性

ALTER USER u2 RENAME TO u2; --修改用户的名字
ALTER USER u22 PASSWORD u22;  --修改用户的密码
ALTER USER u22 CREATEROLE; --修改用户的权限
ALTER USER u22 IN DATABASE testdb RESET ALL --修改数据库testdb中的参数重设为默认值

4. 删除用户

drop user u1;

注意事项：

1. 只用超级用户能够删除超级用户
2. 只有具有createrole权限的用户能删除非超级用户
3. 删除用户前，需要先删除依赖该用户的对象、权限等信息
4. 任何属于该组角色的对象都必须先被删除或者将对象的所有者赋予其它角色，任何赋予该组角色的权限也都必须被撤消
5. 删除组role只会删除组的role本身，组的成员并不会被删除

三、schema

1. 什么是schema（模式）

1. 用户对象的集合叫做模式。比如：scott用户下有表emp、dept、salgrade等，还有几个索引，还有一些约束等等，那么scott用户下所有对象的集合就叫scott模式。

2. 可以把用户下拥有的对象根据业务分类，不同的对象存放在不同的模式下。

3. 新建的数据库默认会创建不同的模式来管理对象。比如：information_schema、pg_catalog、 pg_temp_1、pg_toast、pg_toast_temp_1、public等。

4. 不同的schema下可以有相同名字的表、函数等对象，互相之间是不冲突的。只要有权限，每个schema的对象是可以互相调用的。

posgresql中的默认模式

postgres=# \dnS+
                                     List of schemas
        Name        |  Owner   |  Access privileges   |           Description            
--------------------+----------+----------------------+----------------------------------
 information_schema | postgres | postgres=UC/postgres+| 
                    |          | =U/postgres          | 
 pg_catalog         | postgres | postgres=UC/postgres+| system catalog schema
                    |          | =U/postgres          | 
 pg_temp_1          | postgres |                      | 
 pg_toast           | postgres |                      | reserved schema for TOAST tables
 pg_toast_temp_1    | postgres |                      | 
 public             | postgres | postgres=UC/postgres+| standard public schema
                    |          | =UC/postgres         | 
(6 rows)

2. 用户与模式的对应关系

1. 一个用户可以创建与拥有多个模式。
2. 一个模式只能属手一个用户
3. 普通用户创建模式时需要授权在指定的数据库下创建模式的权限

3. 模式管理

# 创建用户和数据库
$ psql
psql (12.13)
Type "help" for help.
# 
postgres=# create user tpcc password 'tpcc';
CREATE ROLE
postgres=# create database tpcc;
CREATE DATABASE
postgres=# \q
# 没有赋权，没有创建schema权限
$ psql -U tpcc -d tpcc
psql (12.13)
Type "help" for help.

tpcc=> create schema benchmark;
ERROR:  permission denied for database tpcc
tpcc=> \q
# 赋权
$ psql
psql (12.13)
Type "help" for help.

postgres=# grant create on database tpcc to tpcc;
GRANT
postgres=# \q
# 再次创建schema
$ psql -U tpcc -d tpcc
psql (12.13)
Type "help" for help.

tpcc=> create schema benchmark;
CREATE SCHEMA
tpcc=> \dn
   List of schemas
   Name    |  Owner   
-----------+----------
 benchmark | tpcc
 public    | postgres
(2 rows)

授权某个模式下的对象访问权限给其它用户，则需要先授权访问该模式的权限给其它用户。

GRANT USAGE ON SHEMA sch_name TO role_name;
GRANT SELECT ON sch_name.tab_name TO role_name;

删除模式，模式只能由其拥有者或超级用户删除

drop schema schema name;

删除模式时同时删除所依赖的对象

sql drop schema chema name cascade;

4. public模式

初始化数据库时会电动创建一个public模式共享给所有用户使用，任何用户都可以把对象创建在该模式下，由于PostgreSQL下一个用户对应多个模式，创建用户时没有创建对应的默认模式，而对象都是基于模式管理的，所以创建一个公用的模式public。 基于数据库安全考虑，可以删除public模式（可选）。

5. 搜索路径

查看搜索路径

tpcc=> show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

设置搜索路径

set search_path='Suser',public,scott

如果在前面的搜索路径中找到对象，则会放弃后面模式的搜索