How to create a read-only user in PostgreSQL?

How to create a read-only user in PostgreSQL?

Why do we need a read-only user?

Creating a read-only user in any database is essential for several reasons, especially when it comes to security, data integrity, and access control.

In this guide, we'll explore how to create a read-only user in PostgreSQL. Read-only users are useful when you need to provide access to specific users without allowing them to make changes to the data,

Prerequisites

  • psql CLI is installed on your machine.

  • Access to the PostgreSQL admin account.

Log in to PostgreSQL

First, log in to your PostgreSQL instance using the psql command-line interface. You can do this by running the following command on your terminal or command prompt:

psql -U postgres -h dbhosturl

Here, -U postgres specifies the username of the admin, and -h is the host where PostgreSQL is running.

Create a New User

Now, let's create a new user that will be granted read-only access. To create the user, execute the following command:

CREATE USER readonly_user WITH PASSWORD 'strongpassword';

Replace 'readonly_user' with the desired username and strongpassword with a strong password.

Grant Connection Privileges

Once the user is created, we need to grant the user the ability to connect to the desired database. Suppose the database name is mydatabase, the command to grant connection privileges is as follows

GRANT CONNECT ON DATABASE mydatabase TO readonly_user;

This grants the user the right to connect to the specified database (mydatabase).

if we have multiple databases, you can give connect to access to other databases as well.

GRANT CONNECT ON DATABASE anotherdb TO readonly_user;

Grant USAGE Privileges on Schema

PostgreSQL databases are organized into schemas, and before allowing the user to access the tables, we must grant them the ability to use the schema.

If you prefer to see the schemas you can run this

SELECT schema_name
FROM information_schema.schemata;

after you get the schemas name, you GRANT USAGE on schemes

GRANT USAGE ON SCHEMA schema_name TO username;

GRANT USAGE ON SCHEMA public TO readonly_user;

GRANT USAGE ON SCHEMA userschema TO readonly_user;

Grant SELECT Privileges on Tables

granting the user the ability to read data from the tables. This can be done table by table,

Running below command will require an admin to connect to that DB.

For single table

GRANT SELECT ON table_name TO username;

For all tables

but if you want to allow access to all tables in the schema, you can use the following command

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

This command grants the SELECT privilege, which allows the user to query all the tables in the public schema without modifying them.

Apply Future SELECT Privileges (Optional)

If you expect new tables to be created in the schema in the future and you want the readonly_user to automatically gain access to those new tables, you can modify the default privileges

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

This command ensures that any future tables created in the schema will have the SELECT privilege automatically granted to the user.

Verify Permissions

Once you've granted the necessary privileges, you can verify the user's permissions by logging in as the read-only user

psql -U readonly_user -d mydatabase -h localhost

Try running a SELECT query to confirm that the user has read-only access:

SELECT * FROM some_table;

If the user is correctly set up, the query should work, but any attempts to modify data (e.g., INSERT, UPDATE, or DELETE) should fail with permission-denied errors.

Did you find this article valuable?

Support Muhammad Usama by becoming a sponsor. Any amount is appreciated!