PostgreSQL 授予架構的所有權限，但使用者仍然無法插入記錄

這令人抓狂。

我正在使用標準映像在 Docker 容器中執行 PostgreSQL 14 。

# docker-compose.yml
version: "3.7"

services:
 pg:
   image: postgres
   environment:
     - POSTGRES_PASSWORD=localdev
   ports:
     - "5432:5432"
   volumes:
     - ./pg/init:/docker-entrypoint-initdb.d

我引導了幾個模式（foo和bar）和幾個使用者（foo_dba和bar_dba）；並使每個使用者成為其各自架構的所有者。

CREATE USER foo_dba WITH PASSWORD 'localdev';
CREATE SCHEMA foo AUTHORIZATION foo_dba;
GRANT CONNECT ON DATABASE postgres TO foo_dba;
GRANT ALL PRIVILEGES ON SCHEMA foo TO foo_dba;

CREATE USER bar_dba WITH PASSWORD 'localdev';
CREATE SCHEMA bar AUTHORIZATION bar_dba;
GRANT CONNECT ON DATABASE postgres TO bar_dba;
GRANT ALL PRIVILEGES ON SCHEMA bar TO bar_dba;

以 身份登錄時foo_dba，我創建了一個名為foo.things（在foo模式中）的表，並授予bar_dba向其中插入記錄的能力。

CREATE TABLE IF NOT EXISTS foo.things (
   id            SERIAL        PRIMARY KEY
,   uid           UUID          NOT NULL UNIQUE DEFAULT gen_random_uuid()
,   name          TEXT          NOT NULL
,   created_at    TIMESTAMPTZ   NOT NULL DEFAULT timezone('utc', now())
,   updated_at    TIMESTAMPTZ
);

GRANT CREATE ON SCHEMA foo TO bar_dba;
GRANT USAGE ON SCHEMA foo TO bar_dba;
GRANT INSERT ON TABLE foo.things TO bar_dba;
GRANT USAGE ON SEQUENCE foo.things_id_seq TO bar_dba;

information_schema我在適當的視圖（例如role_table_grants，role_usage_grants）中驗證權限。只是為了理智，我執行以下查詢foo_dba：

INSERT INTO foo.things (name)
VALUES ('foo was here!')
RETURNING *;

……它成功了。到現在為止還挺好。

接下來，我連接到數據庫bar_dba並嘗試執行類似的查詢：

INSERT INTO foo.things (name)
VALUES ('bar was here!')
RETURNING *;

…我被拒絕訪問。

---

我試過的：

• RTFM 了解GRANT和權限
• 盡我所能消除一切以保持盡可能簡單
• 授予在 SCHEMA 上創建
• 在 SCHEMA 上授予使用
• 在表上授予插入
• idSERIAL列的 GRANT USAGE ON SEQUENCE
• 授予 SCHEMA 上的所有權限 (!!)
• 確認bar_dba可以執行 SELECT gen_random_uuid()
• 將其關閉並重新打開
• docker-compose 日誌 pg
• docker image prune –all -f
• docker 卷修剪 -f

---

我期望會發生什麼：

架構的所有者foo應該能夠向另一個使用者授予權限，以在他們擁有的架構中的表上執行插入。

---

實際發生了什麼：

postgres.public> INSERT INTO foo.things (name)
                VALUES ('bar was here!')
                RETURNING *
[2022-01-08 02:02:43] [42501] ERROR: permission denied for table things

---

我只是沒看到。我在這裡想念什麼？

您還需要授予選擇權限。我相信這是為了驗證uid和id的唯一性。嘗試插入值時可能會誤用沒有選擇的授權插入，如果失敗，使用者會知道該值已經存在。

更正，以上對更新有效。

在您的情況下，返回是罪魁禍首。刪除它並且 bar_dba 可以插入。

引用自：https://dba.stackexchange.com/questions/305807