一、先看 PostgreSQL 的层级

PostgreSQL 的对象层级可以理解为：

1
2
3
4
5
6
7
8

PostgreSQL 实例
└── database
    └── schema
        ├── table
        ├── view
        ├── sequence
        ├── function
        └── type

也就是说，PostgreSQL 的 database 下面还有一层 schema。表、视图、函数等对象并不是直接属于 database，而是属于 database 里的某个 schema。

例如：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

sales_db
├── public
│   ├── users
│   └── orders
├── sales
│   ├── customers
│   └── orders
├── finance
│   ├── invoices
│   └── payments
└── audit
    └── operation_logs

这里 public.orders 和 sales.orders 可以同时存在，因为它们位于不同 schema，完整名称不同。

二、schema 是什么

schema 可以理解为 database 内部的命名空间。

它主要解决四个问题：

1
2
3
4

1. 对对象进行分类管理
2. 避免表名、函数名冲突
3. 做权限隔离
4. 支持多个业务模块共用同一个 database

例如一个 ERP 系统可以这样设计：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

erp_db
├── hr
│   ├── employees
│   └── departments
├── finance
│   ├── invoices
│   └── payments
├── sales
│   ├── customers
│   └── orders
└── audit
    └── operation_logs

这样比所有表都放在 public 里更清晰，也更容易授权。

三、public schema 是什么

创建一个 PostgreSQL database 后，默认通常会有一个名为 public 的 schema。

例如：

1

CREATE DATABASE testdb;

连接到 testdb 后，如果直接建表：

1
2
3
4

CREATE TABLE users (
    id bigint PRIMARY KEY,
    username text NOT NULL
);

在默认 search_path 下，这张表通常会创建到：

1

public.users

也就是等价于：

1
2
3
4

CREATE TABLE public.users (
    id bigint PRIMARY KEY,
    username text NOT NULL
);

所以 public 不是特殊数据库，它只是 PostgreSQL 默认创建的一个 schema。

四、public schema 和 PUBLIC 角色不是一回事

这里非常容易混淆。

1
2

public schema：一个 schema，名字叫 public
PUBLIC 角色：所有角色的集合，表示所有用户

看这条 SQL：

1

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

第一个 public 是 schema 名：

1

ON SCHEMA public

第二个 PUBLIC 是所有用户：

1

FROM PUBLIC

完整意思是：

1

从所有用户那里，收回在 public schema 中创建对象的权限。

它不是删除 public schema，也不是禁止所有用户永远创建对象，而是取消默认的“人人都能在 public 里创建对象”的行为。

五、为什么要回收 public schema 的 CREATE 权限

在企业环境里，通常不希望所有用户都能在 public schema 里随便建表、建函数、建对象。

例如你创建了一个只读用户：

1
2

CREATE USER readonly WITH PASSWORD 'secret';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

如果这个用户仍然有：

1

CREATE ON SCHEMA public

那么它虽然不能改已有业务表，但仍可能自己创建对象。这与“只读用户”的设计目标不一致。

因此常见安全基线是：

1

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

然后谁确实需要创建对象，再单独授权：

1

GRANT USAGE, CREATE ON SCHEMA public TO app_user;

这体现的是最小权限原则：

1

默认不给所有人创建权限，需要的人再单独授权。

六、如果还需要用户建表，是否冲突

不冲突。

REVOKE CREATE ON SCHEMA public FROM PUBLIC 只是取消“所有用户默认拥有”的权限。

如果某个用户确实需要在 public schema 里建表，可以再单独执行：

1

GRANT USAGE, CREATE ON SCHEMA public TO app_user;

结果是：

1
2
3

普通用户：不能在 public 里建对象
readonly：不能在 public 里建对象
app_user：可以在 public 里建对象

更推荐的方式是不要让应用用户在 public schema 里建表，而是创建独立业务 schema：

1
2

CREATE SCHEMA app;
GRANT USAGE, CREATE ON SCHEMA app TO app_user;

然后业务对象使用：

1
2
3
4

CREATE TABLE app.orders (
    id bigint PRIMARY KEY,
    amount numeric(12,2) NOT NULL
);

七、USAGE 和 CREATE 的区别

schema 上最常见的两个权限是：

1
2

USAGE：允许使用这个 schema，也就是能解析 schema 里的对象名
CREATE：允许在这个 schema 中创建对象

例如只读用户通常需要：

1
2

GRANT USAGE ON SCHEMA sales TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO readonly;

如果没有 USAGE：

1

SELECT * FROM sales.orders;

即使有 orders 表的 SELECT 权限，也可能报 schema 权限错误。

如果要允许用户建表：

1

GRANT USAGE, CREATE ON SCHEMA sales TO app_user;

其中：

1
2

USAGE：让 app_user 能访问 sales 这个命名空间
CREATE：让 app_user 能在 sales 里创建表、视图、函数等对象

八、search_path 是什么

search_path 是 PostgreSQL 用来解析未加 schema 前缀对象名的搜索路径。

查看当前搜索路径：

1

SHOW search_path;

常见默认值类似：

1

"$user", public

如果执行：

1

SELECT * FROM orders;

PostgreSQL 会按 search_path 顺序查找 orders 表。

例如：

1

SET search_path TO sales, public;

此时：

1

SELECT * FROM orders;

PostgreSQL 会先找：

1

sales.orders

如果没有，再找：

1

public.orders

可以为角色设置默认搜索路径：

1

ALTER ROLE app_user SET search_path = app, public;

也可以为某个数据库中的某个角色设置：

1

ALTER ROLE app_user IN DATABASE sales_db SET search_path = app, public;

企业环境中建议业务 SQL 尽量明确 schema，或者统一设置 search_path，避免同名表导致访问错对象。

九、PostgreSQL 和 MySQL 的区别

MySQL 中：

1
2

CREATE DATABASE shop;
CREATE SCHEMA shop;

基本可以认为是同义语句。

MySQL 常见层级：

1
2
3

MySQL 实例
└── database / schema
    └── table

表完整名通常是：

1

shop.orders

PostgreSQL 中：

1
2
3
4

PostgreSQL 实例
└── database
    └── schema
        └── table

连接到 shop database 后，表完整名通常是：

1

public.orders

如果从整体层级看，可以说是：

1

shop database 里的 public.orders

但在 SQL 中通常不写 database 前缀，因为 PostgreSQL 普通 SQL 不能像 MySQL 那样直接用 db.table 跨库访问。

对比总结：

项目MySQLPostgreSQL
database 和 schema基本同义database 下还有 schema
表完整名database.tableschema.table
默认对象空间当前 database当前 database 的 public schema
跨库查询常见 db.table普通 SQL 不能直接跨 database
权限隔离常按 database 隔离可按 database + schema + object 隔离

十、企业中如何使用 schema

不建议所有业务表都堆在 public。

更推荐：

1
2
3
4
5
6
7

app
auth
sales
finance
audit
staging
archive

例如：

1
2
3
4
5
6
7
8

CREATE SCHEMA auth;
CREATE SCHEMA sales;
CREATE SCHEMA audit;

GRANT USAGE ON SCHEMA sales TO sales_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sales_readers;

GRANT USAGE, CREATE ON SCHEMA audit TO audit_writer;

这样权限边界更清楚：

1
2
3

sales_readers 只能读 sales
finance_user 只能访问 finance
audit_writer 只能写 audit

十一、总结

PostgreSQL 中：

1
2
3
4

database 是大隔离单位
schema 是 database 内部的命名空间
public 是默认 schema
PUBLIC 是所有用户

安全实践上建议：

1

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

然后为业务创建独立 schema：

1
2

CREATE SCHEMA app;
GRANT USAGE, CREATE ON SCHEMA app TO app_user;

理解 schema 和 public，是继续学习 PostgreSQL 授权、只读用户、默认权限和企业权限治理的基础。