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.