How to Create Read Only User in PostgreSQL

Read only users are very useful for reporting purposes since they have limited read only access to databases, tables and fields. In this article, we will look at how to create read only user in PostgreSQL.


How to Create Read Only User

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

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;

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;

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.

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!