La trinchera divergente

Es mejor pedir perdón que pedir permiso

Dicho popular

En esta ocasión nos enfrentamos a dar permisos a un usuario dentro de una base de datos en postgres. El objetivo es implementar una capa de seguridad a nivel de BD limitando privilegios y/o accesos a los usuarios dependiendo el rol con el que interactúan con el sistema.

Lo primero con lo que me enfrenté fue: ¿Se crea un rol o un usuario? ¿Cuál es la diferencia?
Si bien en versiones pasadas postgres manejaba usuarios y roles, en las versiones actuales básicamente son lo mismo, la diferencia es que al crear un usuario, realmente creas un rol y al mismo tiempo se le otorga el permiso de login

Crear usuario:

CREATE USER junglacode WITH PASSWORD 'jungla_c#!ng0n';

Crear Rol:

CREATE ROLE junglacode WITH LOGIN PASSWORD 'jungla_c#!ng0n';

Siendo honesto, la primera vez que lo hice, creé el rol pero sin permiso de LOGIN, así que no me dejaba conectar XD.

CREATE ROLE junglacode WITH PASSWORD 'jungla_c#!ng0n';

Otorgando el permiso se solucionó

GRANT CONNECT ON DATABASE db_name TO junglacode;

Ya que tenemos el usuario, lo que sigue es otorgar permisos para usar los schemas, desde aquí podemos comenzar a restringir accesos, por ejemplo si tenemos en un schema tablas que algún usuario no necesita manipular, no le damos el permiso.

GRANT USAGE ON SCHEMA schema_name TO junglacode;

Se puede asignar el permiso a mas de un schema a la vez separando con comas.

Ahora lo que debemos hacer es permitir al usuario realizar ciertas acciones sobre las tablas, pueden ser de índole ddl o dml, de igual manera se usa el comendo GRANT pero dependiendo el permiso es la sintaxis, aquí el enlace a la documentación

https://www.postgresql.org/docs/9.1/sql-grant.html

Como ejemplo, vamos a dar permisos del CRUD sobre todas las tablas de un schema

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO junglacode;

También se puede asignar el permiso a mas de un schema a la vez separando con comas.

Lo que me paso una vez que otorgué estos permisos e intente insertar un registro, fue que me arrojó el error “permission denied for sequence” para lo cual debemos ejecutar:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA schema_name TO junglacode;

Con esto indicamos que podemos usar las secuencias, a grandes rasgos una secuencia en postgresql es un objeto vinculado a un esquema definido por el usuario que genera una secuencia de enteros basada en una especificación (Un generador de secuencias)

Por ultimo, ¿Qué pasa si en determinado momento queremos quitar los privilegios a algún usuario? La solución a esto es REVOKE

REVOKE DELETE, UPDATE ON table_name FROM junglacode;

Esto nos permite implementar una capa de seguridad a nivel usuarios de BD y tener nuestros sistemas mas seguros, ya que si por alguna razón las credenciales del usuario de una aplicación son obtenidas por personas maliciosas, podemos al menos limitar que no hará un drop catastrófico.

Referencias:

https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html
https://www.tutorialspoint.com/postgresql/postgresql_privileges.htm