跳到主要内容

数值类型、安全标签与触发器

数值类型、安全标签与触发器​

本文件包含模块：seg（浮点区间）、sepgsql（SELinux 安全标签）、spi（服务端编程接口）、sslinfo（SSL 信息）、tablefunc（交叉表与连接函数）。

seg​

这个模块为表示线段或者浮点区间实现了一种数据类型seg。 seg可以表示区间端点中的不确定性，这使得它对表达实验室测量特别有用。

该模块被认为是"trusted"，也就是说，它可以由对当前数据库具有 CREATE 权限的非超级用户安装。

原理​

度量的几何结构通常比一个数字连续区中的一个点更复杂。一个度量通常是具有一些模糊限制的连续区的一个分段。由于不确定性和随机性，也因为被度量的值可能天然地就是一个指示某种情况的区间（例如一种蛋白质的稳态的温度范围），度量呈现为区间的形式。

只用常识，我们就知道将这类数据存储为区间比存储为数字对更加方便。 实际上，这样做在大部分应用中也更有效。

还是根据常识，限度的模糊性意味着使用传统的数字数据类型会导致信息丢失。试想：你的仪器读到 6.50，并且你把这个读数输入到数据库。在你取出它时会得到什么？看看：

test=# select 6.50 :: float8 as "pH";
pH
---
6.5
(1 row)

在度量世界里，6.50 和 6.5 并不相同。有时候它们可能很不同。实验者们通常会写下（并且发表）他们信任的数字。6.50 实际上是一个模糊的区间，它被包含于一个更大的而且更模糊的区间 6.5 中，它们的中心点（可能）是它们唯一共享的公共特征。我们绝对不希望这类不同的数据项表现得相同。

结论？一种能够记录具有任意可变精度的区间的限度的特殊数据类型是很好的。这种意义下，每一个数据元素都记录其自身的精度。

来看看这个：

test=# select '6.25 .. 6.50'::seg as "pH";
pH
------------
6.25 .. 6.50
(1 row)

语法​

一个区间的外部表达由通过范围操作符连接的一个或者两个浮点数构成。或者，它也可以被指定为一个中心点加上或者减去一个偏差值。也能够存储可选的确定性指示符（<、 >或者~）。不过，所有内建操作符会忽略确定性指示符。表D.26给出了所有允许的表达形式，表 D.27展示了一些例子。

在表 D.26中，x、y和delta表示浮点数。x和y可以前置一个确定性指示符，但是delta不行。

表 D.26. seg外部表达

  --------------------------------- ---------------------------------
  x                                 单一值（零长度区间）
  x .. y                            从x到y的区间
  x (+-) delta                      从x - delta到 x + delta的区间
  x ..                              下界为x的开区间
  .. x                              上界为x的开区间
  --------------------------------- ---------------------------------

表 D.27. 合法seg输入的例子

5.0创建一个零长度的段（一个点）
~5.0创建一个零长度的段并且在数据中记录~。 ~会被seg操作忽略，但是会被保留为一个注释。
<5.0在 5.0 创建一个点。<会被忽略，但是被保留为一个注释。
>5.0在 5.0 创建一个点。>会被忽略，但是被保留为一个注释。
5(+-)0.3创建一个区间4.7 .. 5.3。 注意(+-)标记不会被保留。
50 ..大于或等于 50 的所有东西
.. 0小于或等于 0 的所有东西
1.5e-2 .. 2E-2创建一个区间0.015 .. 0.02
1 ... 2与1...2、1 .. 2或者1..2相同 （范围操作符周围的空格会被忽略）

由于...运算符被广泛地用在数据源中，它被允许作为..运算符的一种替代。不幸地是，这会带来解析歧义：分不清0...23的上界是23或者0.23。通过要求seg输入中所有数字的小数点前至少有一位可以解决这个问题。

作为一种完整性检查，seg会拒绝下界大于上界的区间，例如5 .. 2。

精度​

seg值在内部被存储为一对 32 位浮点数。这意味着具有超过 7个有效位的数字会被截断。

具有 7 个或者更少有效位的数字会保留它们的原有精度。即，如果你的查询返回0.00，你可以确信拖尾的零不是人工造成的，它们反映了原始数据的精度。前导零的数量不影响精度：值 0.0067 被认为只有 2 个有效位。

用法​

seg模块包括了用于seg值的一个 GiST 索引操作符类。该 GiST 操作符类所支持的操作符在表 D.28中展示。

表 D.28. Seg GiST 操作符

操作符/简述：

  • seg << seg → 布尔值：第一个seg是否完全位于第二个的左侧？ [a, b] << [c, d] is true if b < c.
  • seg >> seg → 布尔值：第一个seg是否完全位于第二个的右侧？ [a, b] >> [c, d] is true if a > d.
  • seg &< seg → 布尔值：第一个seg没有延伸到第二个的右边吗？ [a, b] &< [c, d] is true if b <= d.
  • seg &> seg → 布尔值：第一个seg没有延伸到第二个的左边吗？[a, b] &> [c, d] is true if a >= c.
  • seg = seg → 布尔值：两个seg是否相等？
  • seg && seg → 布尔值：两个seg是否重叠？
  • seg @> seg → 布尔值：第一个seg是否包含第二个？
  • seg <@ seg → 布尔值：第一个seg是否被第二个包含？

除上述运算符外，表9.1中显示的常用比较运算符也可用于类型seg。这些运算符首先比较（a）和（c），如果它们相等，则比较（b）和（d）。在大多数情况下，这会导致相当好的排序，如果您希望将ORDER BY用于此类型，这将非常有用。

注解​

将(+-)转换成常规范围的机制在确定边界的有效位数时并不完全准确。 例如，如果结果区间包括一个 10 的时，它会加上一个额外的位：

test=# select '10(+-)1'::seg as seg;
seg
--------------
9.0 .. 1.1e1
(1 row)

一个 R-树索引的性能很大程度上依赖于输入值的初始顺序。将输入表以seg列进行排序将会非常有帮助。

sepgsql​

sepgsql是一个基于SELinux安全策略的支持基于标签的强制访问控制（MAC）模块。

警告：当前的实现具有明显的限制，并且不支持对所有动作的强制访问控制。详见"限制"小节。

概述​

这个模块和SELinux集成在一起在Halo提供的安全检查之上，提供了一个 额外的安全检查层。从SELinux的角度来看，这个模块允许 Halo作为一个用户空间对象管理器。

对每一次由 DML 查询发起的表或者函数访问将根据系统安全策略进行检查。这种检查是在Halo执行的常规权限检查之外进行的。

SELinux访问控制决定是通过使用安全标签来做出的，安全标签使用system_u:object_r:sepgsql_table_t:s0 这样的字符串表示。每个访问控制决定涉及两个标签：尝试执行该动作的主体的标签，以及要在执行该动作的客体的标签。由于这些标签可以被应用于任何种类的对象，对于存储在数据库中的对象的（用这个模块做出的）访问控制决定，服从于任意其他类型对象（例如文件）的同一种一般准则。这种设计是为了允许一种中央安全策略来保护信息资产，而不依赖于这些资产是如何存储的。

SECURITY LABEL语句允许为一个数据库对象分配安全标签。

安装​

sepgsql只能在启用了 SELinux的 Linux 2.6.28 或者更高版本上使用。在任何其他平台上都无法使用这个模块。你将还需要 libselinux 2.1.10 或者更高版本以及 selinuxpolicy 3.9.13 或者更高版本（尽管某些发行中可能 把必要的规则逆向移植到较老的策略版本中）。

你可以使用sestatus命令检查 SELinux的状态。一种典型的显示是：

$ sestatus

SELinux status: enabled

SELinuxfs mount: /selinux

Current mode: enforcing

Mode from config file: enforcing

Policy version: 24

Policy from config file: targeted

如果没有安装或者启用SELinux，你就必须在安装这个模块之前先安装或者启用它。

回归测试​

由于SELinux的本质，为sepgsql 运行回归测试要求一些额外的配置步骤，某些步骤还需要由 root 来完成。该回归测试无法通过普通的make check或者make installcheck命令运行，你必须建立配置并且接着手工调用测试脚本。这些测试必须在一个已配置 Halo

编译树的contrib/sepgsql目录中运行。尽管它们要求一个编译树，但是 这些测试被设计成在一个已安装服务器上执行，也就是说它们可以比得上 make installcheck（而不是make check）。

首先，根据"安装"小节中的指导在一个工作数据库中设置 sepgsql。注意：当前操作系统用户必须能够不使用口令认证作为超级用户连接到该数据库。

第二，为该回归测试编译和安装策略包。sepgsql-regtest策略是一个特殊的策略包，它提供一组在回归测试浅见要被允许的规则。它应该从策略源文件 sepgsql-regtest.te编译，这需要通过使用 make和一个 SELinux 提供的 Makefile 完成。你将需要在你自己的系统上找到合适的 Makefile，下面展示的路径只是一个例子。（这个 Makefile 通常由 selinuxpolicy-devel 或 selinux-policy RPM 提供。）一旦编译好，使用semodule命令安装这个策略包，它会把所提供的策略包载入到内核中。如果该包被正确地安装，semodule -l应该把 sepgsql-regtest列成一个可用的策略包：

$ cd .../contrib/sepgsql

$ make -f /usr/share/selinux/devel/Makefile

$ sudo semodule -u sepgsql-regtest.pp

\$ sudo semodule -l | grep sepgsql
sepgsql-regtest 1.07

第三，打开sepgsql_regression_test_mode。由于安全性的原因， sepgsql-regtest中的规则默认没有被启用the sepgsql_regression_test_mode参数会启用启动该回归测试所的规则。它可以使用setsebool命令来启用：

$ sudo setsebool sepgsql_regression_test_mode on

$ getsebool sepgsql_regression_test_mode

sepgsql_regression_test_mode --> on

第四，验证你的 shell 在unconfined_t域中操作：

$ id -Z

unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

如果有必要，可以据此调整你的工作域。

最后，运行该回归测试脚本：

$ ./test_sepgsql

这个脚本将尝试验证你已经正确地完成了所有的配置步骤，接下来它将运行 sepgsql模块的回归测试。

完成测试后，推荐你禁用 sepgsql_regression_test_mode参数：

$ sudo setsebool sepgsql_regression_test_mode off

你可能想要完全移除sepgsql-regtest策略：

$ sudo semodule -r sepgsql-regtest

GUC 参数​

sepgsql.permissive (boolean)

不管系统设置如何，这个参数让sepgsql在自由模式中运行。默认值为关闭。这个参数只能在postgresql.conf文件中或者服务器命令行上被设置。

当这个参数为打开时，sepgsql在自由模式中运行，即便 SELinux 运行在强制模式中也是如此。这个参数主要用于测试目的。

sepgsql.debug_audit (boolean)

不管系统策略设置如何，这个参数启用打印审计消息。默认值为关闭，表示将根据系统设置打印消息。

SELinux的安全性策略也具有控制是否记录特定访问的规则。默认情况下，违法访问将会被记录，但是被允许的访问则不会被记录。

这个参数强制打开所有可能的记录而不管该系统策略。

特性​

D.35.5.1. 控制对象类

SELinux的安全模型把所有访问控制规则描述为一个主体（典型的是一个数据库客户端）和一个客体（例如一个数据库对象）之间的关系，每一个这样的关系被一个安全标签标识。

如果尝试访问一个未加标签的客体，会认为该客体被分配了标签unlabeled_t。

当前，sepgsql允许把安全标签分配给模式、表、列、序列、视图和函数。在使用sepgsql时，安全标签会在所支持的数据库对象创建时自动分配给它们。这种标签被称为默认安全标签并且根据系统安全性策略决定，默认安全标签被用来输入创建者标签、分配给新对象父对象的标签以及所构造对象的可选名称。

一个新数据库对象基本上会继承父对象的安全标签，不过当安全策略具有特殊的类型转换规则时，将会应用一个不同的标签。对于模式，其父对象是当前数据库。对于表、序列、视图和函数，父对象是包含它的模式。对于列，其父对象是包含它的表。

D.35.5.2. DML 权限

对于表，根据语句的种类会对所有被引用的目标表检查

db_table:select、db_table:insert、db_table:update或者db_table:delete。此外，对于所有其列被WHERE或RETURNING子句引用、作为 UPDATE的数据源（以及其他情况）的表，都要检查db_table:select。

对每一个被引用的列也将检查列级权限。不仅在使用SELECT读取列时会检查db_column:select，在其他 DML 语句中引用列时也要检查。对于被UPDATE或者INSERT修改的列也将检查 db_column:update或者db_column:insert。

例如，考虑：

UPDATE t1 SET x = 2, y = func1(y) WHERE z = 100;

这里，将对t1.x检查db_column:update，因为它被更新。对t1.y将检查db_column:{select update}，因为它既被更新也被引用。并且会对t1.z检查 db_column:select，因为它只被更新。还将在表层面上检查 db_table:{select update}。

对于序列，当我们使用SELECT引用一个序列对象时会检查 db_sequence:get_value。不过，我们当前不会检查执行相应函数（例如lastval()）的权限。

对于视图，将检查db_view:expand，然后对从视图展开来的任何对象都会分别检查所需的权限。

对于函数，当用户尝试在一个查询中或者使用快路径调用执行一个函数时会检查

db_procedure:{execute}。如果该函数是一个可信过程，也会检查 db_procedure: {entrypoint}权限来看看它能否作为一个可信程序的入口点来执行。

为了访问任何模式对象，在其所在的模式上需要db_schema:search 权限。当不用模式限定引用一个对象时，其上没有该权限的模式不会被搜索（就好 像该用户在该模式上没有USAGE特权）。如果出现一个显式的模式限定，当该用户在提及的模式上没有要求的权限时将会发生一个错误。

客户端必须被允许访问所有引用到的表和列，即便它们是由视图扩展得来的。这样我们可以应用一致的访问控制规则而不管表内容被引用的方式。

默认的数据库特权系统允许数据库超级用户使用 DML 命令修改系统目录并且引用或者修改TOAST 表。当sepgsql被启用时，这些操作会被禁止。

D.35.5.3. DDL 权限

SELinux为每一种对象类型定义了数个权限来控制常用操作，例如创建、修改、删除以及重新标记安全标签。此外，数种对象类型具有特殊的权限来控制它们的特性化操作，例如在一个特定模式中增加或者删除名字项。

创建一个新的数据库对象要求create权限。SELinux将基于客户端的安全标签来授予或者否决这个权限并且为新对象提出安全标签。在某些情况下，还需要额外的特权：

• CREATE DATABASE额外要求源数据库或者模板数据库的getattr权限。

• 创建一个模式对象额外地要求父模式上的add_name权限。

• 创建一个表额外要求创建单个表列的权限，就好像每一个表列都是一个 单独的顶层对象。

• 创建一个被标记为LEAKPROOF的函数额外要求 install权限（当为一个现有函数设置LEAKPROOF时也要检查这个权限）。

当执行DROP命令时，在要移除的对象上会检查drop。 对于通过CASCADE间接被删除的对象也会检查权限。删除包含在一个特定模式内的对象（表、视图、序列以及过程）额外地要求该模式上的 remove_name。

在执行ALTER命令时，会在被修改的对象上为每一种对象类型检查 setattr。附属对象（例如一个表的索引或者触发器）除外，这种情况下权限是在父对象上检查的。在某些情况下，还需要额外的权限：

• 将一个对象移动到一个新的模式要求旧模式上的remove_name 权限以及新模式上的add_name权限。

• 设置一个函数上的LEAKPROOF属性要求install权限。

• 在一个对象上使用SECURITY LABEL会额外对该对象要求 relabelfrom权限连同它的旧安全标签以及relabelto 权限连同它的新安全标签。

D.35.5.4. 可信过程

可信过程类似于 SECURITY DEFINER 函数或者 setuid 命令。 SELinux提供了一个特性来允许可信代码使用一个不同于客户端的安全标签运行，通常这是为了提供对敏感数据的高度控制的访问（例如行可能会被忽略或者存储值的精度可能会被降低）。一个函数是否可以作为可信过程受到其安全标签和操作系统安全性策略的控制。例如：

=# CREATE TABLE customer (

cid int primary key,

cname text,

credit text

);
CREATE TABLE
=# SECURITY LABEL ON COLUMN customer.credit
IS 'system_u:object_r:sepgsql_secret_table_t:s0';
SECURITY LABEL
=# CREATE FUNCTION show_credit(int) RETURNS text
AS 'SELECT regexp_replace(credit, ''-[0-9]+\$'', ''-xxxx'', ''g'')
FROM customer WHERE cid = \$1'
LANGUAGE sql;
CREATE FUNCTION
=# SECURITY LABEL ON FUNCTION show_credit(int)
IS 'system_u:object_r:sepgsql_trusted_proc_exec_t:s0';
SECURITY LABEL

上述的操作应该由一个管理员用户执行。

=# SELECT * FROM customer;
ERROR: SELinux: security policy violation
=# SELECT cid, cname, show_credit(cid) FROM customer;
cid | cname | show_credit
-----+--------+---------------------
1 | taro | 1111-2222-3333-xxxx
2 | hanako | 5555-6666-7777-xxxx
(2 rows)

在这种情况下，一个常规用户无法直接引用customer.credit，但是一个可信过程show_credit允许用户在打印客户的信用卡号时把一些数字掩盖掉。

D.35.5.5. 动态域转换

如果安全性策略允许，可以使用 SELinux 的动态域转换特性来切换客户端进程（客户端域）的安全性标签到一个新的上下文。该客户端域需要 setcurrent权限还有从旧的域到新的域的 dyntransition权限。

动态域转换需要被仔细考虑，因为在用户看来，它们允许用户切换其标签， 并且因而切换特权，而不是（像可信过程的情况那样）受系统的强制性管理。 因此，只有当被用来切换到一个比原来的域具有更少特权的域时， dyntransition才被认为是安全的。例如：

=# select sepgsql_getcon();
sepgsql_getcon
-------------------------------------------------------
unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
(1 row)
=#SELECT sepgsql_setcon('unconfined_u:unconfined_r:unconfined_t:s0-
s0:c1.c4');
sepgsql_setcon
----------------
t
(1 row)
regression=#SELECT sepgsql_setcon('unconfined_u:unconfined_r:unconfined_t:s0-
s0:c1.c1023');
ERROR: SELinux: security policy violation

在上面的这个例子中，我们被允许从较大范围的c1.c1023切换到较小范围的c1.c4，但却禁止切换回去。

动态域转换和可信过程的组合开启了一种有趣的使用案例，它适合典型的 连接池软件的处理生命周期。即便你的连接池软件不被允许运行大部分的 SQL 命令，你可以从一个可信过程中使用 sepgsql_setcon()函数允许它切换该客户端的安全标签，这个过程应该采用一些证据来授权，该请求切换该客户端标签。之后，这个会话将会具有目标用户而不是连接池的特权。该连接池之后可以用 NULL参数再次调用sepgsql_setcon() 逆转这次安全标签改变，当然再次的调用也要在一个可信过程中配合适当的权限检查进行。这里的要点是只有可信过程实际具有权限来更改有效的安全标签，并且只有在得到适当的证据后才这样做。当然，对于安全操作，必须保护证据存储（表、过程定义或者其他什么）不会受到未经授权的访问。

D.35.5.6. 杂项

我们全面拒绝LOAD命令，因为任何模块的装载都可能很轻易地绕过安全策略的强制保护。

Sepgsql 函数​

表 D.29展示了可用的函数。

表 D.29. Sepgsql 函数

函数/简述：

  • sepgsql_getcon () → text：返回客户端域，即客户端的当前安全标签。
  • sepgsql_setcon ( text ) → boolean：如果安全策略允许，则将当前会话的客户端域切换到新域。它还接受NULL输入作为转换到客户机原始域的请求。
  • sepgsql_mcstrans_in ( text ) → text：如果 mcstrans 守护进程正在运行，则将给定的限定 MLS/MCS 范围转换为原始格式。
  • sepgsql_mcstrans_out ( text ) → text：如果 mcstrans 守护程序正在运行，则将给定的原始 MLS/MCS 范围转换为限定格式。
  • sepgsql_restorecon ( text ) → boolean：为当前数据库中的所有对象设置初始安全标签。参数可以是NULL，也可以是要用作系统默认值替代项的规范文件的名称。

限制​

数据定义语言（DDL）权限

收到实现的限制，一些 DDL 操作无法检查权限。

数据控制语言（DCL）权限

由于实现限制，DCL 操作不检查权限。

行级访问控制

Halo支持行级访问，但是 sepgsql不支持行级访问。

隐蔽通道

sepgsql不会尝试隐藏一个特定对象的存在，即便是用户不被允许引用该对象。例如，即便我们无法得到一个不可见对象的内容，我们也可以通过主键冲突、外键违背等等结果来推知该对象的存在。一个绝密表的存在无法被隐藏，我们只希望能够隐藏其内容。

spi​

spi模块提供了多个可工作的使用服务器编程接口 (SPI) 和触发器的例子。尽管这些例子的价值只对它们自己合适，它们甚至更有助于作为例子来修改达到你自己的目的。这些函数足够普通，可以与任何表一起使用，但是在创建一个触发器时你必须指定表名和域名（如下所述）。

下面描述的函数组中的每一个都作为一个独立可安装的扩展被提供。

refint --- 用于实现参照完整性的函数​

check_primary_key()和check_foreign_key()被用来检查外键约束（当然，这个功能很早以前被内建的外建机制取代了，但是该模块还是可以用作一个例子）。

check_primary_key()检查引用表。用法是使用这个函数在一个引用其他表的表上创建一个BEFORE INSERT OR UPDATE触发器。指定该触发器的参数为：引用表中构成外键的列名、被引用表名称以及在被引用表中构成主键/唯一键的列名。要处理多个外键，请为每一个引用创建一个触发器。

check_foreign_key()检查被引用表。用法是使用这个函数在一个被其他表引用的表上创建一个BEFORE DELETE OR UPDATE触发器。指定该触发器的参数为：该函数必须对其执行检查的引用表数量、找到一个引用键后的动作（cascade --- 删除引用行，restrict --- 如果引用键存在则中断事务，setnull --- 设置引用键域为空）、触发器所在表中构成主键/唯一键的列名、引用表名称和列名称（第一个参数指定多少个引用表就重复多少次）。注意：主键/唯一键列应该被标记为 NOT NULL 并且应该有一个唯一索引。

refint.example中有一些例子。

autoinc --- 用于自增域的函数​

autoinc()是一个将序列的下一个值存储到一个整数域的触发器。这和内建的"序数列"特性有些重叠，但是它并不完全一样：autoinc()在插入时会覆盖掉给出的不同域值，并且它可被选择用来在更新时增加域。

用法是使用这个函数创建一个BEFORE INSERT（或者BEFORE INSERT OR UPDATE）触发器。指定两个触发器参数：要被修改的整数列名和将提供值的序列对象名（事实上，如果你想要更新多于一个自增列，你可以指定任意数量的这种名称对）。

在autoinc.example中有一个例子。

insert_username --- 用于跟踪谁修改了一个表的函数​

insert_username()是存储当前用户名到一个文本域的触发器。这有助于跟踪是谁最后在一个表中修改了一个特定行。

用法是使用这个函数创建一个BEFORE INSERT以及/或者UPDATE触发器。指定一个触发器参数：要被修改的文本列名。

在insert_username.example中有一个例子。

moddatetime --- 用于跟踪上一次修改时间的函数

moddatetime()是一个存储当前时间到一个timestamp域的触发器。它有助于跟踪一个表中特定行最后一次的修改时间。

用法是使用这个函数创建一个BEFORE UPDATE触发器。指定一个触发器参数：要被修改的列名。该列必须是类型timestamp或者timestamp with time zone。

在moddatetime.example中有一个例子。

sslinfo​

在连接到Halo时，sslinfo模块提供当前客户端提供的 SSL 证书的有关信息。如果当前连接不使用 SSL，这个模块就没有用处（大部分函数将返回 NULL）。

本模块提供的一些信息，也可以使用内置系统视图 pg_stat_ssl来获得。

除非安装时用--with-openssl进行了配置，这个扩展压根就不会被编译。

提供的函数​

ssl_is_used() 返回 boolean

如果当前到服务器的连接使用 SSL 则返回 true，否则返回 false。

ssl_version() 返回 text

返回 SSL 连接使用的协议名称（如 TLSv1.0、TLSv1.1 或者 TLSv1.2）。

ssl_cipher() 返回 text

返回 SSL 连接所用的加密方法名称（如 DHE-RSA-AES256-SHA）。

ssl_client_cert_present() 返回 boolean

如果当前客户端已经向服务器出示了一个合法的 SSL 客户端证书则返回 true，否则返回 false（服务器可能被配置要求一个客户端配置，也可能没有被配置成这样）。

ssl_client_serial() 返回 numeric

返回当前客户端证书的序列号。证书序列号和证书发行人的组合被确保可以 唯一标识一个证书（但是不能唯一标识其拥有者 --- 拥有者应该定期地更换其密钥，并且从发行人那里得到新的证书）。

因此，如果你运行自己的 CA 并且只允许服务器接收来自于这个 CA 的证书，序列号就是最可靠的（虽然并非很好记忆）标识一个用户的方法。

ssl_client_dn() 返回 text

返回当前客户端证书的完整主题，并且将字符数据转换成当前数据库的编码。我们假定如果你在证书名中使用非 ASCII 字符，你的数据库也有能力展示这些字符。如果你的数据库使用 SQL_ASCII 编码，名称中的非 ASCII 字符将被表示为 UTF-8 序列。

结果看起来像/CN=某人 /C=某个国家 /O=某个组织。

ssl_issuer_dn() 返回 text

返回当前客户端证书的完整的发行人名称，并把字符数据转换成当前数据库的编码。编码转换以与ssl_client_dn相同的方式处理。

这个函数的返回值与证书序列号的组合唯一地标识证书。

如果在服务器的证书授权中心文件中有多于一个的可信 CA 证书，或者如果 CAI 已经发行了某些中间认证授权证书，这个函数就真的很有用。

ssl_client_dn_field(fieldname text) 返回 text

这个函数返回证书主题中指定域的值，如果域不存在则返回 NULL。域的名称是字符串常量，它们被使用 OpenSSL 对象数据库转换成 ASN1 对象标识符。下列值是可接受的：

commonName (alias CN)
surname (alias SN)
name
givenName (alias GN)
countryName (alias C)
localityName (alias L)
stateOrProvinceName (alias ST)
organizationName (alias O)
organizationalUnitName (alias OU)
title
description
initials
postalCode
streetAddress
generationQualifier
description
dnQualifier
x500UniqueIdentifier
pseudonym
role
emailAddress

这些域中除了commonName都是可选的。它们之中哪些会被包括或者不会被包括完全取决于你的 CA 策略。不过，这些域的含义由 X.500 和 X.509 标准严格地定义，因此你不能只是为它们分配任意含义。

ssl_issuer_field(fieldname text) 返回 text

和ssl_client_dn_field一样，但是用于证书发行人而不是证书主题。

ssl_extension_info() 返回 setof record

提供有关客户端证书扩展的信息：扩展名、扩展值以及是否为决定性的扩展。

tablefunc​

tablefunc模块包括多个返回表（也就是多行）的函数。这些函数都很有用，并且也可以作为如何编写返回多行的 C 函数的例子。

这个模块被认为是"可信的"，也就是说，它可以由对当前数据库具有CREATE权限的非超级用户安装。

所提供的函数​

表 D.30总结了tablefunc模块提供的函数。

表 D.30. tablefunc函数

函数/简述：

  • normal_rand ( numvals integer, mean float8, stddev float8 ) → 浮点数集：产生一组正态分布的随机值。
  • crosstab ( sql text ) → 记录集：生成一个"数据透视表"，其中包含行名称和 N 列值，其中 N 由调用查询中指定的行类型决定。
  • crosstabN ( sql text ) → table_crosstab_：集N 产生一个包含行名称外加N个值列的"数据透视表"。crosstab2、crosstab3和crosstab4是被预定义的，但你可以按照下文所述创建额外的crosstabN函数。 crosstab ( source_sql text, category_sql text ) → 记录集产生一个"数据透视表"，其值列由第二个查询指定。
  • crosstab ( sql text, N integer ) → 记录集：crosstab(text)的废弃版本。参数N现在被忽略，因为值列的数量总是由调用查询所决定。
  • connectby ( relname text, keyid_fld text, parent_keyid_fld text [,orderby_fld text ], start_with text, max_depth integer [, branch_delim text ] ) → 记录集：产生一个层次树结构的表达。

D.38.1.1. normal_rand

normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8

normal_rand产生一个正态分布随机值（高斯分布）的集合。

numvals是从该函数返回的值的数量。mean是值的正态分布的均值而stddev是值的正态分布的标准偏差。

例如，这个调用请求 1000 个值，它们具有均值 5 和标准偏差 3：

test=# SELECT * FROM normal_rand(1000, 5, 3);
normal_rand
----------------------
6.558678074264742
3.788525831704606
5.553842704505796
9.753656749832347
6.123704750357675
5.499242993736144
8.766552109565838
.
.
.
2.702899991153945
1.3775170519858717
9.663611813628377
(1000 rows)
**D.38.1.2. crosstab(text)**
crosstab(text sql)
crosstab(text sql, int N)

crosstab函数被用来产生"pivot"显示，在其中数据被横布在页面上而不是直接向下列举。例如，我们可能有这样的数据

row1 val11

row1 val12

row1 val13

...

row2 val21

row2 val22

row2 val23

...

而我们希望显示成这样

row1 val11 val12 val13 ...

row2 val21 val22 val23 ...

...

crosstab函数会采用一个文本参数，该文本是一个 SQL 查询，它产生按照第一种方式格式化的原始数据，并且产生以第二种方式格式化的一个表。

sql参数是一个产生数据的源集合的 SQL 语句。这个语句必须返回一个row_name列、一个category列和一个value列。N是一个废弃参数，即使提供也会被忽略（之前这必须匹配输出值列的数目，但是现在这由调用查询决定了）。

例如，所提供的查询可能会产生这样的一个集合：

row_name cat value

----------+-------+-------
row1 cat1 val1
row1 cat2 val2
row1 cat3 val3
row1 cat4 val4
row2 cat1 val5
row2 cat2 val6
row2 cat3 val7
row2 cat4 val8

crosstab函数被声明为返回setof record， 因此输出列的实际名称和类型必须定义在调用的SELECT语句的FROM子句中，例如：

SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);

这个例子产生这样一个集合：

<== value columns ==>
row_name category_1 category_2
----------+------------+------------
row1 val1 val2
row2 val5 val6

FROM子句必须把输出定义为一个row_name列 （具有 SQL 查询的第一个结果列的相同数据类型），其后跟随着 N 个value列（都具有 SQL 查询的第三个结果列的相同数据类型）。你可以按照你的意愿设置任意多的输出值列。 而输出列的名称取决于你。

crosstab函数为具有相同row_name值的输入行的每一个连续分组产生一个输出行。它使用来自这些行的值域从左至右填充输出的值列。如果一个分组中的行比输出值列少，多余的输出列将被用空值填充。如果行更多，则多余的输入行会被跳过。

事实上，SQL 查询应该总是指定ORDER BY 1,2来保证输入行被正确地排序，也就是说具有相同row_name的值会被放在一起并且在行内被正确地排序。注意crosstab本身并不关注查询结果的第二列，它放在那里只是为了被排序，以便控制出现在页面上的第三列值的顺序。

这是一个完整的例子：

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');
SELECT * FROM crosstab(
'select rowid, attribute, value
from ct
where attribute = ''att2'' or attribute = ''att3''
order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val2 | val3 |
test2 | val6 | val7 |
(2 rows)

你可以避免总是要写出一个FROM子句来定义输出列，方法是设置一个在其定义中硬编码所期望的输出行类型的自定义 crosstab 函数。 这会在下一节中描述。另一种可能性是在一个视图定义中嵌入所需的FROM子句。

D.38.1.3. crosstabN(text)

crosstabN(text sql)

crosstabN系列函数是如何为普通crosstab 函数设置自定义包装器的例子，这样你不需要在调用的SELECT查询中写出列名和类型。tablefunc模块包括crosstab2、 crosstab3以及crosstab4，它们的输入行类型被定义为：

test=# CREATE TYPE tablefunc_crosstab_N AS (
test(# row_name TEXT,
test(# category_1 TEXT,
test(# category_2 TEXT,
test(# category_N TEXT
test(# );
CREATE TYPE

因此，当输入查询产生类型为text的列row_name和value 并且想要 2、3 或 4个输出值列时，这些函数可以被直接使用。在所有其他方法中，它们的行为都和上面的一般crosstab函数完全相同。

例如，前一节给出的例子也可以这样来做

test=# SELECT *FROM crosstab3(
test(# 'select rowid, attribute, value
test'# from ct
test'# where attribute = ''att2'' or attribute = ''att3''
test'# order by 1,2');
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val2 | val3 |
test2 | val6 | val7 |

这些函数主要是出于举例的目的而提供。你可以基于底层的crosstab()函数 创建你自己的返回类型和函数。有两种方法来做：

• 与contrib/tablefunc/tablefunc--1.0.sql中相似，创建一个组合类型来描述所期望的输出列。然后定义一个唯一的函数名，它接受一个text参数并且返回setof your_type_name，但是链接到同样的底层crosstab C 函数。例如，如果你的源数据产生为text类型的行名称，并且值是float8，并且你想要 5 个值列：

est=# CREATE TYPE my_crosstab_float8_5_cols AS (
test(# my_row_name text,
test(# my_category_1 float8,
test(# my_category_2 float8,
test(# my_category_3 float8,
test(# my_category_4 float8,
test(# my_category_5 float8
test(# );
CREATE TYPE
test=# CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
test-# RETURNS setof my_crosstab_float8_5_cols
test-# AS '\$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
CREATE FUNCTION

• 使用OUT参数来隐式定义返回类型。同样的例子也可以这样来做：

CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(
IN text,
OUT my_row_name text,
OUT my_category_1 float8,
OUT my_category_2 float8,
OUT my_category_3 float8,
OUT my_category_4 float8,
OUT my_category_5 float8)
RETURNS setof record
AS '\$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
**D.38.1.4. crosstab(text, text)**
crosstab(text source_sql, text category_sql)

crosstab的单一参数形式的主要限制是它把一个组中的所有值都视作相似， 并且把每一个值插入到第一个可用的列中。如果你想要值列对应于特定的数据分类，并且某些分组可能没有关于某些分类的数据，这样的形式就无法工作。crosstab的双参数形式通过提供一个对应于输出列的显式分类列表来处理这种情况。

source_sql是一个产生源数据集的 SQL 语句。这个语句必须返回一个 row_name列、一个category列以及一个value列。也可以有一个或者多个"extra"列。row_name列必须是第一个。category和value列必须是按照这个顺序的最后两个列。row_name和category之间的任何列都被视作"extra"。对于具有相同row_name值的所有行，其"extra"列都应该相同。

例如，source_sql可能产生一组这样的东西：

SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;
row_name extra_col cat value
----------+------------+-----+---------
row1 extra1 cat1 val1
row1 extra1 cat2 val2
row1 extra1 cat4 val4
row2 extra2 cat1 val5
row2 extra2 cat2 val6
row2 extra2 cat3 val7
row2 extra2 cat4 val8

category_sql是一个产生分类集合的 SQL 语句。这个语句必须只返回一列。 它必须产生至少一行，否则会生成一个错误。还有，它不能产生重复值，否则会生成一个错误。category_sql可能是这样的：

SELECT DISTINCT cat FROM foo ORDER BY 1;
cat
-------
cat1
cat2
cat3
cat4

crosstab函数被声明为返回setof record，这样输出列的实际名称和类型 就必须在调用的SELECT语句的FROM子句中被定义，例如：

SELECT * FROM crosstab('...', '...')
AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4
text);

这将产生这样的结果：

<== value columns ==>
row_name extra cat1 cat2 cat3 cat4
---------+-------+------+------+------+------
row1 extra1 val1 val2 val4
row2 extra2 val5 val6 val7 val8

FROM子句必须定义正确数量的输出列以及正确的数据类型。如果在source_sql 查询的结果中有N列，其中的前N-2 列必须匹配前N-2 个输出列。剩余的输出列必须具有source_sql查询结果的最后一列的类型，并且它们的数量必须正好和source_sql查询结果中的行数相同。

crosstab函数为具有相同row_name值的输入行形成的每一个连续分组产生一个输出行。输出的row_name列外加任意一个"extra"列都是从分组的第一行复制而来。输出的value列被使用具有匹配的category值，行中的 value域填充。如果一个行的category不匹配category_sql 查询的任何输出，它的value会被忽略。匹配的分类不出现于分组中，任何输出行中的输出列会被用空值填充，事实上，source_sql查询应该总是指定ORDER BY 1来保证 具有相同row_name的值会被放在一起。但是，一个分组内分类的顺序并不重要。还有，确保category_sql查询的输出的顺序与指定的输出列顺序匹配是非常重要的。

这里有两个完整的例子：

test=# create table sales(year int, month int, qty int);
CREATE TABLE
test=# insert into sales values(2007, 1, 1000);
INSERT 0 1
test=# insert into sales values(2007, 2, 1500);
INSERT 0 1
test=# insert into sales values(2007, 7, 500);
INSERT 0 1
test=# insert into sales values(2007, 11, 1500);
INSERT 0 1
test=# insert into sales values(2007, 12, 2000);
INSERT 0 1
test=# insert into sales values(2008, 1, 1000);
INSERT 0 1
test=# select * from crosstab(
test(# 'select year, month, qty from sales order by 1',
test(# 'select m from generate_series(1,12) m'
test(# ) as (
test(# year int,
test(# "Jan" int,
test(# "Feb" int,
test(# "Mar" int,
test(# "Apr" int,
test(# "May" int,
test(# "Jun" int,
test(# "Jul" int,
test(# "Aug" int,
test(# "Sep" int,
test(# "Oct" int,
test(# "Nov" int,
test(# "Dec" int
test(# );
-[ RECORD 1 ]
year | 2007
Jan  | 1000
Feb  | 1500
Mar  | 
Apr  | 
May  | 
Jun  | 
Jul  | 500
Aug  | 
Sep  | 
Oct  | 
Nov  | 1500
Dec  | 2000
-[ RECORD 2 ]
year | 2008
Jan  | 1000
Feb  | 
Mar  | 
Apr  | 
May  | 
Jun  | 
Jul  | 
Aug  | 
Sep  | 
Oct  | 
Nov  | 
Dec  | 
test=# CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
CREATE TABLE
test=# INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT 0 1
test=# INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT 0 1
test=# INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT 0 1
test=# INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT 0 1
test=# INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT 0 1
test=# INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March
test'# 2003');
INSERT 0 1
test=# INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');
INSERT 0 1
test=# SELECT * FROM crosstab
test-# (
test(# 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
test(# 'SELECT DISTINCT attribute FROM cth ORDER BY 1'
test(# )
test-# AS
test-# (
test(# rowid text,
test(# rowdt timestamp,
test(# temperature int4,
test(# test_result text,
test(# test_startdate timestamp,
test(# volts float8
test(# );
rowid | rowdt | temperature | test_result | test_startdate | volts
-------+---------------------+-------------+-------------+---------------------+--------
test1 | 2003-03-01 00:00:00 | 42 | PASS | | 2.6987
test2 | 2003-03-02 00:00:00 | 53 | FAIL | 2003-03-01 00:00:00 | 3.1234
(2 rows)

你可以创建预定义的函数，来避免在每个查询中都必须写出结果列的名称和类型。

D.38.1.5. connectby

connectby(text relname, text keyid_fld, text parent_keyid_fld

[, text orderby_fld ], text start_with, int max_depth
[, text branch_delim ])

connectby函数产生存储，在一个表中的层次数据的显示。该表必须具有一个用以唯一标识行的键域，以及一个父亲键域用来引用其父亲（如果有）。connectby能显示从任意行开始向下的子树。

表 D.31解释了参数。

表 D.31. connectby 参数

  ---------------------------------- ------------------------------------------
  参数                               描述
  relname                            源关系的名称
  keyid_fld                          键域的名称
  parent_keyid_fld                   父亲键域的名称
  orderby_fld                        用于排序兄弟的域的名称（可选）
  start_with                         起始行的键值
  max_depth                          要向下的最大深度，零表示无限深度
  branch_delim                       在分支输出中用于分隔键值的字符串（可选）
  ---------------------------------- ------------------------------------------

键域和父亲键域可以是任意数据类型，但是它们必须是同一类型。注意：start_with值必须作为一个文本串被输入，而不管键域的类型如何。

connectby函数被声明为返回setof record，因此输出列的实际名称和类型 就必须在调的SELECT语句的FROM子句中被定义，例如：

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text, pos int);

前两个输出列被用于当前行的键和其父亲行的键，它们必须匹配该表的键域的类型。第三个输出行是该树中的深度，并且必须是类型integer。如果给定了一个branch_delim参数，下一个输出列就是分支显示并且必须是类型text。最后，如果给出了一个orderby_fld参数，最后一个输出列是一个序号，并且必须是类型integer。

"branch"输出列显示了用于到达当前行的由键构成的路径。其中的键用指定的branch_delim 字符串分隔开。如果不需要分支显示，可以在输出列列表中忽略branch_delim参数和分支列。

如果同一父亲的子女之间的顺序很重要，可以包括orderby_fld参数以指定用哪个域对兄弟排序。这个域可以是任何可排序数据类型。当且仅当orderby_fld被指定时，输出列列表必须包括一个最终的整数序号列。

表示表和列名的参数会被原样复制到connectby内部生成的 SQL 查询中。 因此，如果名称是大小写混合或者包含特殊字符，应包括双引号。你也可能需要用模式限定表名。

在大型的表中，除非在父亲键域上有索引，否则性能会很差。

branch_delim字符串不出现在任何键值中是很重要的，否则connectby可能会错误地 报告一个无限递归错误。注意如果没有提供branch_delim，将用一个默认值~来进行递归检测。

这里是一个例子：

CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);
-- 带有分支，但没有 orderby_fld （不保证结果的顺序）
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0,'~')
AS t(keyid text, parent_keyid text, level int, branch text);
keyid | parent_keyid | level | branch
-------+--------------+-------+---------------------
row2 | | 0 | row2
row4 | row2 | 1 | row2~row4
row6 | row4 | 2 | row2~row4~row6
row8 | row6 | 3 | row2~row4~row6~row8
row5 | row2 | 1 | row2~row5
row9 | row5 | 2 | row2~row5~row9
(6 rows)
-- 没有分支，也没有 orderby_fld （不保证结果的顺序）
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
AS t(keyid text, parent_keyid text, level int);
keyid | parent_keyid | level
-------+--------------+-------
row2 | | 0
row4 | row2 | 1
row6 | row4 | 2
row8 | row6 | 3
row5 | row2 | 1
row9 | row5 | 2
(6 rows)
-- 有分支，有 orderby_fld （注意 row5 在 row4 前面）
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
AS t(keyid text, parent_keyid text, level int, branch text, pos int);
keyid | parent_keyid | level | branch | pos
-------+--------------+-------+---------------------+-----
row2 | | 0 | row2 | 1
row5 | row2 | 1 | row2~row5 | 2
row9 | row5 | 2 | row2~row5~row9 | 3
row4 | row2 | 1 | row2~row4 | 4
row6 | row4 | 2 | row2~row4~row6 | 5
row8 | row6 | 3 | row2~row4~row6~row8 | 6
(6 rows)
-- 没有分支，有 orderby_fld （注意 row5 在 row4 前面）
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
AS t(keyid text, parent_keyid text, level int, pos int);
keyid | parent_keyid | level | pos
-------+--------------+-------+-----
row2 | | 0 | 1
row5 | row2 | 1 | 2
row9 | row5 | 2 | 3
row4 | row2 | 1 | 4
row6 | row4 | 2 | 5
row8 | row6 | 3 | 6
(6 rows)