Visualização de Usuários/Papéis

psql# \du
SELECT * FROM pg_user;
SELECT * FROM pg_shadow;

SELECT * FROM pg_roles;

Criação de Papéis

CREATE ROLE [nome];
-- Criar usuário com senha (com o atributo LOGIN por padrão)
CREATE USER "USUARIO$abc" WITH PASSWORD 'abc';

Atributos de Papéis

Um papel com o atributo LOGIN pode ser consideardo equivalente a um "usuário de banco de dados". Para criar um papel com permissão de login, use qualquer uma das opções abaixo:

CREATE ROLE [nome] LOGIN;
CREATE USER [nome];

Criação de Usuários

-- Criação de usuário
CREATE USER "USUARIO$abc" WITH PASSWORD 'abc';

-- Adiciona ao grupo de usuários do banco de dados
CALL spm_grant('spm_1_developer', 'USUARIO$abc');

-- Alteração de senha
ALTER USER "USUARIO$def" WITH PASSWORD 'def';

-- No mesmo banco de dados, o usuário só pode ser adicionado a um único grupo
CALL spm_grant('spm_1_admin', 'USUARIO$abc');

Obs.: Não é possível adicionar o mesmo usuário em múltiplos grupos dentro do mesmo banco de dados.

Gerenciamento Avançado

CALL spm_revoke('spm1_admin', 'USUARIO$tset');
CALL spm_grant('spm1_developer', 'USUARIO$tset');
CALL spm_revoke('spm1_developer', 'USUARIO$tset');
CALL spm_grant('spm1_writer', 'USUARIO$tset');
CALL spm_revoke('spm1_writer', 'USUARIO$tset');
CALL spm_grant('spm1_viewer', 'USUARIO$tset');
CALL spm_revoke('spm1_viewer', 'USUARIO$tset');

-- Para superusuários: Troque para o banco de dados spm_2 e execute:
CALL spm_revoke('spm_2_developer', 'USUARIO$abc');

Modelo Especializado

Contas locais com caracteres especiais precisam incluir "USUARIO$test".

Restrições de Banco de Dados

-- Restringir conexão de todos ao banco de dados [db_name]
REVOKE CONNECT ON DATABASE [db_name] FROM PUBLIC;

-- Permitir que um usuário específico se conecte a um banco de dados
GRANT CONNECT ON DATABASE [db_name] TO USUARIO$especifico;

-- Proibir acesso público ao banco de dados public
REVOKE CONNECT ON DATABASE public FROM PUBLIC;

-- Proibir acesso específico a um banco de dados
REVOKE CONNECT ON DATABASE t2 FROM USUARIO$restrito;

-- Permitir acesso específico a um banco de dados
GRANT CONNECT ON DATABASE [db_name] TO USUARIO$permitido;

Gerenciamento de Schema Básico

No PostgreSQL, as permissões são concedidas em camadas:

1. Conceder permisões de schema ao usuário.
2. Conceder permissões específicas às tabelas dentro do schema. Nota: Ignorar a primeira etapa pode resultar na falha de consultas, mesmo após a concessão direta de permissões.

-- Permitir acesso ao schema ae
GRANT USAGE ON SCHEMA ae TO USUARIO$permitido;

-- Remover acesso ao schema ae
REVOKE USAGE ON SCHEMA ae FROM USUARIO$proibido;

-- Proibir acesso ao schema public
REVOKE USAGE ON SCHEMA public FROM PUBLIC;

-- Permitir acesso ao schema public para um usuário específico
GRANT USAGE ON SCHEMA public TO USUARIO$permitido;

-- Permitir acesso ao schema public para todos
GRANT USAGE ON SCHEMA "public" TO PUBLIC;

-- Proibir acesso ao schema public para todos
REVOKE USAGE ON SCHEMA "public" FROM PUBLIC;

Restrições de Tabela/Visão

-- Revogar todas as permissões em todas as tabelas do schema public para um usuário específico
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM USUARIO$proibido;

-- Revogar todas as permissões em todas as tabelas do schema public para todos
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

-- Permitir acesso SELECT a uma tabela específica
GRANT SELECT ON public.tabela_exemplo TO USUARIO$permitido;

-- Permitir acesso SELECT a uma visão específica
GRANT SELECT ON public.visao_exemplo TO USUARIO$permitido;