Oracle转Postgres新手在pgAdmin4中为my_db指定admin_schema和admin_owner建表

1. 创建admin_owner登录角色 #

使用postgres身份连接PostgreSQL服务器，打开查询工具，执行以下SQL创建带登录权限的角色（替换单引号内的密码为实际使用的密码）：

CREATE ROLE admin_owner WITH LOGIN PASSWORD 'your_strong_password';

之后切换到my_db数据库的查询工具（pgAdmin中展开my_db节点，右键选Query Tool），依次执行以下配置SQL：

1.1 转移admin_schema所有权给admin_owner #

ALTER SCHEMA admin_schema OWNER TO admin_owner;

等价于Oracle中将schema所有权从系统账号转移到业务用户的逻辑，只有所有者才能在schema内创建、修改对象

1.2 收回admin_owner在public schema的建表权限 #

REVOKE CREATE ON SCHEMA public FROM admin_owner;

双保险配置，防止误操作将表创建到默认public schema

1.3 设置admin_owner在my_db下的默认搜索路径 #

ALTER ROLE admin_owner IN DATABASE my_db SET search_path TO admin_schema;

等价于Oracle中用户登录后默认绑定当前schema的逻辑，后续建表无需手动指定schema名，自动归属admin_schema

2. 配置pgAdmin4以admin_owner连接my_db #

• 打开pgAdmin左侧服务器列表，右键已有的PostgreSQL服务器节点，依次选择「Create」→「Server」
• 配置窗口「General」标签页：
  • 名称：自定义标识，例如my_db-admin_owner
• 切换到「Connection」标签页：
  • 主机名/地址：填localhost（本机部署无需修改）
  • 端口：默认5432，若安装时修改过则填实际端口
  • 维护数据库：填my_db
  • 用户名：填admin_owner
  • 密码：填创建角色时设置的密码，可勾选「Save password」避免重复输入
• 点击「Save」保存，双击左侧刚创建的服务器节点，确认连接成功

3. 配置验证 #

使用admin_owner身份连接my_db后，打开查询工具执行以下测试语句：

-- 建测试表
CREATE TABLE test_demo (id int);

-- 查询表归属信息
SELECT 
  n.nspname AS schema_name,
  c.relname AS table_name,
  pg_get_userbyid(c.relowner) AS table_owner
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'test_demo'
  AND c.relkind = 'r';

预期返回结果：

• schema_name为admin_schema
• table_owner为admin_owner
  确认配置生效后，执行DROP TABLE test_demo;清理测试表即可。

注意事项 #

• 后续my_db内的所有业务建表操作，统一使用admin_owner的连接执行，禁止使用postgres身份建表，否则会默认以postgres为所有者、归属public schema
• 若后续需要给其他业务账号授予admin_schema下的表访问权限，单独为对应账号授予schema的USAGE权限及表的对应业务权限即可，无需修改当前的所有权配置

内容的提问来源于stack exchange，提问作者tonyf