PostgreSQL 15对用户权限这块进行了增强。默认情况下，不再设置public schema的CREATE权限。
2021年9月，PG15的版本提交了一个patch：默认情况下不再设置public schema的CREATE权限。该建议来自：https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2018-1058。Commit为：https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b073c3ccd06e4cb845e121387a43faa8c68a7b62
这对于普通用户来说（非超级用户）意味着什么呢？
先看下PG14的操作：
代码语言：javascript复制postgres=# SELECT version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)
postgres=# CREATE ROLE unprivileged WITH LOGIN;
CREATE ROLE
postgres=# CREATE DATABASE priv_test;
CREATE DATABASE
postgres=# c priv_test
You are now connected to database "priv_test" as user "ads".
priv_test=# dn public
List of schemas
Name | Owner | Access privileges | Description
-------- ------- ------------------- ------------------------
public | ads | ads=UC/ads | standard public schema
| | =UC/ads |
(1 row)可以看到，public(访问权限中的第二行)具有USAGE(U)和CREATE(C)的权限。普通用户可以在public schema模式下创建表：
代码语言：javascript复制priv_test=# SET SESSION ROLE unprivileged;
SET
priv_test=> SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
priv_test=> CREATE TABLE priv_test (id INT);
CREATE TABLE
priv_test=> dp priv_test
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-------- ----------- ------- ------------------- ------------------- ----------
public | priv_test | table | | |
(1 row)下面是PG15的操作及其影响：
代码语言：javascript复制postgres=# SELECT version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 15beta3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)
postgres=# CREATE ROLE unprivileged WITH LOGIN;
CREATE ROLE
postgres=# CREATE DATABASE priv_test;
CREATE DATABASE
postgres=# c priv_test
You are now connected to database "priv_test" as user "ads".
priv_test=# dn public
List of schemas
Name | Owner | Access privileges | Description
-------- ------------------- ---------------------------------------- ------------------------
public | pg_database_owner | pg_database_owner=UC/pg_database_owner | standard public schema
| | =U/pg_database_owner |
(1 row)
缺少C（CREATE）。默认情况下，普通用户的CREATE TABLE不再工作：
priv_test=# SET SESSION ROLE unprivileged;
SET
priv_test=> SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
priv_test=> CREATE TABLE priv_test (id INT);
ERROR: permission denied for schema public
LINE 1: CREATE TABLE priv_test (id INT);升级中如何应对这种情况呢？
这里有2种方法可以升级。当然还有其他更多方法，例如重新按照您的应用程序。但是处于本文目的，我们只关注数据从旧版本传输到新版本的方法。
1）从老版本（14或更老）中将数据dump出来，并恢复到新版本中（15及以上）
2）运行pg_upgrade.
两种方式的工作方式有点不同，我们看下细节：
Dump和Restore
使用自带的pg_dump工具来完成。最好使用自定义或者目录格式。并且需要使用较新版本（15）中的pg_dump来转储旧数据库：
代码语言：javascript复制/path/to/15/bin/pg_dump -F c -f /tmp/backup.dump priv_test通过创建一个空数据库（使用template0作为源/模板）来完成恢复，然后使用pg_restore将转储恢复到新数据库中。
代码语言：javascript复制/path/to/15/bin/dropdb --if-exists priv_test
/path/to/15/bin/createdb -T template0 priv_test
/path/to/15/bin/pg_restore -d priv_test -e /tmp/backup.dump然而：
代码语言：javascript复制priv_test=# dn public
List of schemas
Name | Owner | Access privileges | Description
-------- ------------------- ---------------------------------------- ------------------------
public | pg_database_owner | pg_database_owner=UC/pg_database_owner | standard public schema
| | =U/pg_database_owner |
(1 row)为什么public schema中具有“new”权限，而CREATE缺失？因为public模式包含在template0中，并被复制到新创建的数据库中，在PG15中，template0中的public模式具有“new”权限。我们看下：
代码语言：javascript复制priv_test=# SELECT datname, datallowconn FROM pg_database;
datname | datallowconn
----------- --------------p
postgres | t
template1 | t
template0 | f
priv_test | t
(4 rows)
priv_test=# UPDATE pg_database SET datallowconn = TRUE WHERE datname = 'template0';
UPDATE 1
priv_test=# c template0
You are now connected to database "template0" as user "ads".
template0=# dn
List of schemas
Name | Owner
-------- -------------------
public | pg_database_owner
(1 row)
template0=# dn public
List of schemas
Name | Owner | Access privileges | Description
-------- ------------------- ---------------------------------------- ------------------------
public | pg_database_owner | pg_database_owner=UC/pg_database_owner | standard public schema
| | =U/pg_database_owner |
(1 row)如我们所见，template0中的public少了CREATE权限，然后将其复制到新数据库中。任何期望其他情况并依赖于普通用户可写的public应用程序都会遇到问题。可以通过在新数据库中为public添加CREATE权限来解决此问题：
代码语言：javascript复制priv_test=# GRANT CREATE ON SCHEMA public TO PUBLIC;
GRANT
priv_test=# dn public
List of schemas
Name | Owner | Access privileges | Description
-------- ------------------- ---------------------------------------- ------------------------
public | pg_database_owner | pg_database_owner=UC/pg_database_owner | standard public schema
| | =UC/pg_database_owner |
(1 row)如果您希望这是新数据库的默认值，就将此更改应用于template1.
pg_upgrade
升级的第二种方式是pg_upgrade。这会将目录从就数据库复制到新数据库中。然后复制或链接数据文件。这是将服务器升级到新版本的顺畅方法。
代码语言：javascript复制/path/to/14/bin/pg_ctl -m fast -D /data/14/data stop
/path/to/15/bin/pg_ctl -m fast -D /data/15/data stop
rm -rf /data/15/data/*
/path/to/15/bin/initdb --pgdata=/data/15/data
/path/to/15/bin/pg_upgrade -b /path/to/14/bin -B /path/to/15/bin -d /data/14/data -D /data/15/data -p 5454 -P 5455 -v这将运行pg_upgrade，将14版本更新到15，之后public模式看起来与14相同：
代码语言：javascript复制priv_test=# dn public
List of schemas
Name | Owner | Access privileges | Description
-------- ------- ------------------- ------------------------
public | ads | ads=UC/ads | standard public schema
| | =UC/ads |
(1 row)PG不应用pg_database_owner也不用设置（撤销）“新”权限，所有内容都像以前一样被复制。如果想拥有15的新型为，需要从public撤销CREATE：
代码语言：javascript复制priv_test=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE还可以将所有者设置为新的pg_database_owner：
代码语言：javascript复制priv_test=# ALTER SCHEMA public OWNER TO pg_database_owner;
ALTER SCHEMA
priv_test=# dn public
List of schemas
Name | Owner | Access privileges | Description
-------- ------------------- ---------------------------------------- ------------------------
public | pg_database_owner | pg_database_owner=UC/pg_database_owner | standard public schema
| | =U/pg_database_owner |
(1 row)总结
将 PostgreSQL 数据库从版本 <= 14 升级到版本 15 或更高版本会给public模式带来一些挑战。两种最常见的升级方式在处理更改时表现不同。
最好不要依赖可写的public模式。
原文
https://andreas.scherbaum.la/blog/archives/1120-Changes-to-the-public-schema-in-PostgreSQL-15-and-how-to-handle-upgrades.html
