侧边栏壁纸
博主头像
极客日记 博主等级

行动起来，活在当下

  • 累计撰写 93 篇文章
  • 累计创建 17 个标签
  • 累计收到 1 条评论

目 录CONTENT

文章目录
PostgreSQL

PostgreSQL 学习笔记 - PG模式与用户管理

Jack.Jia
Jack.Jia
2022-12-07 / 0 评论 / 0 点赞 / 10 阅读 / 0 字

模式管理

模式概念

模式（schema）可以理解为一个命名空间或目录。不同的模式下可以有相同名称的表、函数等对象且互相不冲突，只要有权限，每个模式（schema）的对象可以互相调用。

在 PostgreSQL 中，一个数据库包含一个或多个模式，模式中又包含了表、函数及操作符等数据库对象。创建或者访问模式中的对象，需要加上模式名：schema_name.table_name

通常情况下，在不指定模式名的情况下创建和访问表，访问的都是”public”模式。在创建一个新的数据库时，PostgreSQL 都会自动创建一个名为”public”的模式。当登录到该数据库时，如果没有特殊的指定，都是以该模式（public）操作各种数据对象的。

PostgreSQL 中提供了模式搜索路径，有些类似于 Linux 中的 PATH 环境变量，只有在该命令位于 $PATH 的目录列表中时，才可以通过命令名直接执行，否则就需要输入它的全路径名。

常看当前搜索路径∶

postgres=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

这里显示的是 search_path 的默认配置，一般情况下，若创建的表默认没有指定模式，那都会在”public”模式下。在 psql 中使用“/d”命令总是可以把“public”模式下的表显示出来。

修改模式搜索路径:

SET search_path TO myschema,public;

模式操作示例

官网的链接如下：
https://www.postgresql.org/docs/current/sql-createschema.html
https://www.postgresql.org/docs/current/sql-alterschema.html
https://www.postgresql.org/docs/current/sql-dropschema.html

创建语法：

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_ROLE
  | CURRENT_USER
  | SESSION_USER

创建模式∶

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

postgres=# create schema cndba;
CREATE SCHEMA
postgres=# /dn
  List of schemas
  Name  |  Owner
--------+----------
 cndba  | postgres
 public | postgres
(2 rows)

postgres=#

这里创建的模式是 postgres 这个用户的，也可以为指定的用户创建 schema：

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

postgres=# create schema dave AUTHORIZATION dave;
CREATE SCHEMA
postgres=# /dn
  List of schemas
  Name  |  Owner
--------+----------
 cndba  | postgres
 dave   | dave
 public | postgres
(3 rows)

postgres=#

修改模式的名称和属主如下∶

postgres=# /dn
  List of schemas
  Name  |  Owner
--------+----------
 cndba  | postgres
 dave   | dave
 public | postgres
(3 rows)

postgres=# alter schema dave owner to postgres;
ALTER SCHEMA
postgres=# /dn
  List of schemas
  Name  |  Owner
--------+----------
 cndba  | postgres
 dave   | postgres
 public | postgres
(3 rows)

postgres=# alter schema dave rename to ustc;
ALTER SCHEMA
postgres=# /dn
  List of schemas
  Name  |  Owner
--------+----------
 cndba  | postgres
 public | postgres
 ustc   | postgres
(3 rows)

postgres=#

删除模式∶

postgres=# /dn
  List of schemas
  Name  |  Owner
--------+----------
 cndba  | postgres
 public | postgres
 ustc   | postgres
(3 rows)

postgres=# drop schema ustc;
DROP SCHEMA
postgres-# /dn
  List of schemas
  Name  |  Owner
--------+----------
 cndba  | postgres
 public | postgres
(2 rows)

postgres-#

模式权限

默认情况下，用户无法访问模式中不属于他们的对象。若要访问，模式的所有者必须在模式上赋予他们“USAGE”权限。

用户也可以在其他用户的模式里创建对象，这需要被赋予了在该模式上的”CREATE”权限。

默认情况下每个用户在”public”模式上都有”CREATE”和”USAGE”权限，也就是说允许所有可以连接到指定数据库上的用户在这里创建对象。

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] } 
ON SCHEMA schema_name [, ...] 
TO role_specification [, ...] [ WITH GRANT OPTION ] 
[ GRANTED BY role_specification ]

REVOKE [ GRANT OPTION FOR ] 
{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] } 
ON SCHEMA schema_name [, ...] 
FROM role_specification [, ...] 
[ GRANTED BY role_specification ] 
[ CASCADE | RESTRICT ]

可以撤销这个权限，命令如下∶

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE USAGE ON SCHEMA public FROM PUBLIC;

第一个”public”是模式的名称，第二个”PUBLIC”是一个特殊的角色，代表着所有用户。

用户管理

用户和角色说明

PostgreSQL 使用角色的概念管理数据库访问权限。角色是一系列相关权限的集合。在 PostgreSQL 中，角色与用户是没有区别的，一个用户也是一个角色，我们可以把一个用户的权限赋给另一个用户。

用户和角色在整个数据库实例中都是全局的，在初始化数据库系统时，有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。如果数据库是建在操作系统用户”postgres”下的，这个数据库超级用户的名称也会叫“postgres”。

PG 中每个数据库的逻辑结构对象（包括数据库）都有一个所有者，也就是说任何数据库对象都是属于某个用户的，用户的权限分为两类，一类是在创建用户时就指定的权限，这些权限如下∶

  • 超级用户的权限
  • 创建数据库的权限
  • 是否允许 LOGIN 的权限
    这些权限是创建用户时指定的，后面可使用 ALTER ROLE 命令来修改。
    还有一类权限，是由命令 GRANT 和 REVOKE 来管理的，这些权限如下∶
  • 在数据库中创建模式（SCHEMA）
  • 允许在指定的数据库中创建临时表
  • 连接某个数据库
  • 在模式中创建数据库对象，如创建表、视图、函数等
  • 在一些表中做 SELECT、UPDATE、INSERT、DELETE 等操作
  • 在一张表的具体列上进行 SELECT、UPDATE、INSERT 操作
  • 对序列进行查询（执行序列的 currval 函数）、使用（执行序列的 currval 函数和 nextval 函数）、更新等操作
  • 在声明表上创建触发器
  • 可以把表、索引等建到指定的表空间

https://www.postgresql.org/docs/current/sql-grant.html

创建用户和角色

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

CREATE ROLE name [ [ WITH ] option [ ... ] ]

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

在 PostgreSQL 中，用户与角色是没有区别的。除了“CREATEUSER”默认创建出来的用户有“LOGIN”的权限，而“CREATEROLE”创建出来的用户没有“LOGIN”的权限之外，”CREATERULE”与”CREATE USER”没有其他任何的区别。
上面语法中的”option”的内容：

  • SUPERUSER|NOSUPERUSER∶表示创建出来的用户是否为超级用户。只有超级用户才能创建超级用户。
  • CREATEDB|NOCREATEDB∶指定创建出来的用户是否有执行”CREATEDATABASE”的权限。
  • CREATEROLE|NOCREATEROLE∶指定创建出来的用户是否有创建其他角色的权限。CREATEUSER|NOCREATEUSER∶指定创建出来的用户是否有创建其他用户的权限。INHERIT|NOINHERIT∶如果创建的一个用户拥有某一个或某几个角色，这时若指定 INHERIT，则表示用户自动拥有相应角色的权限，否则这个用户没有该角色的权限。
  • LOGIN|NOLOGIN∶指定创建出来的用户是否有”LOGIN”的权限，可以临时地禁止一个用户的”LOGIN”权限，这时这个用户就不能连接到数据库了。
  • CONNECTION LIMIT connlimit∶指定该用户可以使用的并发连接数量。默认值是 -1，表示没有限制。
  • [ENCRYPTED|UNENCRYPTED]PASSWORD ‘password’∶用于控制存储在系统表里面的口令是否加密。
  • VALID UNTIL ‘timestamp’∶密码失效时间，如果不指定这个子句，那么口令将永远有效。
  • IN ROLE role_name∶指定用户成为哪些角色的成员，请注意没有任何选项可以
  • 把新角色添加为管理员，必须使用独立的 GRANT 命令来做这件事情。
  • IN GROUP role_name∶与 IN ROLE 相同，是已过时的语法。
  • ROLE role_name∶role_name 将成为这个新建的角色的成员。
  • ADMIN role_name∶role_name 将有这个新建角色的 WITH ADMIN OPTION 权限。USER role_name∶与 ROLE 子句相同，但已过时。
  • SYSID uid∶此子句主要是为了 SQL 向下兼容，实际没有什么用处。

    权限操作示例

    PostgreSQL 中的权限有如下几个层次∶

    1) 首先管理赋在用户特殊属性上的权限，如超级用户的权限、创建数据库的权限、创建用户的权限、Login 的权限，等等。
    2) 然后是在数据库中创建模式的权限。
    3) 接着是在模式中创建数据库对象的权限，如创建表、创建索引，等等。
    4) 之后是查询表、往表中插入数据、更新表、删除表中数据的权限。
    5) 最后是操作表中某些字段的权限。

1. 修改超级管理员 postgres 密码：

-bash-4.2$ psql
psql (14.6)
Type "help" for help.

postgres=# alter user postgres with password 'postgres';
ALTER ROLE
postgres=#
-bash-4.2$ psql -U postgres -h localhost
Password for user postgres:
psql (14.6)
Type "help" for help.

postgres=# /conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".
postgres=#

2. 创建用户：

postgres=# CREATE USER cndba WITH password 'cndba';
CREATE ROLE

3. 创建属主为 cndba 的库 ustc:

postgres=# CREATE DATABASE ustc OWNER cndba;
CREATE DATABASE
postgres=# /l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 cndba     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 ustc      | cndba    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/cndba            +
           |          |          |             |             | cndba=CTc/cndba
(5 rows)

postgres=#

4. 回收 public 权限（每个库的模式是独立的，操作时要连上对应的数据库）

在初始化数据库实例后，数据库中默认就会存在一个名称为“public”的 schema，任何用户都有在 public schema 上的 create 权限，如果业务没有单独创建 scheme，那么权限 public schema 的权限就比较大，所以通常我们需要把这个权限回收回来。

-bash-4.2$ psql -d ustc -U postgres
psql (14.6)
Type "help" for help.

ustc=# revoke create on schema public from public;
REVOKE
ustc=#

5. 为 cndba 用户赋予 ustc 库的所有权限：

-bash-4.2$ psql -d ustc -U postgres
psql (14.6)
Type "help" for help.
postgres=# GRANT all ON  DATABASE ustc TO cndba;
GRANT

6. 创建业务用户，并赋予 public 模式下增删改查的权限：

-bash-4.2$ psql -d ustc -U postgres
psql (14.6)
Type "help" for help.
postgres=# CREATE USER app WITH password 'cndba';
CREATE ROLE
postgres=# GRANT  update,delete,insert,select  ON  ALL TABLES IN SCHEMA public TO app;
GRANT

-bash-4.2$ psql -d ustc -U app -h localhost
Password for user app:
psql (14.6)
Type "help" for help.

ustc=> create table t3(id int);
ERROR:  permission denied for schema public
LINE 1: create table t3(id int);
                     ^
ustc=>

当然这里也可以单独创建一个模式来存放业务对象。

7. 创建只读用户（连上对应的数据库后操作）

# 回收public模式的创建权限
REVOKE CREATE ON SCHEMA public from public;
# 创建只读用户 readonly，密码为 readonly
CREATE USER readonly PASSWORD 'readonly';
# 授权 public 模式给 readonly 用户
GRANT USAGE ON SCHEMA public TO readonly;
# 授权 public 模式的所有表查询权限给 readonly 用户
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
# 将 public 模式的默认表查询权限授予 readonly 用户（关键）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;  

# 切换到 readonly 用户进行测试
-bash-4.2$ psql -d ustc -U readonly -h localhost
Password for user readonly:
psql (14.6)
Type "help" for help.

ustc=> create table t4(id int);
ERROR:  permission denied for schema public
LINE 1: create table t4(id int);
                     ^
ustc=>

8. 禁用用户

# nologin方式：禁止用户登录
ustc=# alter user readonly nologin;
ALTER ROLE
# 限制连接数为0：限制用户连接
ustc=# alter user readonly connection limit 0;
ALTER ROLE
# 修改有效期：用户密码的过期时间
ustc=# alter user readonly valid until '2022-12-08';
ALTER ROLE

9. 查看用户权限

#查看某用户的系统权限
SELECT * FROM  pg_roles WHERE rolname='postgres';

#查看某用户的表级别权限
select * from information_schema.table_privileges where grantee='postgres';

#查看某用户的usage权限
select * from information_schema.usage_privileges where grantee='postgres';

#查看某用户在存储过程函数的执行权限
select * from information_schema.routine_privileges where grantee='postgres'; 

#查看某用户在某表的列上的权限
select * from information_schema.column_privileges where grantee='postgres'; 

#查看当前用户能够访问的数据类型
select * from information_schema.data_type_privileges ; 

#查看用户自定义类型上授予的USAGE权限
select * from information_schema.udt_privileges where grantee='postgres';
0
PostgreSQL
版权归属： Jack.Jia
本文链接： https://www.explorexd.com/archives/9e122cbe-babc-4c4e-8c25-43c6d232948b
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区