Gerenciamento de Roles e Permissões no PostgreSQL

Introdução

Neste laboratório, você explorará o gerenciamento de roles e permissões no PostgreSQL. O objetivo principal é aprender como criar roles com atributos específicos e gerenciar privilégios de banco de dados.

Você começará criando um role chamado reporting_role com o atributo NOLOGIN, impedindo-o de se conectar diretamente ao banco de dados. Em seguida, você verificará a criação do role. Os passos subsequentes cobrirão a concessão e teste de privilégios de banco de dados, a troca de usuários para verificar o acesso e a revogação de privilégios de um role.

Criar um Role

Nesta etapa, você aprenderá como criar um role no PostgreSQL. Roles são usados para gerenciar o acesso e as permissões do banco de dados.

Conectar ao servidor PostgreSQL:

Abra um terminal e conecte-se ao servidor PostgreSQL usando a ferramenta de linha de comando psql como o usuário postgres.
```
sudo -u postgres psql
```
Você deverá ver um prompt semelhante a:
```
postgres=#
```
Criar o role reporting_role:

Agora, vamos criar o reporting_role usando o comando CREATE ROLE. Especificaremos o atributo NOLOGIN, o que significa que este role não poderá ser usado para conectar diretamente ao banco de dados. Ele será usado apenas para conceder permissões a outros roles ou usuários.
```
CREATE ROLE reporting_role NOLOGIN;
```
Este comando cria o role. Você deverá ver a seguinte saída:
```
CREATE ROLE
```

Verificar a criação do role:

Para verificar se o role foi criado, você pode usar o comando \du (abreviação de \list users). Este comando lista todos os roles no banco de dados.

\du

Você deverá ver reporting_role na lista de roles. A saída será algo como isto:

List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------------------+-----------
labex | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
reporting_role | Cannot login | {}

Saia do shell psql.

\q

Conceder Privilégios de Banco de Dados

Nesta etapa, você aprenderá como conceder privilégios de banco de dados a um role. Vamos usar o reporting_role criado na etapa anterior.

Conectar ao servidor PostgreSQL:

Conecte-se ao servidor PostgreSQL usando a ferramenta de linha de comando psql como o usuário postgres.
```
sudo -u postgres psql
```
Criar um banco de dados:

Vamos criar um banco de dados chamado reporting_db ao qual nosso reporting_role terá acesso.
```
CREATE DATABASE reporting_db;
```
Você deverá ver a seguinte saída:
```
CREATE DATABASE
```
Conectar ao banco de dados reporting_db:

Conecte-se ao banco de dados recém-criado:
```
\c reporting_db
```
O prompt deverá mudar para reporting_db=#.
Criar uma tabela:

Vamos criar uma tabela simples chamada sales_data dentro do banco de dados reporting_db.
```
CREATE TABLE sales_data (
    id SERIAL PRIMARY KEY,
    product VARCHAR(255),
    amount DECIMAL
);
```
Você deverá ver a seguinte saída:
```
CREATE TABLE
```
Conceder privilégio SELECT na tabela sales_data ao reporting_role:

Agora, concederemos o privilégio SELECT na tabela sales_data ao reporting_role. Isso permitirá que qualquer role ou usuário que seja membro do reporting_role leia dados da tabela sales_data.
```
GRANT SELECT ON sales_data TO reporting_role;
```
Você deverá ver a seguinte saída:
```
GRANT
```
Saia do shell psql.
```
\q
```

Criar Usuário e Testar Privilégios

Nesta etapa, você criará um usuário e concederá a ele a associação ao reporting_role para testar os privilégios.

Conectar ao servidor PostgreSQL:

Conecte-se ao servidor PostgreSQL usando a ferramenta de linha de comando psql como o usuário postgres.
```
sudo -u postgres psql
```
Conectar ao banco de dados reporting_db:

Conecte-se ao banco de dados reporting_db:
```
\c reporting_db
```
Criar um usuário e conceder associação ao reporting_role:

Vamos criar um novo usuário chamado report_user. Em seguida, concederemos a este usuário a associação ao reporting_role. Isso significa que report_user herdará os privilégios concedidos ao reporting_role.
```
CREATE USER report_user;
GRANT reporting_role TO report_user;
```
Você deverá ver a seguinte saída:
```
CREATE ROLE
GRANT ROLE
```
Configurar o PostgreSQL para Autenticação sem Senha:

Por padrão, o PostgreSQL usa a autenticação peer, que pode causar problemas de conexão neste laboratório. Alteraremos para trust para simplificar o acesso para o report_user.

Primeiro, saia do shell psql:
```
\q
```
Agora, modifique o arquivo pg_hba.conf para alterar o método de autenticação. O comando sed abaixo fará backup do arquivo original como pg_hba.conf.bak e substituirá peer por trust para conexões locais.
```
sudo sed -i.bak 's/^local\s\+all\s\+all\s\+peer/local all all trust/' /etc/postgresql/14/main/pg_hba.conf
```
Recarregue o PostgreSQL para aplicar as alterações:
```
sudo service postgresql reload
```
Inserir dados na tabela sales_data:

Conecte-se novamente ao banco de dados como usuário postgres.
```
sudo -u postgres psql -d reporting_db
```
Insira alguns dados de exemplo na tabela sales_data para fins de teste.
```
INSERT INTO sales_data (product, amount) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);
```
Você deverá ver a seguinte saída:
```
INSERT 0 3
```
Saia do shell psql.
```
\q
```
Conectar ao banco de dados reporting_db como report_user:

Abra uma nova janela de terminal e use o seguinte comando:
```
psql -d reporting_db -U report_user
```
Você será conectado ao banco de dados como report_user sem solicitação de senha.
Testar os privilégios:

Agora, tente selecionar dados da tabela sales_data:
```
SELECT * FROM sales_data;
```
Você deverá ver os dados que inseriu:
```
 id | product  | amount
----+----------+--------
  1 | Laptop   | 1200.00
  2 | Mouse    |   25.00
  3 | Keyboard |   75.00
(3 rows)
```
Se você vir esta saída, significa que o report_user tem o privilégio SELECT na tabela sales_data, que foi concedido através do reporting_role.

Saia do shell psql.
```
\q
```

Revogar Privilégios

Nesta etapa, você revogará o privilégio SELECT do reporting_role e verificará que o report_user não tem mais acesso à tabela sales_data.

Conectar ao servidor PostgreSQL como usuário postgres:

Volte para o terminal onde você está conectado como usuário postgres. Conecte-se ao servidor PostgreSQL usando a ferramenta de linha de comando psql como o usuário postgres.
```
sudo -u postgres psql
```
Conectar ao banco de dados reporting_db:

Conecte-se ao banco de dados reporting_db:
```
\c reporting_db
```
Revogar o privilégio SELECT do reporting_role:

Agora, revogaremos o privilégio SELECT na tabela sales_data do reporting_role.
```
REVOKE SELECT ON sales_data FROM reporting_role;
```
Você deverá ver a seguinte saída:
```
REVOKE
```
Saia do shell psql.
```
\q
```
Conectar ao banco de dados reporting_db como report_user:

Volte para o terminal onde você está conectado como report_user.
```
psql -d reporting_db -U report_user
```
Verificar a negação de acesso como report_user:

Como report_user, execute a consulta SELECT novamente para verificar se você não tem mais acesso aos dados na tabela sales_data:
```
SELECT * FROM sales_data;
```
Você deverá ver uma mensagem de erro semelhante a:
```
ERROR:  permission denied for table sales_data
```
Isso confirma que o report_user não tem mais privilégios SELECT na tabela sales_data porque o privilégio foi revogado do reporting_role.

Saia do shell psql.
```
\q
```

Resumo

Neste laboratório, você aprendeu a criar roles, conceder privilégios, criar usuários, atribuir roles a usuários e revogar privilégios no PostgreSQL. Estes são conceitos fundamentais para gerenciar a segurança do banco de dados e o controle de acesso.