当心PostgreSQL中的'public'

默认情况下，PostgreSQL包含"public"模式，我们始终建议在PostgreSQL的14版本中drop掉。原因是，默认情况下，连接到数据库的用户都可以在public模式中创建对象。PostgreSQL 15从public模式中删除全局写权限。您可以查看该更改的发行说明：“移除public模式上的creation权限（Noah Misch）”。

除了“public”模式之外，还有特殊“public”角色，可以用来向系统中的每一个角色授予一个权限，但这也带来了对默认情况下可能出现的一些情况的疑惑。默认情况下，public角色具有以下权限：

 username | object_type |    object_name     |            array            
----------+-------------+--------------------+-----------------------------
 public   | DATABASE    | postgres           | {CONNECT,TEMPORARY}
 public   | DATABASE    | template1          | {CONNECT}
 public   | LANGUAGE    | internal           | {USAGE}
 public   | LANGUAGE    | c                  | {USAGE}
 public   | LANGUAGE    | sql                | {USAGE}
 public   | LANGUAGE    | plpgsql            | {USAGE}
 public   | SCHEMA      | pg_catalog         | {USAGE}
 public   | SCHEMA      | public             | {USAGE}
 public   | SCHEMA      | information_schema | {USAGE}
(9 rows)
复制

有一些“usuage”权限，看起来不错，但也有针对“postgres”和“template1”数据库的“connect”权限（如果您想知道如何从系统中获取这些权限，那么您应该查看文档中的“has_***”函数）。

上周，一位客户与我们联系，他遇到了这种情况：

postgres=# create database d;
CREATE DATABASE
postgres=# create user u with login password 'u';
CREATE ROLE
postgres=# revoke connect on database d from u;
REVOKE
复制

简单设置：一个数据库，一个用户，并从该用户撤销该数据库的连接权限。该用户是否能够连接到数据库？从数据库主机外部进行简单测试：

[email protected]:~$ psql -h 192.168.100.241 -p 5432 -U u d
psql (14.5, server 16devel)
WARNING: psql major version 14, server major version 16.
         Some psql features might not work.
Type "help" for help.

d=> 
复制

即使connect权限被撤销，仍然可以连接（当然我之前修改过pg_hba.conf）。为什么？原因是“public”角色。如果我们再次检查该角色的权限，我们可以看到以下内容：

 username | object_type |    object_name     |            array
----------+-------------+--------------------+-----------------------------
 public   | DATABASE    | postgres           | {CONNECT,TEMPORARY}
 public   | DATABASE    | template1          | {CONNECT}
 public   | DATABASE    | d                  | {CONNECT,TEMPORARY}
 public   | LANGUAGE    | internal           | {USAGE}
 public   | LANGUAGE    | c                  | {USAGE}
 public   | LANGUAGE    | sql                | {USAGE}
 public   | LANGUAGE    | plpgsql            | {USAGE}
 public   | SCHEMA      | pg_catalog         | {USAGE}
 public   | SCHEMA      | public             | {USAGE}
 public   | SCHEMA      | information_schema | {USAGE}
(10 rows)
复制

译者注：默认情况下，在创建数据库之后，允许public角色连接，即允许任何人连接。

由于“public”角色默认具有连接权限，因此所有其他用户都可以自动使用该权限，这就是它仍然有效的原因。你应该做的是这样的：

postgres=# revoke connect on database d from public;
REVOKE
复制

从public角色撤销connect后，用户将无法再连接：

[email protected]:~$ psql -h 192.168.100.241 -p 5432 -U u d
psql: error: connection to server at "192.168.100.241", port 5432 failed: FATAL:  permission denied for database "d"
DETAIL:  User does not have CONNECT privilege.
复制

请记住，授予“public”的权限对所有用户都有效。

原文地址：https://www.dbi-services.com/blog/be-careful-with-public-in-postgresql/

原文作者：Daniel Westermann