原文地址：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 和超級用戶鎖定功能。
