Postgresql学习笔记之——逻辑结构管理之用户、角色、模式_new_owner current_user session_user

最新推荐文章于 2024-05-16 07:43:13 发布

2301_82242990

最新推荐文章于 2024-05-16 07:43:13 发布

阅读量618

点赞数 16

分类专栏：程序员文章标签： postgresql 学习笔记

本文链接：https://blog.csdn.net/2301_82242990/article/details/138531179

版权

程序员专栏收录该内容

190 篇文章 1 订阅

订阅专栏

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

6.超级用户能够更改任何人的会话默认值。具有CREATEROLE特权的角色能够更改非超级用户的默认值。普通角色只能为它们自己设置默认值。某些配置变量不能以这种方式设置，或者只能由一个超级用户发出的命令设置。只有超级用户能够更改所有角色在所有数据库中的设置。

3.删除角色

语法：

DROP ROLE [ IF EXISTS ] name [, …]

参数解析：

1.IF EXISTS：如果角色存在就删除，不存在也不会报错。

2.name：需要被删除的角色名称。

示例：

删除角色：DROP ROLE jonathan;

PS:同样有一个程序dropuser，它具有与这个命令相同的功能(实际上，它调用这个命令)，但是可以从命令shell中运行。参考：https://www.postgresql.org/docs/12/app-dropuser.html

4.角色代表了权限

上面说到角色是一系列权限的集合，其中组成角色的权限分为了两类，一类是上述在 CREATE ROLE\USER 或者 ALTER ROLE 进行创建和i修改的。还有一类权限则需要使用 GRANT和REVOKE进行管理，比如：
1.在数据库中创建模式（schema）；
2.在指定的库中创建临时表；
3.链接某个数据库；
4.在指定的模式中创建数据库对象（public模式任何用户都可以创建）；
5.对一些指定的表或者表中指定的列进行DML操作；
6.对序列进行查询，使用、更新操作；
7.在声明表上创建触发器；
8.对数据对象进行迁移操作等。

GRANT语法：

1.让某个用户成为某个角色的成员，让其有整个角色的权限：

GRANT role_name[ , … ] TO role_name[ , … ] [ WITH ADMIN OPTION ]

2.把某些数据库逻辑结构的操作权限赋予某个用户或角色：

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

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

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

以上语法可以简写为：

GRANT some_privilege ON database_object_type object_name TO role_name;

其中的 ”some_privilege“ 表示在这个数据库对象中的权限， ”database_object_type“ 是数据库对象的类型，如 TABLE、sequence、schema等。

”some_privilege“ 主要包含一下权限：

SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE
TEMP
ALL PRIVILEGES

权限的示例

创建一个只读用户：
1.首先执行一下SQL：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

在Postgresql中任何用户默认都可以在名称为public的模式中创建表，单纯的只读用户不允许，与因此要收回这个权限。

2.创建一个名称为readonly的用户：

CREATE USER readonly WITH PASSWORD ‘query’;

3.然后将模式postgres下的所有表的SELECT权限授予只读用户readonly，执行如下SQL：

GRANT SELECT ON ALL TABLES IN SCHEMA postgres TO readonly;

注意：上面的授权SELECT的语句是将postgres中现有表的权限授予了readonly用户，如果后面有创建新的表，readonly还是没有查询权限的。这个问题可以通过下面的授权语句解决：

ALTER DEFAULT PRIVILEGES IN SCHEMA postgres GRANT SELECT ON TABLES TO readonly;

如果还想将其他模式的表的SELECT权限授予readonly用户，需要重复执行一下语句：

GRANT SELECT ON ALL TABLES IN SCHEMA other_schema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA other_schema GRANT SELECT ON TABLES TO readonly;

二、模式

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

1.在Postgresql中一个数据库可以包含多个模式，不同的数据库之间可以有相同名称的模式（schema）。

2.一个数据库连接不能同时访问不同数据库中的对象，需要访问另一个数据库中模式下的对象时需要从新指定链接中配置的数据库。

3.一个数据库链接可以访问同一个数据库下不同的模式（只要有相应的权限）。

1.模式的创建

语法：

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

其中 role_specification 可以是：

    user_name
  | CURRENT_USER
  | SESSION_USER

语法解析：

1.schema_name
要创建的一个模式名。如果省略， user_name将被用作模式名。该名称不能以pg_开始，因为这样的名称是用作系统模式的。

2.user_name
将拥有新模式的用户的角色名。如果省略，默认为执行该命令的用户。要创建由另一个角色拥有的角色，你必须是那个角色的一个直接或者间接成员，或者是一个超级用户。

3.schema_element
要在该模式中创建的对象的定义 SQL 语句。当前，只有CREATE TABLE、CREATE VIEW、CREATE INDEX、CREATE SEQUENCE、CREATE TRIGGER以及GRANT被接受为 CREATE SCHEMA中的子句。其他类型的对象可以在模式被创建之后用单独的命令创建。

4.IF NOT EXISTS
如果一个具有同名的模式已经存在，则什么也不做（不过发出一个提示）。使用这个选项时不能包括 schema_element子命令。

5.AUTHORIZATION role_specification
在创建模式时指定模式的owner。

注意：要创建一个模式，调用用户必须拥有当前数据库的CREATE 特权（当然，超级用户可以绕过这种检查）。

示例

1.创建一个模式：

CREATE SCHEMA myschema;

2.为用户joe创建一个模式，该模式也将被命名为 joe：

CREATE SCHEMA AUTHORIZATION joe;

3.创建一个被用户joe拥有的名为test的模式，除非已经有一个名为test的模式（不管joe 是否拥有该已经存在的模式）。

CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;

4.创建一个模式并且在其中创建一个表和视图：

CREATE SCHEMA hollywood
CREATE TABLE films (title text, release date, awards text[])
CREATE VIEW winners AS
SELECT title, release FROM films WHERE awards IS NOT NULL;

注意子命令不以分号结束。

下面是达到相同结果的等效的方法：

CREATE SCHEMA hollywood;
CREATE TABLE hollywood.films (title text, release date, awards text[]);
CREATE VIEW hollywood.winners AS
SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;

2.模式的修改

语法：

1.重命名模式：

ALTER SCHEMA name RENAME TO new_name;

2.更改模式的owner：

ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER };

注意：要使用ALTER SCHEMA，你必须拥有该模式。要重命名一个模式，你还必须拥有该数据库的CREATE特权。要更改拥有者，你还必须
是新拥有角色的一个直接或者间接成员，并且该角色必须具有该数据库上的 CREATE特权（注意超级用户自动拥有所有这些特权）。

3.删除模式

语法：

DROP SCHEMA [ IF EXISTS ] name [, …] [ CASCADE | RESTRICT ]

语法解析：

1.IF EXISTS
如果该模式不存在则不要抛出一个错误，而是发出一个提示。

2.name
一个模式的名称。

3.CASCADE
自动删除包含在该模式中的对象（表、函数等），然后删除所有依赖于那些对象的对象（见第 5.13 节）。

4.RESTRICT
如果该模式含有任何对象，则拒绝删除它。这是默认值。

注意：使用CASCADE选项可能会使这条命令移除除
指定模式之外其他模式中的对象。因为其他模式中的对象依赖于被删除的模式中的对象，会被级联删除。

4.模式中对象的访问

在创建数据库时Postgresql会在数据库中默认创建名称为 public 的模式，任何用户链接到数据库中都可以在public模式下创建表或者试图等对象，

1.public模式下的表可以直接访问。

postgres=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | tb_public | table | postgres
(1 row)

postgres=# select \* from tb_public ;
 id | name 
----+------
(0 rows)

2.非public模式下的表的访问方式时：

# 创建mytest模式下的表tb_mytest
postgres=# create table mytest.tb_mytest (id int,name text);
CREATE TABLE

# \d 默认只能查看public模式下的表
postgres=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | tb_public | table | postgres
(1 row)

# 查看指定模式mytest下所有表即相关信息
postgres=# \d mytest.\*
             Table "mytest.tb\_mytest"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 name   | text    |           |          | 

# 查询如果没有加模式名前缀会提示表不存在
postgres=# select \* from tb_mytest;
ERROR:  relation "tb\_mytest" does not exist
LINE 1: select \* from tb_mytest;

# 查询指定模式（非public）下表的方式：                 ^
postgres=# select \* from mytest.tb_mytest ;
 id | name 
----+------
(0 rows)

模式的搜索路径

访问数据库对象时，虽然可以通过加模式名前缀这样全称的方式访问，但是这样做程序中就需要输入每个模式的名称，这很繁琐也不安全，因此，Postgresql提供了一种类似于Linux系统中$PATH这样的环境变量的方式进行快捷访问——模式搜索路径。

数据库执行命令：

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

第一个元素说明一个和当前用户同名的模式会被搜索。如果不存在这个模式，该项将被忽略。第二个元素指向我们已经见过的公共模式。

搜索路径中的第一个模式是创建新对象的默认存储位置。这就是默认情况下对象会被创建在公共模式中的原因。当对象在任何其他没有模式限定的环境中被引用（表修改、数据修改或查询命令）时，搜索路径将被遍历直到一个匹配对象被找到。因此，在默认配置中，任何非限定访问将只能指向公共模式。

如果你想修改它默认的搜索和创建路径，可以：

# 将模式mytest加到默认路径中
postgres=# set search_path to mytest,public;

# 再次查询mytest模式下的表时就不需要加上模式名前缀了
postgres=# select \* from tb_mytest ;
 id | name 
----+------
(0 rows)

# 在使用\d命令时除了显示pubcli模式下对象，也会显示mytest下相关对象
postgres=# \d


![img](https://i-blog.csdnimg.cn/blog_migrate/eb5a6a66a9cc8396f05730c91aa9f2ab.png)
![img](https://img-blog.csdnimg.cn/img_convert/0984f5d90659eb5f77820d8c313ff081.png)

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化的资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618608311)**

**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

ublic;

# 再次查询mytest模式下的表时就不需要加上模式名前缀了
postgres=# select \* from tb_mytest ;
 id | name 
----+------
(0 rows)

# 在使用\d命令时除了显示pubcli模式下对象，也会显示mytest下相关对象
postgres=# \d


[外链图片转存中...(img-LczmM7vH-1715063459213)]
[外链图片转存中...(img-Xq5op3pL-1715063459213)]

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化的资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618608311)**

**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**