PostgreSQL实现跨数据库授权查询的详细步骤

目录

• 引言
• 第一步：回顾与确认授权 (由A用户或超级用户执行)
• 第二步：B 用户进行查询
• 可能遇到的问题与解决方案
• 总结
• 精细的权限控制，授权后怎么能让b用户连上数据库a只能看到授权的表
• 解决方案
• 1. 创建专用模式（推荐）
• 2. 将授php权表移动到专用模式或创建视图
• 3. 设置搜索路径和默认权限
• 4. 精确的权限控制
• 5. 隐藏系统信息
• 完整示例
• 验证效果
• 更严格的安全设置
• 1. 使用行级安全策略（RLS）
• 2. 使用数据库角色继承
• 总结

引言

在PostgreSQL中，由于一个数据库实例下的不同数据库在逻辑上是隔离的，你不能像在同一个数据库内跨模式（schema）那样直接查询。因此，你需要分两步走：先授权，后查询。

你已经完成了第一步“授权”，我们这里会先简要回顾以确保授权正确，然后重点说明第二步，即B用户如何查询。

第一步：回顾与确认授权 (由A用户或超级用户执行)

假设你的环境如下：

• 数据库 A： 用户 a_user 是表 c 的所有者。
• 数据库 B： 用户 b_user 需要查询 a_user 在数据库 A 中的表 c。

授权过程需要在数据库 A 中执行：

连接到数据库 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;

验证权限 (可选)

可以检查权限是否已正确授予。

\androiddp public.c

• 在输出中，你应该能看到 b_user 具有 r (SELECT) 权限。

重要提示： 仅仅这样授权还不够。因为 b_user 默认在数据库 A 中没有登录权限（如果它是一个新用户）。你需要确保 b_user 可以连接到数据库 A。

确保 B 用户能连接数据库 A (如果尚未授权)

-- 在数据库 A 中执行，授予连接权限
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： 强制提示输入密码。

执行查询

连接成功后，你就可以像查询普通表一样，在 psql 命令行中执行 SQL 查询。

SELECT * FROM c LIMIT 10;

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

SELECT * FROM public.c LIMIT 10;

可能遇到的问题与解决方案

错误：权限被拒绝 (Permission denied)

请返回第一步，仔细检查：

1. GRANT CONNECT ON DATABASE a TO b_user; 是否已执行？
2. GRANT USAGE ON SCHEMA public TO b_user; 是否已执行？
3. GRANT SELECT ON public.c TO b_user; 是否已执行？

错误：数据库 “a” 不存在 (Database “a” does not exist)

请检查数据库名称是否正确。可以使用 \l 命令（由超级用户执行）列出所有数据库。

B 用户想在数据库 B 中直接查询数据库 A 的表

这是无法直接实现的。PostgreSQL 不支持跨数据库查询。如果这是硬性需求，你需要使用额外的工具：

• 外部数据包装器 (Fdw)： 这是最正统的解决方案。你可以在数据库 B 中创建一个指向数据库 A 的外部服务器，然后创建一个外部表来映射 a.public.c。之后，b_user 就可以在数据库 B 中查询这个外部表了。
• 逻辑复制： 将表 c 从数据库 A 复制到数据库 B。
• 程序逻辑： 在应用程序中分别连接两个数据库，然后在内存中进行数据关联。

总结

对于你当前的需求，最简单的流程就是：

1. 授权方 (A用户/超级用户)：在数据库 A 中，授予 b_user 连接权限、模式使用权限和表的选择权限。
2. 使用方 (B用户)：连接到数据库 A，然后执行 SELECT * FROM c;。

B用户不能在数据库 B 中直接查询 a.c，必须切换到数据库 A 的连接中。

精细的权限控制，授权后怎么能让b用户连上数据库a只能看到授权的表

这是一个很好的数据库安全实践问题。要让B用户连接上数据库A后只能看到授权的表，需要进行精细的权限控制。以下是完整的实现方案：

解决方案

1. 创建专用模式（推荐）

最佳实践是为B用户创建一个专用模式，而不是使用public模式。

-- 以超级用户或a_user身份在数据库A中执行
CREATE SCHEMA b_user_schema;

2. 将授权表移动到专用模式或创建视图

方案A：移动表到专用模式

-- 将表移动到专用模式
ALTER TABLE public.c SET SCHEMA b_user_schema;

方案B：在专用模式中创建视图（更灵活）

-- 在专用模式中创建视图
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;
-- 然后使用其他方式同步数据（如触发器、作业等）

3. 设置搜索路径和默认权限

-- 设置B用户的搜索路径，使其默认看到专用模式
ALTER USER b_user SET search_path TO b_user_schema, public;

-- 设置专用模式的默认权限（可选）
ALTER DEFAULT PRIVILEGES IN SCHEMA b_user_schema 
GRANTjavascript 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. 隐藏系统信息

-- 禁止查看系统目录（重要！）
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. 创建专用模式
CRqjDrIqKgRTEATE SCHEMA IF NOT EXISTS restricted_schema;

-- 2. 在专用模式中创建视图
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. 保护系统目录
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

连接后执行：

-- 只能看到授权的视图
\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;

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

2. 使用数据库角色继承

-- 创建只读角色
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;

总结

通过这种精细的权限控制，B用户连接数据库A后：

• ✅ 只能看到授权模式下的表/视图
• ✅ 只能执行授予的操作（如SELECT）
• ✅ 无法查看系统目录和其他用户的对象
• ✅ 无法访问public模式下的其他表

这种方案既满足了业务需求，又遵循了最小权限原则，确保了数据库的安全性。

以上就是PostgreSQL实现跨数据库授权查询的详细步骤的详细内容，更多关于PostgreSQL跨数据库授权查询的资料请关注编程客栈(www.devze.com)其它相关文章！