[PostgreSQL] Creating a Read-Only User in PostgreSQL

Creating a Read-Only User in PostgreSQL

In many scenarios, you may need to provide users with access to your PostgreSQL database without allowing them to modify the data. This is common for reporting purposes, data analysis, or third-party application integrations. Creating a read-only user ensures the integrity and security of your database. This guide will walk you through the process of setting up a read-only user in PostgreSQL.

Step 1: Access the PostgreSQL Command Line

First, you need to switch to the PostgreSQL user and access the PostgreSQL command line interface (psql):

# su -l postgres
psql

Step 2: Create a New User

Next, create a new user with login privileges. Replace <user> with the username you want to create and <password> with a secure password.

CREATE USER <user> WITH LOGIN PASSWORD '<password>';

Step 3: Set the User to Read-Only

Set the default transaction mode for the new user to read-only. This ensures that all transactions initiated by this user are read-only by default.

ALTER USER <user> SET default_transaction_read_only = on;

Step 4: Grant Connection Privileges

Grant the new user permission to connect to the desired database. Replace <database> with the name of your database.

GRANT CONNECT ON DATABASE <database> TO <user>;

Step 5: Switch to the Target Database

Change the current database to the one you want the user to access. Again, replace <database> with the name of your database.

\c <database>;

Step 6: Grant Read-Only Access

Grant the new user usage and select permissions on all sequences and tables in the public schema. This allows the user to read data but not modify it.

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO <user>;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user>;

Step 7: Revoke Write Permissions

Ensure the user cannot insert, update, or delete data by explicitly revoking these privileges.

REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM <user>;

Step 8: Configure Client Authentication

Edit the pg_hba.conf file to allow the new user to connect from any IP address. Open the file and add the following line, replacing <database> and <user> accordingly:

host    <database>  <user> 0.0.0.0/0   md5

After saving the changes, reload the PostgreSQL configuration to apply them.

SELECT pg_reload_conf();

Conclusion

By following these steps, you have successfully created a read-only user in PostgreSQL. This user can now connect to the database, read data, and perform queries without the risk of altering any data. This setup is ideal for users who need to analyze data or generate reports without compromising the integrity of your database.

Maintaining a read-only user ensures that sensitive data remains protected while still providing the necessary access for various operations. As a best practice, always review and monitor user permissions to maintain a secure database environment.