id: 2749942
title: "How to Create a Read-Only User in PostgreSQL (With Access to Statistics)"
published: true
tags: ["postgres", "security", "database", "devops"]
series: I Fixed It and I Don't Know How
description: "Practical guide to configuring a user with limited permissions in PostgreSQL, allowing data and statistics queries without compromising security."
canonical_url: "https://dev.to/ivajofranc/how-to-create-a-read-only-user-in-postgresql-with-access-to-statistics-1394"
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"

How to Create a Read-Only User in PostgreSQL (With Access to Statistics)

🇪🇸 Lee también este post en español

📝 Test Report: Read-Only User Access in PostgreSQL

📋 Test Context

A user with read-only permissions was created for the test PostgreSQL database. The goal was:

1. Allow the user to query all tables (except sensitive ones).
2. Prevent administrative privileges.
3. Allow monitoring of active processes (like MySQL’s SHOW PROCESSLIST).

The test user was: test_ro_user.

---

⚙️ User Creation and Permission Statements

-- Create the read-only user with explicit restrictions
CREATE USER test_ro_user
    LOGIN
    PASSWORD 'LecTur@_2025!'
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOINHERIT;

-- Grant database access
GRANT CONNECT ON DATABASE test TO test_ro_user;

-- Grant usage of the public schema
GRANT USAGE ON SCHEMA public TO test_ro_user;

-- Grant read-only access to all current tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_ro_user;

-- Auto-grant read-only access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO test_ro_user;

-- Explicitly revoke access to the "configuracion" table
REVOKE ALL ON TABLE public.configuracion FROM test_ro_user;

-- Grant full access to system statistics views
GRANT pg_read_all_stats TO test_ro_user;

---

⚙️ Actions Taken

1. User Creation with Restrictions

The script above was executed.

Result: ✅ Successful

• The user was created with the intended restrictions.
• No write access to any table.
• configuracion table is inaccessible (queries are denied).

2. Querying pg_stat_activity (Process Status)

Queries executed:

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_activity WHERE state != 'idle';

Result: ⚠️ Partially Successful

• The user could access pg_stat_activity.
• The query column showed <insufficient privilege> in most rows.

3. Querying Table Usage Statistics

Query executed:

SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables;

Result: ✅ Successful

• The user obtained usage stats for all accessible tables.
• The configuracion table was not listed (as expected).

4. Fixing Visibility in pg_stat_activity

Granted role:

GRANT pg_read_all_stats TO test_ro_user;

Result: ✅ Successful

• The user could fully view the query column in pg_stat_activity.
• No extra privileges were granted beyond stats visibility.

---

🟢 Final Status

Test	Result
Read-only user creation	✅ Success
Access to tables (except restricted ones)	✅ Success
Initial visibility in pg_stat_activity	⚠️ Partial
Fix with pg_read_all_stats	✅ Success
Access to table usage stats	✅ Success

---

📝 Final Recommendations

• Keep the user without write or admin permissions.
• Document pg_read_all_stats usage for advanced read-only profiles.
• Replicate the config for new schemas.
• Consider audits to monitor what this user sees in processes.