postgres postgresql authorization

Todo objeto criado tem um dono, que é o papel que (inicialmente) pode fazer todas as ações sobre aquele objeto. Outros papéis que quiserem usar o objeto devem receber os privilégios corretos.

Privilégios são as características que permitem que uma ação seja executada sobre um objeto por um papel. Se o privilégio não existir, a ação vai ser impedida. A única exceção é que superusuários ignoram essa checagem.

O dono do objeto pode atribuir (GRANT) ou revogar (REVOKE) privilégios sobre o objeto, assim como pode tornar outro papel o novo dono do objeto. Ele também pode revogar alguns dos próprios privilégios.

Com os comandos GRANT e REVOKE é possível atribuir todos os privilégios (ALL) ou apenas privilégios específicos. Também é possível atribuí-los a todos os papéis (PUBLIC) ou apenas a papéis específicos.

Com o comando ALTER DEFAULT PRIVILEGES é possível alterar os privilégios padrão dos objetos, ou seja, aqueles que serão usados para objetos que serão criados no futuro.

Cada privilégio é abreviado por uma letra e é usado por um conjunto de tipos de objetos, como na tabela:

Privilégio	Abreviação	Tipos de objeto	O que permite
SELECT	r ("read")	LARGE OBJECT, SEQUENCE, TABLE (e objetos similares), coluna de tabela	leitura de dados
INSERT	a ("append")	TABLE, coluna de tabela	escrita de dados
UPDATE	w ("write")	LARGE OBJECT, SEQUENCE, TABLE, coluna de tabela	alteração de dados
DELETE	d	TABLE	deleção de dados
TRUNCATE	D	TABLE	truncar tabelas
REFERENCES	x	TABLE, coluna de tabela	referências entre tabelas
TRIGGER	t	TABLE	gatilhos em tabelas
CREATE	C	DATABASE, SCHEMA, TABLESPACE	criação de outros objetos dentro deste
CONNECT	c	DATABASE	conectar ao banco de dados
TEMPORARY	T	DATABASE	criação de tabelas temporárias
EXECUTE	X	FUNCTION, PROCEDURE	execução
USAGE	U	DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE	uso do objeto

Cada tipo de objeto usa um conjunto de todos os privilégios que pode usar, um conjunto de privilégios permitidos a todos (PUBLIC) por padrão e um comando psql que permite listá-los, como na tabela:

Tipo de objeto	Todos os privilégios	Privilégios PUBLIC padrão	Comando psql
DATABASE	CTc	Tc	\l
DOMAIN	U	U	\dD+
FUNCTION or PROCEDURE	X	X	\df+
FOREIGN DATA WRAPPER	U	nenhum	\dew+
FOREIGN SERVER	U	nenhum	\des+
LANGUAGE	U	U	\dL+
LARGE OBJECT	rw	nenhum
SCHEMA	UC	nenhum	\dn+
SEQUENCE	rwU	nenhum	\dp
TABLE (e objetos similares)	arwdDxt	nenhum	\dp
Coluna de tabela	arwx	nenhum	\dp
TABLESPACE	C	nenhum	\db+
TYPE	U	U	\dT+

[[local]:5432] app@app=# GRANT SELECT, INSERT ON logs TO PUBLIC;

Criamos um usuário da aplicação, um grupo para conter a permissões de leitura e um usuário comum que fará a leitura:

[[local]:5432] postgres@postgres=# CREATE USER app;
[[local]:5432] postgres@postgres=# CREATE GROUP leitor;
[[local]:5432] postgres@postgres=# CREATE USER auditor IN ROLE leitor;

Atribuímos as permissões sobre os objetos atuais:

[[local]:5432] postgres@template1=# GRANT USAGE ON SCHEMA public TO leitor;

Atribuímos as permissões sobre os objetos futuros criados pelo usuário app:

[[local]:5432] postgres@template1=# ALTER DEFAULT PRIVILEGES FOR ROLE app GRANT USAGE ON SCHEMAS to leitor;
[[local]:5432] postgres@template1=# ALTER DEFAULT PRIVILEGES FOR ROLE app GRANT SELECT ON TABLES to leitor;
[[local]:5432] postgres@template1=# ALTER DEFAULT PRIVILEGES FOR ROLE app GRANT SELECT ON SEQUENCES to leitor;

Após esse ponto, se um banco novo for criado e populado com objetos pelo usuário app, eles serão acessíveis pelo usuário auditor, com os privilégios de leitor:

[[local]:5432] postgres@postgres=# CREATE DATABASE app OWNER app TEMPLATE template1;
[[local]:5432] postgres@postgres=# \c app app
[[local]:5432] app@app=> CREATE TABLE dados ();
[[local]:5432] app@app=> INSERT INTO dados DEFAULT VALUES;
[[local]:5432] app@app=> \c app auditor
[[local]:5432] auditor@app=> SELECT COUNT(*) FROM dados;
╔═══════╗
║ count ║
╠═══════╣
║     1 ║
╚═══════╝
[[local]:5432] auditor@app=>

Vamos criar um grupo de permissões elevadas (superusuários) que precisam ser invocadas explicitamente para serem usadas, similar a como um usuário comum do sistema operacional pode executar um comando setuid (sudo ou su) para se tornar um outro usuário e posteriormente retornar ao usuário inicial. Esse comportamento é definido no padrão SQL.

Grupo de DBAs, em que os membros são superusuários:

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

Os usuários serão criados sem herdar (NOINHERIT) os privilégios de superusuário automaticamente, ou seja, devem executar SET ROLE dba para usá-los:

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

Alice é adicionada ao grupo, com a permissão de adicionar outros membros ao grupo:

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

Bob é adicionado ao grupo, sem a permissão de adicionar outros membros ao grupo:

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

A partir deste momento, tanto alice quanto bob podem usar os privilégios de superusuário explicitamente como:

[[local]:5432] bob@postgres=> SET ROLE dba;
[[local]:5432] dba@postgres=# -- faz coisas como superusuário
[[local]:5432] dba@postgres=# RESET ROLE;
[[local]:5432] bob@postgres=> -- retorna ao usuário comum

E alice pode adicionar outros ao grupo, mas bob não pode:

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

É importante não usar superusuários para atividades que não tenham necessidade. Uma delas é monitoramento, para a qual existe um papel pg_monitor que permite a leitura de estatísticas e configurações.

[[local]:5432] postgres@postgres=# CREATE USER monitoramento IN GROUP pg_monitor;

Ou:

[[local]:5432] postgres@postgres=# CREATE USER monitoramento;
[[local]:5432] postgres@postgres=# GRANT pg_monitor TO monitoramento;

Crie ao menos um grupo para dbas (superusuários), um para leitores de todos os dados para a equipe de BI, um para a equipe de monitoramento e um para a equipe da aplicação (com permissões de edição).

Crie um banco de dados para a aplicação (o dono será o grupo da equipe da aplicação), um usuário alice para a equipe de BI, um usuário bob para a equipe de monitoramento e um charlie para a equipe da aplicação.

Teste os seguintes pontos:

1. Crie uma tabela usando charlie - deve funcionar
2. Escreva na tabela com o usuário charlie - deve funcionar
3. Escreva na tabela com o usuário bob - não deve funcionar
4. Escreva na tabela com o usuário alice - não deve funcionar
5. Leia a tabela com o usuário charlie - deve funcionar
6. Leia a tabela com o usuário bob - não deve funcionar
7. Leia a tabela com o usuário alice - deve funcionar
8. Execute a função pg_ls_waldir() (restrita a monitoramento) com o usuário charlie - não deve funcionar
9. Execute a função pg_ls_waldir() com o usuário bob - deve funcionar
10. Execute a função pg_ls_waldir() com o usuário alice - não deve funcionar