PostgreSQL 02 - 配置

发表于 更新于 分类于 ， 评论数： 阅读次数：

  PostgreSQL: The World's Most Advanced Open Source Relational Database.

客户端身份验证

  当客户端应用程序连接到数据库服务器时，它会指定希望以哪个 PostgreSQL 数据库用户名进行连接，这与用户以特定用户身份登录 Unix 计算机的方式非常相似。PostgreSQL 数据库用户名在逻辑上与服务器运行所在的操作系统的用户名分离。

pg_hba.conf 文件

  客户端认证由一个配置文件控制，传统上命名为pg_hba.conf，并存储在数据库集群的数据目录中。pg_hba.conf文件的一般格式是一组记录，每行一个记录。空白行将被忽略，#注释字符之后的任何文本也将被忽略。可以通过在行尾加上反斜杠来将记录续到下一行。每个认证记录指定一个连接类型、一个客户端 IP 地址范围 (如果与连接类型相关)、一个数据库名称、一个用户名以及用于匹配这些参数的连接的认证方法。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

  注释说的比较清晰，这里所说的 USER 是 PG 数据中有 LOGIN 权限的 ROLE，而不是系统用户。METHOD 字段用于控制客户端以该 USER 登录服务器时的验证方式：trust表示无需密码即可连接，如果要求客户端连接时输入 USER 的密码，则 METHOD 应为password，md5或scram-sha-256。但实际常用的方式应该是后两种，因为password会使密码以明文的形式被传递。

  如果要求客户端输入密码，则 USER 的密码不能为 NULL，否则所有密码都无法通过验证。

postgresql.conf 文件

  postgresql.conf 是另一个重要的配置文件，它包含了很多配置项目，这里只列两条：

listen_addresses = 'localhost'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)

  这两个配置项用于控制服务器监听地址和端口，localhost表示 PG 只会监听本机地址，外部主机将无法连接服务器。将其修改为*以允许外部主机连接服务器。

角色

  PostgreSQL 使用角色的概念管理数据库访问权限。根据角色的设置方式，可以将角色视为数据库用户或数据库用户组。角色可以拥有数据库对象 (如表和函数），并且可以将这些对象上的权限分配给其他角色，以控制谁可以访问哪些对象。

  角色的概念包含了用户和组的概念。在 8.1 之前的 PostgreSQL 版本中，用户和组是不同类型的实体，但现在只有角色。任何角色都可以充当用户、组或两者。

管理角色

  从概念上讲，数据库角色与操作系统用户完全分离。在实践中，保持对应关系可能很方便，但这并不是必需的。数据库角色在整个数据库集群安装中是全局的(而不是针对每个单独的数据库）。

  1. 创建角色
CREATE ROLE name [LOGIN];
  1. 删除角色
DROP ROLE name;

  PG 提供了以上两个语句的 shell 包装器：

createuser name
dropuser name
  1. 查询角色

  查询数据库集群中现有的角色：

SELECT rolname FROM pg_roles;

  查询有登录权限的角色：

SELECT rolname FROM pg_roles WHERE rolcanlogin;

  查询角色也可以使用 PG 的元命令：

\du

角色属性

  数据库角色可以具有多个属性，这些属性定义了其权限并与客户端身份验证系统进行交互。

  1. 登录权限

  只有具有LOGIN属性的角色才能连接到数据库服务器。创建具有登录权限的角色:

CREATE ROLE name LOGIN;
CREATE USER name;

  CREATE USER 等同于附加 LOGIN 属性的 CREATE ROLE。

  1. 密码

  只有在要求用户在连接到数据库时提供密码时，密码才具有意义。数据库密码与操作系统密码是分开的，创建用户时指定密码：

CREATE ROLE name LOGIN PASSWORD 'string';

  这里只介绍最常用的两个角色属性，更多属性参见官方文档 Role Attributes。

  修改角色的登录密码：

ALTER {ROLE | USER} username [WITH] PASSWORD 'new password';

  删除角色的密码：

ALTER {ROLE | USER} username [WITH] PASSWORD NULL;

角色成员资格

  为了方便管理权限，经常将用户分组，这样可以向整个组授予或撤销权限。在 PostgreSQL 中，这是通过创建一个代表该组的角色，然后向各个用户角色授予组角色中的成员资格来完成的。

  首先创建一个代表组的角色，通常，用作组的角色不会有 LOGIN 属性：

CREATE ROLE name;

  组角色存在后，使用GRANT和REVOKE命令向组中添加和删除成员：

GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;

  成员角色有两种方式可以使用组角色的权限：继承组角色的权限并以自己的身份使用，或者暂时成为组角色然后以组角色的身份使用权限 (此时创建的任何数据库对象都被视为由组角色而不是成员角色拥有)。

GRANT group_role TO role [INHERIT TRUE, SET TRUE];

  暂时成为组角色通过SET命令实现：

SET ROLE name;

  以下任意一种方法都可以恢复原来的身份：

SET ROLE login_role;
SET ROLE NONE;
RESET ROLE;

删除角色

  由于角色可以拥有数据库对象，并且可以持有访问其他对象的权限，因此删除角色通常不仅仅是快速DROP ROLE的问题。必须先删除或重新分配角色拥有的任何对象给其他所有者；并且必须撤销授予角色的任何权限。

  可以使用ALTER命令逐个传输对象的所有权，例如：

ALTER TABLE bobs_table OWNER TO alice;

  或使用REASSIGN OWNED命令将要删除角色所拥有的所有对象的所有权重新分配给另一个角色。由于 REASSIGN OWNED 无法访问其他数据库中的对象，因此必须在包含要删除角色所拥有对象的每个数据库中运行它。

  一旦任何有价值的对象已转移给新所有者，便可以使用DROP OWNED命令删除要删除角色所拥有的任何剩余对象。同样，此命令无法访问其他数据库中的对象，因此必须在包含要删除角色所拥有对象的每个数据库中运行它。此外 DROP OWNED 不会删除整个数据库或表空间，因此如果角色拥有尚未转移给新所有者的任何数据库或表空间，则必须手动执行此操作。

  DROP OWNED 还会负责删除授予目标角色的任何权限，这些权限用于不属于该角色的对象。由于 REASSIGN OWNED 不会触及此类对象，因此通常需要同时运行 REASSIGN OWNED 和 DROP OWNED (按此顺序!) 才能完全删除要删除角色的依赖项。

  简而言之，删除用于拥有对象的某个角色的最通用方法是：

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- repeat the above commands in each database of the cluster
DROP ROLE doomed_role;

  如果并非所有拥有对象都转移给同一继承所有者，最好手动处理异常，然后执行上述步骤进行清理。如果在仍有依赖对象时尝试 DROP ROLE，它将发出消息，指出需要重新分配或删除哪些对象。

管理数据库

  少数对象 (如角色、数据库和表空间名称) 在集群级别定义，并存储在pg_global表空间中。集群内部有多个数据库，它们彼此隔离，但都可以访问集群级对象。每个数据库内部有多个模式，其中包含表和函数等对象。因此，完整的层次结构是：集群、数据库、模式、表 (或其他类型的对象，例如函数)。

  连接到数据库服务器时，客户端必须在其连接请求中指定数据库名称，一个连接只能访问一个数据库。但是，客户端可以对同一数据库或不同数据库打开多个连接。数据库级安全性有两个组成部分：访问控制 (在连接级别管理) 和授权控制 (通过授权系统管理)。

  虽然可以在单个集群内创建多个数据库，但应仔细考虑收益是否大于风险和限制。虽然从用户的角度来看，集群中的各个数据库是隔离的，但从数据库管理员的角度来看，它们是紧密绑定的。

  以下元命令可列出集群中所有的数据库：

\l

创建数据库

  使用以下 SQL 命令创建数据库：

CREATE DATABASE name;

  当前角色自动成为新数据库的所有者。数据库的所有者有权删除数据库 (也将删除数据库中的所有对象，即使它们有不同的所有者)。创建数据库是一项受限操作，有CREATEDB属性的用户才能执行此命令。

  要执行 SQL 命令，必须先连接到数据库，因此在initdb时，PG 会创建一个名为postgres的普通数据库。

  在集群初始化期间，PG 还创建了另外两个数据库template1和template0。每当在集群中创建新数据库时，template1 默认会被克隆。这意味着在 template1 中所做的任何更改都会传播到所有随后创建的数据库。template0 被认为是 template1 的原始内容的原始副本。当要创建一个没有任何此类站点本地附加项的数据库时，可以克隆它而不是 template1。

  PG 还提供了一个包装脚本来快速创建数据库：

createdb dbname

  它会连接到 postgres 数据库并发出 CREATE DATABASE 命令，与上面描述的完全相同。不带任何参数的 createdb 将使用当前用户名创建一个数据库。

模板数据库

  CREATE DATABASE 实际上通过复制现有数据库来工作。默认情况下，它复制名为 template1 的标准系统数据库。因此，该数据库是制作新数据库的模板。如果将对象添加到 template1，那么这些对象将被复制到随后创建的用户数据库中。此行为允许对数据库中标准对象集进行站点本地修改。

  但是，CREATE DATABASE 不会复制附加到源数据库的数据库级GRANT权限。新数据库具有默认数据库级权限。

  template0 是第二个标准系统数据库。此数据库包含与 template1 的初始内容相同的数据，即仅包含 PostgreSQL 版本预定义的标准对象。在数据库集群初始化后，template0 永远不应更改。复制 template0 而不是 template1，可以创建一个原始 用户数据库 (其中不存在用户定义的对象，并且系统对象未被更改)，该数据库不包含 template1 中的任何站点本地添加项。

  复制 template0 而不是 template1 的另一个常见原因是，在复制 template0 时可以指定新的编码和区域设置，而 template1 的副本必须使用与它相同的设置。这是因为 template1 可能包含特定于编码或特定于区域设置的数据，而 template0 已知不包含此类数据。

  复制 template0 创建数据库：

CREATE DATABASE dbname TEMPLATE template0;

  或者：

createdb -T template0 dbname

  可以通过这一特性来复制其他数据库。但是，这一方式 (目前) 并非旨在作为通用的 COPY DATABASE 工具。主要的限制是：在复制源数据库时，不允许任何其他会话连接到该数据库。如果在启动时存在任何其他连接，CREATE DATABASE 将失败；在复制操作期间，将阻止与源数据库建立新连接。

  在pg_database中，每个数据库存有两个标志：datistemplate和datallowconn。如果设置了 datistemplate，则具有 CREATEDB 权限的任何用户都可以克隆该数据库，否则，只有超级用户和数据库所有者才能克隆该数据库。如果 datallowconn 为 false，则不允许与该数据库建立任何新连接 (但不会终止现有会话）。template0 通常标记为datallowconn = false以防止其被修改。template0 和 template1 都应始终标记为datistemplate = true。

删除数据库

  使用以下 SQL 命令删除数据库：

DROP DATABASE name;

  只有数据库所有者或超级用户可以删除数据库。删除数据库会移除数据库中包含的所有对象。删除操作不可撤消。

  连接到目标数据库时，无法执行 DROP DATABASE 命令。但是，可以连接到其他数据库 (如 template1)。

  或者使用包装脚本：

dropdb dbname

  与 createdb 不同，它不使用当前用户名作为删除数据库的默认值。

表空间

  PostgreSQL 中的表空间允许数据库管理员定义文件系统中的位置，其中可以存储表示数据库对象的那些文件。创建后，在创建数据库对象时，可以通过名称来引用表空间。通过使用表空间，管理员可以控制PostgreSQL 安装的磁盘布局。

  使用以下 SQL 命令创建表空间：

CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';

  位置必须是属于 PostgreSQL 操作系统用户拥有的空目录。随后在表空间内创建的所有对象都将存储在此目录下的文件中。位置不能位于可移动或临时存储上，因为如果表空间丢失，群集可能无法正常运行。表空间本身的创建必须作为数据库超级用户来完成，但之后可以允许普通数据库用户使用它。

  可以将表、索引和整个数据库分配给特定的表空间。要做到这一点，拥有给定表空间的 CREATE 权限的用户必须将表空间名称作为参数传递给相关命令。例如：

CREATE TABLE foo(i int) TABLESPACE space1;

  初始化数据库集群时，将自动创建两个表空间。pg_global表空间用于共享系统目录。pg_default表空间是 template1 和 template0 数据库的默认表空间 (因此，也将是其他数据库的默认表空间，除非在 CREATE DATABASE 中的 TABLESPACE 子句中另行指定)。

  创建后，只要请求用户拥有足够的权限，就可以从任何数据库使用表空间。这意味着，在使用该表空间的所有数据库中的所有对象都被移除之前，无法删除该表空间。

  使用以下命令删除一个表空间：

DROP TABLESPACE space1;

  使用以下命令查询集群中存在的表空间：

SELECT spcname FROM pg_tablespace;

  或者使用元命令：

\db

模式

  PostgreSQL 数据库集群包含一个或多个已命名的数据库，角色和其他一些对象类型在整个集群中共享。客户端连接到服务器只能访问单个数据库中的数据。数据库包含一个或多个已命名的模式，模式又包含表和其他类型的已命名对象 (数据类型、函数和运算符)。不同模式可以包含相同名称的对象，且与数据库对象不同，用户可以访问数据库中所有的模式 (如果有权限的话)。

  使用模式的好处有以下几点：

  • 允许许多用户使用一个数据库，而不会互相干扰。
  • 将数据库对象组织成逻辑组，以便更易于管理。
  • 可以将第三方应用程序放入单独的模式中，以便它们不会与其他对象的名称冲突。

创建模式

  创建模式使用以下 SQL 语句：

CREATE SCHEMA myschema;

  使用限定名称创建和访问模式中的对象 (如表)：

schema.table

  限定的运算符名称是一个特例：

OPERATOR(schema.operator)

  创建一个一个由他人所有的模式：

CREATE SCHEMA myschema AUTHORIZATION username;

  可以省略模式名，默认将以用户名创建模式。注意：以pg_开头的模式为系统保留，用户不能使用。

  每个新创建的数据库都包含一个public模式，当创建表 (或其他对象) 而没有指明其所属的模式时，它们默认会被放在 public 模式中 (如果 public 模式是搜索路径中的第一个非空模式)。

删除模式

  使用以下 SQL 命令删除一个空模式 (不包含任何对象)：

DROP SCHEMA myschema;

  强制删除一个模式 (即使模式非空)：

DROP SCHEMA myschema CASCADE;

模式搜索路径

  当使用非限定名称创建或访问对象时，系统会通过模式搜索路径 (查找模式列表) 来自动搜索模式。

  1. 显示模式搜索路径
SHOW search_path;

  通常返回：

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

  第一个元素指定与用户同名的模式，如果不存在此模式，则忽略该条目，第二个则是 public 模式。搜索路径中存在的第一个模式是创建新对象的默认位置，这就是默认情况下在公共模式中创建对象的原因。

  1. 修改模式搜索路径
SET search_path TO myschema;

  此后，不能再以非限定名称访问 public 中的对象。public 模式只是个默认被创建的模式，它可以被删除。

  搜索路径对数据类型名称、函数名称和运算符名称的工作方式与对表名称的工作方式相同。数据类型和函数名称可以与表名称完全相同的方式进行限定。

模式权限

  默认情况下，用户无法访问非自己所有模式中的任何对象。除非模式的所有者授予用户在该模式上的USAGE权限。默认情况下，每个人都对 public 模式拥有 USAGE 权限。

  通过授予模式上的CREATE权限，可以允许用户在其他人的模式中创建对象。在 PostgreSQL14 或更早版本升级的数据库中，每个人都对 public 模式拥有该权限。可以使用以下命令撤销该权限：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

  第一个 public 是模式 (即标识符)，第二个 PUBLIC 表示每个用户 (即关键字)。

系统目录模式

  除了 public 和用户创建的模式，每个数据库都包含一个pg_catalog模式，其中包含系统表以及所有内置数据类型、函数和运算符。 pg_catalog 始终是搜索路径的一部分。如果它未显示的出现在搜索路径中，那么它会隐式的成为第一个被搜索的模式。这确保了始终可以找到内置名称。如果希望让自定义的名称覆盖内置名称，可以将 pg_catalog 显式放在搜索路径的末尾。

安全和可移植性

  在 PostgreSQL 14 及以前，每个用户都拥有对 public 模式的 USAGE 和 CREATE 权限，并且 public 模式通常是默认模式。此外，SQL 标准中没有 public 架构的概念。

  因此，推荐的做法是：不使用 public 模式，对于每一位用户，创建与其角色名相同的模式：

CREATE SCHEMA alice AUTHORIZATION alice;

权限

  每个对象都有一个所有者，且通常是执行创建语句的角色。对于大多数类型的对象，初始时只有所有者 (或超级用户) 可以对对象执行任何操作。要允许其他角色使用它，必须授予权限。

  有不同类型的权限：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE、SET和ALTER SYSTEM。

  修改和销毁对象是所有者的固有权限，不能被授予或撤销 (但该权限可以由所有者角色的成员继承)。

  可以使用 ALTER 命令将对象分配给新所有者，例如：

ALTER TABLE table_name OWNER TO new_owner;

  超级用户始终可以执行此操作，普通角色只能在他们既是对象的当前所有者 (或继承所有者角色的权限) 并且能够使用SET ROLE设置为新的所有者角色时才能执行此操作。

  使用 GRANT 命令为其他角色分配对象上的权限，例如：

GRANT UPDATE ON accounts TO joe;

  特殊权限ALL将授予与对象类型相关的全部权限。特殊角色PUBLIC可用于向系统上的每个角色授予权限。此外，还可以设置组角色，以同时授予多个角色权限。

  使用 REVOKE 命令撤销角色在对象上的权限，例如：

REVOKE ALL ON accounts FROM PUBLIC;

  通常，只有对象的拥有者 (或超级用户) 才能授予或撤销对象上的权限。但是，可以授予具有授予选项的权限，这赋予接收者将其再次授予他人的权利。如果随后撤销了授予选项，那么从该接收者 (直接或通过一系列授予) 接收该权限的所有人将失去该权限 (具体参见 GRANT 和 REVOKE 命令)。

  对象的拥有者可以选择撤销自己的普通权限，例如，让自己和其他人只能读取表。但是，拥有者始终被视为持有所有授予选项，因此他们始终可以重新授予自己的权限。

  可用的权限有很多，且每个类型的对象都有其可用的权限集合，这里不一一列出，参见官方文档。