postgres postgresql usuario user role group

Da mesma forma que um sistema operacional mantém um cadastro de usuários, grupos e respectivas permissões sobre seus objetos (arquivos, diretórios, processos…), o PostgreSQL também mantém um cadastro similar de usuários e grupos e permissões sobre seus objetos (tabelas, schemas, visões, bancos de dados…). Esses cadastros são independentes um do outro, mas em alguns pontos da autenticação o PostgreSQL pode usar informações do usuário do sistema operacional, por exemplo nas autenticações peer e ident.

O termo geral role (papel) engloba tanto user (usuário) quanto group (grupo). Mesmo esses dois são similares, com a única diferença sendo que um usuário é um grupo criado com permissão de LOGIN, enquanto que um grupo é um usuário criado sem a permissão de LOGIN.

Um papel pode ter permissões específicas associadas a ele, que controlam sua autorização em ações que são indepentendes de bancos de dados (como operações sobre objetos globais) e propriedades próprias do usuário (senha, login).

As permissões de papéis são:

• SUPERUSER: um superusuário contorna quase todas as verificações de autorização (exceto pelas cobertas por LOGIN e REPLICATION)
• LOGIN: pode estabelecer conexões novas com bancos de dados desta instância (contanto que exista ao menos um método de autenticação para ele); grupos são usuários comuns sem a permissão de login
• REPLICATION: pode estabelecer conexões que afetam toda a instância, como backups e replicações
• CREATEROLE: pode criar outros papéis
• CREATEDB: pode criar bancos de dados
• BYPASSRLS: pode contornar políticas de RLS (Row Level Security)
• INHERIT: pode usar os privilégios adquiridos por outros papéis/grupos sobre objetos locais sem executar SET ROLE

Papéis/usuários podem fazer parte de papéis/grupos. Dessa forma, as permissões atribuídas ao papel/grupo estão disponíveis para todos os membros dele.

As três formas a seguir são válidas e produzem o mesmo resultado:

[[local]:5432] postgres@postgres=# CREATE USER alice;

Com CREATE ROLE, adicionando a permissão de LOGIN:

[[local]:5432] postgres@postgres=# CREATE ROLE alice LOGIN;

Com o sinônimo CREATE GROUP:

[[local]:5432] postgres@postgres=# CREATE GROUP alice LOGIN;

As três formas a seguir são válidas e produzem o mesmo resultado:

[[local]:5432] postgres@postgres=# CREATE ROLE dba SUPERUSER;

Com o sinônimo CREATE GROUP:

[[local]:5432] postgres@postgres=# CREATE GROUP dba SUPERUSER;

Com CREATE USER, removendo a permissão de LOGIN:

[[local]:5432] postgres@postgres=# CREATE USER dba NOLOGIN SUPERUSER;

[[local]:5432] postgres@postgres=# GRANT dba TO alice;

[[local]:5432] postgres@postgres=# ALTER USER alice BYPASSRLS;

[[local]:5432] postgres@postgres=# \du
                                    List of roles
╔═══════════╤════════════════════════════════════════════════════════════╤═══════════╗
║ Role name │                         Attributes                         │ Member of ║
╠═══════════╪════════════════════════════════════════════════════════════╪═══════════╣
║ alice     │ Bypass RLS                                                 │ {dba}     ║
║ dba       │ Superuser, Cannot login                                    │ {}        ║
║ postgres  │ Superuser, Create role, Create DB, Replication, Bypass RLS │ {}        ║
╚═══════════╧════════════════════════════════════════════════════════════╧═══════════╝

[[local]:5432] postgres@postgres=#

[[local]:5432] postgres@postgres=# REVOKE dba FROM alice;

A forma recomendada de alterar senhas é através do metacomando \password, que não mostra a senha na tela e não guarda ela no histórico:

[[local]:5432] postgres@postgres=# \password alice
Enter new password:
Enter it again:
[[local]:5432] postgres@postgres=#

Também é possível alterar a senha com um ALTER USER ou CREATE USER, que não são recomendados pois a senha é apresentada na tela, fica registrada no histórico do psql e, possivelmente, também nos logs do servidor:

[[local]:5432] postgres@postgres=# ALTER USER bob PASSWORD 'segredo_indevidamente_visivel';

E:

[[local]:5432] postgres@postgres=# CREATE USER bob PASSWORD 'segredo_indevidamente_visivel';

Obs.: Existe uma cláusula ENCRYPTED PASSWORD, que é ignorada mas mantida por razões históricas. Toda senha é armazenada como um hash md5 (devidamente salgado) ou um hash scram-sha-256.

[[local]:5432] postgres@postgres=# DROP USER alice;