如何在 DATABASE vs SCHEMA 上管理使用者的預設權限？

我想將一個相當簡單的、內部的、數據庫驅動的應用程序從 SQLite3 遷移到 PostgreSQL 9.3，並在我進行時收緊數據庫中的權限。

該應用程序目前包含一個更新數據的命令；和一個查詢它。當然，我還需要以其他方式維護數據庫（創建新表、視圖、觸發器等）。

雖然這個應用程序最初將是唯一託管在伺服器上的應用程序，但我更願意假設它將來可能會託管在具有其他數據庫的伺服器上，而不是在以後有必要時不得不爭先恐後未來。

我認為這些將是一組相當常見的要求，但我很難找到一個簡單的教程來解釋如何在 PostgreSQL 中設置一個新的數據庫，並使用這種使用者/權限分離。關於組、使用者、角色、數據庫、模式和域的參考資料很詳細；但我發現它們令人困惑。

這是我迄今為止嘗試過的（從內部psql作為’postgres’）：

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;
\connect hostdb
CREATE SCHEMA hostdb;
CREATE USER hostdb_admin WITH PASSWORD 'youwish';
CREATE USER hostdb_mgr   WITH PASSWORD 'youwish2';
CREATE USER hostdb_usr WITH PASSWORD 'youwish3';

GRANT ALL PRIVILEGES ON DATABASE hostdb TO hostdb_admin;
GRANT CONNECT ON DATABASE hostdb TO hostdb_mgr, hostdb_usr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hostdb_mgr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT ON TABLES TO hostdb_usr;

但我沒有得到預期的語義。我想對其進行配置，以便只有hostdb_admin可以創建（以及刪除和更改）表；預設情況下hostdb_mgr可以對所有表進行讀取、插入、更新和刪除；並且hostdb_usr只能讀取所有表（和視圖）。

當我嘗試這個時，我發現我能夠以hostdb任何這些使用者的身份創建表；但是，對於每個使用者，我只能讀取或修改該使用者創建的表——除非我使用顯式的GRANT.

我猜在CREATE DATABASEand之間缺少CREATE SCHEMA一些東西，可以應用SCHEMA到DATABASE?

（隨著事情變得更先進，我也會有問題需要對TRIGGERS、儲存過程VIEWS和其他對象應用類似的限制）。

我在哪裡可以找到一個不錯的指南、教程或影片系列？

Postgres 14添加了預定義的角色 pg_read_all_data和pg_write_all_data，它允許使用快捷方式來實現名稱所暗示的目的。

---

我在哪裡可以找到一個不錯的指南、教程或影片系列？

您會在手冊中找到*所有內容。*下面的連結。

誠然，這件事並非微不足道，有時甚至令人困惑。這是案例的配方：

食譜

我想對其進行配置，以便只有hostdb_admin可以創建（以及刪除和更改）表；

預設情況下hostdb_mgr可以對所有表進行讀取、插入、更新和刪除；

並且hostdb_usr只能讀取所有表（和視圖）。

作為超級使用者postgres：

CREATE USER schma_admin WITH PASSWORD 'youwish';
-- CREATE USER schma_admin WITH PASSWORD 'youwish' CREATEDB CREATEROLE; -- see below
CREATE USER schma_mgr   WITH PASSWORD 'youwish2';
CREATE USER schma_usr   WITH PASSWORD 'youwish3';

如果你想要一個更強大的管理員，也可以管理數據庫和角色，添加角色屬性CREATEDB及CREATEROLE以上。

將每個角色授予下一個更高級別，因此所有級別都“繼承”至少來自下一個較低級別（級聯）的權限集：

GRANT schma_usr TO schma_mgr;
GRANT schma_mgr TO schma_admin;

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;  -- see notes below!

GRANT CONNECT ON DATABASE hostdb TO schma_usr;  -- others inherit

\connect hostdb  -- psql syntax

我正在命名模式schma（不會hostdb令人困惑）。選擇任何名稱。（可選）使schma_admin架構的所有者：

CREATE SCHEMA schma AUTHORIZATION schma_admin;

SET search_path = schma;  -- see notes

ALTER ROLE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inherited
ALTER ROLE schma_mgr   IN DATABASE hostdb SET search_path = schma;
ALTER ROLE schma_usr   IN DATABASE hostdb SET search_path = schma;

GRANT USAGE  ON SCHEMA schma TO schma_usr;
GRANT CREATE ON SCHEMA schma TO schma_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT SELECT                           ON TABLES TO schma_usr;  -- only read

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO schma_mgr;  -- + write, TRUNCATE optional

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO schma_mgr;  -- SELECT, UPDATE are optional 

請and drop and alter參閱下面的註釋。

Postgres 14添加了預定義的非登錄角色pg_read_all_data，並pg_write_all_data為所有模式中的所有對象提供只讀/只寫訪問權限。這超出了這裡的要求，但可能有用。看：

• PostgreSQL：將所有權限授予 PostgreSQL 數據庫上的使用者

---

隨著事情變得更先進，我也會有問題要對TRIGGERS、儲存過程VIEWS和可能的其他對象應用類似的限制。

觀點很特別。

手冊：

…（但請注意，這ALL TABLES被認為包括視圖和外部表）。

對於可更新視圖：

請注意，對視圖執行插入、更新或刪除操作的使用者必須對視圖具有相應的插入、更新或刪除權限。此外，視圖的所有者必須對基礎基礎關係具有相關權限，但執行更新的使用者不需要基礎基礎關係的任何權限（參見 第 38.5 節）。

觸發器也很特殊。您需要在TRIGGER桌子上的特權，並且：

• 在 PostgreSQL 8.4 中執行觸發器函式需要哪些權限？

重要筆記

所有權

如果您想允許schma_admin（單獨）刪除和更改表，請讓角色擁有所有對象。文件：

刪除對像或以任何方式更改其定義的權利不被視為可授予的特權；它是所有者固有的，不能授予或撤銷。（但是，通過授予或撤銷擁有該對象的角色的成員資格可以獲得類似的效果；見下文。）所有者也隱含地擁有該對象的所有授予選項。

ALTER TABLE some_tbl OWNER TO schma_admin;

或者創建具有角色schma_admin的所有對象，然後您無需顯式設置所有者。它還簡化了預設權限，您只需為一個角色設置：

預先存在的對象

預設權限僅適用於新創建的對象，並且僅適用於創建它們的特定角色。您還需要調整現有對象的權限：

• 關係 <table> 的權限被拒絕

如果您創建具有未DEFAULT PRIVILEGES設置角色的對象（例如 superuser ），這同樣適用postgres。手動重新分配所有權schma_admin並設置權限 - 或同時設置權限DEFAULT PRIVILEGES（postgres連接到正確的數據庫時！）：

ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ...  -- etc.

預設權限

ALTER DEFAULT PRIVILEGES你錯過了命令的一個重要方面。除非另有說明，否則它適用於目前角色：

• 無法更改預設權限

預設權限僅適用於目前數據庫。所以你不會弄亂數據庫集群中的其他數據庫。文件：

對於在目前數據庫中創建的所有對象

您可能還想為FUNCTIONSand TYPES（不僅僅是TABLESand SEQUENCES）設置預設權限，但可能不需要這些權限。

預設權限PUBLIC

授予的預設權限PUBLIC是基本的並且被某些人高估了。文件：

PostgreSQL 將某些類型的對象的預設權限授予 PUBLIC. **預設情況下，不會對錶、列、模式或表空間****授予任何權限。PUBLIC**對於其他類型，授予的預設權限PUBLIC如下：CONNECT和CREATE TEMP TABLE對於數據庫；EXECUTE功能特權；和USAGE 語言的特權。

大膽強調我的。通常，這一個命令（包括在頂部）就是您所需要的：

REVOKE ALL ON DATABASE hostdb FROM public;

特別是，沒有PUBLIC為新模式授予預設權限。名為“public”的預設模式ALL以PUBLIC. 這只是一個方便的功能，可以簡化新創建的數據庫的啟動。它不會以任何方式影響其他模式。您可以在模板數據庫中撤銷這些權限template1，然後此集群中所有新創建的數據庫都將在沒有它們的情況下啟動：

\connect template1
REVOKE ALL ON SCHEMA public FROM public;

特權TEMP

由於我們撤銷了hostdbfrom 的所有權限PUBLIC，因此普通使用者無法創建臨時表，除非我們明確允許。您可能想要也可能不想添加：

GRANT TEMP ON DATABASE hostdb TO schma_mgr;

search_path

不要忘記設置search_path. 如果集群中只有一個數據庫，則可以在postgresql.conf. 否則（更有可能）將其設置為數據庫的屬性，或者僅用於涉及的角色，甚至兩者的組合。細節：

• search_path 如何影響標識符解析和“目前模式”

如果您也schma, public使用公共模式，或者甚至（不太可能）$user, schma, public…

search_path另一種方法是使用預設模式“public”，除非您更改它，否則它應該與預設設置一起使用。PUBLIC在這種情況下，請記住撤銷權限。

有關的

• 授予 PostgreSQL 中特定數據庫的權限
• PostgreSQL - 只應允許 DB 使用者呼叫函式

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