postgresql 9.* - 使用者無法從 SCHEMA 內的 VIEW 中選擇作為預設特權

希望您能夠幫助我。

我有 3 個使用者

• 首先是我，數據庫管理員。我執行 GRANT 查詢，我可以做任何事情。（名字現在不重要了）
• 其次是數據管理員使用者。= manager_u
• 第三是數據查看器使用者。= viewer_u

“數據查看器”應該從“數據管理器”創建的視圖中讀取，之後無需管理權限 因此我創建了一個由管理器擁有的模式，該模式應該對查看器完全開放。

問題/實際情況是（在執行我的以下程式碼之後）這個。

當 manager_u 創建一個命名視圖時， viewer_u 不能從中選擇。

如果我執行這個，在創建之後：

GRANT SELECT ON ALL TABLES IN SCHEMA views_for_viewer_u TO viewer_u;

他可以閱讀。一切正常。但我喜歡他可以在不管理他的權限的情況下閱讀任何進一步的觀點。我試過了，但沒有成功：

ALTER DEFAULT PRIVILEGES FOR ROLE viewer_u IN SCHEMA views_for_viewer_u GRANT SELECT ON TABLES TO viewer_u;

在這裡，完整的程式碼：

--
-- QUERIES as db admin
-- 

CREATE ROLE manager_u LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE viewer_u LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;


ALTER ROLE manager_u WITH PASSWORD 'yyy';
ALTER ROLE viewer_u WITH PASSWORD 'xxx';


GRANT CONNECT ON DATABASE mydb TO manager_u;
GRANT CONNECT ON DATABASE mydb TO viewer_u;


---
---                     schema
---
CREATE SCHEMA IF NOT EXISTS views_for_viewer_u;

ALTER SCHEMA views_for_viewer_u OWNER TO manager_u;

GRANT ALL PRIVILEGES ON SCHEMA views_for_viewer_u TO manager_u;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA views_for_viewer_u TO manager_u;
ALTER DEFAULT PRIVILEGES FOR ROLE manager_u IN SCHEMA views_for_viewer_u GRANT ALL PRIVILEGES ON TABLES TO manager_u;

---
---   privileges on views_for_viewer_u schema
---   I think the problem is here but I'm going crazy
---   I beg help
---

GRANT USAGE ON SCHEMA views_for_viewer_u TO viewer_u;
GRANT SELECT ON ALL TABLES IN SCHEMA views_for_viewer_u TO viewer_u;
ALTER DEFAULT PRIVILEGES FOR ROLE viewer_u IN SCHEMA views_for_viewer_u GRANT SELECT ON TABLES TO viewer_u;



--
-- QUERIES as data manager:  manager_u
--

CREATE OR REPLACE VIEW views_for_viewer_u.proxy_table
   AS SELECT * FROM another_schema.a_table;


--
-- QUERIES as viewer:  viewer_u
--

SELECT * FROM views_for_viewer_u.proxy_table; -- this is what is not working
--
--
-- thanks to anyone can give an hint
--
--    

你的錯誤在ALTER DEFAULT PRIVILEGES命令中。它應該是

ALTER DEFAULT PRIVILEGES **FOR ROLE manager_u** ...

這樣，由創建的視圖manager_u將獲得授予的權限。

根據創建者角色在創建時授予預設權限。

FOR ROLE xxx

代表那個。

問題出在這裡：xxx 應該是誰將進行創建查詢…如果省略“FOR ROLE xxx”，則“FOR ROLE actual_role”將是隱含的：在我的情況下是 db admin。

所以我不得不這樣做。

因為 manager_u 是誰將創建表…

GRANT USAGE ON SCHEMA views_for_viewer_u TO viewer_u;
GRANT SELECT ON ALL TABLES IN SCHEMA views_for_viewer_u TO viewer_u;
--- here
ALTER DEFAULT PRIVILEGES FOR ROLE manager_u IN SCHEMA views_for_viewer_u GRANT 
SELECT ON TABLES TO viewer_u;

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