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 the read-only user
CREATE USER mindquery_analyst WITH PASSWORD 'secure_password';

-- Grant connect permission to the database 
GRANT CONNECT ON DATABASE your_database TO mindquery_analyst;

-- Grant schema-related permissions
GRANT USAGE ON SCHEMA public TO mindquery_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mindquery_analyst;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mindquery_analyst;

-- Grant permission to view table definitions and schema information
GRANT SELECT ON information_schema.tables TO mindquery_analyst;
GRANT SELECT ON information_schema.columns TO mindquery_analyst;
GRANT SELECT ON information_schema.views TO mindquery_analyst;

-- Grant permission on ALL views in information_schema
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO mindquery_analyst;

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.