How to Create a Read-Only User in PostgreSQL

Sometimes, we will need read-only access to our database, right? So, we can add a read-only access user using the below commands

But, make sure that you can connect to the database as the admin user. After that, execute this query to your database

— create readaccess role;

CREATE ROLE readaccess;

— grant connect to the readaccess role;

GRANT CONNECT ON DATABASE postgres TO readaccess;

— grant usage to public schema to readaccess role;

GRANT USAGE ON SCHEMA public TO readaccess;

— grant select to all tables in public schema to readccess role;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

— create new read only user with password;

CREATE USER ro WITH PASSWORD ‘r34d0nly’;

— grant newly created user to readaccess role;

GRANT readaccess TO ro;

That’s it. Now we have the read-only user for our database.

Hopefully, you found this post useful. Happy Coding!