学习如何在PostgreSQL中管理安全性

共计 9338 个字符，预计需要花费 24 分钟才能阅读完成。

学习如何在 PostgreSQL 中管理安全性

来源 | 愿码 (ChainDesk.CN) 内容编辑
愿码 Slogan | 连接每个程序员的故事
网站 | http://chaindesk.cn

本文阅读时长：17min
在处理安全性时，记住这些级别以便以有组织的方式处理与安全相关的问题。
· 绑定地址：listen_addresses 在文件中 postgresql.conf
· 基于主机的访问控制：pg_hba.conf 文件
· 实例级权限：用户，角色，数据库创建，登录和复制
· 数据库级权限：连接，创建模式等
· 架构级权限：使用架构并在架构内创建对象
· 表级权限：选择，插入，更新等
· 列级权限：允许或限制对列的访问
· 行级安全性：限制对行的访问
为了读取值，PostgreSQL 必须确保我们在每个级别都有足够的权限。整个权限链必须是正确的。
在本文中，您将学习处理 SSL，列级安全性和配置默认权限等的过程。
了解绑定地址和连接

配置 PostgreSQL 服务器时，需要做的第一件事就是定义远程访问。默认情况下，PostgreSQL 不接受远程连接。重要的是由于 PostgreSQL 不能监听端口，所以导致它接收不到连接。如果我们尝试连接，错误消息实际上将来自操作系统。
假设有一个使用默认配置的数据库服务器 192.168.0.123，将发生以下情况：
iMac:~ hs$ telnet 192.168.0.123 5432
Trying 192.168.0.123…
telnet: connect to address 192.168.0.123: Connection refused
telnet: Unable to connect to remote host
Telnet 尝试在端口上创建连接，5432 并立即被远程框拒绝。从外面看，看起来 PostgreSQL 根本就没有运行。
成功连接的关键可以在 postgresql.conf 文件中找到：
# – Connection Settings –
# shop_addresses =’localhost’ # 要监听的 IP 地址; # 逗号分隔的地址列表; # defaults 为 ’localhost’; 使用 ’*’ 表示所有#（更改需要重启）
该 listen_addresses 设置将告诉 PostgreSQL 要监听的地址。从技术上讲，这些地址是绑定地址。这究竟意味着什么？假设我们的机器中有四个 IP 地址。我们这些 IP 地址只监听其中的三个。PostgreSQL 只考虑这三个 IP 的请求，而不考虑第四个。
如果我们放入 *，PostgreSQL 会监听分配给您机器的每个 IP。
但是，有更多与连接管理相关的设置对于理解非常重要。它们如下：
#port = 5432
# (change requires restart)
max_connections = 100
# (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of
# shared memory per
# connection slot, plus lock space
# (see max_locks_per_transaction).
#superuser_reserved_connections = 3
# (change requires restart)
#unix_socket_directories = ‘/tmp’
# comma-separated list of directories
# (change requires restart)
#unix_socket_group = ”
# (change requires restart)
#unix_socket_permissions = 0777
# begin with 0 to use octal notation
# (change requires restart)
首先，PostgreSQL 侦听单个 TCP 端口，默认值为 5432。请记住，PostgreSQL 只会侦听单个端口。每当有请求进入时，postmaster 将创建一个新进程来处理连接。默认情况下，最多允许 100 个普通连接。最重要的是，为超级用户保留了三个额外的连接。这意味着我们可以拥有 97 个连接加上三个超级用户或 100 个超级用户连接。
处理 SSL

PostgreSQL 允许我们加密服务器和客户端之间的传输。加密非常有用，特别是如果我们进行远距离通信。SSL 提供了一种简单而安全的方式来确保没有人能够收听您的通信。
在本节中，我们将学习如何设置 SSL。
首先要做的是在服务器启动时将 ssl 参数设置 on 为 postgresql.conf 文件。在下一步中，我们可以将 SSL 证书放入 $PGDATA 目录中。如果我们不希望证书位于其他目录中，请更改以下参数：
#ssl_cert_file = ‘server.crt’ # (change requires restart)
#ssl_key_file = ‘server.key’ # (change requires restart)
#ssl_ca_file = ” # (change requires restart)
#ssl_crl_file = ” # (change requires restart)
如果我们要使用自签名证书，请执行以下步骤：
openssl req -new -text -out server.req
回答 OpenSSL 提出的问题。确保我们输入本地主机名作为通用名称。我们可以将密码留空。此调用将生成一个受密码保护的密钥; 它不会接受长度少于四个字符的密码短语。
要删除密码（如果要自动启动服务器，则必须如此），请运行以下命令：
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
输入旧密码以解锁现有密钥。现在，执行此操作将证书转换为自签名证书，并将密钥和证书复制到服务器将查找的位置：
openssl req -x509 -in server.req -text
-key server.key -out server.crt
执行此操作后，请确保文件具有正确的权限集：
chmod og-rwx server.key
一旦将适当的规则放入 pg_hba.conf 文件中，我们就可以使用 SSL 连接到您的服务器。要验证我们确实使用 SSL，请考虑签出该 pg_stat_ssl 功能。它将告诉我们每个连接以及它是否使用 SSL，它将提供有关加密的一些重要信息：
test=# \d pg_stat_sslView “pg_catalog.pg_stat_ssl”
Column | Type | Modifiers ————-+———-+———– pid | integer | ssl | boolean | version | text | cipher | text | bits | integer | compression | boolean |

clientdn | text |
如果 ssl 进程的字段包含 true; PostgreSQL 做了我们期望它做的事情：
postgres=# select * from pg_stat_ssl;
-[RECORD 1]
—————————-
pid | 20075
ssl | t
version | TLSv1.2
cipher | ECDHE-RSA-AES256-GCM-SHA384
bits | 256
compression | f
clientdn |
处理实例级安全性
到目前为止，我们已经配置了绑定地址，我们告诉 PostgreSQL 使用哪种 IP 范围进行身份验证。到目前为止，配置纯粹与网络相关。
在下一步中，我们可以将注意力转移到实例级别的权限。最重要的是要知道 PostgreSQL 中的用户存在于实例级别。如果我们创建一个用户，它不仅在一个数据库中可见; 它可以被所有数据库看到。用户可能只具有访问单个数据库的权限，但基本上用户是在实例级别创建的。
对于那些刚接触 PostgreSQL 的人来说，还有一件事要记住：用户和角色是一回事。CREATE ROLE 和 CREATE USER 子句有不同的默认值（字面上，唯一的区别是 LOGIN 默认情况下角色没有得到属性），但在一天结束时，用户和角色是相同的。因此，CREATE ROLE 和 CREATE USER 子句支持完全相同的语法：
test=# \h CREATE USER
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [[ WITH] option […] ]
where option can be:

SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ENCRYPTED] PASSWORD ‘password’
| VALID UNTIL ‘timestamp’
| IN ROLE role_name [, …]
| IN GROUP role_name [, …]
| ROLE role_name [, …]
| ADMIN role_name [, …]
| USER role_name [, …]
| SYSID uid
让我们逐个讨论这些语法元素。我们首先看到的是用户可以是超级用户或普通用户。如果某人被标记为 a SUPERUSER，则不再有普通用户必须面对的任何限制。A SUPERUSER 可以根据需要删除对象（数据库等）。
下一个重要的事情是它在实例级别上获取创建新数据库的权限。
规则是这样的：创建者总是自动成为对象的所有者（除非另有说明，否则可以使用该 CREATE DATABASE 子句）。美丽的是，对象所有者也可以再次丢弃一个对象。
下一个重要的是 INHERIT/ NOINHERITclause。如果 INHERIT 设置了子句（这是默认值），则用户可以继承其他用户的权限。使用继承权限允许我们使用角色，这是抽象权限的好方法。例如，我们可以创建角色 bookkeeper 并使许多其他角色继承 bookkeeper。我们的想法是 bookkeeper，即使我们有很多人从事会计工作，我们也只需要告诉 PostgreSQL 一次允许做什么。
该 LOGIN/ NOLOGIN 子句定义一个角色是否可以登录到该实例。
在理论介绍之后，是时候实际创建用户并看看在实际示例中如何使用事物：
test=# CREATE ROLE bookkeeper NOLOGIN;
CREATE ROLE
test=# CREATE ROLE joe LOGIN;
CREATE ROLE
test=# GRANT bookkeeper TO joe;
GRANT ROLE
这里做的第一件事 bookkeeper 是创建一个名为的角色。
请注意，我们不希望人们以身份登录 bookkeeper，因此角色标记为 NOLOGIN。
另请注意，NOLOGIN 如果使用该 CREATE ROLE 子句，则为默认值。如果您更喜欢该 CREATE USER 子句，则默认设置为 LOGIN。
然后，joe 创建角色并标记为 LOGIN。最后，将 bookkeeper 角色分配给 joe 角色，以便他可以执行 bookkeeper 实际允许的所有操作。
一旦用户到位，我们可以测试到目前为止我们拥有的内容：
[hs@zenbook ~]$ psql test -U bookkeeper
psql: FATAL: role “bookkeeper” is not permitted to log in
正如所料，该 bookkeeper 角色不允许登录系统。如果 joe 角色尝试登录会发生什么？
[hs@zenbook ~]$ psql test -U joe
…
test=>
这实际上将按预期工作。但请注意，命令提示符已更改。这只是 PostgreSQL 向您显示您未以超级用户身份登录的一种方式。
创建用户后，可能需要对其进行修改。我们可能想要改变的一件事是密码。在 PostgreSQL 中，允许用户更改自己的密码。下面是它的工作原理：
test=> ALTER ROLE joe PASSWORD ‘abc’;
ALTER ROLE
test=> SELECT current_user;
current_user
————–
joe
(1 row)
该 ALTER ROLE 条款（或 ALTER USER）将允许我们改变它可以创建用户时设置大多数设置。但是，管理用户还有很多。在许多情况下，我们希望为用户分配特殊参数。该 ALTER USER 条款为我们提供了这样做的方法：
ALTER ROLE {role_specification | ALL}
[IN DATABASE database_name]
SET configuration_parameter {TO | =} {value | DEFAULT}
ALTER ROLE {role_specification | ALL}
[IN DATABASE database_name]
SET configuration_parameter FROM CURRENT
ALTER ROLE {role_specification | ALL}
[IN DATABASE database_name] RESET configuration_parameter
ALTER ROLE {role_specification | ALL}
[IN DATABASE database_name] RESET ALL
语法非常简单，非常简单。为了描述为什么这非常有用，我添加了一个真实的例子。让我们假设 Joe 碰巧住在毛里求斯岛上。当他登录时，即使他的数据库服务器位于欧洲，他也希望自己在他自己的时区：
test=> ALTER ROLE joe SET TimeZone = ‘UTC-4’;
ALTER ROLE
test=> SELECT now();
now
——————————-
2017-01-09 20:36:48.571584+01
(1 row)

test=> q
[hs@zenbook ~]$ psql test -U joe
…
test=> SELECT now();
now
——————————-
2017-01-09 23:36:53.357845+04
(1 row)
该 ALTER ROLE 子句将修改用户。一旦 joe 重新连接，就会为他设置时区。
时区不会立即更改。您应该重新连接或使用 SET … TO DEFAULT 子句。
这里重要的是这对于某些内存参数也是可能的，例如 work_mem 等等。
数据库级别的安全性

在实例级别配置用户之后，可以深入挖掘并查看在数据库级别可以执行的操作。出现的第一个主要问题是：我们明确允许 Joe 登录数据库实例，但是谁或什么允许 Joe 实际连接到其中一个数据库？也许我们不希望 Joe 访问系统中的所有数据库。限制对某些数据库的访问正是我们在此级别上可以实现的目标。
对于数据库，可以使用 GRANT 子句设置以下权限：
GRANT {{ CREATE | CONNECT | TEMPORARY | TEMP} [, …]
| ALL [PRIVILEGES] }
ON DATABASE database_name [, …]
TO role_specification [, …] [WITH GRANT OPTION]
数据库级别有两个主要权限值得密切关注：
· CREATE：这允许某人在数据库中创建模式。请注意，CREATE 子句不允许创建表; 它是关于模式的。在 PostgreSQL 中，表位于模式中，因此您必须首先进入模式级别才能创建表。
· CONNECT：这允许有人连接到数据库。
现在的问题是：没有人明确 CONNECT 为 joe 角色分配权限，那么这些权限实际上来自何处？答案是：有一个叫做的东西 public，类似于 Unix 世界。如果世界被允许做某事，那么 joe，谁是一般公众的一部分。
最重要的是，public 它不是一个角色，它可以被删除和重命名。我们可以简单地将其视为系统中每个人的等价物。
因此，为了确保不是每个人都可以随时连接到任何数据库，CONNECT 可能必须从公众中撤销。为此，我们可以以超级用户身份进行连接并解决问题：
[hs@zenbook ~]$ psql test -U postgres
…
test=# REVOKE ALL ON DATABASE test FROM public;
REVOKE
test=# \q
[hs@zenbook ~]$ psql test -U joe
psql: FATAL: permission denied for database “test”
DETAIL: User does not have CONNECT privilege.
我们可以看到，该 joe 角色不再允许连接。此时，只有超级用户才能进行测试。
通常，postgres 即使在创建其他数据库之前，最好还是从数据库中撤消权限。这个概念背后的想法是，这些权限将不再存在于所有新创建的数据库中。如果某人需要访问某个数据库，则必须明确授予权限。权利不再自动存在。
如果我们想允许 joe 角色连接到测试数据库，请以超级用户身份尝试以下行：
[hs@zenbook ~]$ psql test -U postgres
…
test=# GRANT CONNECT ON DATABASE test TO bookkeeper;
GRANT
test=# \q
[hs@zenbook ~]$ psql test -U joe
…
test=>
基本上，这里有两种选择：
· 我们可以 joe 直接允许角色，以便只有 joe 角色才能连接。
· 或者，我们可以为该 bookkeeper 角色授予权限。请记住，joe 角色将继承角色的所有权限 bookkeeper，因此，如果我们希望所有会计师都能够连接到数据库，则为角色分配权限 bookkeeper 似乎是一个有吸引力的想法。
如果我们为该 bookkeeper 角色授予权限，则它没有风险，因为该角色不允许首先登录到该实例，因此它纯粹作为权限来源。
处理列级安全性

在某些情况下，并不是每个人都可以看到所有数据。想象一下银行，有些人可能会看到有关银行帐户的全部信息，而其他人可能仅限于数据的一部分。在现实世界的情况下，可能不允许某人阅读余额栏，或者有人可能看不到人民贷款的利率。
另一个例子是，人们可以看到人们的个人资料，但不能看到他们的照片或其他私人信息。现在的问题是：如何使用列级安全性？
为了证明这一点，我们将向属于该 joe 角色的现有表添加一列：
test=> ALTER TABLE t_useful ADD COLUMN name text;
ALTER TABLE
该表现在由两列组成。该示例的目标是确保用户只能看到其中一列：
test=> \d t_useful
Table “public.t_useful”
Column | Type | Modifiers
——–+———+———–
id | integer |
name | text |
作为超级用户，让我们创建一个用户并让它访问包含我们表的模式：
test=# CREATE ROLE paul LOGIN;
CREATE ROLE
test=# GRANT CONNECT ON DATABASE test TO paul;
GRANT
test=# GRANT USAGE ON SCHEMA public TO paul;
GRANT
CONNECT 被撤销了 public。因此，明确授予绝对是必要的，以确保我们甚至可以到达桌面。
该 SELECT 权限可以赋予 paul 角色：
test=# GRANT SELECT (id) ON t_useful TO paul;
GRANT
基本上，这已经足够了。已经可以以用户身份连接到数据库 paul 并阅读该列：
[hs@zenbook ~]$ psql test -U paul
…
test=> SELECT id FROM t_useful;
id
—-
(0 rows)
如果我们使用列级权限，请记住一件重要的事情，我们应该停止使用 SELECT *，因为它不再起作用了：
test=> SELECT * FROM t_useful;
ERROR: permission denied for relation t_useful
仍然意味着所有列，但由于无法访问所有列，事情将立即出错。
配置默认权限

到目前为止，已经配置了很多东西。如果将新表添加到系统会发生什么？逐个处理这些表并设置适当的权限可能会非常痛苦和风险。如果这些事情会自动发生，那不是很好吗？这正是该 ALTER DEFAULT PRIVILEGES 条款的作用。这个想法是为用户提供一个选项，让 PostgreSQL 在对象出现后立即自动设置所需的权限。有人不能再忘记设置这些权利了。
以下清单显示了语法规范的第一部分：
postgres=# \h ALTER DEFAULT PRIVILEGES
Command: ALTER DEFAULT PRIVILEGES
Description: define default access privileges
Syntax:
ALTER DEFAULT PRIVILEGES
[FOR { ROLE | USER} target_role [, …] ]
[IN SCHEMA schema_name [, …] ]
abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:

GRANT {{ SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER}
[, …] | ALL [PRIVILEGES] }
ON TABLES
TO {[ GROUP] role_name | PUBLIC } [, …] [WITH GRANT OPTION]
…
基本上，语法与 GRANT 子句类似，因此使用起来简单直观。为了向我们展示它的工作原理，我编写了一个简单的例子。我们的想法是，如果 joe 角色创建了一个表，该 paul 角色将自动使用它：
test=# ALTER DEFAULT PRIVILEGES FOR ROLE joe
IN SCHEMA public GRANT ALL ON TABLES TO paul;
ALTER DEFAULT PRIVILEGES
让我们 joe 现在作为角色连接并创建一个表：
[hs@zenbook ~]$ psql test -U joe
…
test=> CREATE TABLE t_user (id serial, name text, passwd text);
CREATE TABLE
作为 paul 角色连接将证明该表已分配给适当的权限集：
[hs@zenbook ~]$ psql test -U paul
…
test=> SELECT * FROM t_user;
id | name | passwd
—-+——+——–
(0 row

学习如何在PostgreSQL中管理安全性

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）