Gestión de Roles y Permisos en PostgreSQL

Introducción

En este laboratorio, explorará la gestión de roles y permisos de PostgreSQL. El objetivo principal es aprender a crear roles con atributos específicos y gestionar los privilegios de la base de datos.

Comenzará creando un rol llamado reporting_role con el atributo NOLOGIN, lo que impedirá que se conecte directamente a la base de datos. Luego, verificará la creación del rol. Los pasos posteriores cubrirán la concesión y prueba de privilegios de base de datos, el cambio de usuarios para verificar el acceso y la revocación de privilegios a un rol.

Crear un Rol

En este paso, aprenderá cómo crear un rol en PostgreSQL. Los roles se utilizan para gestionar el acceso y los permisos de la base de datos.

Conéctese al servidor PostgreSQL:

Abra una terminal y conéctese al servidor PostgreSQL utilizando la herramienta de línea de comandos psql como usuario postgres.
```
sudo -u postgres psql
```
Debería ver un prompt similar a:
```
postgres=#
```
Cree el rol reporting_role:

Ahora, creemos el rol reporting_role utilizando el comando CREATE ROLE. Especificaremos el atributo NOLOGIN, lo que significa que este rol no se puede utilizar para conectarse directamente a la base de datos. Solo se utilizará para otorgar permisos a otros roles o usuarios.
```
CREATE ROLE reporting_role NOLOGIN;
```
Este comando crea el rol. Debería ver la siguiente salida:
```
CREATE ROLE
```

Verifique la creación del rol:

Para verificar que el rol ha sido creado, puede usar el comando \du (abreviatura de \list users). Este comando lista todos los roles en la base de datos.

\du

Debería ver reporting_role en la lista de roles. La salida se verá algo así:

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

Salga del shell de psql.

\q

Otorgar Privilegios de Base de Datos

En este paso, aprenderá cómo otorgar privilegios de base de datos a un rol. Nos basaremos en el rol reporting_role creado en el paso anterior.

Conéctese al servidor PostgreSQL:

Conéctese al servidor PostgreSQL utilizando la herramienta de línea de comandos psql como usuario postgres.
```
sudo -u postgres psql
```
Cree una base de datos:

Creemos una base de datos llamada reporting_db a la que nuestro rol reporting_role tendrá acceso.
```
CREATE DATABASE reporting_db;
```
Debería ver la siguiente salida:
```
CREATE DATABASE
```
Conéctese a la base de datos reporting_db:

Conéctese a la base de datos recién creada:
```
\c reporting_db
```
El prompt debería cambiar a reporting_db=#.
Cree una tabla:

Creemos una tabla simple llamada sales_data dentro de la base de datos reporting_db.
```
CREATE TABLE sales_data (
    id SERIAL PRIMARY KEY,
    product VARCHAR(255),
    amount DECIMAL
);
```
Debería ver la siguiente salida:
```
CREATE TABLE
```
Otorgue el privilegio SELECT en la tabla sales_data al rol reporting_role:

Ahora, otorgaremos el privilegio SELECT en la tabla sales_data al rol reporting_role. Esto permitirá que cualquier rol o usuario que sea miembro de reporting_role pueda leer datos de la tabla sales_data.
```
GRANT SELECT ON sales_data TO reporting_role;
```
Debería ver la siguiente salida:
```
GRANT
```
Salga del shell de psql.
```
\q
```

Crear Usuario y Probar Privilegios

En este paso, creará un usuario y le otorgará membresía al rol reporting_role para probar los privilegios.

Conéctese al servidor PostgreSQL:

Conéctese al servidor PostgreSQL utilizando la herramienta de línea de comandos psql como usuario postgres.
```
sudo -u postgres psql
```
Conéctese a la base de datos reporting_db:

Conéctese a la base de datos reporting_db:
```
\c reporting_db
```
Cree un usuario y otorgue membresía al reporting_role:

Creemos un nuevo usuario llamado report_user. Luego, otorgaremos a este usuario membresía al rol reporting_role. Esto significa que report_user heredará los privilegios otorgados a reporting_role.
```
CREATE USER report_user;
GRANT reporting_role TO report_user;
```
Debería ver la siguiente salida:
```
CREATE ROLE
GRANT ROLE
```
Configure PostgreSQL para Autenticación sin Contraseña:

Por defecto, PostgreSQL utiliza la autenticación peer, que puede causar problemas de conexión en este laboratorio. La cambiaremos a trust para simplificar el acceso para report_user.

Primero, salga del shell de psql:
```
\q
```
Ahora, modifique el archivo pg_hba.conf para cambiar el método de autenticación. El comando sed a continuación hará una copia de seguridad del archivo original como pg_hba.conf.bak y reemplazará peer con trust para las conexiones locales.
```
sudo sed -i.bak 's/^local\s\+all\s\+all\s\+peer/local all all trust/' /etc/postgresql/14/main/pg_hba.conf
```
Recargue PostgreSQL para aplicar los cambios:
```
sudo service postgresql reload
```
Inserte datos en la tabla sales_data:

Vuelva a conectarse a la base de datos como usuario postgres.
```
sudo -u postgres psql -d reporting_db
```
Inserte algunos datos de ejemplo en la tabla sales_data para fines de prueba.
```
INSERT INTO sales_data (product, amount) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);
```
Debería ver la siguiente salida:
```
INSERT 0 3
```
Salga del shell de psql.
```
\q
```
Conéctese a la base de datos reporting_db como report_user:

Abra una nueva ventana de terminal y use el siguiente comando:
```
psql -d reporting_db -U report_user
```
Se conectará a la base de datos como report_user sin que se le solicite una contraseña.
Pruebe los privilegios:

Ahora, intente seleccionar datos de la tabla sales_data:
```
SELECT * FROM sales_data;
```
Debería ver los datos que insertó:
```
 id | product  | amount
----+----------+--------
  1 | Laptop   | 1200.00
  2 | Mouse    |   25.00
  3 | Keyboard |   75.00
(3 rows)
```
Si ve esta salida, significa que report_user tiene el privilegio SELECT en la tabla sales_data, que se otorgó a través del rol reporting_role.

Salga del shell de psql.
```
\q
```

Revocar Privilegios

En este paso, revocará el privilegio SELECT del rol reporting_role y verificará que el usuario report_user ya no tiene acceso a la tabla sales_data.

Conéctese al servidor PostgreSQL como usuario postgres:

Vuelva a la terminal donde está conectado como usuario postgres. Conéctese al servidor PostgreSQL utilizando la herramienta de línea de comandos psql como usuario postgres.
```
sudo -u postgres psql
```
Conéctese a la base de datos reporting_db:

Conéctese a la base de datos reporting_db:
```
\c reporting_db
```
Revogue el privilegio SELECT del rol reporting_role:

Ahora, revocaremos el privilegio SELECT en la tabla sales_data del rol reporting_role.
```
REVOKE SELECT ON sales_data FROM reporting_role;
```
Debería ver la siguiente salida:
```
REVOKE
```
Salga del shell de psql.
```
\q
```
Conéctese a la base de datos reporting_db como report_user:

Vuelva a la terminal donde está conectado como report_user.
```
psql -d reporting_db -U report_user
```
Verifique la denegación de acceso como report_user:

Como report_user, ejecute la consulta SELECT nuevamente para verificar que ya no tiene acceso a los datos en la tabla sales_data:
```
SELECT * FROM sales_data;
```
Debería ver un mensaje de error similar a:
```
ERROR:  permission denied for table sales_data
```
Esto confirma que report_user ya no tiene privilegios SELECT en la tabla sales_data porque el privilegio fue revocado del rol reporting_role.

Salga del shell de psql.
```
\q
```

Resumen

En este laboratorio, ha aprendido a crear roles, otorgar privilegios, crear usuarios, asignar roles a usuarios y revocar privilegios en PostgreSQL. Estos son conceptos fundamentales para gestionar la seguridad de la base de datos y el control de acceso.