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

psql -d a -u a_user
# 或者使用超级用户，如 postgres

-- 授予 select 权限
grant select on public.c to b_user;

-- 如果需要所有权限，可以使用 all
-- grant all on public.c to b_user;

\dp public.c

-- 在数据库 a 中执行，授予连接权限
grant connect on database a to b_user;

-- 还需要授予 public schema 的使用权限（如果尚未拥有）
grant usage on schema public to b_user;

# 通过命令行连接
psql -d a -u b_user -w

select * from c limit 10;

select * from public.c limit 10;

-- 以超级用户或a_user身份在数据库a中执行
create schema b_user_schema;

-- 将表移动到专用模式
alter table public.c set schema b_user_schema;

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

-- 设置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;

-- 撤销所有默认权限
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;

-- 禁止查看系统目录（重要！）
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;

psql -d a -u b_user -w

-- 只能看到授权的视图
\dt
-- 或
\dn+

-- 可以查询授权视图
select * from c_restricted;

-- 尝试访问其他表会失败
select * from public.c;  -- 权限被拒绝
select * from other_table;  -- 表不存在
\dt public.*  -- 看不到public模式下的表

-- 在表上启用行级安全
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');

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