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?
Você pode definir privilégios de nível de coluna para permitir de forma muito específica (e restrita) que usuários sem privilégios acessem dados. No seu caso, são dois catálogos:
pg_attribute
epg_type
.Eu tenho um usuário chamado
alice
que é restrito da mesma forma que seu usuário:Emita as seguintes
GRANT
declarações:Após estes,
alice
pode fazer o seguinte, por exemplo:Você pode estender trivialmente os privilégios para atender às suas necessidades.