Comandos Essenciais para Gerenciar Usuários, Funções e Permissões no PostgreSQL

Usuários, funções e permissões no PostgreSQL decidem quem pode conectar, o que podem ler e o que podem alterar. Se essas permissões crescerem por tentativa e erro, seu banco de dados se torna difícil de auditar e fácil de expor demais.

O PostgreSQL usa um único conceito, a função, tanto para usuários de login quanto para grupos. Uma função com LOGIN pode conectar. Uma função sem LOGIN é frequentemente usada como um grupo que carrega privilégios compartilhados.

Criar e Alterar Funções

Crie uma função de login de aplicação com uma senha:

CREATE ROLE app_user WITH LOGIN PASSWORD 'senha_forte_aqui';

Crie uma função de grupo para acesso de leitura compartilhado:

CREATE ROLE reporting_readonly NOLOGIN;

Atributos comuns de função incluem:

Atributo	O que permite
LOGIN	Conectar ao servidor de banco de dados.
CREATEDB	Criar bancos de dados.
CREATEROLE	Criar, alterar e excluir muitas funções, com limites importantes em torno de funções de superusuário.
REPLICATION	Usar conexões de replicação.
SUPERUSER	Ignorar verificações normais de permissão. Use apenas para funções de administrador rigorosamente controladas.
INHERIT	Usar privilégios de funções das quais é membro. Este é o padrão.

Altere uma senha ou desabilite o login com ALTER ROLE:

ALTER ROLE app_user WITH PASSWORD 'nova_senha_forte';
ALTER ROLE funcionario_antigo NOLOGIN;

Antes de excluir uma função, lide com tudo que ela possui e quaisquer privilégios que foram concedidos a ela:

REASSIGN OWNED BY funcionario_antigo TO postgres;
DROP OWNED BY funcionario_antigo;
DROP ROLE funcionario_antigo;

REASSIGN OWNED transfere objetos de propriedade. DROP OWNED remove privilégios concedidos à função e pode excluir objetos ainda de propriedade dela no banco de dados atual, então revise cuidadosamente antes de executá-lo.

Use Funções de Grupo para Acesso Mais Limpo

Conceda permissões a funções de grupo e depois adicione pessoas ou funções de aplicação a esses grupos.

GRANT reporting_readonly TO alice, bob;
REVOKE reporting_readonly FROM bob;

WITH ADMIN OPTION permite que o membro conceda e revogue essa associação de função para outros. Isso por si só não torna o membro um superusuário nem dá poder irrestrito sobre o banco de dados.

GRANT reporting_readonly TO lider_equipe WITH ADMIN OPTION;

Esse padrão mantém o acesso mais fácil de auditar. Em vez de verificar 50 usuários para concessões de tabela, você pode verificar a função de grupo.

Conceder Permissões de Objeto

Use GRANT para permitir ações específicas em objetos específicos.

GRANT SELECT ON TABLE production.orders TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.orders TO app_user;

Privilégios comuns de tabela são SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES e TRIGGER.

Permissões de esquema são separadas das permissões de tabela. Uma função geralmente precisa de USAGE no esquema e privilégios nas tabelas dentro dele:

GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_user;

Para inserções que usam sequências, conceda acesso à sequência também:

GRANT USAGE, SELECT ON SEQUENCE app.orders_id_seq TO app_user;

Para funções, conceda execução explicitamente:

GRANT EXECUTE ON FUNCTION app.audit_log_insert(text) TO app_user;

Revogar Permissões

REVOKE remove uma concessão direta.

REVOKE INSERT ON TABLE production.orders FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM reporting_readonly;

Se um usuário ainda tiver acesso após uma revogação, verifique a associação a grupos. O usuário pode herdar o mesmo privilégio através de outra função.

Definir Permissões para Tabelas Futuras

GRANT ON ALL TABLES afeta apenas tabelas existentes. Para tabelas criadas posteriormente, use ALTER DEFAULT PRIVILEGES a partir da função que criará essas tabelas.

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_readonly;

Esta é uma das etapas ausentes mais comuns em configurações de permissão do PostgreSQL. Um usuário pode ler as tabelas de hoje, mas é negado nas tabelas de amanhã porque os privilégios padrão nunca foram configurados.

Auditar Acesso Atual

No psql, estes comandos são verificações rápidas:

\du
\du+ app_user
\dp app.orders
\dn+

As visões de catálogo são melhores para relatórios repetíveis:

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

Para concessões de tabela:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

Hábitos de Segurança que Valem a Pena

Dê funções dedicadas às aplicações em vez de usar uma conta de DBA humana. Conceda o menor conjunto de privilégios que a aplicação precisa. Evite SUPERUSER e concessões amplas de ALL PRIVILEGES para o trabalho do dia a dia.

Use funções de grupo para acesso compartilhado e revise as associações regularmente. Quando alguém muda de equipe, remover uma associação de grupo é mais seguro do que caçar concessões em nível de tabela.

O próximo passo prático é escolher um esquema de produção e listar suas funções, associações, concessões de tabela e privilégios padrão. Corrija as lacunas lá antes de expandir o mesmo padrão para o resto dos seus bancos de dados.