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

A segurança do banco de dados depende de um controle de acesso robusto. No PostgreSQL, esse controle é gerenciado principalmente através de Funções (Roles) e Permissões (Permissions) (ou privilégios) associadas. Entender como criar, modificar e gerenciar essas entidades de segurança é fundamental para qualquer administrador de banco de dados ou desenvolvedor.

Este guia abrangente explora os comandos SQL essenciais necessários para gerenciar usuários com segurança, definir políticas de acesso e implementar o princípio do menor privilégio em seu ambiente PostgreSQL. Abordaremos a criação de funções, a configuração de atributos, a associação a grupos e o gerenciamento detalhado de permissões de objetos usando GRANT e REVOKE.

Entendendo as Funções do PostgreSQL

Ao contrário de alguns outros sistemas de banco de dados que diferenciam estritamente entre usuários e grupos, o PostgreSQL usa um conceito unificado: a Função (Role). Uma função pode representar:

1. Um usuário de banco de dados (uma entidade que pode fazer login, geralmente com o atributo LOGIN).
2. Um grupo de usuários (uma entidade usada exclusivamente para agrupar privilégios, geralmente sem o atributo LOGIN).

A segurança eficaz começa com a definição de funções que reflitam com precisão as responsabilidades dos indivíduos ou aplicativos que acessam o banco de dados.

1. Gerenciamento de Funções: Criação, Modificação e Exclusão

Os comandos fundamentais para gerenciar os principais do banco de dados giram em torno de CREATE ROLE, ALTER ROLE e DROP ROLE.

Criando Novas Funções

Ao criar uma função, você deve especificar seus atributos, particularmente se ela pode fazer login (LOGIN) e suas credenciais de autenticação (PASSWORD).

Criando um Usuário Básico de Login

Para criar uma função de usuário padrão que requer uma senha para conexão:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

Criando uma Função com Atributos Específicos

As funções podem ter vários atributos que definem suas capacidades e permissões de sistema:

Atributo	Descrição
LOGIN	Permite que a função se conecte ao banco de dados.
SUPERUSER	Concede todos os privilégios do banco de dados (use com moderação).
CREATEROLE	Permite que a função crie, altere e exclua outras funções.
CREATEDB	Permite que a função crie novos bancos de dados.
REPLICATION	Permite que a função inicie a replicação por streaming.
INHERIT / NOINHERIT	Controla se a função herda automaticamente os privilégios das funções das quais é membro (o padrão é INHERIT).

Exemplo: Criando uma Função de Administrador (Não Superusuário)

CREATE ROLE db_admin WITH
    LOGIN
    PASSWORD 'admin_secret'
    CREATEROLE
    CREATEDB
    VALID UNTIL '2025-01-01'; -- Data de expiração opcional

Modificando Funções Existentes

Use ALTER ROLE para alterar atributos ou atualizar a senha de uma função existente.

-- Altera a senha de um usuário existente
ALTER ROLE app_user WITH PASSWORD 'new_strong_password';

-- Revoga a capacidade de login (transformando um usuário em um grupo)
ALTER ROLE db_admin NOLOGIN;

-- Bloqueia um usuário
ALTER ROLE old_employee NOLOGIN;

Excluindo Funções

Antes de excluir uma função, certifique-se de que a função não possua nenhum objeto de banco de dados (tabelas, esquemas, etc.). Se possuir, você deve primeiro transferir a propriedade usando REASSIGN OWNED.

-- Exclui todos os objetos pertencentes à função e os reatribui a 'postgres'
REASSIGN OWNED BY old_employee TO postgres;

-- Em seguida, exclui a função
DROP ROLE old_employee;

Aviso: A exclusão de uma função é irreversível. Use cautela, especialmente com funções que possuem muitos objetos.

2. Gerenciando Associação a Grupos

As funções frequentemente funcionam como grupos para simplificar o gerenciamento de permissões. Em vez de conceder permissões a 50 usuários individuais, você as concede a uma função de grupo e, em seguida, torna os usuários membros desse grupo.

Criando uma Função de Grupo

Grupos são geralmente criados sem o atributo LOGIN.

CREATE ROLE data_analysts NOLOGIN;

Concedendo e Revogando Associação a Grupos

Use o comando GRANT para adicionar membros a uma função de grupo e REVOKE para removê-los.

-- Adiciona os usuários 'alice' e 'bob' ao grupo 'data_analysts'
GRANT data_analysts TO alice, bob;

-- Remove 'bob' do grupo 'data_analysts'
REVOKE data_analysts FROM bob;

A Opção WITH ADMIN OPTION

Se você incluir WITH ADMIN OPTION ao conceder a associação, a função receptora poderá conceder associação a esse grupo a outros e também poderá DROP (excluir) a função de grupo.

GRANT data_analysts TO supervisor WITH ADMIN OPTION;

3. Gerenciando Permissões de Objetos (Privilégios)

As permissões definem quais ações uma função pode realizar em quais objetos de banco de dados (tabelas, visualizações, funções, esquemas, etc.). Este é o cerne da segurança do banco de dados.

Sintaxe do Comando GRANT

GRANT privilege_list ON object_type object_name TO role_name [WITH GRANT OPTION];

Privilégios Comuns e Exemplos

Privilégios de Tabela

Privilégio	Ação Permitida
SELECT	Ler dados da tabela.
INSERT	Adicionar novas linhas.
UPDATE	Modificar linhas existentes.
DELETE	Remover linhas existentes.
TRUNCATE	Esvaziar a tabela completamente.
REFERENCES	Criar restrições de chave estrangeira.

Exemplo: Concedendo acesso de Leitura/Gravação a uma tabela específica.

GRANT SELECT ON TABLE production.orders TO data_analysts;

-- Concede todas as operações DML em uma tabela
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE staging.temp_data TO app_user;

Privilégios de Banco de Dados e Esquema

Privilégios de banco de dados e esquema são cruciais para controlar a estrutura do ambiente.

Objeto	Privilégio Chave	Propósito
DATABASE	CONNECT	Permite a conexão ao banco de dados.
DATABASE	CREATE	Permite a criação de novos esquemas, tablespaces, etc.
SCHEMA	USAGE	Permite o acesso a objetos dentro do esquema.
SCHEMA	CREATE	Permite a criação de novos objetos dentro do esquema.

Exemplo: Concedendo Acesso a Esquema

Se um usuário precisar acessar tabelas dentro do app_schema, ele deve ter USAGE nesse esquema.

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

Privilégios de Sequência e Função

O uso de sequências (para IDs auto-incrementais) e a execução de funções exigem privilégios específicos.

-- Permite que o usuário avance a sequência (necessário para INSERTs)
GRANT USAGE, SELECT ON SEQUENCE app_schema.user_id_seq TO app_user;

-- Permite a execução de um procedimento armazenado ou função específica
GRANT EXECUTE ON FUNCTION audit_log_insert(text) TO app_user;

Revogando Permissões

Use o comando REVOKE para remover permissões específicas concedidas anteriormente. A sintaxe espelha GRANT.

-- Revoga a capacidade de inserir novos registros na tabela orders
REVOKE INSERT ON TABLE production.orders FROM app_user;

-- Revoga todas as permissões concedidas anteriormente (nota: não revoga a propriedade)
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM data_analysts;

Nota sobre Revogação: Se um privilégio foi concedido a uma função, e essa função é membro de outro grupo, revogar o privilégio afeta apenas a permissão concedida diretamente. Se a função ainda herdar o privilégio por associação a um grupo, ela o mantém.

Aplicando Permissões a Objetos Futuros

Gerenciar permissões para objetos que ainda não existem requer o uso de ALTER DEFAULT PRIVILEGES. Isso é essencial para esquemas onde os aplicativos criam frequentemente novas tabelas.

Exemplo: Garantir que um usuário possa fazer SELECT em todas as tabelas futuras criadas pela função app_owner dentro do app_schema:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO app_user;

4. Visualizando Permissões Atuais

Para auditar as configurações de segurança, o PostgreSQL fornece várias ferramentas e visualizações de catálogo.

Comando/Visualização	Descrição
\du (no psql)	Lista todas as funções e seus atributos.
\du+ nome_da_funcao (no psql)	Mostra atributos detalhados da função e associação.
\dp nome_da_tabela (no psql)	Lista as permissões (privilégios) concedidas em uma tabela.
pg_roles	Visualização do catálogo do sistema contendo definições de funções.
information_schema.table_privileges	Visualização mostrando privilégios concedidos em tabelas.

Exemplo: Verificando privilégios em uma tabela via psql

=> \dp production.orders

Melhores Práticas de Segurança para Gerenciamento de Usuários

1. Princípio do Menor Privilégio (PoLP): Conceda apenas o conjunto mínimo de permissões necessárias para que uma função execute sua tarefa. Evite conceder ALL PRIVILEGES ou SUPERUSER, a menos que seja absolutamente necessário.
2. Funções de Aplicação Separadas: Use funções dedicadas para aplicativos (por exemplo, api_service_role) separadas das funções de DBA humanas. Os aplicativos geralmente devem ter apenas permissões DML (SELECT, INSERT, UPDATE, DELETE) e não permissões DDL (CREATE, DROP).
3. Use Grupos Extensivamente: Crie funções de não login (grupos) para gerenciar conjuntos de permissões (por exemplo, read_only_group, etl_writer_group). Atribua usuários individuais a esses grupos em vez de conceder permissões individualmente.
4. Evite Usar Funções Padrão: Nunca use a função superusuária postgres para tarefas gerais de aplicação ou desenvolvimento.
5. Autenticação Segura: Sempre use senhas fortes e, sempre que possível, utilize autenticação por certificado de cliente ou soluções de gerenciamento de identidade centralizadas em vez de autenticação baseada em senha.

Conclusão

O gerenciamento eficaz de funções e permissões é a base da segurança do PostgreSQL. Ao dominar CREATE ROLE, ALTER ROLE, GRANT e REVOKE, os administradores de banco de dados podem implementar controle granular, garantindo que cada usuário ou aplicativo que acessa o banco de dados tenha precisamente o acesso necessário e nada mais. A aplicação consistente do princípio do menor privilégio e o uso de funções de grupo simplificam a manutenção a longo prazo e fortalecem a postura de segurança do seu banco de dados contra acesso não autorizado.