[譯]PostgreSQL中更安全的應用使用者

Maleah_rabbit發表於2022-02-19
SQL

原文地址： 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時間戳列，可以更安全的刪除資料

現在我們可以放心的休息了，因為我們的生產資料不會受到那些討厭的測試指令碼的影響了！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70012788/viewspace-2856454/，如需轉載，請註明出處，否則將追究法律責任。

相關文章