as.beaulieu's questions

我有一个 postgres 视图“people_codes”，其中有一列是整数的 ARRAY_AGG。我正在尝试使用整数数组查询可能具有这些代码之一的所有行的视图

uuid    |  name     |     codes
(uuid)  | (varchar) |   (bigint[])
abc-def |  john     |  {5294, 5295}
bee-aaa |  jane     |  {1123, 2222}
111-a98 |  bob      |     {1933}

我可以用一个整数成功查询视图

SELECT * FROM people_codes WHERE 5294 = ANY (codes);

这将返回我需要的行：

abc-def   | john    |    {5294, 5295}    |

我的最终目标是能够使用数组查询我的视图，以便我可以找到具有一个或多个给定代码的所有行。

我尝试将我的值更改为 int，也将其转换为 ::bigint[]，但在括号中出现语法错误。无论括号 (、[ 或 {

SELECT * FROM people WHERE [5294,1933]::bigint[] = ANY (codes);

ERROR:  syntax error at or near "["
LINE 6: SELECT * FROM people WHERE [5294,1933]::bigint...

我也尝试过使用 @> 和 && 比较运算符，但又遇到了值数组的问题。它试图暗示添加一个显式类型转换，这就是我认为正在做的事情。尝试将搜索值放入 ARRAY_AGG 也是如此

SELECT * FROM people WHERE codes @> IN (5294,1933)::bigint[];

ERROR:  operator does not exist: bigint[] @>
LINE 8: ...ROM people WHERE codes @> IN (529...

获取包含查询数组中任何元素的行的正确查询是什么？通过搜索 [5294,1933] 的数组，我期望找回 john 和 bob 的行

我有一个应用程序可以自动创建 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);

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

我正在针对为用户配置数据库的应用程序构建集成测试。为其创建的用户不是超级用户，并且无权访问 schema_information.tables 因为当我尝试以下脚本时：

SELECT table_name
FROM information_schema.tables
WHERE table_schema='{schema}'

我得到了 0 的回报，因为这个用户没有权限。

我正在尝试查询数据库以验证创建的表和列。我可以使用以下脚本通过系统目录获取表名列表：

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

这会以我想要的方式输出表名：

业务，位置，人等...

我找不到带有系统目录的脚本，然后找到每个表的列名（作为奖励，数据类型）。到目前为止，我已经尝试了以下方法：

SELECT attname, format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE  attrelid = 'business'

这是错误：

ERROR:  invalid input syntax for type oid: "business"
LINE 1: ...od) AS type FROM   pg_attribute WHERE  attrelid = 'business'
                                                         ^```

也试过：

SELECT
    a.attname as "Column",
    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
FROM
    pg_catalog.pg_attribute a
WHERE
    a.attnum > 0
    AND NOT a.attisdropped
    AND a.attrelid = (
        SELECT c.oid
        FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ '**Dont know what to put here, Schema? Database?**'
            --AND pg_catalog.pg_table_is_visible(c.oid)
    );

这将返回 0 和架构或数据库。我不知道该为 c.relname 放什么。我是否也看到 0 是因为基本用户无法看到比模式中的表更深的内容？