How to Create Read Only User in PostgreSQL

Last updated on August 29th, 2024 at 04:58 am

Read only users are very useful for reporting purposes since they have limited read only access to databases, tables and fields. They can only read data and do not have the permission to insert, modify or delete data, tables and columns. Such users do not pose much threat to your data, even if their credentials are stolen or leaked. So, often database administrators and web developers need to create read-only Postgres users. In this article, we will look at how to create read only user in PostgreSQL.

How to Create Read Only User

In PostgreSQL, users and roles are two different components. A role is a collection of permissions and user is login credentials attached to a role. So when you create a read-only user, you need to define two things – its login credentials and its permissions. Here are the steps to create read only user in PostgreSQL.

1. Create Role

Log into PostgreSQL and run the following command to a create read only role readaccess. Replace role name as per your requirement.

postgres-# CREATE ROLE readaccess;

Also read : How to Compare Two Schemas in PostgreSQL

2. Assign Permission

The new role will not have any permission by default. You need to explicitly assign them. Run the following commands to assign access to newly created role, for database mydb. You can change database name as per your requirement.

postgres-# GRANT CONNECT ON DATABASE mydb TO readaccess;

The above command allows the new role to connect to the said database.

Further we will grant access to all tables in public schema. You can change schema name as per your requirement.

postgres-# GRANT USAGE ON SCHEMA public TO readaccess;
postgres-# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

If you want to grant select permission for specific tables (e.g mytable) run the following command

postgres-# GRANT SELECT ON mytable IN SCHEMA public TO readaccess;

Also Read : Top 5 Database Design Tools

3. Create User

Finally, we create a PostgreSQL user and assign the newly created role to that user. Update read_user and read_password in commands below with your username and password.

postgres-# CREATE USER read_user WITH PASSWORD 'read_password';
postgres-# GRANT readaccess TO read_user;

This will automatically assign all permissions available to the role, to the new user.

If you need to change or remove the permissions of read-only user, modify the role instead of user.

Conclusion

In PostgreSQL database, user and role are separate things. When we need to create a read only user, we need to basically create a role with read-only permissions, and then assign the role to a user. The read-only permissions will include the ability to connect to your database, run SELECT queries on all or specific tables in it. Read only users are very useful in limiting user access and permissions, and protecting your data from unnecessary changes. Hopefully, this article will help you create read only user in PostgreSQL.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.