36. PostgreSQL 安全性

PostgreSQL 拥有丰富而灵活的权限系统，能够将特定权限分配给特定的角色，并为用户提供一个或多个这些角色的权限。

此外，PostgreSQL 服务器可以使用多种不同的系统来验证用户身份。这意味着数据库可以使用与其他架构组件相同的身份验证基础设施，从而简化密码管理。

36.1. 用户和角色

在本章中，我们将创建两个有用的生产用户

  • 一个只读用户，用于发布应用程序。

  • 一个读/写用户，供开发人员在构建软件或分析数据时使用。

我们不是创建用户并授予他们必要的权限，而是创建两个具有正确权限的角色，然后创建两个用户并将它们添加到相应的角色。这样，我们可以在创建更多用户时轻松重用这些角色。

36.1.1. 创建角色

角色是用户，用户是角色。唯一的区别是，“用户”可以被认为是一个具有“登录”权限的角色。

因此，从功能上讲，下面的两个 SQL 语句是相同的，它们都创建一个“具有登录权限的角色”，也就是说，一个“用户”。

CREATE ROLE mrbean LOGIN;
CREATE USER mrbean;

36.1.2. 只读用户

我们的只读用户将用于 Web 应用程序查询 nyc_streets 表。

该应用程序将具有对 nyc_streets 表的特定访问权限，但将从 postgis_reader 角色继承 PostGIS 操作所需的系统访问权限。

-- A user account for the web app
CREATE USER app1;
-- Web app needs access to specific data tables
GRANT SELECT ON nyc_streets TO app1;

-- A generic role for access to PostGIS functionality
CREATE ROLE postgis_reader INHERIT;
-- Give that role to the web app
GRANT postgis_reader TO app1;

现在，当我们以 app1 身份登录时，我们可以从 nyc_streets 表中选择行。但是，我们无法运行 ST_Transform 调用！为什么？

-- This works!
SELECT * FROM nyc_streets LIMIT 1;

-- This doesn't work!
SELECT ST_AsText(ST_Transform(geom, 4326))
  FROM nyc_streets LIMIT 1;

ERROR:  permission denied for relation spatial_ref_sys
CONTEXT:  SQL statement "SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326 LIMIT 1"

答案包含在错误语句中。尽管我们的 app1 用户可以很好地查看 nyc_streets 表的内容，但它无法查看 spatial_ref_sys 的内容，因此对 ST_Transform 的调用失败。

因此，我们还需要授予 postgis_reader 角色对所有 PostGIS 元数据表的读取访问权限

GRANT SELECT ON geometry_columns TO postgis_reader;
GRANT SELECT ON geography_columns TO postgis_reader;
GRANT SELECT ON spatial_ref_sys TO postgis_reader;

现在，我们有了一个很好的通用 postgis_reader 角色，我们可以将其应用于任何需要从 PostGIS 表中读取的用户。

-- This works now!
SELECT ST_AsText(ST_Transform(geom, 4326))
  FROM nyc_streets LIMIT 1;

36.1.3. 读/写用户

我们需要考虑两种读/写场景

  • 需要写入现有数据表的 Web 应用程序和其他应用程序。

  • 需要在其工作中创建新表和几何列的开发人员或分析师。

对于需要对数据表进行写入访问的 Web 应用程序，我们只需要向表本身授予额外的权限，并且我们可以继续使用 postgis_reader 角色。

-- Add insert/update/delete abilities to our web application
GRANT INSERT,UPDATE,DELETE ON nyc_streets TO app1;

例如，读/写 WFS 服务需要这些类型的权限。

对于开发人员和分析师，需要更多地访问主要的 PostGIS 元数据表。我们将需要一个可以编辑 PostGIS 元数据表的 postgis_writer 角色！

-- Make a postgis writer role
CREATE ROLE postgis_writer;

-- Start by giving it the postgis_reader powers
GRANT postgis_reader TO postgis_writer;

-- Add insert/update/delete powers for the PostGIS tables
GRANT INSERT,UPDATE,DELETE ON spatial_ref_sys TO postgis_writer;

-- Make app1 a PostGIS writer to see if it works!
GRANT postgis_writer TO app1;

现在尝试以 app1 用户身份执行上面的表创建 SQL，看看结果如何！

36.2. 加密

PostgreSQL 提供了许多加密功能，其中许多是可选的，有些默认启用。

  • 默认情况下，所有密码都经过 MD5 加密。客户端/服务器握手会双重加密 MD5 密码，以防止任何截获密码的人重复使用哈希值。

  • 客户端和服务器之间可以选择使用SSL 连接，以加密所有数据和登录信息。使用 SSL 连接时，还可以使用 SSL 证书身份验证。

  • 可以使用pgcrypto 模块加密数据库内部的列，该模块包括哈希算法、直接密码（blowfish、aes）以及公钥和对称 PGP 加密。

36.2.1. SSL 连接

为了使用 SSL 连接，您的客户端和服务器都必须支持 SSL。

  • 首先，关闭 PostgreSQL，因为激活 SSL 将需要重新启动。

  • 接下来，我们获取或生成 SSL 证书和密钥。证书将不需要任何密码短语，否则数据库服务器将无法启动。您可以按如下方式生成自签名密钥

    # Create a new certificate, filling out the certification info as prompted
openssl req -new -text -out server.req

# Strip the passphrase from the certificate
openssl rsa -in privkey.pem -out server.key

# Convert the certificate into a self-signed cert
openssl req -x509 -in server.req -text -key server.key -out server.crt

# Set the permission of the key to private read/write
chmod og-rwx server.key

  • 将 server.crt 和 server.key 复制到 PostgreSQL 数据目录中。

  • 通过将“ssl”参数设置为“on”，在 postgresql.conf 文件中启用 SSL 支持。

  • 现在重新启动 PostgreSQL；服务器已准备好进行 SSL 操作。

在服务器启用 SSL 的情况下，创建加密连接很容易。在 PgAdmin 中，创建一个新的服务器连接（文件 > 添加服务器…），并将 SSL 参数设置为“require”。

_images/ssl_create.jpg

使用新连接连接后，您可以在其属性中看到它正在使用 SSL 连接。

_images/ssl_props.jpg

由于默认的 SSL 连接模式是“prefer”，因此连接时甚至不需要指定 SSL 首选项。使用命令行 psql 终端的连接将获取 SSL 选项并默认使用它

psql (8.4.9)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=#

请注意终端如何报告连接的 SSL 状态。

36.2.2. 数据加密

pgcrypto 模块具有大量的加密选项，因此我们将仅演示最简单的用例：使用对称密码加密数据列。

  • 首先，通过在 PgAdmin 或 psql 中加载 contrib SQL 文件来启用 pgcrypto。

    pgsql/8.4/share/postgresql/contrib/pgcrypto.sql

  • 然后，测试加密功能。

    -- encrypt a string using blowfish (bf)
SELECT encrypt('this is a test phrase', 'mykey', 'bf');

  • 并确保它是可逆的！

    -- round-trip a string using blowfish (bf)
SELECT decrypt(encrypt('this is a test phrase', 'mykey', 'bf'), 'mykey', 'bf');

36.3. 身份验证

PostgreSQL 支持许多不同的身份验证方法，以便轻松集成到现有的企业架构中。对于生产目的，通常使用以下方法

  • 密码是基本系统，其中密码由数据库存储，并使用 MD5 加密。

  • Kerberos 是一种标准的的的企业身份验证方法，PostgreSQL 中的 GSSAPI 和 SSPI 方案都使用它。使用 SSPI，PostgreSQL 可以针对 Windows 服务器进行身份验证。

  • LDAP 是另一种常见的企业身份验证方法。大多数 Linux 发行版捆绑的 OpenLDAP 服务器提供了 LDAP 的开源实现。

  • 如果您希望所有客户端连接都通过 SSL 进行，并且能够管理密钥的分配，则证书身份验证是一个选项。

  • 如果您使用的是 Linux 或 Solaris，并且使用 PAM 方案进行透明身份验证，则 PAM 身份验证是一个选项。

身份验证方法由 pg_hba.conf 文件控制。文件名中的“HBA”代表“基于主机的访问”，因为除了允许您指定每个数据库使用的身份验证方法之外，它还允许您使用网络地址限制主机访问。

这是一个示例 pg_hba.conf 文件

# TYPE  DATABASE    USER        CIDR-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          trust
# IPv6 local connections:
host    all         all         ::1/128               trust
# remote connections for nyc database only
host    nyc         all         192.168.1.0/2         ldap

该文件由五列组成

  • TYPE 确定访问类型，对于来自同一服务器的连接，使用“local”，对于远程连接，使用“host”。

  • DATABASE 指定配置行引用的数据库，或所有数据库使用“all”

  • USER 指定该行引用的用户，或所有用户使用“all”

  • CIDR-ADDRESS 使用 network/netmask 语法指定远程连接的网络限制

  • METHOD 指定要使用的身份验证协议。“trust”完全跳过身份验证，只接受任何有效的用户名，而无需质询。

通常，本地连接是受信任的，因为对服务器本身的访问通常是特权的。安装 PostgreSQL 时，默认禁用远程连接：如果要从远程计算机连接，则必须添加一个条目。

上面示例中 nyc 的行是一个远程访问条目的示例。nyc 示例允许仅对本地网络（在本例中为 192.168.1. 网络）上的机器进行 LDAP 身份验证访问，并且仅限于 nyc 数据库。根据您网络的安全级别，您将在生产设置中使用或多或少严格的版本。