云数据库 PostgreSQL PUBLIC 权限操作

向 PUBLIC 授予权限等同于向所有用户（包括未显式创建的用户）授予该权限。你可以按需求撤销这些默认 PUBLIC 权限，以便更精确地为数据库用户授权，下文将详细为你举例。

注意：说明：

假设你现在有两个数据库账号 am_a 和 dbadmin，详情如下所示，用户创建请参考 控制台操作指引：

其中数据库账号 dbadmin 是 database test_db 的 OWNER：

首先，我们使用账号 dbadmin 登录 database test_db，如下所示：

[am@VM-91-60-centos ~]$psql -h10.*.*.* -p5432 -Udbadmin -dtest_dbPassword for user dbadmin: psql (16.0, server 16.10)Type "help" for help.

切换到 am_a 账号，我们可以验证 am_a 通过 PUBLIC 拥有 database 的登录权限和系统表的查询权限，具体如下：

test_db=> \\c – am_apsql (16.0, server 16.10)You are now connected to database "test_db" as user "am_a".test_db=> select oid FROM pg_class LIMIT 1; oid —— 2619(1 row)

使用 dbadmin 账号回收 PUBLIC 权限后，am_a 账号无法访问系统表：

test_db=> \\c – dbadminPassword for user dbadmin: psql (16.0, server 16.10)You are now connected to database "test_db" as user "dbadmin".test_db=> REVOKE SELECT ON pg_class FROM PUBLIC;REVOKEtest_db=> \\c – am_aPassword for user am_a: psql (16.0, server 16.10)You are now connected to database "test_db" as user "am_a".test_db=> select oid FROM pg_class LIMIT 1;ERROR: permission denied for table pg_class

使用 dbadmin 账号回收 PUBLIC 权限后，am_a 账号无法连接数据库 test_db：

test_db=> \\c – dbadminPassword for user dbadmin: psql (16.0, server 16.10)You are now connected to database "test_db" as user "dbadmin".test_db=> REVOKE CONNECT ON DATABASE test_db FROM PUBLIC;REVOKEtest_db=> \\c – am_a Password for user am_a: connection to server at "10.*.*.*", port 5432 failed: FATAL: permission denied for database "test_db"DETAIL: User does not have CONNECT privilege.Previous connection kept

使用 dbadmin 账号重新授予 am_a 权限后，am_a 可以连接数据库 test_db，也可以访问系统表：

test_db=> \\c – dbadminpsql (16.0, server 16.10)You are now connected to database "test_db" as user "dbadmin".test_db=> GRANT CONNECT ON DATABASE test_db TO am_a;GRANTtest_db=> GRANT SELECT ON pg_class TO am_a;GRANTtest_db=> \\c – am_aPassword for user am_a: psql (16.0, server 16.10)You are now connected to database "test_db" as user "am_a".test_db=> select oid FROM pg_class LIMIT 1; oid —— 2619(1 row)