postgres：模式的權限被拒絕

我是 postgresql 的新手，如果您能就如何解決以下錯誤提供建議，我將不勝感激。

我已發出以下命令：

ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA doc FROM PUBLIC ;
ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA net FROM PUBLIC ;

ip_spotlight# GRANT USAGE ON SCHEMA doc TO netopsapp ;

ip_spotlight-# ALTER DEFAULT PRIVILEGES IN SCHEMA doc GRANT ALL ON TABLES TO netopsapp ;
ip_spotlight-# ALTER DEFAULT PRIVILEGES IN SCHEMA net GRANT ALL ON TABLES TO netopsapp ;

以下是特權列表：

ip_spotlight# \dn+
                         List of schemas
 Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
doc    | postgres |                      | 
net    | postgres |                      | 
public | postgres | postgres=UC/postgres+| standard public schema
       |          | =UC/postgres         | 
(3 rows)

錯誤是：

python3 -m pwiz --engine=postgresql --host=x.x.x.x --port=5432 --user=netopsapp --password  --schema=doc --tables=bgp_communities ip_spotlight
Password: 
Traceback (most recent call last):
 File "/usr/lib/python3.4/site-packages/peewee.py", line 3768, in execute_sql
   cursor.execute(sql, params or ())
psycopg2.ProgrammingError: permission denied for schema doc
LINE 1: SELECT * FROM "doc"."bgp_communities" LIMIT 1

您能否建議如何設置權限以便netopsapp使用者可以訪問架構中定義的表doc

PS：前 2 個命令在 postgresql 書中被提及為最佳實踐

這解決了它：

postgres=# \connect ip_spotlight
You are now connected to database "ip_spotlight" as user "postgres".
ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netops ;
ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netopsapp ;

ip_spotlight=# GRANT SELECT ON ALL TABLES IN SCHEMA doc,net TO netops ;
ip_spotlight=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA doc,net TO netops ;
ip_spotlight-# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA doc,net TO netops ;

ip_spotlight-# GRANT ALL ON ALL TABLES IN SCHEMA doc,net TO netopsapp ;
ip_spotlight-# GRANT ALL ON ALL SEQUENCES IN SCHEMA doc,net TO netopsapp ;
ip_spotlight-# GRANT ALL ON ALL FUNCTIONS IN SCHEMA doc,net TO netopsapp ;

GRANT/GRANT ALL當連接到正確的數據庫時，您沒有執行。也許你混淆了開發和生產？

當您\dn+在 ip_spotlight 上執行時，您可以看到您沒有授予netopsapp. 否則應該在那裡列出。

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