Menu
Is it possible to restrict my connection to a database through the psql client to be read only. I.E. The role using to connect to the database has full privileges on it, but I want to restrict some of my connections to being read-only.
Is this possible or do I have to simply create a new database role with my desired privileges.
319
In mysql we can check whether the database is in read-only mode by triggering the below query. SELECT schemaname||'. '||tablename FROM pg_tables WHERE has_table_privilege ( 'postgres', schemaname||'. '||tablename, 'select' ) AND schemaname NOT IN ( 'pg_catalog','information_schema');
You can create read-only user as -> create a new database user; grant connect on DB; then grant usage on schema and then grant select on tables.
Here is a reference, https://tableplus.com/blog/2018/04/postgresql-how-to-create-read-only-user.html
However allowing read-only to a fully privileged user over a (specific) connection isn't feasible (using built-in features).
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
