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 impide que se conecte directamente a la base de datos. Luego, verificará la creación del rol. Los pasos siguientes cubrirán la concesión y prueba de privilegios de la base de datos, el cambio de usuarios para verificar el acceso y la revocación de privilegios de un rol.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_setup("Create New Database") postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/user_setup("Create User Account") postgresql/PostgreSQLGroup -.-> postgresql/perm_read("Grant Table Read") postgresql/PostgreSQLGroup -.-> postgresql/perm_write("Grant Table Write") postgresql/PostgreSQLGroup -.-> postgresql/perm_remove("Revoke User Privilege") postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") subgraph Lab Skills postgresql/db_setup -.-> lab-550960{{"Gestión de Roles y Permisos en PostgreSQL"}} postgresql/db_access -.-> lab-550960{{"Gestión de Roles y Permisos en PostgreSQL"}} postgresql/user_setup -.-> lab-550960{{"Gestión de Roles y Permisos en PostgreSQL"}} postgresql/perm_read -.-> lab-550960{{"Gestión de Roles y Permisos en PostgreSQL"}} postgresql/perm_write -.-> lab-550960{{"Gestión de Roles y Permisos en PostgreSQL"}} postgresql/perm_remove -.-> lab-550960{{"Gestión de Roles y Permisos en PostgreSQL"}} postgresql/table_init -.-> lab-550960{{"Gestión de Roles y Permisos en PostgreSQL"}} end

Crear un Rol

En este paso, aprenderá a 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 el usuario postgres.
```
sudo -u postgres psql
```
Debería ver un indicador similar a:
```
postgres=#
```
Cree el rol reporting_role:

Ahora, creemos el 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 se ha creado, puede utilizar 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á así:

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

Salga de la shell de psql.

\q

Otorgar Privilegios de Base de Datos

En este paso, aprenderá a otorgar privilegios de base de datos a un rol. Nos basaremos en el 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 el usuario postgres.
```
sudo -u postgres psql
```
Cree una base de datos:

Creemos una base de datos llamada reporting_db a la que nuestro 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 indicador 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 reporting_role:

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

Crear Usuario y Probar Privilegios

En este paso, creará un usuario y le otorgará membresía al 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 el 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 con una contraseña (reemplace your_password con una contraseña segura). Luego, le otorgaremos a este usuario membresía al reporting_role. Esto significa que report_user heredará los privilegios otorgados a reporting_role.
```
CREATE USER report_user WITH PASSWORD 'your_password';
GRANT reporting_role TO report_user;
```
Debería ver la siguiente salida:
```
CREATE ROLE
GRANT
```
Inserte datos en la tabla sales_data:

Inserte algunos datos de muestra en la tabla sales_data con 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 de la shell de psql.
```
\q
```
Conéctese a la base de datos reporting_db como el report_user:

Abra una nueva ventana de terminal y utilice el siguiente comando:
```
psql -d reporting_db -U report_user -W
```
Se le pedirá la contraseña que estableció para report_user. Introdúzcala.
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 el report_user tiene el privilegio SELECT en la tabla sales_data, que se otorgó a través del reporting_role.

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

Revocar Privilegios

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

Conéctese al servidor PostgreSQL como el usuario postgres:

Vuelva a la terminal donde está conectado como el usuario postgres. Conéctese al servidor PostgreSQL utilizando la herramienta de línea de comandos psql como el 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
```
Revoque el privilegio SELECT del reporting_role:

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

Vuelva a la terminal donde está conectado como el report_user.
```
psql -d reporting_db -U report_user -W
```
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 el report_user ya no tiene privilegios SELECT en la tabla sales_data porque el privilegio fue revocado del reporting_role.

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

Resumen

En este laboratorio, ha aprendido cómo crear roles, otorgar privilegios, crear usuarios, asignar roles a usuarios y revocar privilegios en PostgreSQL. Estos son conceptos fundamentales para administrar la seguridad de la base de datos y el control de acceso (access control).