How to Create a Read-Only User in PostgreSQL

#postgressql #postgres #database #readonly

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;