How to Create a Read-Only User in PostgreSQL
Almatin Siswanto
Posted on June 12, 2024
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!
💖 💪 🙅 🚩
Almatin Siswanto
Posted on June 12, 2024
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.