--- title: "Gestionar usuarios, roles y permisos en PostgreSQL 16" description: "Crea roles en PostgreSQL 16 sobre Ubuntu 24.04, separa propietario, escritura y lectura, define privilegios por defecto y pg_hba.conf, y verifica los permisos." category: "Gestión de Bases de Datos" language: es source: https://cubepath.com/es/docs/gestión-de-bases-de-datos/gestion-de-usuarios-y-permisos-en-postgresql updated: 2026-09-25 --- # Gestionar usuarios, roles y permisos en PostgreSQL 16 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: ```bash sudo -u postgres psql ``` Lista los roles con el metacomando `\du`: ```sql \du ``` ```output 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: ```sql 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: ```sql 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: ```sql \c appdb \dn+ public ``` ```output 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: ```sql 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: ```sql 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: ```sql 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`: ```sql \ddp ``` ```output 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. ```sql 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: ```sql \du ``` ```output 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: ```sql SET ROLE app_owner; CREATE TABLE clientes (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, nombre text, email text); RESET ROLE; \dp clientes ``` ```output 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: ```bash psql -h localhost -U app_web -d appdb ``` ```sql INSERT INTO clientes (nombre, email) VALUES ('Prueba', 'prueba@example.com'); SELECT * FROM clientes; DROP TABLE clientes; ``` ```output INSERT 0 1 id | nombre | email ----+--------+-------------------- 1 | Prueba | prueba@example.com (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: ```sql SELECT has_table_privilege('ana', 'clientes', 'INSERT'); ``` ```output 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: ```sql 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: ```sql 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: ```bash sudo nano /etc/postgresql/16/main/pg_hba.conf ``` ```text # 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`): ```bash sudo nano /etc/postgresql/16/main/postgresql.conf ``` ```ini listen_addresses = 'localhost,10.0.0.2' ``` Reinicia PostgreSQL y abre el puerto solo para el servidor de aplicación: ```bash 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: ```bash 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();" ``` ```output 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: ```sql 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: ```sql \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.