將 CREATE SCHEMA（僅）授予使用者

想像一下，我為我的班級設置了一個共享數據庫，每個學生都有可能訪問（對數據庫的訪問是開放的，只需要 kerberos 身份驗證）。公共空間是我放置所有預設課程材料（表格、視圖等）的地方，任何感興趣的人都可以訪問其中的大部分內容。

我想要的是：

1. 每個學生（任何經過身份驗證的使用者）都能夠創建自己的模式並將其用作他們可以創建表和視圖的範圍
2. 學生不能在公共場合創建表/視圖/對象
3. 將這些設置應用於所有使用者；或者更具體地說，在分配權限時不必針對特定的使用者帳戶

---

思考過程：

REVOKE ALL ON DATABASE class FROM PUBLIC
GRANT CREATE ON DATABASE class TO PUBLIC  -- can it be CREATE SCHEMA ?
-- being able to create on database makes it seem like they can create in public
-- can permissions be revoked from public?

作為使用者：

psql -h <host> class

-- desire: permitted
CREATE SCHEMA <current_user>;
GRANT USAGE ON <current_user> TO teacher;
CREATE TABLE <current_user>.<table name> ...; 
SELECT * FROM public.<table name>;            
SELECT * FROM <current_user>.<table name>;    

-- desire: not permitted
CREATE TABLE public.<table name> ...;

---

我不確定這些是正確的權限。我也很好奇我是否可以創建一個卷並使用該角色來管理權限。棘手的一點是我希望任何經過身份驗證的使用者都成為該角色的一部分，我認為您不能將 public 添加到角色中，所以我不知道這將如何工作。

我還沒有徹底考慮過這一點，但感謝提供的任何回饋、疑慮和答案。當然，我可以回答問題以進行澄清——在提供全面細節的同時保持簡潔是一個挑戰。

Postgres 非常擅長做這些事情，這也是我喜歡它的原因之一。

您可以創建任意複雜的角色層次結構，但就我們的目的而言，一個簡單的角色層次結構就足夠了：

CREATE ROLE student;

REVOKE CREATE ON SCHEMA public FROM public;  -- the second public is a pseudorole
                                            -- every role (user, group) is a member of
REVOKE CREATE ON DATABASE class FROM public; -- disallows schema creation, see below

GRANT USAGE ON SCHEMA public TO student;

此時，student是一個無法登錄的角色。我們將使用它作為實際學生的權限收集器，您必須像這樣創建它：

CREATE USER jsmith IN ROLE student;  -- will be able to log in, 
                                    -- and a member of the student role

您可以將後兩個語句放入儲存的函式/過程（其中將包括一些動態 SQL）或您的第二喜歡語言的腳本。

這樣，您的學生將能夠為自己創建一個模式（名稱可以是任何名稱），並且由於他們擁有該模式，他們還可以在其中創建對象。如果要將它們限制為以它們命名的模式，請在 之後執行以下操作CREATE USER：

CREATE SCHEMA jsmith AUTHORIZATION jsmith;

要允許教師訪問所有學生模式和對象，最簡單的解決方案是：

GRANT student TO teacher;

現在剩下的是允許學生訪問public模式中現有和未來的表：

GRANT SELECT ON ALL TABLES IN SCHEMA public TO student;
ALTER DEFAULT PRIVILEGES IN SCHEMA public FOR ROLE teacher 
     GRANT SELECT ON TABLES TO student;

最後一個命令假定表將由 user/role 創建teacher。

您可能想以不同的方式解決這個問題。創建一個教師可以執行的腳本（假設教師被設置為數據庫上的 dba）來設置所有這些。該腳本將完成您讓學生啟動和執行所需的一切。

創建腳本：輸入使用者名

• 在數據庫上創建使用者
• 創建架構
• 根據模板架構創建所需的對象
• 從模板模式載入所需數據
• 根據需要授予/撤銷權限

拆解腳本：輸入使用者名

• 刪除架構
• 刪除使用者

你可以讓它更高級一點，並允許傳入一封電子郵件，它可以通過電子郵件向學生髮送他們登錄數據庫所需的資訊。這是假設您的數據庫有權發送電子郵件和使用可靠的電子郵件系統。

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