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

引言

在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;

验证权限 (可选)

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

\dp 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)
请返回第一步，仔细检查：

grant connect on database a to b_user; 是否已执行？
grant usage on schema public to b_user; 是否已执行？
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。
程序逻辑：在应用程序中分别连接两个数据库，然后在内存中进行数据关联。

总结

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

授权方 (a用户/超级用户)：在数据库 a 中，授予 b_user 连接权限、模式使用权限和表的选择权限。
使用方 (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 
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. 隐藏系统信息

-- 禁止查看系统目录（重要！）
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. 创建专用模式
create 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跨数据库授权查询的资料请关注代码网其它相关文章！

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

2025年10月23日 • MsSqlserver •我要评论

引言