En PostgreSQL, usuarios y grupos son la misma cosa: roles. Un rol con el atributo LOGIN actúa como usuario, y un rol sin él sirve como grupo al que se asignan privilegios para heredarlos. En este tutorial crearás en PostgreSQL 16 sobre Ubuntu 24.04 un esquema de permisos habitual para una aplicación: un rol propietario de la base de datos, un grupo de lectura y escritura, un grupo de solo lectura y usuarios que heredan de ellos, con privilegios por defecto para las tablas futuras y acceso remoto controlado en pg_hba.conf.

Requisitos previos

Para seguir esta guía necesitas:

  • Un servidor con Ubuntu 24.04 LTS, por ejemplo un VPS de CubePath, con PostgreSQL 16 instalado (sudo apt install postgresql).
  • Un usuario no root con privilegios sudo.
  • Conocimientos básicos de SQL.

Paso 1: Conectarse como superusuario y revisar los roles

La instalación crea el rol de superusuario postgres, que se autentica por socket local con el método peer: solo el usuario del sistema postgres puede usarlo. Abre psql con él:

sudo -u postgres psql

Lista los roles con el metacomando \du:

\du

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

Reserva postgres para administración. Ninguna aplicación debe conectarse con un superusuario, porque puede leer y borrar cualquier dato y ejecutar comandos en el sistema operativo.

Paso 2: Crear el propietario y la base de datos

El propietario de una base de datos y de sus tablas puede hacer cualquier cosa con ellas, incluido borrarlas. Crea un rol propietario sin permiso de inicio de sesión, que solo usarás para crear y modificar el esquema:

CREATE ROLE app_owner NOLOGIN;
CREATE DATABASE appdb OWNER app_owner;

Por defecto, cualquier rol puede conectarse a cualquier base de datos (el privilegio CONNECT se concede a PUBLIC, es decir, a todos). Retíralo para decidir tú quién entra:

REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;

Conéctate a la nueva base de datos. Desde PostgreSQL 15, el esquema public pertenece al rol especial pg_database_owner (es decir, al propietario de cada base de datos, aquí app_owner) y el resto de roles ya no puede crear objetos en él, así que no hay que retirar ese permiso a mano:

\c appdb
\dn+ public

                                          List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |

=U significa que todos los roles tienen USAGE (pueden ver objetos del esquema si además tienen permiso sobre ellos), y solo el propietario de la base de datos tiene C (CREATE).

Paso 3: Crear los grupos de lectura y escritura

Crea dos roles de grupo, sin LOGIN, que concentran los privilegios:

CREATE ROLE app_rw NOLOGIN;
CREATE ROLE app_ro NOLOGIN;

GRANT CONNECT ON DATABASE appdb TO app_rw, app_ro;
GRANT USAGE ON SCHEMA public TO app_rw, app_ro;

Los privilegios sobre tablas en PostgreSQL se aplican a objetos concretos, así que GRANT ... ON ALL TABLES solo afecta a las tablas que existen ahora. Para las tablas que app_owner cree en el futuro, define privilegios por defecto:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO app_ro;

El permiso sobre secuencias es necesario para que app_rw pueda insertar en tablas con columnas serial o identity. Si la base de datos ya tiene tablas, concede también los privilegios sobre las existentes:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;

Revisa los privilegios por defecto con \ddp:

\ddp

                   Default access privileges
   Owner   | Schema |   Type   |      Access privileges
-----------+--------+----------+-----------------------------
 app_owner | public | sequence | app_rw=rU/app_owner
 app_owner | public | table    | app_ro=r/app_owner         +
           |        |          | app_rw=arwd/app_owner

Las letras son abreviaturas: r = SELECT, a = INSERT, w = UPDATE, d = DELETE, U = USAGE.

Paso 4: Crear los usuarios

Ahora crea los roles con LOGIN que usarán las personas y las aplicaciones, y hazlos miembros de los grupos. Usa \password para fijar la contraseña: así no queda escrita en el historial de psql ni en los registros del servidor.

CREATE ROLE app_migrator LOGIN;
GRANT app_owner TO app_migrator;
\password app_migrator

CREATE ROLE app_web LOGIN CONNECTION LIMIT 50;
GRANT app_rw TO app_web;
\password app_web

CREATE ROLE ana LOGIN VALID UNTIL '2027-03-31';
GRANT app_ro TO ana;
\password ana

Qué obtiene cada uno:

  • app_migrator hereda de app_owner. Úsalo para las migraciones del esquema. Para que las tablas nuevas pertenezcan a app_owner (y se apliquen los privilegios por defecto del paso 3), las migraciones deben ejecutar primero SET ROLE app_owner;.
  • app_web hereda de app_rw y es la cuenta de la aplicación. CONNECTION LIMIT 50 evita que agote las conexiones del servidor.
  • ana hereda de app_ro y solo puede leer. VALID UNTIL hace que su contraseña deje de funcionar en esa fecha.

Comprueba las pertenencias:

\du

                                  List of roles
  Role name   |                         Attributes
--------------+------------------------------------------------------------
 ana          | Password valid until 2027-03-31 00:00:00+00
 app_migrator |
 app_owner    | Cannot login
 app_ro       | Cannot login
 app_rw       | Cannot login
 app_web      | 50 connections
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS

Para ver de qué grupos es miembro cada rol, usa \drg, disponible desde PostgreSQL 16.

Paso 5: Probar los permisos

Crea una tabla como app_owner, para que se apliquen los privilegios por defecto:

SET ROLE app_owner;
CREATE TABLE clientes (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, nombre text, email text);
RESET ROLE;
\dp clientes

                                    Access privileges
 Schema |   Name   | Type  |     Access privileges     | Column privileges | Policies
--------+----------+-------+---------------------------+-------------------+----------
 public | clientes | table | app_ro=r/app_owner       +|                   |
        |          |       | app_rw=arwd/app_owner    +|                   |
        |          |       | app_owner=arwdDxt/app_owner |                   |

La tabla ha recibido automáticamente los permisos de app_rw y app_ro. Sal de psql con \q y conéctate como app_web por TCP a localhost, que en Ubuntu usa autenticación por contraseña:

psql -h localhost -U app_web -d appdb

INSERT INTO clientes (nombre, email) VALUES ('Prueba', '[email protected]');
SELECT * FROM clientes;
DROP TABLE clientes;

INSERT 0 1
 id | nombre |       email
----+--------+--------------------
  1 | Prueba | [email protected]
(1 row)

ERROR:  must be owner of table clientes

Repite como ana: el SELECT funciona y el INSERT falla con ERROR: permission denied for table clientes. Como administrador, también puedes consultar un permiso concreto sin cambiar de usuario:

SELECT has_table_privilege('ana', 'clientes', 'INSERT');

 has_table_privilege
---------------------
 f

Paso 6: Limitar columnas y filas

Para ocultar columnas sensibles, concede SELECT solo sobre algunas columnas en lugar de sobre toda la tabla. Primero retira el permiso general del grupo y después concede el de columnas:

REVOKE SELECT ON clientes FROM app_ro;
GRANT SELECT (id, nombre) ON clientes TO app_ro;

Ahora ana puede ejecutar SELECT id, nombre FROM clientes, pero SELECT email o SELECT * fallan con permission denied.

Para limitar qué filas ve cada usuario, PostgreSQL ofrece seguridad a nivel de fila (RLS). Por ejemplo, en una tabla con una columna comercial que guarda el nombre del rol responsable de cada cliente:

ALTER TABLE clientes ADD COLUMN comercial text;
ALTER TABLE clientes ENABLE ROW LEVEL SECURITY;
CREATE POLICY clientes_propios ON clientes
  FOR SELECT TO app_ro
  USING (comercial = current_user);
CREATE POLICY clientes_aplicacion ON clientes
  TO app_rw
  USING (true) WITH CHECK (true);

La segunda política es imprescindible: con RLS activo, un rol sin ninguna política aplicable no ve ni modifica ninguna fila, y la aplicación (app_rw) dejaría de funcionar.

El propietario de la tabla no está sujeto a las políticas salvo que uses ALTER TABLE ... FORCE ROW LEVEL SECURITY, y los superusuarios nunca lo están.

Paso 7: Permitir conexiones remotas en pg_hba.conf

El archivo pg_hba.conf decide quién puede conectarse, a qué base de datos, desde dónde y con qué método. Las reglas se evalúan en orden y se aplica la primera que coincide. Por defecto, en Ubuntu solo se aceptan conexiones locales.

Si app_web se conecta desde un servidor de aplicación con IP privada 10.0.0.5, añade una regla específica al final del archivo:

sudo nano /etc/postgresql/16/main/pg_hba.conf

# TYPE   DATABASE  USER     ADDRESS        METHOD
hostssl  appdb     app_web  10.0.0.5/32    scram-sha-256

hostssl solo acepta conexiones cifradas (Ubuntu activa SSL con un certificado autofirmado al instalar PostgreSQL) y scram-sha-256 es el método de contraseña seguro. Evita reglas con all y 0.0.0.0/0.

Haz que PostgreSQL escuche en la IP privada del servidor de base de datos (en el ejemplo, 10.0.0.2):

sudo nano /etc/postgresql/16/main/postgresql.conf

listen_addresses = 'localhost,10.0.0.2'

Reinicia PostgreSQL y abre el puerto solo para el servidor de aplicación:

sudo systemctl restart postgresql
sudo ufw allow from 10.0.0.5 to any port 5432 proto tcp

Desde 10.0.0.5, con el cliente instalado (sudo apt install postgresql-client), comprueba la conexión:

psql "host=10.0.0.2 dbname=appdb user=app_web sslmode=require" -c "SELECT current_user, ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

 current_user | ssl
--------------+-----
 app_web      | t
(1 row)

Para futuros cambios en pg_hba.conf basta con recargar: sudo systemctl reload postgresql.

Paso 8: Retirar un usuario

Para quitar el acceso a un usuario de forma inmediata sin borrarlo, desactiva su inicio de sesión:

ALTER ROLE ana NOLOGIN;

Para eliminarlo, primero reasigna o borra los objetos que posea, en cada base de datos donde tenga alguno, y luego borra el rol:

\c appdb
REASSIGN OWNED BY ana TO app_owner;
DROP OWNED BY ana;
DROP ROLE ana;

DROP OWNED también retira los privilegios concedidos directamente al rol; sin ese paso, DROP ROLE falla si al rol le queda algún permiso.

Solución de problemas

ERROR: permission denied for schema public: el rol no tiene USAGE (para leer) o CREATE (para crear tablas) en el esquema. Desde PostgreSQL 15, nadie salvo el propietario puede crear objetos en public. Crea las tablas como app_owner o concede CREATE explícitamente a quien lo necesite.

Un usuario no ve las tablas nuevas (permission denied for table): la tabla la creó un rol distinto del indicado en ALTER DEFAULT PRIVILEGES FOR ROLE. Comprueba el propietario con \dt y asegúrate de que las migraciones ejecutan SET ROLE app_owner;. Para arreglar las ya creadas, repite los GRANT ... ON ALL TABLES del paso 3.

FATAL: Peer authentication failed for user "app_web": te conectaste por socket local sin -h, y la regla local usa peer, que exige un usuario del sistema con el mismo nombre. Conéctate con -h localhost.

FATAL: no pg_hba.conf entry for host "10.0.0.5", user "app_web", database "appdb", no encryption: el cliente se conecta sin SSL y la regla es hostssl, o no hay regla para esa IP. Añade sslmode=require en el cliente y revisa pg_hba.conf.

FATAL: too many connections for role "app_web": se alcanzó el CONNECTION LIMIT del rol. Revisa las conexiones abiertas con SELECT count(*) FROM pg_stat_activity WHERE usename = 'app_web'; y usa un pool de conexiones en la aplicación.

Conclusión

Has separado el acceso a la base de datos en un propietario, grupos de lectura y escritura y usuarios que heredan de ellos, con privilegios por defecto para las tablas futuras, restricciones por columna y fila, y acceso remoto cifrado limitado a una IP. Como siguientes pasos, puedes registrar las conexiones con log_connections = on, auditar las consultas con la extensión pgAudit (paquete postgresql-16-pgaudit) y colocar PgBouncer delante del servidor para gestionar las conexiones de la aplicación.