引言

在PostgreSQL中，由于一個(gè)數(shù)據(jù)庫(kù)實(shí)例下的不同數(shù)據(jù)庫(kù)在邏輯上是隔離的，你不能像在同一個(gè)數(shù)據(jù)庫(kù)內(nèi)跨模式（schema）那樣直接查詢。因此，你需要分兩步走：先授權(quán)，后查詢。

你已經(jīng)完成了第一步“授權(quán)”，我們這里會(huì)先簡(jiǎn)要回顧以確保授權(quán)正確，然后重點(diǎn)說明第二步，即B用戶如何查詢。

第一步：回顧與確認(rèn)授權(quán) (由A用戶或超級(jí)用戶執(zhí)行)

假設(shè)你的環(huán)境如下：

• 數(shù)據(jù)庫(kù) A： 用戶 a_user 是表 c 的所有者。
• 數(shù)據(jù)庫(kù) B： 用戶 b_user 需要查詢 a_user 在數(shù)據(jù)庫(kù) A 中的表 c。

授權(quán)過程需要在數(shù)據(jù)庫(kù) A 中執(zhí)行：

連接到數(shù)據(jù)庫(kù) A

psql -d a -U a_user
# 或者使用超級(jí)用戶，如 postgres

授予權(quán)限

你需要至少授予 SELECT 權(quán)限。如果需要，還可以授予 INSERT, UPDATE, DELETE 等。

-- 授予 SELECT 權(quán)限
GRANT SELECT ON public.c TO b_user;

-- 如果需要所有權(quán)限，可以使用 ALL
-- GRANT ALL ON public.c TO b_user;

驗(yàn)證權(quán)限 (可選)

可以檢查權(quán)限是否已正確授予。

\dp public.c

• 在輸出中，你應(yīng)該能看到 b_user 具有 r (SELECT) 權(quán)限。

重要提示： 僅僅這樣授權(quán)還不夠。因?yàn)?nbsp;b_user 默認(rèn)在數(shù)據(jù)庫(kù) A 中沒有登錄權(quán)限（如果它是一個(gè)新用戶）。你需要確保 b_user 可以連接到數(shù)據(jù)庫(kù) A。

確保 B 用戶能連接數(shù)據(jù)庫(kù) A (如果尚未授權(quán))

-- 在數(shù)據(jù)庫(kù) A 中執(zhí)行，授予連接權(quán)限
GRANT CONNECT ON DATABASE a TO b_user;

-- 還需要授予 public schema 的使用權(quán)限（如果尚未擁有）
GRANT USAGE ON SCHEMA public TO b_user;

第二步：B 用戶進(jìn)行查詢

現(xiàn)在，b_user 已經(jīng)獲得了在數(shù)據(jù)庫(kù) A 中查詢表 c 的權(quán)限。b_user 不能從數(shù)據(jù)庫(kù) B 中直接訪問數(shù)據(jù)庫(kù) A 的表。必須連接到數(shù)據(jù)庫(kù) A 才能進(jìn)行查詢。

以下是 b_user 的操作步驟：

連接到正確的數(shù)據(jù)庫(kù)

b_user 必須連接到 數(shù)據(jù)庫(kù) A，而不是數(shù)據(jù)庫(kù) B。

# 通過命令行連接
psql -d a -U b_user -W

• -d a： 指定連接到數(shù)據(jù)庫(kù) a。
• -U b_user： 指定使用用戶 b_user 登錄。
• -W： 強(qiáng)制提示輸入密碼。

執(zhí)行查詢

連接成功后，你就可以像查詢普通表一樣，在 psql 命令行中執(zhí)行 SQL 查詢。

SELECT * FROM c LIMIT 10;

因?yàn)楸?nbsp;c 位于 public 模式中，而 public 模式在搜索路徑中，所以你可以直接使用表名 c。為了更清晰，你也可以使用完全限定名：

SELECT * FROM public.c LIMIT 10;

可能遇到的問題與解決方案

錯(cuò)誤：權(quán)限被拒絕 (Permission denied)
請(qǐng)返回第一步，仔細(xì)檢查：

1. GRANT CONNECT ON DATABASE a TO b_user; 是否已執(zhí)行？
2. GRANT USAGE ON SCHEMA public TO b_user; 是否已執(zhí)行？
3. GRANT SELECT ON public.c TO b_user; 是否已執(zhí)行？

錯(cuò)誤：數(shù)據(jù)庫(kù) “a” 不存在 (Database “a” does not exist)
請(qǐng)檢查數(shù)據(jù)庫(kù)名稱是否正確?？梢允褂?nbsp;\l 命令（由超級(jí)用戶執(zhí)行）列出所有數(shù)據(jù)庫(kù)。

B 用戶想在數(shù)據(jù)庫(kù) B 中直接查詢數(shù)據(jù)庫(kù) A 的表
這是無法直接實(shí)現(xiàn)的。PostgreSQL 不支持跨數(shù)據(jù)庫(kù)查詢。如果這是硬性需求，你需要使用額外的工具：

• 外部數(shù)據(jù)包裝器 (FDW)： 這是最正統(tǒng)的解決方案。你可以在數(shù)據(jù)庫(kù) B 中創(chuàng)建一個(gè)指向數(shù)據(jù)庫(kù) A 的外部服務(wù)器，然后創(chuàng)建一個(gè)外部表來映射 a.public.c。之后，b_user 就可以在數(shù)據(jù)庫(kù) B 中查詢這個(gè)外部表了。
• 邏輯復(fù)制： 將表 c 從數(shù)據(jù)庫(kù) A 復(fù)制到數(shù)據(jù)庫(kù) B。
• 程序邏輯： 在應(yīng)用程序中分別連接兩個(gè)數(shù)據(jù)庫(kù)，然后在內(nèi)存中進(jìn)行數(shù)據(jù)關(guān)聯(lián)。

總結(jié)

對(duì)于你當(dāng)前的需求，最簡(jiǎn)單的流程就是：

1. 授權(quán)方 (A用戶/超級(jí)用戶)：在數(shù)據(jù)庫(kù) A 中，授予 b_user 連接權(quán)限、模式使用權(quán)限和表的選擇權(quán)限。
2. 使用方 (B用戶)：連接到數(shù)據(jù)庫(kù) A，然后執(zhí)行 SELECT * FROM c;。

B用戶不能在數(shù)據(jù)庫(kù) B 中直接查詢 a.c，必須切換到數(shù)據(jù)庫(kù) A 的連接中。

精細(xì)的權(quán)限控制，授權(quán)后怎么能讓b用戶連上數(shù)據(jù)庫(kù)a只能看到授權(quán)的表

這是一個(gè)很好的數(shù)據(jù)庫(kù)安全實(shí)踐問題。要讓B用戶連接上數(shù)據(jù)庫(kù)A后只能看到授權(quán)的表，需要進(jìn)行精細(xì)的權(quán)限控制。以下是完整的實(shí)現(xiàn)方案：

解決方案

1. 創(chuàng)建專用模式（推薦）

最佳實(shí)踐是為B用戶創(chuàng)建一個(gè)專用模式，而不是使用public模式。

-- 以超級(jí)用戶或a_user身份在數(shù)據(jù)庫(kù)A中執(zhí)行
CREATE SCHEMA b_user_schema;

2. 將授權(quán)表移動(dòng)到專用模式或創(chuàng)建視圖

方案A：移動(dòng)表到專用模式

-- 將表移動(dòng)到專用模式
ALTER TABLE public.c SET SCHEMA b_user_schema;

方案B：在專用模式中創(chuàng)建視圖（更靈活）

-- 在專用模式中創(chuàng)建視圖
CREATE VIEW b_user_schema.c_view AS 
SELECT * FROM public.c 
WHERE 1=1; -- 可以在這里添加行級(jí)權(quán)限控制

-- 或者直接復(fù)制表結(jié)構(gòu)（如果不想移動(dòng)原表）
CREATE TABLE b_user_schema.c AS SELECT * FROM public.c LIMIT 0;
-- 然后使用其他方式同步數(shù)據(jù)（如觸發(fā)器、作業(yè)等）

3. 設(shè)置搜索路徑和默認(rèn)權(quán)限

-- 設(shè)置B用戶的搜索路徑，使其默認(rèn)看到專用模式
ALTER USER b_user SET search_path TO b_user_schema, public;

-- 設(shè)置專用模式的默認(rèn)權(quán)限（可選）
ALTER DEFAULT PRIVILEGES IN SCHEMA b_user_schema 
GRANT SELECT ON TABLES TO b_user;

4. 精確的權(quán)限控制

-- 撤銷所有默認(rèn)權(quán)限
REVOKE ALL ON DATABASE a FROM b_user;
REVOKE ALL ON SCHEMA public FROM b_user;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM b_user;

-- 授予最小必要權(quán)限
GRANT CONNECT ON DATABASE a TO b_user;
GRANT USAGE ON SCHEMA b_user_schema TO b_user;
GRANT SELECT ON ALL TABLES IN SCHEMA b_user_schema TO b_user;

-- 如果使用視圖
GRANT SELECT ON b_user_schema.c_view TO b_user;

5. 隱藏系統(tǒng)信息

-- 禁止查看系統(tǒng)目錄（重要?。?
REVOKE ALL ON pg_catalog.pg_class FROM b_user;
REVOKE ALL ON pg_catalog.pg_namespace FROM b_user;
REVOKE ALL ON pg_catalog.pg_attribute FROM b_user;
REVOKE ALL ON pg_catalog.pg_type FROM b_user;

-- 或者更嚴(yán)格地，在postgresql.conf中設(shè)置
-- row_security = on

完整示例

-- 1. 創(chuàng)建專用模式
CREATE SCHEMA IF NOT EXISTS restricted_schema;

-- 2. 在專用模式中創(chuàng)建視圖
CREATE OR REPLACE VIEW restricted_schema.c_restricted AS 
SELECT id, name, created_date 
FROM public.c 
WHERE status = 'active';  -- 可以添加行級(jí)過濾

-- 3. 設(shè)置用戶權(quán)限
REVOKE ALL ON DATABASE a FROM b_user;
GRANT CONNECT ON DATABASE a TO b_user;

REVOKE ALL ON SCHEMA public FROM b_user;
GRANT USAGE ON SCHEMA restricted_schema TO b_user;

GRANT SELECT ON restricted_schema.c_restricted TO b_user;

-- 4. 設(shè)置用戶搜索路徑
ALTER USER b_user SET search_path TO restricted_schema;

-- 5. 保護(hù)系統(tǒng)目錄
REVOKE ALL ON pg_catalog.pg_class FROM b_user;
REVOKE ALL ON pg_catalog.pg_namespace FROM b_user;

驗(yàn)證效果

讓B用戶連接數(shù)據(jù)庫(kù)A進(jìn)行測(cè)試：

psql -d a -U b_user -W

連接后執(zhí)行：

-- 只能看到授權(quán)的視圖
\dt
-- 或
\dn+

-- 可以查詢授權(quán)視圖
SELECT * FROM c_restricted;

-- 嘗試訪問其他表會(huì)失敗
SELECT * FROM public.c;  -- 權(quán)限被拒絕
SELECT * FROM other_table;  -- 表不存在
\dt public.*  -- 看不到public模式下的表

更嚴(yán)格的安全設(shè)置

如果需要更高級(jí)別的隔離，可以考慮：

1. 使用行級(jí)安全策略（RLS）

-- 在表上啟用行級(jí)安全
ALTER TABLE public.c ENABLE ROW LEVEL SECURITY;

-- 創(chuàng)建策略只允許查看特定行
CREATE POLICY b_user_policy ON public.c
FOR SELECT TO b_user
USING (department = 'authorized_department');

2. 使用數(shù)據(jù)庫(kù)角色繼承

-- 創(chuàng)建只讀角色
CREATE ROLE read_only;
GRANT USAGE ON SCHEMA restricted_schema TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA restricted_schema TO read_only;

-- 將用戶賦予角色
GRANT read_only TO b_user;

總結(jié)

通過這種精細(xì)的權(quán)限控制，B用戶連接數(shù)據(jù)庫(kù)A后：

• ? 只能看到授權(quán)模式下的表/視圖
• ? 只能執(zhí)行授予的操作（如SELECT）
• ? 無法查看系統(tǒng)目錄和其他用戶的對(duì)象
• ? 無法訪問public模式下的其他表

這種方案既滿足了業(yè)務(wù)需求，又遵循了最小權(quán)限原則，確保了數(shù)據(jù)庫(kù)的安全性。

以上就是PostgreSQL實(shí)現(xiàn)跨數(shù)據(jù)庫(kù)授權(quán)查詢的詳細(xì)步驟的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL跨數(shù)據(jù)庫(kù)授權(quán)查詢的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論