Creating a Read-Only PostgreSQL User for Data Analysis

This guide explains how to create a read-only user in PostgreSQL to allow an analysis tool to access data securely.

Prerequisites

  • Access to the PostgreSQL database as a superuser or a user with sufficient privileges.
  • The database to which the user needs read-only access.

Create the User and Grant Permissions

Run the following SQL commands to create the user, grant database connection access, schema usage, and read-only access to all existing tables:

CREATE USER readonly_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE your_database TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Log in to PostgreSQL

Use the psql command-line tool or any database client to connect to your PostgreSQL instance:

psql -U postgres -d your_database

Verify the Configuration

Log in as the read-only user to ensure the permissions are working as expected:

psql -U readonly_user -d your_database

Run a query to test access:

SELECT * FROM some_table;

The read-only user is now ready for use by the analysis tool.