postgres 創建可以選擇表和這些表的列的受限使用者

我有一個應用程序可以自動創建 PostgreSQL 數據庫，還可以自動創建數據庫使用者。出於安全目的，此數據庫使用者被授予有限訪問權限，因為它將被移交給客戶端使用。 我使用的版本是 Postgres 9.6

CREATE USER %USERSETUP% WITH
   LOGIN PASSWORD '%USERSETUPPASS%'
   NOSUPERUSER
   NOCREATEDB
   NOCREATEROLE
   INHERIT
   NOREPLICATION
   CONNECTION LIMIT -1;

ALTER DEFAULT PRIVILEGES IN SCHEMA %SCHEMA% GRANT INSERT, SELECT, UPDATE, DELETE, REFERENCES ON TABLES TO %USERSETUP%;

現在，我的測試也進入了創建的數據庫，並驗證它是否已正確創建，使用查詢腳本獲取表名，然後獲取每個表中的列名（作為獎勵，還獲取列數據類型）

由於這些權限，使用者不能使用 information_schema。 另外，我隻請求帶有 SYSTEM CATALOG 的查詢腳本

我為表名編寫的腳本有效：

SELECT tablename FROM pg_catalog.pg_tables where schemaname = '{schema}'

這會將表名作為列表返回：

業務、位置、人員、客戶等…

但是，當我執行以下兩個腳本來獲取列名時，我得到的返回值為 0，就好像這個使用者正在嘗試查詢 information_schema。我猜這是因為特權：

SELECT c.oid,
 n.nspname,
 c.relname
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ 'schemaaryzdhyqoi'
 AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

還

SELECT c.oid,
 n.nspname,
 c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
 SELECT a.attname,
   pg_catalog.format_type(a.atttypid, a.atttypmod),
   (
     SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
     FROM pg_catalog.pg_attrdef d
     WHERE d.adrelid = a.attrelid
       AND d.adnum = a.attnum
       AND a.atthasdef
   ),
   a.attnotnull, a.attnum,
   (
     SELECT c.collname
     FROM
       pg_catalog.pg_collation c,
       pg_catalog.pg_type t
     WHERE c.oid = a.attcollation
       AND t.oid = a.atttypid
       AND a.attcollation <> t.typcollation
   ) AS attcollation
 FROM pg_catalog.pg_attribute a
 WHERE a.attrelid = c.oid
   AND a.attnum > 0
   AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(schemaaryzdhyqoi)$'  -- YOUR SCHEMA HERE
AND pg_catalog.pg_table_is_visible(c.oid);

問題是：如何為受限使用者添加權限，使其也能夠僅訪問架構中每個表內的列的名稱（可能還有數據類型）？

您可以將列級別權限設置為非常明確（並且嚴格）允許其他非特權使用者訪問數據。在您的情況下，它是兩個目錄：pg_attribute和pg_type.

我有一個使用者alice，他與您的使用者同樣受到限制：

alice@test=> > SELECT * FROM pg_class LIMIT 1;
ERROR:  permission denied for relation pg_class

發出以下GRANT語句：

GRANT SELECT (oid, typname) ON pg_type TO alice;
GRANT SELECT (attrelid, attname, atttypid, attnum) ON TABLE pg_attribute TO alice;

在這些之後，alice可以執行以下操作，例如：

SELECT attname, typname 
 FROM pg_attribute JOIN pg_type t ON t.oid = atttypid
WHERE attrelid = 't2'::regclass AND attnum > 0;

attname │ typname 
─────────┼─────────
key     │ jsonb
value   │ jsonb

您可以輕鬆地擴展權限以滿足您的需求。

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