[译]postgresql中更安全的应用用户 -m6米乐安卓版下载

原创 lmj 2022-02-19

844

原文地址：
原文作者：
翻译：lmj

“我们删了数据库”。

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

drop所有的表，删除schema
从头开始create

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

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

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

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

drop表
truncate表

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

超级用户

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

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

postgres=# create user admin with password 'correcthorsebatterystaple' superuser;
create role
postgres=# \du admin
           list of roles
 role name | attributes | member of
----------- ------------ -----------
 admin     | superuser  | {}

应用用户

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

不要给应用用户赋予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

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

检查访问权限

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

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

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)