
-- 若用户已存在, 忽略此句

-- ===========================================================================
-- 第 2 步  postgres_fdw —— 跨 Vastbase 实例查询
-- ===========================================================================
-- 2.1 建扩展 (vastbase 用户进入业务库)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- 2.2 授权 FDW 给链接用户
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO vastbaselink;

-- 2.3 切换为链接用户, 并开启 MSSQL 兼容格式(单机版也要设)
SET ROLE vastbaselink;            -- 或重新用 vastbaselink 登录后再执行
SET enable_set_variable_mssql_format = on;
SET mssql_format_behavior_compat_options = 'use_mssql_execute_gram';

-- 2.4 建链接服务器(指向另一台 Vastbase 的目标库)
--     把 link_目标简称 换成易识别名(如 link_kptframe); datasrc 为目标 IP:端口
EXEC sp_addlinkedserver
  @server     = 'link_目标简称',
  @srvproduct = 'vastbase',
  @datasrc    = '<目标库IP>,5432',
  @catalog     = '<目标库名>';



-- ===========================================================================
-- 第 3 步  tds_fdw —— 跨 SQL Server 查询(如 yserp 外部表)
-- ===========================================================================
-- 3.1 建扩展并授权
CREATE EXTENSION IF NOT EXISTS tds_fdw;
GRANT USAGE ON FOREIGN DATA WRAPPER tds_fdw TO vastbaselink;
SET ROLE vastbaselink;

-- 3.2 建链接服务器(SQL Server)
EXEC sp_addlinkedserver
  @server     = 'yserp',
  @srvproduct = 'sqlserver',
  @datasrc    = '<SQLServerIP>,1433',
  @catalog     = '<SQLServer库名>';



-- ===========================================================================
-- 第 4 步  跨库 schema 批量授权(关键! 否则报 permission denied for schema dbo)
--          用 vastbaselink 可查所有业务库的所有 schema
--          把下面脚本里的 vastbaselink 换成你的实际链接用户
-- ===========================================================================
DO $$
DECLARE
  db_name   text;
  system_dbs text[] := ARRAY['vastbase','postgres','template0','template1'];
  grant_sql  text;
BEGIN
  FOR db_name IN SELECT datname FROM pg_database
                 WHERE datname NOT IN (SELECT unnest(system_dbs)) LOOP

    EXECUTE format('GRANT CONNECT ON DATABASE %I TO vastbaselink;', db_name);

    grant_sql := format('
      DO $do$ DECLARE s text; BEGIN
        FOR s IN SELECT nspname FROM pg_namespace
                 WHERE nspname NOT IN (''pg_catalog'',''information_schema'',''pg_toast'') LOOP
          EXECUTE format(''GRANT USAGE ON SCHEMA %%I TO vastbaselink;'', s);
          EXECUTE format(''GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA %%I TO vastbaselink;'', s);
          EXECUTE format(''GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA %%I TO vastbaselink;'', s);
          EXECUTE format(''ALTER DEFAULT PRIVILEGES IN SCHEMA %%I GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO vastbaselink;'', s);
          EXECUTE format(''ALTER DEFAULT PRIVILEGES IN SCHEMA %%I GRANT USAGE,SELECT ON SEQUENCES TO vastbaselink;'', s);
        END LOOP;
      END $do$;', db_name);

    EXECUTE grant_sql;
    RAISE NOTICE '✅ 已完成数据库 % 的授权', db_name;
  END LOOP;
END $$;

-- ===========================================================================
-- 第 5 步  应急: 用户映射密码失效重置(最高频复发 E6 / E7)
-- ---------------------------------------------------------------------------
-- 现象 : 直连查询正常, 但跨库视图 / 跨库 SQL 突然报
--        42501 permission denied 或连 SQL Server 报错
-- 根因 : 重新生成过密钥文件(第0步) 或 改过密码 -> 旧映射失效
-- 解法 : 用映射所属用户执行(通常即 vastbaselink), 重置明文密码
-- ===========================================================================
-- 先切回链接用户
SET ROLE vastbaselink;





-- ===========================================================================
-- 第 6 步  默认 schema 与验证
-- ===========================================================================
-- 连接后默认搜 dbo 与 public(查询可省 schema 前缀; DDL 仍建议带)
ALTER DATABASE <当前库名> SET search_path TO dbo, public;

-- 查看已建链接服务器
SELECT s.srvname, s.srvowner, u.rolname AS owner_name
FROM pg_foreign_server s
LEFT JOIN pg_authid u ON s.srvowner = u.oid;

-- 跨库查询验证(把 server/表换成实际)
-- SELECT count(1) FROM dbo.某跨库视图;

-- ===========================================================================
-- ⚠ 重要注意: 跨库视图的"定义者权限"(对应 E5)
-- ---------------------------------------------------------------------------
-- 视图以"定义者(definer)"执行。若视图由 dbo 定义、内部跨库引用其他库,
-- 而 dbo 对目标库没有映射/授权 -> 报 42501 permission denied。
-- 处理二选一:
--   A. 给 dbo 用户也授予目标库 schema 权限(同第 4 步, 把 vastbaselink 换成 dbo);
--   B. 用链接用户(vastbaselink)重建该视图, 使其以映射用户身份执行。
-- 主备都要建相同的 dblink 用户映射, 否则主备切换后跨库查询失效(对应 E8)。
-- 客户库备份还原到新环境后, dblink 不继承, 需按本 SOP 重建(对应 E10)。
-- ============================================================================
