DEV Community

Cover image for Cómo crear un usuario de solo lectura en PostgreSQL (con acceso a estadísticas)
Ivaj O'Franc
Ivaj O'Franc

Posted on • Edited on • Originally published at dev.to

Cómo crear un usuario de solo lectura en PostgreSQL (con acceso a estadísticas)

#postgres #security #database #devops 
id: 2749608
title: "Cómo crear un usuario de solo lectura en PostgreSQL (con acceso a estadísticas)"
published: true
tags: ["postgres", "security", "database", "devops"]
series: Lo Arreglé y No Sé Cómo
description: "Guía práctica para configurar un usuario con permisos limitados en PostgreSQL, permitiéndole consultar datos y estadísticas sin comprometer la seguridad."
canonical_url: "https://dev.to/ivajofranc/como-crear-un-usuario-de-solo-lectura-en-postgresql-con-acceso-a-estadisticas-3kkf"
cover_image: "https://media2.dev.to/dynamic/image/width=1000,height=420,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2Fd117i3lx8tkd8wxnqqy9.png"
Enter fullscreen mode Exit fullscreen mode

Cómo crear un usuario de solo lectura en PostgreSQL (con acceso a estadísticas)

🇬🇧 Also read this post in English

📝 Informe de Pruebas: Acceso de Usuario Solo Lectura en PostgreSQL

📋 Contexto de la Prueba

Se ha creado un usuario con permisos de solo lectura sobre la base de datos test en PostgreSQL. El objetivo era:

  1. Permitir al usuario consultar todas las tablas (excepto las sensibles).
  2. Evitar que el usuario tenga permisos administrativos.
  3. Permitir al usuario monitorizar procesos activos (equivalente a SHOW PROCESSLIST en MySQL).

El usuario de prueba fue: test_ro_user.

⚙️ Sentencias de Creación de Usuario y Permisos 

-- Crear el usuario de solo lectura con restricciones explícitas
CREATE USER test_ro_user
    LOGIN
    PASSWORD 'LecTur@_2025!'
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOINHERIT;

-- Conceder acceso a la base de datos
GRANT CONNECT ON DATABASE test TO test_ro_user;

-- Conceder uso del esquema público
GRANT USAGE ON SCHEMA public TO test_ro_user;

-- Conceder acceso de solo lectura a todas las tablas actuales
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_ro_user;

-- Conceder acceso automático de solo lectura a futuras tablas
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO test_ro_user;

-- Revocar acceso explícitamente a la tabla "configuracion"
REVOKE ALL ON TABLE public.configuracion FROM test_ro_user;

-- Permitir acceso completo a las vistas de estadísticas del sistema
GRANT pg_read_all_stats TO test_ro_user;
Enter fullscreen mode Exit fullscreen mode

⚙️ Acciones Realizadas

1. Creación del Usuario con Restricciones

Se ejecutó el script anterior.

Resultado: ✅ Correcto

  • El usuario fue creado con las restricciones deseadas.
  • No tiene acceso de escritura a ninguna tabla.
  • La tabla configuracion no es accesible por este usuario (consultas sobre esta tabla son denegadas).

2. Consulta a pg_stat_activity (Estado de procesos)

Consultas ejecutadas:

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_activity WHERE state != 'idle';
Enter fullscreen mode Exit fullscreen mode

Resultado: ⚠️ Parcialmente Correcto

  • El usuario pudo acceder a la vista pg_stat_activity.
  • En la columna query aparecía <insufficient privilege> en la mayoría de registros.

3. Consulta a Estadísticas de Tablas del Usuario

Consulta ejecutada:

SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables;
Enter fullscreen mode Exit fullscreen mode

Resultado: ✅ Correcto

  • El usuario obtuvo correctamente las estadísticas de uso de todas las tablas accesibles.
  • La tabla configuracion no se mostró (como esperado).

4. Corrección de Visibilidad en pg_stat_activity

Se otorgó el rol:

GRANT pg_read_all_stats TO test_ro_user;
Enter fullscreen mode Exit fullscreen mode

Resultado: ✅ Correcto

  • El usuario pudo ver el contenido completo de la columna query en pg_stat_activity.
  • No se concedieron permisos adicionales más allá de la visibilidad de estadísticas.

🟢 Estado Final

Prueba Resultado
Creación de usuario con solo lectura ✅ Correcto
Acceso a tablas (excepto las restringidas) ✅ Correcto
Visibilidad en pg_stat_activity inicial ⚠️ Parcial
Corrección visibilidad con pg_read_all_stats ✅ Correcto
Acceso a estadísticas de uso de tablas ✅ Correcto

📝 Recomendaciones Finales

  • Mantener el usuario sin permisos de escritura ni roles administrativos.
  • Documentar el uso de pg_read_all_stats para perfiles de solo lectura avanzados.
  • Replicar la configuración si se crean nuevos esquemas.
  • Considerar auditorías si se desea monitorizar qué procesos visualiza este usuario.

Top comments (0)