【PostgreSQL】从零开始:（十三）PostgreSQL-SQL语句操作架构（模式） Schema

Schema概述

PostgreSQL 数据库集群包含一个或多个命名数据库。角色和一些其他对象类型在整个集群中共享。与服务器的客户端连接只能访问单个数据库中的数据，该数据库在连接请求中指定。

用户不一定有权访问集群中的每个数据库。共享角色名称意味着不能在同一集群中的两个数据库中命名不同的角色;但是可以将系统配置为仅允许访问某些数据库。

数据库包含一个或多个命名架构，而这些架构又包含表。架构还包含其他类型的命名对象，包括数据类型、函数和运算符。相同的对象名称可以在不同的架构中使用而不会发生冲突;例如，both 和 可以包含名为 的表。与数据库不同，架构不是严格分离的：如果用户具有访问权限，则可以访问他们所连接到的数据库中任何架构中的对象。

可能想要使用架构的原因有以下几个：

• 允许多个用户使用一个数据库而不会相互干扰。
• 将数据库对象组织到逻辑组中，使其更易于管理。
• 第三方应用程序可以放入单独的架构中，以便它们不会与其他对象的名称发生冲突。
  架构类似于操作系统级别的目录，只是架构不能嵌套。

若要在架构中创建或访问对象，请编写一个由架构名称和表名称组成的限定名称，并用点分隔：

schema.table

这适用于需要表名的任何位置，包括表修改命令和以下章节中讨论的数据访问命令。（为简洁起见，我们只讨论表，但同样的想法也适用于其他类型的命名对象，例如类型和函数。
实际上，更通用的语法

database.schema.table

也可以使用，但目前这只是为了形式上符合 SQL 标准。如果写入数据库名称，则该名称必须与连接到的数据库相同。

因此，若要在新架构中创建表，请使用：

CREATE TABLE myschema.mytable (
 ...
);

创建Schema

命令

postgres=# \help 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_ROLE
  | CURRENT_USER
  | SESSION_USER

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

postgres=# 

测试

postgres=# \c ci_database_test02 circledba; #切换到ci_database_test02数据库下 用户名为circledba
Password for user circledba: 
You are now connected to database "ci_database_test02" as user "circledba".
ci_database_test02=> create schema circle_oa; # 创建模式circle_oa
CREATE SCHEMA
ci_database_test02=> create schema circle_center;# 创建模式circle_center
CREATE SCHEMA
ci_database_test02=> create schema circle_shop;# 创建模式circle_shop
CREATE SCHEMA
ci_database_test02=> \dn # 查看模式
          List of schemas
     Name      |       Owner       
---------------+-------------------
 circle_center | circledba
 circle_oa     | circledba
 circle_shop   | circledba
 public        | pg_database_owner
(4 rows)

ci_database_test02=> 

再从pgAdmin看看

其中public是创建数据库时候自带的。

通常，您需要创建其他人拥有的架构（因为这是将用户的活动限制在定义明确的命名空间中的方法之一）。其语法为：

CREATE SCHEMA schema_name AUTHORIZATION user_name;

您甚至可以省略架构名称，在这种情况下，架构名称将与用户名相同。

以pg_开头的架构名称保留用于系统目的，用户无法创建。

公共模式

在前面的部分中，我们在未指定任何架构名称的情况下创建了表。默认情况下，此类表（和其他对象）会自动放入名为“public”的架构中。每个新数据库都包含这样的架构。因此，以下内容是等效的：

CREATE TABLE products ( ... );
CREATE TABLE public.products ( ... );

查看用户Schema

限定名称的编写起来很繁琐，通常最好不要将特定的架构名称连接到应用程序中。因此，表通常由非限定名称引用，这些名称仅由表名称组成。系统通过遵循搜索路径（要查找的架构列表）来确定哪个表。搜索路径中的第一个匹配表被视为所需的表。如果搜索路径中没有匹配项，则会报告错误，即使数据库中的其他架构中存在匹配的表名也是如此。

在不同架构中创建同名对象的能力使编写每次都引用完全相同对象的查询变得复杂。它还为用户提供了恶意或意外更改其他用户查询行为的可能性。由于查询中普遍存在非限定名称及其在 PostgreSQL 内部中的使用，因此添加架构以有效地信任对该架构具有权限的所有用户。当您运行普通查询时，能够在搜索路径的架构中创建对象的恶意用户可以控制并执行任意 SQL 函数，就像您执行了它们一样。search_pathCREATE

搜索路径中命名的第一个架构称为当前架构。除了是第一个搜索的架构之外，它还是在命令未指定架构名称时将在其中创建新表的架构。CREATE TABLE

若要显示当前搜索路径，请使用以下命令：

SHOW search_path;

在默认设置中，这将返回：

 search_path
--------------
 "$user", public

第一个元素指定要搜索与当前用户同名的架构。如果不存在此类架构，则忽略该条目。第二个元素指的是我们已经看到的公共架构。

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

设置默认Schema

SET search_path TO
 myschema,public;

模式的权限

默认情况下，用户无法访问他们不拥有的架构中的任何对象。若要允许这样做，架构的所有者必须授予对架构的权限。默认情况下，每个人都对架构具有该权限。要允许用户使用架构中的对象，可能需要根据对象的需要授予其他权限。

还可以允许用户在其他人的架构中创建对象。若要允许这样做，需要授予对架构的权限。

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

修改模式

命令

ci_database_test02=> \help alter schema
Command:     ALTER SCHEMA
Description: change the definition of a schema
Syntax:
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
URL: https://www.postgresql.org/docs/16/sql-alterschema.html

ci_database_test02=> 

测试

1.修改架构circle_center 的架构名为circle_blog

ci_database_test02=> alter schema circle_center rename to circle_blog;
ALTER SCHEMA
ci_database_test02=> \dn
         List of schemas
    Name     |       Owner       
-------------+-------------------
 circle_blog | circledba
 circle_oa   | circledba
 circle_shop | circledba
 public      | pg_database_owner
(4 rows)

ci_database_test02=> 

2.修改架构circle_shop 的用户属主为postgres

ci_database_test02=> alter schema circle_shop owner to postgres;
ERROR:  must be able to SET ROLE "postgres" # 提示我们没有postgres权限,不让我们修改，circledba只是个普通用户
ci_database_test02=> \c - postgres #切换到数据库管理员postgres
Password for user postgres: 
You are now connected to database "ci_database_test02" as user "postgres".
ci_database_test02=# alter schema circle_shop owner to postgres; #再次修改
ALTER SCHEMA
ci_database_test02=# \dn
         List of schemas
    Name     |       Owner       
-------------+-------------------
 circle_blog | circledba
 circle_oa   | circledba
 circle_shop | postgres
 public      | pg_database_owner
(4 rows)

ci_database_test02=# 

看到circle_shop 的属主已经变为了postgres

删除Schema

命令

ci_database_test02=# \help drop schema;
Command:     DROP SCHEMA
Description: remove a schema
Syntax:
DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

URL: https://www.postgresql.org/docs/16/sql-dropschema.html
ci_database_test02=# 

测试

删除名为cricle_shop的Schema

ci_database_test02=# drop schema circle_shop;
DROP SCHEMA
ci_database_test02=# \dn
         List of schemas
    Name     |       Owner       
-------------+-------------------
 circle_blog | circledba
 circle_oa   | circledba
 public      | pg_database_owner
(3 rows)

ci_database_test02=# 

已经成功了

查看Schema列表

命令1

\dn 

测试

ci_database_test02=# \dn
         List of schemas
    Name     |       Owner       
-------------+-------------------
 circle_blog | circledba
 circle_oa   | circledba
 public      | pg_database_owner
(3 rows)

ci_database_test02=# 

命令2

SELECT
	pn.oid AS schema_oid,
	iss.CATALOG_NAME,
	iss.schema_owner,
	iss.SCHEMA_NAME 
FROM
	information_schema.schemata iss
	INNER JOIN pg_namespace pn ON pn.nspname = iss.SCHEMA_NAME
WHERE iss.catalog_name = '[dbname]';
    

测试

ci_database_test02=# SELECT                                                                                                                                                                                                                   pn.oid AS schema_oid,                                                                                                                                                                                                                         iss.CATALOG_NAME,                                                                                                                                                                                                                             iss.schema_owner,                                                                                                                                                                                                                             iss.SCHEMA_NAME                                                                                                                                                                                                                               FROM                                                                                                                                                                                                                                          information_schema.schemata iss                                                                                                                                                                                                               INNER JOIN pg_namespace pn ON pn.nspname = iss.SCHEMA_NAME                                                                                                                                                                                    WHERE iss.catalog_name = 'ci_database_test02';
 schema_oid |    catalog_name    |   schema_owner    |    schema_name     
------------+--------------------+-------------------+--------------------
       2200 | ci_database_test02 | pg_database_owner | public
      13918 | ci_database_test02 | postgres          | information_schema
         11 | ci_database_test02 | postgres          | pg_catalog
         99 | ci_database_test02 | postgres          | pg_toast
      16405 | ci_database_test02 | circledba         | circle_blog
      16404 | ci_database_test02 | circledba         | circle_oa
(6 rows)

ci_database_test02=#