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 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!