Posted on Jun 12, 2024

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;