PostgreSQL: roles y privilegios por esquema, pg_hba.conf con scram-sha-256, autovacuum y tablas hinchadas, conexiones con PgBouncer, pg_stat_statements y una aplicación que no conectaPostgreSQL: roles and schema privileges, pg_hba.conf with scram-sha-256, autovacuum and bloated tables, connections with PgBouncer, pg_stat_statements and an application that cannot connect

Qué vas a conseguir

Operar un servidor PostgreSQL en Linux con criterio: saber qué cambio se aplica con una recarga y cuál necesita reiniciar, dar permisos que no se rompen en el siguiente despliegue, abrir el acceso en pg_hba.conf solo a quien toca, ver si autovacuum llega a tiempo, entender cuándo faltan conexiones y cuándo sobran, encontrar las consultas que más pesan y resolver el ticket "la aplicación no conecta" leyendo cuál de los tres errores típicos es.

Antes de empezar

• PostgreSQL 16 o 17 en Ubuntu, Debian o RHEL (paquetes del proyecto, PGDG), con sudo en el servidor. Se entra como superusuario con sudo -u postgres psql.
• La cuenta de la aplicación, la de copias, la copia programada y el retraso de la réplica están en la guía de MySQL, MariaDB y PostgreSQL: permisos mínimos y copias; la sesión que bloquea y el disco lleno, en la de PostgreSQL, MySQL y SQL Server.
• Una ventana de cambios para lo que exige reinicio (paso 1).

1. Dónde está todo y qué pide reinicio

En la duda, el propio servidor lo dice:

›sudo -u postgres psql -c "SHOW config_file;" -c "SHOW hba_file;"               config_file----------------------------------------- /etc/postgresql/17/main/postgresql.conf               hba_file------------------------------------- /etc/postgresql/17/main/pg_hba.conf›sudo -u postgres psql -c "SELECT name, setting, context, pending_restart FROM pg_settings WHERE name IN ('max_connections','listen_addresses','shared_preload_libraries','idle_in_transaction_session_timeout','log_min_duration_statement');"                name                 |        setting        |  context   | pending_restart-------------------------------------+-----------------------+------------+----------------- idle_in_transaction_session_timeout | 0                     | user       | f listen_addresses                    | localhost,10.20.40.11 | postmaster | f log_min_duration_statement          | -1                    | superuser  | f max_connections                     | 100                   | postmaster | f shared_preload_libraries            |                       | postmaster | f

La columna context responde a la pregunta de cada cambio:

• postmaster: reinicio del servicio (max_connections, listen_addresses, shared_preload_libraries, shared_buffers). Corta todas las conexiones: ventana de cambios.
• sighup, superuser, user: basta con recargar (SELECT pg_reload_conf(); o sudo systemctl reload postgresql@17-main). Las sesiones abiertas siguen.
• pending_restart = t tras una recarga: el valor está en el fichero, pero no se aplicará hasta reiniciar. Es la forma de saber que un cambio "no ha entrado".

pg_hba.conf no es un parámetro, pero va igual: se recarga, no se reinicia. Para cambios en postgresql.conf, mejor un fichero propio incluido (include_dir = 'conf.d', que Debian ya trae) o ALTER SYSTEM SET ..., que escribe en postgresql.auto.conf dentro del directorio de datos y se lee al final.

2. Roles y privilegios: el esquema y lo que se cree mañana

En PostgreSQL los permisos van en tres niveles, y faltar cualquiera da permission denied:

1. Base de datos: GRANT CONNECT ON DATABASE erp TO ....
2. Esquema: GRANT USAGE ON SCHEMA erp TO ... (ver lo que hay dentro) y, solo para quien crea tablas, CREATE.
3. Objetos: GRANT SELECT ... ON ALL TABLES IN SCHEMA erp para las tablas que ya existen, y ALTER DEFAULT PRIVILEGES para las que se creen después.

Desde PostgreSQL 15, el esquema public ya no deja crear objetos a cualquiera (CREATE retirado a PUBLIC) y su dueño es pg_database_owner. Una aplicación migrada desde una versión antigua que creaba tablas en public sin permisos explícitos falla con permission denied for schema public tras la actualización. La solución buena es un esquema propio de la aplicación con su dueño, no devolver CREATE a todo el mundo.

›CREATE ROLE erp_lectura NOLOGIN;›GRANT CONNECT ON DATABASE erp TO erp_lectura;›GRANT USAGE ON SCHEMA erp TO erp_lectura;›GRANT SELECT ON ALL TABLES IN SCHEMA erp TO erp_lectura;›ALTER DEFAULT PRIVILEGES FOR ROLE erp_owner IN SCHEMA erp GRANT SELECT ON TABLES TO erp_lectura;›GRANT erp_lectura TO laura_prado;›\dn+ erp                           List of schemas Name |   Owner   |    Access privileges     | Description------+-----------+--------------------------+------------- erp  | erp_owner | erp_owner=UC/erp_owner  +|      |           | erp_rw=U/erp_owner      +|      |           | erp_lectura=U/erp_owner  |›\ddp               Default access privileges   Owner   | Schema | Type  |   Access privileges-----------+--------+-------+----------------------- erp_owner | erp    | table | erp_rw=arwd/erp_owner+           |        |       | erp_lectura=r/erp_owner

• \dn+ muestra los permisos del esquema (U = USAGE, C = CREATE); \dp erp.facturas, los de una tabla (r = SELECT, a = INSERT, w = UPDATE, d = DELETE); \ddp, los privilegios por defecto.
• La trampa de ALTER DEFAULT PRIVILEGES: solo se aplica a los objetos que cree el rol de FOR ROLE. Si un despliegue se hace con otra cuenta (o con postgres), sus tablas nuevas no los heredan y la aplicación da permission denied esa misma noche. Los despliegues, siempre con el dueño (erp_owner), y \ddp en la revisión.
• Para lecturas de todo y para monitorización hay roles predefinidos: pg_read_all_data (leer todas las tablas, PostgreSQL 14+), pg_monitor y pg_read_all_stats (ver la actividad y el texto de las consultas de otros usuarios). Mejor eso que hacer superusuario a la herramienta de monitorización.

3. pg_hba.conf: quién entra, desde dónde y cómo

Cada línea es tipo base_de_datos usuario dirección método, y gana la primera que coincide, de arriba abajo. Si ninguna coincide, se rechaza la conexión.

# TYPE   DATABASE  USER        ADDRESS            METHODlocal    all       postgres                       peerlocal    all       all                            peerhost     all       backup      127.0.0.1/32       scram-sha-256hostssl  erp       erp_app     10.20.30.0/24      scram-sha-256hostssl  erp       +erp_lectura 10.20.50.0/24     scram-sha-256host     all       all         0.0.0.0/0          reject

• local es el socket Unix; peer deja entrar al usuario del sistema con el mismo nombre (por eso funciona sudo -u postgres psql). host es TCP con o sin TLS; hostssl, solo con TLS.
• +erp_lectura significa "cualquier miembro de ese rol": las altas y bajas se hacen con GRANT/REVOKE, sin tocar el fichero.
• scram-sha-256 es el método recomendado y el cifrado por defecto de las contraseñas desde PostgreSQL 14 (password_encryption = scram-sha-256). Una cuenta cuya contraseña se guardó en MD5 (una base de datos migrada desde una versión antigua) no puede entrar por una línea scram-sha-256: hay que volver a poner su contraseña. SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'; dice cuáles son. MD5 está marcado como obsoleto desde PostgreSQL 18: no lo uses en líneas nuevas.
• Nada de trust fuera de un laboratorio, ni de 0.0.0.0/0 con un método que no sea reject.

Antes de recargar, comprueba que el fichero se entiende:

›sudo -u postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules WHERE error IS NOT NULL;" line_number | type | database | user_name | address | auth_method |              error-------------+------+----------+-----------+---------+-------------+---------------------------------          96 | host | {erp}    | {erp_app} |         |             | invalid IP mask "/33": ...›sudo -u postgres psql -c "SELECT pg_reload_conf();"

pg_hba_file_rules lee el fichero tal como está en disco, no lo que está cargado: si muestra errores, una recarga dejaría el fichero anterior en uso y el log diría pg_hba.conf was not reloaded. Corrige y vuelve a mirar hasta que no salga nada. Y recuerda que el servidor tiene que estar escuchando en esa red: listen_addresses (por defecto, solo localhost) pide reinicio.

4. Autovacuum y tablas hinchadas

Un UPDATE o un DELETE no borra la fila: deja una tupla muerta que VACUUM marca después como espacio reutilizable. Autovacuum lo hace solo, por tabla, cuando las tuplas muertas superan autovacuum_vacuum_threshold (50) más autovacuum_vacuum_scale_factor (0,2, el 20 %) de la tabla. En una tabla de 50 millones de filas, eso son 10 millones de filas muertas antes de que empiece.

›SELECT relname, n_live_tup, n_dead_tup, round(100.0*n_dead_tup/greatest(n_live_tup,1),1) AS pct_muertas, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 4;    relname     | n_live_tup | n_dead_tup | pct_muertas |        last_autovacuum        |       last_autoanalyze----------------+------------+------------+-------------+-------------------------------+------------------------------- movimientos    |   48211904 |    9120533 |        18.9 | 2026-10-19 04:12:51.20871+01  | 2026-11-03 06:40:02.11734+01 sesiones_web   |     211560 |    1904377 |       900.2 | 2026-11-03 09:58:11.40325+01  | 2026-11-03 09:58:12.00412+01 facturas       |     918244 |       3104 |         0.3 | 2026-11-02 23:10:44.88102+01  | 2026-11-02 23:10:45.10288+01

Dos casos distintos en la misma pantalla:

• movimientos: el último autovacuum fue hace dos semanas. Con el 20 % por defecto, en una tabla grande llega tarde. Se le baja el umbral solo a esa tabla: ALTER TABLE erp.movimientos SET (autovacuum_vacuum_scale_factor = 0.01); (1 %).
• sesiones_web: autovacuum pasó hace minutos y aun así hay 9 muertas por cada viva. Eso es que no puede limpiar: alguna transacción abierta desde hace horas retiene esas versiones. Se busca así:

›SELECT pid, usename, application_name, state, now()-xact_start AS abierta, age(backend_xmin) AS xmin_age FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC LIMIT 3;  pid  |  usename   | application_name |        state        |     abierta     | xmin_age-------+------------+------------------+---------------------+-----------------+---------- 31877 | informes   | DBeaver 24.2     | idle in transaction | 05:41:12.30211  |  8812440 32014 | erp_app    | erp-api          | active              | 00:00:00.41022  |       12

Una sesión idle in transaction de casi seis horas desde un cliente de escritorio. Mientras siga abierta, ningún VACUUM puede limpiar lo que ha cambiado desde entonces. Se termina con SELECT pg_terminate_backend(31877); (avisando al usuario) y se pone un tope para la cuenta: ALTER ROLE informes SET idle_in_transaction_session_timeout = '15min';. Las réplicas con hot_standby_feedback y los slots de replicación abandonados retienen igual: mira también pg_replication_slots.

• Hinchazón (bloat): VACUUM deja el espacio libre dentro de la tabla para reutilizarlo, no lo devuelve al disco. Para medirlo de verdad, la extensión pgstattuple (SELECT * FROM pgstattuple('erp.sesiones_web');, campo free_percent).
• Para recuperar el disco, VACUUM FULL reescribe la tabla pero la bloquea por completo mientras dura: solo en ventana y en tablas pequeñas. En tablas grandes y sin parada, la extensión pg_repack.
• Pon log_autovacuum_min_duration = '1min' (desde PostgreSQL 15 viene en 10 minutos) para ver en el log qué tablas tardan en limpiarse.

5. Conexiones y PgBouncer

Cada conexión de PostgreSQL es un proceso del sistema con su memoria. max_connections viene en 100 y se cambia con reinicio. Subirlo a 1.000 no es gratis: cada consulta puede usar hasta work_mem por operación de ordenación o hash, y con cientos de conexiones activas la memoria se va.

›SELECT usename, client_addr, state, count(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY 1,2,3 ORDER BY 4 DESC; usename  | client_addr |        state        | count----------+-------------+---------------------+------- erp_app  | 10.20.30.31 | idle                |    58 erp_app  | 10.20.30.32 | idle                |    21 erp_app  | 10.20.30.31 | active              |     4 informes | 10.20.50.18 | idle in transaction |     1

79 conexiones idle para 4 activas: la aplicación abre muchas y usa pocas. Es el caso de PgBouncer: un pool delante de PostgreSQL que recibe cientos de conexiones de clientes y las sirve con unas pocas conexiones reales.

[databases]erp = host=127.0.0.1 port=5432 dbname=erp [pgbouncer]listen_addr = 10.20.40.11listen_port = 6432auth_type = scram-sha-256auth_file = /etc/pgbouncer/userlist.txtpool_mode = transactionmax_client_conn = 1000default_pool_size = 20admin_users = pgbouncer_admin

• La aplicación apunta al puerto 6432 en lugar de al 5432; el resto de la cadena de conexión no cambia.
• pool_mode = transaction es el que más ahorra: la conexión real se devuelve al acabar cada transacción. Lo que dependa de la sesión (SET sin LOCAL, bloqueos advisory, tablas temporales, LISTEN) no funciona igual; las sentencias preparadas sí, desde PgBouncer 1.21 con max_prepared_statements. Confírmalo con el equipo de la aplicación o empieza en pool_mode = session.
• psql -h 10.20.40.11 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW POOLS;" muestra por pool los clientes activos y en espera (cl_active, cl_waiting) y las conexiones reales (sv_active, sv_idle). cl_waiting mayor que 0 de forma sostenida es que el pool se queda corto.

6. pg_stat_statements: las consultas que más pesan

pg_stat_activity dice qué pasa ahora; pg_stat_statements acumula todas las consultas agrupadas por forma, con tiempos y llamadas. Necesita cargarse al arrancar (un reinicio, una vez):

›sudo -u postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"›sudo systemctl restart postgresql@17-main›sudo -u postgres psql -d erp -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

›SELECT calls, round(total_exec_time/1000) AS total_s, round(mean_exec_time::numeric,1) AS media_ms, rows, left(query,70) AS consulta FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3; calls  | total_s | media_ms |  rows   |                                consulta--------+---------+----------+---------+------------------------------------------------------------------------ 412880 |    6120 |     14.8 |  412880 | SELECT * FROM erp.movimientos WHERE cuenta_id = $1 AND fecha >= $2 ORDER BY    288 |    2210 |   7673.6 |     288 | SELECT cliente_id, sum(importe) FROM erp.facturas f JOIN erp.lineas l ON  91220 |     480 |      5.3 |   91220 | UPDATE erp.sesiones_web SET ultimo_acceso = $1 WHERE id = $2

• Ordena por total_exec_time: la primera consulta tarda 15 ms, pero se ejecuta 400.000 veces y suma más que el informe de 7,7 segundos. Los valores aparecen como $1, $2.
• El siguiente paso es EXPLAIN (ANALYZE, BUFFERS) con valores reales, en un entorno de pruebas o con una consulta de solo lectura: un Seq Scan sobre movimientos con millones de filas pide un índice, que se crea sin bloquear las escrituras con CREATE INDEX CONCURRENTLY idx_mov_cuenta_fecha ON erp.movimientos (cuenta_id, fecha);.
• SELECT pg_stat_statements_reset(); pone los contadores a cero: hazlo después de un cambio para medir el antes y el después. Para ver el texto de las consultas de otros usuarios hace falta superusuario o pg_read_all_stats.
• Para ver en el log cada consulta lenta: log_min_duration_statement = '2s' y recarga.

7. El ticket: "la aplicación no conecta a la base de datos"

Martes, 8:45. El ERP no abre en dos sedes; en otra funciona. La aplicación muestra un error genérico. Lo primero es leer el FATAL exacto en el log del servidor, porque hay tres muy distintos:

›sudo grep -o "FATAL: .*" /var/log/postgresql/postgresql-17-main.log | sort | uniq -c | sort -rn | head    412 FATAL:  no pg_hba.conf entry for host "10.20.31.14", user "erp_app", database "erp", SSL encryption     97 FATAL:  sorry, too many clients already     12 FATAL:  remaining connection slots are reserved for ...

Caso 1: no pg_hba.conf entry. La IP 10.20.31.14 es de la red nueva de una de las sedes, migrada el fin de semana. pg_hba.conf solo tiene 10.20.30.0/24:

›sudoedit /etc/postgresql/17/main/pg_hba.conf# añadir debajo de la línea de 10.20.30.0/24:# hostssl  erp  erp_app  10.20.31.0/24  scram-sha-256›sudo -u postgres psql -tAc "SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL;"0›sudo -u postgres psql -c "SELECT pg_reload_conf();"›psql "host=srv-db-02.contoso.local dbname=erp user=erp_app sslmode=require" -c "SELECT 1;"

• La línea va antes de cualquier reject general: gana la primera que coincide.
• El mensaje dice SSL encryption o no encryption: si el cliente conecta sin TLS y la línea es hostssl, tampoco coincide.
• Si el error cambia a password authentication failed for user "erp_app", la línea ya coincide: el problema es la contraseña o un hash MD5 con método scram-sha-256 (paso 3).
• Y si el cliente recibe Connection refused o un tiempo de espera, ni siquiera llegó: listen_addresses, el cortafuegos (5432/tcp) o la red. Eso no deja FATAL en el log.

Casos 2 y 3: conexiones agotadas. Entra como superusuario por el socket (sudo -u postgres psql usa uno de los huecos reservados; si sale too many clients already, ni eso queda) y mira quién las tiene:

›SHOW max_connections; 100›SELECT usename, client_addr, state, count(*), max(now()-state_change) AS mas_antigua FROM pg_stat_activity WHERE backend_type='client backend' GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT 3; usename | client_addr |        state        | count |   mas_antigua---------+-------------+---------------------+-------+----------------- erp_app | 10.20.30.32 | idle                |    74 | 13:02:44.11 erp_app | 10.20.30.31 | active              |     6 | 00:00:01.20 erp_app | 10.20.30.32 | idle in transaction |    11 | 02:15:09.84›SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename='erp_app' AND client_addr='10.20.30.32' AND state IN ('idle','idle in transaction') AND now()-state_change > interval '10 minutes';

1. Devolver el servicio: cerrar las conexiones inactivas de ese origen, con más de 10 minutos, como arriba. No hace falta reiniciar PostgreSQL, y reiniciarlo cortaría también a los usuarios que sí funcionan.
2. La causa: un nodo (10.20.30.32) con 85 conexiones, 11 de ellas idle in transaction desde hace dos horas. Al equipo de la aplicación: el tamaño del pool por nodo y la transacción que no se cierra.
3. Que el servidor se defienda: ALTER ROLE erp_app SET idle_in_transaction_session_timeout = '10min'; (se aplica a las sesiones nuevas de ese rol, sin reinicio) y, si la aplicación lo tolera, idle_session_timeout para las inactivas sin transacción.
4. La solución de fondo: PgBouncer (paso 5) delante, con la aplicación en el 6432. Subir max_connections es la última opción: necesita reinicio en ventana y memoria para ello.

En el ticket: qué FATAL era (con el recuento del log), la línea añadida a pg_hba.conf o las conexiones cerradas y de qué origen, si fue recarga o reinicio, y la alerta nueva: conexiones > 80 % de max_connections con postgres_exporter.

Siguientes pasos

• Monitoriza conexiones por estado, tuplas muertas por tabla, la edad de la transacción más antigua y cl_waiting de PgBouncer con postgres_exporter y la guía de Grafana.
• Para alta disponibilidad con conmutación automática, Patroni sobre la réplica en streaming; para copias físicas con recuperación a un momento concreto, pgBackRest.
• La guía de MySQL y MariaDB resuelve el mismo problema de conexiones agotadas en el otro motor.

¿Falta algo o en tu entorno es distinto? Escríbeme y lo corrijo.