postgresql|学习如何在PostgreSQL中管理安全性开发|程序员|编程

来源 | 愿码(ChainDesk.CN)内容编辑

愿码Slogan | 连接每个程序员的故事

网站 | http://chaindesk.cn

愿码愿景 | 打造全学科IT系统免费课程，助力小白用户、初级工程师0成本免费系统学习、低成本进阶，帮助BAT一线资深工程师成长并利用自身优势创造睡后收入。

官方公众号 | 愿码 | 愿码服务号 | 区块链部落

免费加入愿码全思维工程师社群 | 任一公众号回复“愿码”两个字获取入群二维码

本文阅读时长：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 ROLEbookkeeper NOLOGIN; CREATE ROLE test=# CREATE ROLEjoe LOGIN; CREATE ROLE test=# GRANTbookkeeper 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=> ALTERROLEjoe PASSWORD 'abc'; ALTERROLE 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=> ALTERROLEjoe SET TimeZone = 'UTC-4'; ALTERROLE test=> SELECT now(); now ------------------------------- 2017-01-09 20:36:48.571584+01 (1 row) test=> q [hs@zenbook ~]$ psqltest-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 ~]$ psqltest-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=# GRANTSELECT (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角色将自动使用它：