[译]PostgreSQL中更安全的应用用户

原创 lmj 2022-02-19

902

原文地址：https://blog.crunchydata.com/blog/safer-application-users-in-postgres
原文作者： MIKE PALMIOTTO
翻译：lmj

“我们删了数据库”。

两年前的一个周五下午4点左右，我让客户开了一张支持票。客户认为他们是在开发环境中运行测试组件，但实际上是在生产环境中运行。在一些测试组件的早期步骤之一就是保证有一个干净的环境：

Drop所有的表，删除schema
从头开始CREATE

有了故障恢复和基于时间点恢复，我们可以将数据库回滚到过去的任何时间点。所以我们得到时间戳后，他们运行了命令，将他们数TB的数据库恢复到了之前那个时刻。周五下午压力很大，但是没有数据丢失。

你可能会思考各种方法来防止这种情况。当连接到生产环境时将你的shell颜色设置为为红色。不允许公共网络访问生产。只允许CI-驱动的部署。还有一个有助于降低生产风险的选项：不允许生产环境的应用用户删除数据。

在生产中阻止app 用户删除数据

在生产环境中为了防止应用用户删除数据，我们需要降低风险，限制应用用户进行如下操作：

DROP表
TRUNCATE表

该方法需要结合最佳实践和适当的配置。开始前，让我们先创建用户！

超级用户

超级用户负责创建数据库的schema和表（数据定义语言，DDL）

让我们创建这个例子中的超级用户：

postgres=# CREATE USER admin with PASSWORD 'correcthorsebatterystaple' SUPERUSER;
CREATE ROLE

postgres=# \du admin
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 admin     | Superuser  | {}

应用用户

应用用户通常只能执行一些定义在数据库表和schema上的操作（数据操纵语言，DML）

不要给应用用户赋予DROP和TRUNCATE权限。

生产的应用应该仅需要新增和更新数据的权限。一个典型的生产应用程序通过以下方式增长：

向表中增加列
增加行
更新行记录

如果应用程序遵循上述的设计模式，你可能不会给应用用户赋予DROP、TRUNCATE、DELETE表的权限

在下面的例子中，我们将会使用名为‘myappuser’的应用用户。所以让我们创建它：

postgres=# CREATE USER myappuser WITH PASSWORD 'verygoodpasswordstring';
CREATE ROLE

超级用户创建表

现在角色已经被创建，让我们设置场景。

我们只能通过超级用户创建生产环境的表。默认情况下，表的创建者是表的所有者。只有owner和超级用户可以执行DROP TABLE等操作。这可以防止应用用户意外删除生产表中的数据。应用用户只能drop属于自己的表。

在制作生产的沙盒之前，让我们确保是正确的管理员：

postgres=# SELECT current_user;
 current_user
--------------
 admin
(1 row)

创建一个生产的SCHEMA并GRANT合适的权限：

postgres=# CREATE SCHEMA prod;
CREATE SCHEMA
postgres=# GRANT USAGE ON SCHEMA prod TO myappuser;
GRANT

现在我们为生产数据创建一张表，开始测试一些概念：

postgres=# CREATE TABLE prod.userdata (col1 integer, col2 text, col3 text);
CREATE TABLE

myappuser用户登录时，不能drop表：

postgres=# \c postgres myappuser
Password for user myappuser:
You are now connected to database "postgres" as user "myappuser".
postgres=> DROP TABLE prod.userdata;
ERROR:  must be owner of table userdata

最小权限

我们已经展示了如何阻止应用用户DROP表。为了防止删除表中的数据，我们需要做更多的工作。应用用户应该只能访问其所需内容。

为此，综上所述，我们仅GRANT应用用户需要的权限：

postgres=> \c postgres admin
Password for user admin:
You are now connected to database "postgres" as user "admin".
postgres=# GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA prod TO myappuser;
GRANT

如果已经存在了一些应用用户，可以REVOKE不想要的生产权限：

postgres=# REVOKE DELETE, TRUNCATE ON ALL TABLES IN SCHEMA prod FROM myappuser;
REVOKE

现在我们的应用用户删除不了数据：

postgres=# \c postgres myappuser
Password for user myappuser:
You are now connected to database "postgres" as user "myappuser".
postgres=> DELETE FROM prod.userdata *;
ERROR:  permission denied for table userdata
postgres=> TRUNCATE TABLE prod.userdata;
ERROR:  permission denied for table userdata

我们已经缩小了权限，但是怎么能知道是否有遗漏呢？

检查访问权限

在使用角色和权限时，最好进行检查访问权限。我推荐使用crunchy_check_access扩展来遍历访问和权限树。

使用超级用户登录，查看赋予应用用户的权限：

postgres=# SELECT base_role,objtype,schemaname,objname,privname FROM all_access() WHERE base_role = 'myappuser' AND schemaname = 'prod';
 base_role | objtype | schemaname | objname  | privname
-----------+---------+------------+----------+----------
 myappuser | schema  | prod       | prod     | USAGE
 myappuser | table   | prod       | userdata | SELECT
 myappuser | table   | prod       | userdata | INSERT
 myappuser | table   | prod       | userdata | UPDATE
(4 rows)

应用用户删除记录

在数据库中，我们已经回收权限，防止了“意外”删除数据的错误，但是应用用户仍然需要删除数据。对于删除应用数据，来看一个更安全的可替代的设计。

应用删除数据的一种普遍的模式是标记元组，而不是彻底删除。

我们修改上面创建的表，增加名为deleted的timestamp列。有两个好处：

数据实际上没有被删除，所以上述的问题不用担心
每个时刻都有一个记录快照，可以快速、轻松的回滚应用级别的状态

增加`deleted`列

假设生产表已经创建，可以使用如下方法增加deleted列;

postgres=# ALTER TABLE prod.userdata ADD COLUMN deleted timestamp;
ALTER TABLE

提示：上述的ADD COLUMN语法需要花费很高的代价，因为它会在表上持有Exclusive Lock。

正常表的insert和update操作会采取相同的形式：

INSERT INTO prod.userdata VALUES (generate_series(1,10), md5(random()::text), md5(random()::text)) ;
INSERT 0 10

现在可以选择更新一行，将其标记为删除。假设应用想要删除所有where col1 < 3的记录：

postgres=> UPDATE prod.userdata SET deleted = now() WHERE col1 < 3;
UPDATE 2

查看所有被保留的记录：

postgres=> SELECT * from prod.userdata WHERE deleted IS NULL;
 col1 |               col2               |               col3               | deleted
------+----------------------------------+----------------------------------+---------
    3 | 828748efff06ce5b6f0f8e8931429bd3 | e50fe6654ee497de8ad75746849fba0f |
    4 | 4241511ee0a8f7f76976f0bab43b47f0 | d08e31ba79f972a2983301832ec67b94 |
    5 | 93de032bc9157362593a0259a8558514 | 6cd1639323a0c1a96fb3e781283e19d3 |
    6 | af1e1d81ef68dbd5ac14a0ae55195e2a | a4e500cf2c3ecd24c0a745c42b5af939 |
    7 | bcd0c74ca0d416b3f1b3e7ffda375615 | 361ed5d6bff759df7c138daf4b4b0e1b |
    8 | 35856a2d5b0e5b3e1d3ea4e09f0f88fe | a6d0977908e08626bad8278e965e9315 |
    9 | 43de7e949e9777969248b9b1d751d44e | 196390d618931a8dd3d5473cc23869fa |
   10 | 3fc5661e900a25b96b708f3c22cf1d59 | 2f29a28b25e1a1e25fc10b45fc22bc91 |
(8 rows)

也可以通过时间戳筛选。我们要删除更多的记录，假设要删除没有被删除并且where col1 < 6的列：

postgres=> UPDATE prod.userdata SET deleted = now() WHERE deleted IS NULL AND col1 < 6;
UPDATE 3
postgres=> SELECT * from prod.userdata;
 col1 |               col2               |               col3               |          deleted
------+----------------------------------+----------------------------------+----------------------------
    6 | af1e1d81ef68dbd5ac14a0ae55195e2a | a4e500cf2c3ecd24c0a745c42b5af939 |
    7 | bcd0c74ca0d416b3f1b3e7ffda375615 | 361ed5d6bff759df7c138daf4b4b0e1b |
    8 | 35856a2d5b0e5b3e1d3ea4e09f0f88fe | a6d0977908e08626bad8278e965e9315 |
    9 | 43de7e949e9777969248b9b1d751d44e | 196390d618931a8dd3d5473cc23869fa |
   10 | 3fc5661e900a25b96b708f3c22cf1d59 | 2f29a28b25e1a1e25fc10b45fc22bc91 |
    1 | b4fb51aff93bf865c6bc8c5f32b306cf | 49d37b3934e2c44f20ddd87019bc525e | 2022-02-03 16:30:49.445571
    2 | e53507d91f39905f6bcd193636b13c3d | 66066e4c78a3eb701086391052c19b56 | 2022-02-03 16:30:49.445571
    3 | 828748efff06ce5b6f0f8e8931429bd3 | e50fe6654ee497de8ad75746849fba0f | 2022-02-03 16:34:19.953742
    4 | 4241511ee0a8f7f76976f0bab43b47f0 | d08e31ba79f972a2983301832ec67b94 | 2022-02-03 16:34:19.953742
    5 | 93de032bc9157362593a0259a8558514 | 6cd1639323a0c1a96fb3e781283e19d3 | 2022-02-03 16:34:19.953742
(10 rows)

现在，我们可以使用上次的删除时间戳恢复状态：

postgres=> SELECT * from prod.userdata WHERE deleted IS NULL OR deleted >= timestamp '2022-02-03 16:34:19.953742';
 col1 |               col2               |               col3               |          deleted
------+----------------------------------+----------------------------------+----------------------------
    6 | af1e1d81ef68dbd5ac14a0ae55195e2a | a4e500cf2c3ecd24c0a745c42b5af939 |
    7 | bcd0c74ca0d416b3f1b3e7ffda375615 | 361ed5d6bff759df7c138daf4b4b0e1b |
    8 | 35856a2d5b0e5b3e1d3ea4e09f0f88fe | a6d0977908e08626bad8278e965e9315 |
    9 | 43de7e949e9777969248b9b1d751d44e | 196390d618931a8dd3d5473cc23869fa |
   10 | 3fc5661e900a25b96b708f3c22cf1d59 | 2f29a28b25e1a1e25fc10b45fc22bc91 |
    3 | 828748efff06ce5b6f0f8e8931429bd3 | e50fe6654ee497de8ad75746849fba0f | 2022-02-03 16:34:19.953742
    4 | 4241511ee0a8f7f76976f0bab43b47f0 | d08e31ba79f972a2983301832ec67b94 | 2022-02-03 16:34:19.953742
    5 | 93de032bc9157362593a0259a8558514 | 6cd1639323a0c1a96fb3e781283e19d3 | 2022-02-03 16:34:19.953742
(8 rows)

更安全的应用用户总结

我们已经展示如何降低意外删除生产数据的风险，通过下面的操作：

确保超级用户是对象的所有者
应用用户仅有新增更新数据的操作权限
通过使用deleted时间戳列，可以更安全的删除数据

现在我们可以放心的休息了，因为我们的生产数据不会受到那些讨厌的测试脚本的影响了！

有关限制数据库用户权限的更多信息，请查看Creating a Read-Only Postgres User博文
PostgreSQL的权限环境非常复杂。最小权限通常要比表面上看到的要多。要更深的研究复杂性，查看 PostgreSQL Defaults and Impact on Security博客系列
如果对保护用户数据方面感兴趣，请查看Crunchy Hardened PostgreSQL的增强型RBAC 和超级用户锁定功能。