适合seo优化的站点,idea 网站开发,中国电力工程造价信息网,营业推广策划PostgreSQL 数据库集群包含一个或多个命名数据库。角色和一些其他对象类型在整个集群中共享。与服务器的客户端连接只能访问单个数据库中的数据#xff0c;该数据库在连接请求中指定。
数据库包含一个或多个命名schema#xff0c;而这些schema又包含表。schema还包含其他类型…PostgreSQL 数据库集群包含一个或多个命名数据库。角色和一些其他对象类型在整个集群中共享。与服务器的客户端连接只能访问单个数据库中的数据该数据库在连接请求中指定。
数据库包含一个或多个命名schema而这些schema又包含表。schema还包含其他类型的命名对象包括数据类型、函数和运算符。相同的对象名称可以在不同的架构中使用而不会发生冲突;例如both 和myschema可以包含名为mytable的表。与数据库不同架构不是严格分离的如果用户具有访问权限则可以访问他们所连接到的数据库中任何架构中的对象。
可能想要使用架构的原因有以下几个 允许多个用户使用一个数据库而不会相互干扰。 将数据库对象组织到逻辑组中使其更易于管理。 第三方应用程序可以放入单独的架构中以便它们不会与其他对象的名称发生冲突。
架构类似于操作系统级别的目录只是架构不能嵌套。
创建模式
要创建模式请使用 CREATE SCHEMA 命令。为架构指定您选择的名称。例如
CREATE SCHEMA myschema;若要在架构中创建或访问对象请编写一个由架构名称和表名称组成的限定名称并用点分隔
schema.table这适用于需要表名的任何位置包括表修改命令和以下章节中讨论的数据访问命令。为简洁起见我们只讨论表但同样的想法也适用于其他类型的命名对象例如类型和函数。
实际上更通用的语法
database.schema.table也可以使用但目前这只是为了形式上符合 SQL 标准。如果写入数据库名称则该名称必须与连接到的数据库相同。
因此若要在新架构中创建表请使用
CREATE TABLE myschema.mytable (...
);若要删除架构如果架构为空其中的所有对象都已删除请使用
DROP SCHEMA myschema;若要删除包含所有包含对象的架构请使用
DROP SCHEMA myschema CASCADE;通常您需要创建其他人拥有的架构因为这是将用户的活动限制在定义明确的命名空间中的方法之一。其语法为
CREATE SCHEMA schema_name AUTHORIZATION user_name;您甚至可以省略架构名称在这种情况下架构名称将与用户名相同。参见第 5.9.6 节了解它如何有用。
以pg_开头的架构名称保留用于系统目的用户无法创建。
公共模式
在前面的部分中我们在未指定任何架构名称的情况下创建了表。默认情况下此类表和其他对象会自动放入名为“public”的架构中。每个新数据库都包含这样的架构。因此以下内容是等效的
CREATE TABLE products ( ... );和
CREATE TABLE public.products ( ... );模式搜索路径
限定名称的编写起来很繁琐通常最好不要将特定的架构名称连接到应用程序中。因此表通常由非限定名称引用这些名称仅由表名称组成。系统通过遵循搜索路径要查找的架构列表来确定哪个表。搜索路径中的第一个匹配表被视为所需的表。如果搜索路径中没有匹配项则会报告错误即使数据库中的其他架构中存在匹配的表名也是如此。
在不同架构中创建同名对象的能力使编写每次都引用完全相同对象的查询变得复杂。它还为用户提供了恶意或意外更改其他用户查询行为的可能性。由于查询中普遍存在非限定名称及其在 PostgreSQL 内部中的使用因此添加架构以有效地信任对该架构具有权限的所有用户。当您运行普通查询search_path时能够在搜索路径的架构中创建对象的恶意用户可以控制并执行任意 SQL 函数就像您执行了CREATE一样。
搜索路径中命名的第一个架构称为当前架构。除了是第一个搜索的架构之外它还是在命令未指定schema名称时将在其中创建新表CREATE TABLE的schema。
若要显示当前搜索路径请使用以下命令
SHOW search_path;在默认设置中这将返回 search_path
--------------$user, public第一个元素指定要搜索与当前用户同名的架构。如果不存在此类架构则忽略该条目。第二个元素指的是我们已经看到的公共架构。
搜索路径中存在的第一个架构是创建新对象的默认位置。这就是默认情况下在公共架构中创建对象的原因。在没有架构限定表修改、数据修改或查询命令的情况下在任何其他上下文中引用对象时将遍历搜索路径直到找到匹配的对象。因此在默认配置中任何非限定访问都只能引用公共架构。
为了将我们的新架构放在路径中我们使用
SET search_path TO myschema,public;我们省略了此处因为我们没有立即需要它。然后我们可以在没有模式限定的情况下访问该表$user
DROP TABLE mytable;此外由于是路径中的第一个元素因此默认情况下将在其中创建新对象。myschema
我们也可以这样写
SET search_path TO myschema;这样如果没有显式限定我们就无法再访问公共架构。公共架构没有什么特别之处只是它默认存在。它也可以被丢弃。
数据类型名称、函数名称和运算符名称的搜索路径的工作方式与表名称的搜索路径相同。数据类型和函数名称可以采用与表名称完全相同的方式限定。如果需要在表达式中写入限定的运算符名称则有一项特殊规定必须将
OPERATOR(schema.operator)这是避免语法歧义所必需的。例如
SELECT 3 OPERATOR(pg_catalog.) 4;在实践中人们通常依赖于运算符的搜索路径这样就不必写出如此丑陋的东西。
模式和权限
默认情况下用户无法访问他们不拥有的架构中的任何对象。若要允许这样做架构的所有者必须授予对架构的权限。默认情况下每个人都对架构具有该权限。要允许用户使用架构中的对象可能需要根据对象的需要授予其他权限 USAGE public。
还可以允许用户在其他人的架构中创建对象。若要允许这样做需要授予对架构的权限。在从 PostgreSQL 14 或更早版本升级的数据库中每个人都对架构具有该权限。某些使用模式要求撤消该权限CREATE public
REVOKE CREATE ON SCHEMA public FROM PUBLIC;第一个“public”是架构第二个“public”是“每个用户”。在第一种意义上它是一个标识符在第二种意义上它是一个关键词因此大小写不同。
系统目录模式
除了用户创建的架构之外每个数据库还包含一个架构public其中包含系统表和所有内置数据类型、函数和运算符。pg_catalog 始终是搜索路径的有效组成部分。如果未在路径中显式命名则在搜索路径的架构之前会隐式搜索它。这可确保始终可以找到内置名称。但是如果您希望让用户定义的名称覆盖内置名称则可以显式放置在搜索路径的末尾。
由于系统表名称以pg_开头因此最好避免使用此类名称以确保在将来的某个版本定义了与您的表同名的系统表时不会发生冲突。使用默认搜索路径时对表名的非限定引用将解析为系统表。系统表将继续遵循名称以pg_开头的约定因此只要用户避免使用前缀它们就不会与非限定的用户表名称冲突。
使用模式
架构可用于以多种方式组织数据。安全架构使用模式可防止不受信任的用户更改其他用户查询的行为。当数据库不使用安全架构使用模式时希望安全查询该数据库的用户将在每个会话开始时采取保护措施。具体来说他们将通过设置为空字符串或以其他方式从 中删除非超级用户可写入的模式来开始每个会话。默认配置可以轻松支持几种使用模式search_path
将普通用户限制为用户专用架构。若要实现此模式请首先确保没有架构具有公共权限。然后对于需要创建非临时对象的每个用户创建与该用户同名的架构例如CREATE SCHEMA alice AUTHORIZATION alice $user ADMIN OPTION 。回想一下默认搜索路径以 开头解析为用户名。因此如果每个用户都有单独的架构则默认情况下他们会访问自己的架构。此模式是一种安全模式使用模式除非不受信任的用户是数据库所有者或已被授予相关角色在这种情况下不存在安全模式使用模式。
在 PostgreSQL 15 及更高版本中默认配置支持此使用模式。在早期版本中或者使用从先前版本升级的数据库时需要从架构中删除公共权限问题。然后考虑审核架构中类似对象命名的对象的架构。 REVOKE CREATE ON SCHEMA public FROM PUBLIC public pg_catalog通过修改 postgresql.conf 或发出 .然后授予在公共架构中创建的权限。只有限定的名称才会选择公共架构对象。虽然限定的表引用很好但对公共架构中的函数的调用将是不安全或不可靠的。如果在公共架构中创建函数或扩展请改用第一种模式。否则与第一种模式一样这是安全的除非不受信任的用户是数据库所有者或已被授予相关角色。
ALTER ROLE ALL SET search_path $userADMIN OPTION保留默认搜索路径并授予在公共架构中创建的权限。所有用户都隐式访问公共架构。这模拟了架构完全不可用的情况从而提供了从非架构感知世界的平滑过渡。但是这从来都不是一个安全的模式。仅当数据库具有单个用户或几个相互信任的用户时才可接受。在从 PostgreSQL 14 或更早版本升级的数据库中这是默认设置。
对于任何模式要安装共享应用程序每个人都要使用的表、第三方提供的附加函数等请将它们放入单独的架构中。请记住授予适当的权限以允许其他用户访问它们。然后用户可以通过使用架构名称限定名称来引用这些附加对象也可以根据自己的选择将其他架构放入其搜索路径中。
可移植性
在 SQL 标准中不存在同一架构中的对象由不同用户拥有的概念。此外某些实现不允许您创建与其所有者名称不同的架构。事实上模式和用户的概念在仅实现标准中指定的基本模式支持的数据库系统中几乎是等价的。因此许多用户认为限定名称实际上由 .如果您为每个用户创建每用户架构这就是 PostgreSQL 的有效行为方式。user_name.table_name
此外SQL 标准中没有架构的概念。为了最大限度地符合标准不应使用架构。
当然某些 SQL 数据库系统可能根本不实现架构或者通过允许可能受限的跨数据库访问来提供命名空间支持。如果您需要使用这些系统那么完全不使用架构即可实现最大的可移植性。
