当前位置：首页 > 数据库 > PostgreSQL教程 > [译]PostgreSQL中更安全的应用用户 > 正文

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

来源：这里教程网 时间：2026-03-14 20:27:22 作者：

原文地址： https://blog.crunchydata.com/blog/safer-application-users-in-postgres

原文作者： MIKE PALMIOTTO

“我们删了数据库”

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

  1. Drop所有的表，删除 schema
  2. 从头开始 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列。有两个好处：

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

    增加 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)

    更安全的应用用户总结

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

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

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

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

    • 编辑推荐：

    相关推荐

    最新软件资讯

    check_postgres脚本集检查数据库健康情况
    check_postgres脚本集检查数据库健康情况

    热文推荐

    天极热推