34. PostgreSQL 安全性

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

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

34.1. 用户和角色

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

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

  • 一个用于开发人员在构建软件或分析数据时的读写用户。

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

34.1.1. 创建角色

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

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

CREATE ROLE mrbean LOGIN;
CREATE USER mrbean;

34.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;

34.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_writer 角色，该角色可以编辑 PostGIS 元数据表！

-- 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，看看结果如何！

34.2. 加密

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

  • 默认情况下，所有密码都使用 MD5 加密。客户端/服务器握手对 MD5 密码进行双重加密，以防止任何拦截密码的人重新使用哈希值。

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

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

34.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 状态。

34.2.2. 数据加密

pgcrypto 模块具有广泛的加密选项，因此我们只演示最简单的用例：使用对称密码加密一列数据。

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

    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');

34.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 使用网络/网络掩码语法指定远程连接的网络限制

  • METHOD 指定要使用的身份验证协议。“trust”完全跳过身份验证，并且简单地接受任何有效的用户名而无需挑战。

本地连接通常是可信的，因为对服务器本身的访问通常是特权的。默认情况下，在安装 PostgreSQL 时会禁用远程连接：如果您想从远程机器连接，则需要添加一个条目。

上面示例中 nyc 的行是远程访问条目的示例。 nyc 示例仅允许本地网络（在本例中为 192.168.1. 网络）上的机器使用 LDAP 身份验证访问 nyc 数据库。根据您网络的安全性，您将在生产环境中使用这些规则的更严格或更宽松的版本。