Gerenciando usuários, grupos, roles no PostgreSQL
O PostgreSQL tem um sistema super completo de gerenciamento de usuários e grupos (roles). Ele utiliza o conceito de users e roles.
A diferença entre user e role é que o user tem poder de fazer login no banco, a role não.
O PostgreSQL usa 3 tabelas para gerenciar users, roles e membros:
pg_catalog.pg_user
pg_catalog.pg_roles
pg_catalog.pg_auth_members
A tabela pg_user contém apenas as roles onde a coluna rolcanlogin = true
Existem algumas ambiguidades entre algumas colunas das tabelas pg_user e pg_roles, são elas:
pg_user.usename = pg_roles.rolname
pg_user.usesysid = pg_roles.oid
Usando o PGAdmin 4
No PG Admin 4, se você quiser adicionar um usuário a um grupo existente, clique com o botão direito no usuário, Properties... Membership e adicione o grupo desejado. A lógica dessa interface é meio estranha pois é o usuário quem define em que grupo ele está, mas é assim que o PG Admin 4 implementou essa funcionalidade.
Listando todos os Usuários
SELECT *FROM pg_catalog.pg_user;
Listando todas as Roles
SELECT *FROM pg_catalog.pg_roles;
Listando todos os membros de Roles
SELECT *FROM pg_catalog.pg_auth_members;
Listando todas as Roles que são membros de outras Roles
SELECT "role".rolname "role", "is_member_of".rolname "is_member_of", "grantor".rolname "grantor"FROM pg_catalog.pg_auth_members membership JOIN pg_catalog.pg_roles "is_member_of" ON "is_member_of".oid = membership.roleid JOIN pg_catalog.pg_roles "role" ON "role".oid = membership.member JOIN pg_catalog.pg_roles "grantor" ON "grantor".oid = membership.grantor ORDER BY "role";
E onde as senhas dos usuários são guardados?
As senhas são guardadas na tabela pg_catalog.pg_authid
As senhas são por padrão salvas com hash MD5, entretanto essa tabela só é acessível por usuários que são SUPER USER.
Criando um novo usuário role e adicionando-o a uma group role
CREATE ROLE writer INHERIT;GRANT USAGE ON SCHEMA batata TO writer;GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA batata TO writer;ALTER DEFAULT PRIVILEGES FOR writer IN SCHEMA batata GRANT ALL PRIVILEGES ON TABLES TO batata;GRANT USAGE ON SCHEMA cebola TO writer;GRANT SELECT ON TABLE cebola.lista TO writer; CREATE ROLE joaozinho INHERIT LOGIN PASSWORD 'senha-secreta-1234' VALID UNTIL '2022-05-01' CONNECTION LIMIT 100;GRANT writer TO joaozinho; Tipos de GRANTs
GRANT permissao_de_uso ON tipo_do_item(tabela, schema, etc) TO role;
Schema
CREATE | USAGE
... ON SCHEMA schema_name ...
Tabela (como um todo)
SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ALL
... ON TABLE table_name ...
Colunas em uma tabela
SELECT | INSERT | UPDATE | REFERENCES | ALL
... coluna_nome ON TABLE nome_tabela ...
Sequence (aqueles números auto incrementáveis dos ids)
USAGE | SELECT | UPDATE | ALL
... ON SEQUENCE sequence_name...
Database
CREATE | CONNECT | TEMPORARY | TEMP | ALL
... ON DATABASE database_name ...
Domain
USAGE
... ON DOMAIN domain_name ...
Foreign Data Wrapper
USAGE
... ON FOREIGN DATA WRAPPER fdw_name
Foreign Server
USAGE
... ON FOREIGN SERVER server_name ...
Function, Procedure, Routine
EXECUTE
... ON FUNCTION function_name
Usando Linguagem Procedural (PL)
USAGE
... ON LANGUAGE langage_name ...
Tablespace
CREATE
... ON TABLESPACE tbs_name ...
Exemplo:
GRANT SELECTON TABLE tabela_123TO role_desejada WITH GRANT OPTION; Default Privileges, Privilégios Padrão
DEFAULT PRIVILEGES são os privilégios padrão que uma role recebe quando um novo item (tabela, sequence, funcion) é adicionada em um schema.
Imagine que uma nova tabela é adicionada em um schema. Se não houver alguma política de default privilege, essa nova tabela não será manipulável por até mesmo roles que tenham permissões em todas as tabelas.
ALTER DEFAULT PRIVILEGES FOR role_desejadaIN SCHEMA meu_schemaGRANTALL ON TABLESTO role_desejada; Referências
Tutorial CREATE ROLE
https://www.postgresqltutorial.com/postgresql-roles/
Tutorial ALTER ROLE
https://www.postgresqltutorial.com/postgresql-administration/postgresql-alter-role/
Tutorial ROLE MEMBERSHIP (GRANT ROLE MEMBERSHIP)
https://www.postgresqltutorial.com/postgresql-administration/postgresql-role-membership/
Tutorial GRANT (PRIVILEGES)
https://www.postgresqltutorial.com/postgresql-administration/postgresql-grant/
Tutorial REVOKE
https://www.postgresqltutorial.com/postgresql-administration/postgresql-revoke/