How to Create a Read-Only User in PostgreSQL

In many real-world applications, you may need to grant someone access to a PostgreSQL database just to view the data, without allowing any changes. This is where creating a read-only user becomes essential.

In this post, we’ll walk through the step-by-step process to create a read-only user in PostgreSQL.

You may want a read-only user for:

• - Reporting tools (like Metabase, Power BI, etc.)
• - Data auditors
• - External analysts
• - Monitoring dashboards

Prerequisites

• - PostgreSQL installed and running
• - Access to a superuser account (like postgres)
• - A database (we'll use mydatabase as an example)

Step-by-Step Procedure to Create Read-Only User

Connect to PostgreSQL

Using the psql CLI or any GUI like pgAdmin, connect to your database:

psql -U postgres -d mydatabase

Create the Read-Only User

CREATE USER readonly_user WITH PASSWORD 'StrongPassword123!';

Grant CONNECT on the Database

GRANT CONNECT ON DATABASE mydatabase TO readonly_user;

Grant USAGE on the Schema
Assuming your schema is public:

GRANT USAGE ON SCHEMA public TO readonly_user;

If you have multiple schemas, repeat this step for each.

Grant SELECT on Existing Tables

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Grant SELECT on Existing Sequences (Optional)
If your app needs to read auto-incrementing values:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;

Ensure Future Tables Are Also Covered
To make sure new tables are also readable by readonly_user, use:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON SEQUENCES TO readonly_user;

(Optional) Revoke Write Access Just in Case

REVOKE CREATE ON SCHEMA public FROM readonly_user;
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM readonly_user;

Final Test

psql -U readonly_user -d mydatabase
SELECT * FROM some_table;
-- should work
INSERT INTO some_table VALUES (...);
-- should be denied