In PostgreSQL, a user is created to give login permission and with that, the user can be able to access some parts of the database. The user is like a profile that can be used for multiple purposes like read-only access as it only has permission to read the data. The root user has all the access control that can be divided between multiple users to perform their specific tasks.

This guide will explain how to create a read-only user in a PostgreSQL server.

How to Create a Read-Only User in PostgreSQL

To create a read-only user in the PostgreSQL database, simply follow the following simple and easy steps:

Step 1: Create a Role

Open the PostgreSQL client from the local system or connect to it from the Terminal and use the following query to create a Role:

CREATE ROLE readaccess;

Running the above query has created a role in the PostgreSQL database:

Step 2: Assign Multiple Permissions

Once the Role is created, grant a few permissions to it, starting by running the following command:

Contact us today for all your Postgres and Open Source consulting and support needs.

GRANT CONNECT ON DATABASE postgres 
 TO readaccess;

The above code will grant the role with permission to connect to the Postgres database:

Use the following command to grant second permission to the role:

GRANT USAGE ON SCHEMA public TO readaccess;

Executing the above code will allow the role to use the public schema of the Postgres database:

Use the following command to grant permissions to access the tables in the database:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

Running the above code will enable the Role to use all the tables from the public Schema:

Step 3: Create Read-Only User

After all the permissions are assigned to the Role, simply use the following command to create a user in PostgreSQL with its password attach to it:

CREATE USER read_user WITH 
PASSWORD 'abc12345';

Running the above code will create a user that has the password set at the time of its creation:

Grant the role to the user to assign all the permissions that are attached to the role:

GRANT readaccess TO read_user;

Running the above query will grant the Role to the user and with it all the permissions granted to the role will automatically be attached to the user:

That’s all about creating a Read-Only user in PostgreSQL.

Conclusion

To create a read-only user in the PostgreSQL database, simply connect to the PostgreSQL server and type the query to create a Role in the PostgreSQL database. Use the role as the permission role by granting it all the read permissions such as Connecting to the database, Accessing schema, and tables in it. Create a user and grant the role to the user which makes it a read-only user in the PostgreSQL database.