2

I am new to postgres. In mysql we can check whether the database is in read-only mode by triggering the below query.

SELECT @@global.read_only

Likewise can anyone pls help me with the query to do the same in postgres? I tried few things like below 

SELECT schemaname||'.'||tablename FROM pg_tables
WHERE
  has_table_privilege ( 'postgres', schemaname||'.'||tablename, 'select' )
  AND schemaname NOT IN ( 'pg_catalog','information_schema');

But it is listing like below which I am not expecting. 

         ?column?                
----------------------------------------
 public.schema_migrations
 public.credential_methods
 public.notifications
 public.site_defaults
 public.apis
 public.client_applications
 public.api_groups
 public.operations
 public.client_application_labels
 public.client_application_label_values
 public.roles
 public.users
 public.sdm_user_roles
 public.permissions_roles
 public.keys
 public.o_two_access_tokens
 public.settings
 public.sdm_users
 public.permissions
 public.audits
 public.oauth_requesttokens
 public.oauth_access_tokens
 public.oauth_verifiers
 public.logged_exceptions
 public.api_call_details
 public.api_access_roles
 public.api_access_users
 public.login_attempts
 public.system_scopes
 public.keys_system_scopes
 public.o_two_auth_codes
 public.o_two_refresh_tokens
 public.service_profiles
 public.error_traces

I also tried "\du" but this one is working only in terminal but not from a ruby file.

query=ActiveRecord::Base.connection.execute("\du;")

ActiveRecord::StatementInvalid: PGError: ERROR: syntax error at or near "du" LINE 1: du;

Thanks, Rafiu

Rafiu
  • 4,700
  • 6
  • 25
  • 27
  • The backslash-commands (like `\du` ) are not part of SQL but are terminal-monitor commands for the psql commandline front-end. For your original question: I don't know the answer. Just try to insert or update, and handle the error. – wildplasser Feb 14 '12 at 10:32
  • 1
    I don't think there is a "read-only" database concept in PostgreSQL. Additionally: *But it is listing me something which I am not expecting*. So you should show us what you get **and** what you are expecting. –  Feb 14 '12 at 10:54
  • Hey thanks for your response. I am getting the below output when I trigger the query – Rafiu Feb 14 '12 at 12:16
  • And what **would** you expect? The output is all tables where the user `postgres` has the `select` privilege. As the `postgres` user is usually the "super user" this output is not really surprising. –  Feb 14 '12 at 12:46

2 Answers2

7

If you have a multi-node instance cluster, and you have the hot standby configuration. The output of SELECT pg_is_in_recovery() can tell you if the cluster is in the read-only mode.

Vishrant
  • 15,456
  • 11
  • 71
  • 120
4

You probably want something of the has_*_privilege() family function for relevant tables and relevant privileges. See here. Other than that I'm not sure if postgres has a concept of read-only mode.

Well, there's also show transaction_read_only inside a read-only transaction, but that doesn't seem to be like what you're asking for. And I don't think that transaction being readonly affects privileges of the user.

I'm not sure what you expect from your query, but if you want something boolean, as in whether you have access anywhere, you can use count(*)!=0 (and, probably, not select).

Michael Krelin - hacker
  • 138,757
  • 24
  • 193
  • 173
  • My DBA tells me that PostgreSQL does have a read-only mode. In my organization, our PostgreSQL clusters are configured with an A server (r/w) and a read-only B server that's in "[hot standby](https://www.postgresql.org/docs/10/static/hot-standby.html)" mode. Changes made to A replicate to B. The B server accepts connections, but gives an error if you try to make a change. The docs say "All such connections are strictly read-only; not even temporary tables may be written." To determine if you accidentally connected to the B server, our DBA recommends `show transaction_read_only`. – David Costanzo Jun 25 '18 at 15:10
  • @DavidCostanzo, I *think* your DBA is right, though I'm a bit behind with postgres' replication and stuff, I also *think*, though that my answer was pretty correct for the valentine's day of 2012 ;-) If I knew more I would update it, but I need to get myself up to date first, so let's hope people read these comments and do the homework themselves ;-) – Michael Krelin - hacker Jun 26 '18 at 08:21