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.

Este es un Guided Lab, que proporciona instrucciones paso a paso para ayudarte a aprender y practicar. Sigue las instrucciones cuidadosamente para completar cada paso y obtener experiencia práctica. Los datos históricos muestran que este es un laboratorio de nivel principiante con una tasa de finalización del 96%. Ha recibido una tasa de reseñas positivas del 93% por parte de los estudiantes.

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.