How to Create a Read-Only User in PostgreSQL

Introduction

When working with PostgreSQL, there are scenarios where you need to provide access to a database without granting modification rights. This is particularly useful for reporting, analytics, or when you want to expose data securely to external users or applications.

In this article, we’ll walk through the process of creating a read-only user in PostgreSQL. We’ll also explain each command to ensure you understand what’s happening at each step.

Steps to Create a Read-Only User in PostgreSQL

1. Create a New User

CREATE USER readonly_user WITH PASSWORD 'securepassword';

This command creates a new PostgreSQL user named readonly_user with a specified password. Replace 'securepassword' with a strong password of your choice.

2. Grant Connection Privileges

GRANT CONNECT ON DATABASE mydatabase TO readonly_user;

This allows the readonly_user to connect to the database named mydatabase. Without this privilege, the user won’t be able to access the database.

3. Switch to the Target Database

\c mydatabase

This command switches to the database where you want to grant permissions. If you’re using a SQL query tool, make sure you’re connected to the correct database.

4. Grant Schema Usage Permission

GRANT USAGE ON SCHEMA public TO readonly_user;

PostgreSQL databases can have multiple schemas. This command grants permission to use the public schema, which is the default schema where tables are stored.

5. Grant Read-Only Access to All Tables

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

This allows the readonly_user to read (SELECT) data from all tables within the public schema. However, it won’t allow modifications such as INSERT, UPDATE, or DELETE.

6. Ensure Future Tables Have Read-Only Access

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

If new tables are added in the future, they will automatically inherit the SELECT permission for the readonly_user. Without this step, the user wouldn’t be able to query newly created tables.

Conclusion

Creating a read-only user in PostgreSQL is a simple yet crucial step for securely granting access to data without allowing modifications. By following the above steps, you ensure that your user has the necessary permissions to query the database while maintaining security and data integrity

Monday, March 3, 2025