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

📝 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.