主页 / dba / 问题 / 195727
Accepted
as.beaulieu
Asked: 2018-01-19 13:23:28 +0800 CST

postgres 创建可以选择表和这些表的列的受限用户

  • 772

我有一个应用程序可以自动创建 PostgreSQL 数据库，还可以自动创建数据库用户。出于安全目的，此数据库用户被授予有限访问权限，因为它将被移交给客户端使用。 我使用的版本是 Postgres 9.6

CREATE USER %USERSETUP% WITH
    LOGIN PASSWORD '%USERSETUPPASS%'
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1;

ALTER DEFAULT PRIVILEGES IN SCHEMA %SCHEMA% GRANT INSERT, SELECT, UPDATE, DELETE, REFERENCES ON TABLES TO %USERSETUP%;

现在，我的测试也进入了创建的数据库，并验证它是否已正确创建，使用查询脚本获取表名，然后获取每个表中的列名（作为奖励，还获取列数据类型）

由于这些权限，用户不能使用 information_schema。 另外，我只请求带有 SYSTEM CATALOG 的查询脚本

我为表名编写的脚本有效：

SELECT tablename FROM pg_catalog.pg_tables where schemaname = '{schema}'

这会将表名作为列表返回：

业务、位置、人员、客户等...

但是，当我运行以下两个脚本来获取列名时，我得到的返回值为 0，就好像这个用户正在尝试查询 information_schema。我猜这是因为特权：

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ 'schemaaryzdhyqoi'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

还

SELECT c.oid,
  n.nspname,
  c.relname, t.*
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
  SELECT a.attname,
    pg_catalog.format_type(a.atttypid, a.atttypmod),
    (
      SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
      FROM pg_catalog.pg_attrdef d
      WHERE d.adrelid = a.attrelid
        AND d.adnum = a.attnum
        AND a.atthasdef
    ),
    a.attnotnull, a.attnum,
    (
      SELECT c.collname
      FROM
        pg_catalog.pg_collation c,
        pg_catalog.pg_type t
      WHERE c.oid = a.attcollation
        AND t.oid = a.atttypid
        AND a.attcollation <> t.typcollation
    ) AS attcollation
  FROM pg_catalog.pg_attribute a
  WHERE a.attrelid = c.oid
    AND a.attnum > 0
    AND NOT a.attisdropped
) AS t
WHERE n.nspname ~ '^(schemaaryzdhyqoi)$'  -- YOUR SCHEMA HERE
AND pg_catalog.pg_table_is_visible(c.oid);

问题是：如何为受限用户添加权限，使其也能够仅访问架构中每个表内的列的名称（可能还有数据类型）？

postgresql permissions

1 个回答

  1. Best Answer

    您可以将列级别权限设置为非常明确（并且严格）允许其他非特权用户访问数据。在您的情况下，它是两个目录：pg_attribute和pg_type.

    我有一个用户alice，他与您的用户同样受到限制：

    alice@test=> > SELECT * FROM pg_class LIMIT 1;
ERROR:  permission denied for relation pg_class

    发出以下GRANT语句：

    GRANT SELECT (oid, typname) ON pg_type TO alice;
GRANT SELECT (attrelid, attname, atttypid, attnum) ON TABLE pg_attribute TO alice;

    在这些之后，alice可以执行以下操作，例如：

    SELECT attname, typname 
  FROM pg_attribute JOIN pg_type t ON t.oid = atttypid
 WHERE attrelid = 't2'::regclass AND attnum > 0;

 attname │ typname 
─────────┼─────────
 key     │ jsonb
 value   │ jsonb

    您可以轻松地扩展权限以满足您的需求。

    • 7

相关问题