as.beaulieu's questions

Eu tenho uma visão postgres "people_codes" que tem uma coluna que é um ARRAY_AGG de intergers. Estou tentando consultar na exibição com uma matriz de inteiros para todas as linhas que podem ter um desses códigos

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

Posso consultar com êxito a exibição com apenas um inteiro para a consulta

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

E isso retorna a linha que eu preciso:

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

Meu objetivo final é poder consultar minha visão com um array, para que eu possa encontrar todas as linhas que tenham um ou mais dos códigos fornecidos.

Eu tentei alterar meu valor para um int, também lançando-o como :: bigint[], mas recebo um erro de sintaxe nos colchetes. Ocorre independentemente dos colchetes (, [, ou {

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

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

Eu também tentei usar os operadores de comparação @> e &&, mas tendo problemas com a matriz de valores novamente. Ele está tentando sugerir a adição de uma conversão de tipo explícito, que é o que eu pensei que estava fazendo. Mesma coisa com tentar colocar os valores de pesquisa em um 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...

O que é uma consulta correta para obter linhas que contenham elementos na matriz de consulta? Ao pesquisar no Array de [5294,1933], espero obter de volta as linhas de john e bob

Tenho um aplicativo que automatiza a criação de um banco de dados PostgreSQL, e também automatiza a criação de um usuário de banco de dados. Este usuário do banco de dados tem acesso limitado para fins de segurança, pois será entregue a um cliente para uso. A versão que estou usando é o 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%;

Agora, meu teste também entra no banco de dados criado e verifica se ele foi criado corretamente, usando Scripts de consulta para pegar os nomes das tabelas e, em seguida, pegar os nomes das colunas dentro de cada tabela ( como bônus, pegue também o tipo de dados da coluna )

Devido a essas permissões, o usuário não pode usar information_schema. Além disso, estou solicitando apenas scripts de consulta com SYSTEM CATALOG, por favor

O script que tenho para os nomes das tabelas funciona:

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

E isso retorna os nomes das tabelas como uma lista:

negócio, localização, pessoa, cliente, etc...

No entanto, quando executo os dois scripts a seguir para obter o nome da coluna, recebo um retorno de 0, como se esse usuário estivesse tentando consultar information_schema. Acho que é por causa dos privilégios:

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;

também

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);

A pergunta é: Como adiciono a permissão para que o usuário limitado também possa acessar apenas o nome (e talvez o tipo de dados) das colunas dentro de cada tabela no esquema?

Estou construindo um teste de integração em um aplicativo que provisiona um banco de dados para um usuário. O usuário criado para ele não é um superusuário, e não tem acesso ao schema_information.tables pois quando tento o seguinte script:

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

Recebo um retorno de 0, como deveria, pois esse usuário não tem permissão.

Estou tentando consultar o banco de dados para verificar as tabelas e as colunas criadas. Posso obter a lista de nomes de tabelas através do catálogo do sistema com o seguinte script:

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

E isso gera os nomes das tabelas do jeito que eu quero:

empresa, localização, pessoa, etc...

Não consigo encontrar o script com o catálogo do sistema para encontrar os nomes das colunas ( E como bônus, o tipo de dados ) de cada tabela. Até agora tentei o seguinte:

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

e aqui está o erro:

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

Também tentei:

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)
    );

e isso retorna 0 com um esquema ou banco de dados. Não tenho certeza do que colocar lá para c.relname. Também estou vendo 0 com isso porque um usuário básico simplesmente não pode ver mais profundamente do que tabelas em um esquema, ponto final?