数据库、模式、表、字段权限

PostgreSQL 中的数据库、模式、表、字段权限是分开授权的。允许用户访问某个数据库不代表允许其访问数据库下的模式。

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema. By default, everyone has that privilege on the schema public. To allow users to make use of the objects in a schema, additional privileges might need to be granted, as appropriate for the object.

——PostgreSQL: Documentation: 15: 5.9. Schemas

授权用户使用模式：

1
GRANT USAGE ON SCHEMA test1 TO user1

查看用户对于模式的权限：

1
2
3
4
5
6
7
8
9
10
SELECT nspname                                           AS "SCHEMA_NAME",
       HAS_SCHEMA_PRIVILEGE(CURRENT_USER, oid, 'CREATE') AS "CREATE",
       HAS_SCHEMA_PRIVILEGE(CURRENT_USER, oid, 'USAGE')  AS "USAGE"
FROM pg_namespace
WHERE 1 = 1
  AND nspname NOT IN ('pg_toast', 'information_schema')
  AND nspname NOT LIKE 'pg_temp_%'
  AND nspname NOT LIKE 'pg_toast_temp_%'
  AND 1 = 1
ORDER BY nspname;

示例结果：

SCHEMA_NAME CREATE USAGE
pg_catalog false true
public true true
table1000 false false
test1 false true
test2 false false

GRANT

允许访问全部表：

1
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;

允许访问单张表：

1
GRANT SELECT ON schema_name.table_name TO role_name;

查看 GRANT

使用 SQL

1
2
3
4
5
6
7
8
9
WITH "names"("name") AS (
  SELECT n.nspname AS "name"
    FROM pg_catalog.pg_namespace n
      WHERE n.nspname !~ '^pg_'
        AND n.nspname <> 'information_schema'
) SELECT "name",
  pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS "create",
  pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS "usage"
    FROM "names";

参考：sql - postgresql - view schema privileges - Stack Overflow

使用 psql

Use psql‘s \dp command to obtain information about existing privileges for tables and columns. For example:

——PostgreSQL: Documentation: 9.4: GRANT

ALTER DEFAULT PRIVILEGES

ALTER DEFAULT PRIVILEGES 可以设置对于新创建的对象的默认权限，但不影响当前已经存在的对象的权限。

示例：

1
2
-- 允许 role2 读取 role1 以后在 schema_name 中新建的表
ALTER DEFAULT PRIVILEGES FOR USER role1 IN SCHEMA schema_name GRANT SELECT ON TABLES TO role2;

查看 DEFAULT PRIVILEGES

使用 SQL

1
2
3
4
5
SELECT 
  nspname,         -- schema name
  defaclobjtype,   -- object type
  defaclacl        -- default access privileges
FROM pg_default_acl a JOIN pg_namespace b ON a.defaclnamespace=b.oid;

sql - Display default access privileges for relations, sequences and functions in Postgres - Stack Overflow

使用 psql

Use psql‘s \ddp command to obtain information about existing assignments of default privileges. The meaning of the privilege values is the same as explained for \dp under GRANT.

——PostgreSQL: Documentation: 9.4: ALTER DEFAULT PRIVILEGES

其他

GRANT SELECT ON ALL TABLES 时报错

GRANT SELECT ON ALL TABLES ... 可能会报错 ERROR: tuple concurrently updated。

一种原因可能是对应 schema 中正在删除表，导致 GRANT 对应表的权限的时候，因为表被删除，导致失败。

登录权限

1
The specified database user/password combination is rejected: [28000] FATAL: no pg_hba.conf entry for host "x.x.x.x", user "xxx", database "xxx"

PostgreSQL: Documentation: 9.1: The pg_hba.conf File

程序链接PostgreSQL 时报错”no pg_hba.conf entry”_刘春明的博客-CSDN博客

PostgreSQL重新读取pg_hba.conf文件 - TIME_dy - 博客园

GreenPlum：Configuring Client Authentication | Tanzu Greenplum Docs

用户

创建用户

PostgreSQL: Documentation: 9.4: CREATE USER

角色

创建角色

PostgreSQL: Documentation: 14: CREATE ROLE

查看用户所有角色

1
2
3
SELECT oid, rolname
FROM pg_roles
WHERE PG_HAS_ROLE('peng', oid, 'member');

postgresql - How to get all roles that a user is a member of (including inherited roles)? - Database Administrators Stack Exchange

授予角色给用户

1
grant db_role1 to pguser_1;

PUBLIC 角色

PostgreSQL - Who or what is the “PUBLIC” role? - Database Administrators Stack Exchange

常见问题

任意用户都可以看到所有表问题

在 PostgreSQL 数据库中创建一个只有 LOGIN 权限的普通用户，这个普通用户就可以看到整个数据库服务中的所有数据库、模式、表、用户、角色等信息。

但是一般情况下我们可能期望一个只有 LOGIN 权限的普通用户不应该能够获取到这些信息。另外，这些元数据信息和实际的访问权限控制是分开的，用户可以看到这些元数据信息，但是如果用户没有对应表的访问权限，是无法正常读取的。比如尝试 SELECT 一张没有权限的表，会报错：[42501] 错误: 对模式 xxx 权限不够。

像 Oracle 数据库中，普通用户只能访问 ALL_TABLES 获取其能够访问到的表。

一个临时方案是，如果有多个数据库，可以限制用户只能连接到某个数据库，连接不上其他数据库。但是一个用户只要能够连接到一个数据库，就能读取到这个数据库中的所有模式名、表名等元数据。

You can try that, but things may break in unexpected ways. For example, psql’s utility commands will probably stop working. I would test any such change thoroughly.

Not all system catalogs are visible for everybody, tables and views containing passwords for example can only be read by superusers.

PostgreSQL has fewer restrictions on reading system catalogs than Oracle. I can see how a seasoned Oracle DBA might feel uneasy if everybody can find out all user names on the database cluster.

——PostgreSQL: Re: system catalog privilege and create privilege ??? how to control them?? thanks

资料：

PostgreSQL: system catalog privilege and create privilege ??? how to control them?? thanks

PostgreSQL: Re: system catalog privilege and create privilege ??? how to control them?? thanks

postgresql - How to prevent a user from being able to see other databases and the tables from other databases? - Stack Overflow

postgresql - Prevent users from seeing objects to which they have no access - Stack Overflow

permissions - How to prevent a login from ‘listing’ a table or view definition in PostgreSQL? - Database Administrators Stack Exchange

postgresql - Postgres: Restricting users ability to view schemas - Stack Overflow

参考资料

  1. PostgreSQL: Documentation: 15: 5.7. Privileges
  2. PostgreSQL: Documentation: 9.4: GRANT
  3. PostgreSQL: Documentation: 9.4: ALTER DEFAULT PRIVILEGES
  4. sql - Grant privileges on future tables in PostgreSQL? - Stack Overflow
  5. Postgresql 数据库用户权限授权（用户角色分配模式）-阿里云开发者社区
  6. database - What does GRANT USAGE ON SCHEMA do exactly? - Stack Overflow
  7. sql - postgresql - view schema privileges - Stack Overflow【查看用户对于模式的权限】
  8. PostgreSQL - Who or what is the “PUBLIC” role? - Database Administrators Stack Exchange【PUBLIC 角色】

快来参与讨论吧~