腳本之家服務器常用軟件

快捷導航

PostgreSQL實現跨數據庫授權查詢的詳細步驟

更新時間：2025年10月23日 09:50:00 作者：安審若無

在PostgreSQL中,由于一個數據庫實例下的不同數據庫在邏輯上是隔離的,你不能像在同一個數據庫內跨模式那樣直接查詢,因此,你需要分兩步走：先授權,后查詢,所以本文給大家介紹了PostgreSQL實現跨數據庫授權查詢的詳細步驟,需要的朋友可以參考下

引言

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

你已經完成了第一步“授權”，我們這里會先簡要回顧以確保授權正確，然后重點說明第二步，即B用戶如何查詢。

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

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

數據庫 A：用戶 a_user 是表 c 的所有者。
數據庫 B：用戶 b_user 需要查詢 a_user 在數據庫 A 中的表 c。

授權過程需要在數據庫 A 中執(zhí)行：

連接到數據庫 A

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

授予權限

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

-- 授予 SELECT 權限
GRANT SELECT ON public.c TO b_user;

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

驗證權限 (可選)

可以檢查權限是否已正確授予。

\dp public.c

在輸出中，你應該能看到 b_user 具有 r (SELECT) 權限。

重要提示：僅僅這樣授權還不夠。因為 b_user 默認在數據庫 A 中沒有登錄權限（如果它是一個新用戶）。你需要確保 b_user 可以連接到數據庫 A。

確保 B 用戶能連接數據庫 A (如果尚未授權)

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

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

第二步：B 用戶進行查詢

現在，b_user 已經獲得了在數據庫 A 中查詢表 c 的權限。b_user 不能從數據庫 B 中直接訪問數據庫 A 的表。必須連接到數據庫 A 才能進行查詢。

以下是 b_user 的操作步驟：

連接到正確的數據庫

b_user 必須連接到 數據庫 A，而不是數據庫 B。

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

-d a：指定連接到數據庫 a。
-U b_user：指定使用用戶 b_user 登錄。
-W：強制提示輸入密碼。

執(zhí)行查詢

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

SELECT * FROM c LIMIT 10;

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

SELECT * FROM public.c LIMIT 10;

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

錯誤：權限被拒絕 (Permission denied)
請返回第一步，仔細檢查：

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

錯誤：數據庫 “a” 不存在 (Database “a” does not exist)
請檢查數據庫名稱是否正確。可以使用 \l 命令（由超級用戶執(zhí)行）列出所有數據庫。

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

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

總結

對于你當前的需求，最簡單的流程就是：

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

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

精細的權限控制，授權后怎么能讓b用戶連上數據庫a只能看到授權的表

這是一個很好的數據庫安全實踐問題。要讓B用戶連接上數據庫A后只能看到授權的表，需要進行精細的權限控制。以下是完整的實現方案：

解決方案

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

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

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

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

方案A：移動表到專用模式

-- 將表移動到專用模式
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; -- 可以在這里添加行級權限控制

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

3. 設置搜索路徑和默認權限

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

-- 設置專用模式的默認權限（可選）
ALTER DEFAULT PRIVILEGES IN SCHEMA b_user_schema 
GRANT SELECT ON TABLES TO b_user;

4. 精確的權限控制

-- 撤銷所有默認權限
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;

-- 授予最小必要權限
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;

-- 或者更嚴格地，在postgresql.conf中設置
-- 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';  -- 可以添加行級過濾

-- 3. 設置用戶權限
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. 設置用戶搜索路徑
ALTER USER b_user SET search_path TO restricted_schema;

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

驗證效果

讓B用戶連接數據庫A進行測試：

psql -d a -U b_user -W

連接后執(zhí)行：

-- 只能看到授權的視圖
\dt
-- 或
\dn+

-- 可以查詢授權視圖
SELECT * FROM c_restricted;

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

更嚴格的安全設置

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

1. 使用行級安全策略（RLS）

-- 在表上啟用行級安全
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. 使用數據庫角色繼承

-- 創(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;