Hi PG Developers,
I didn’t find any convenient way to restrict access to PostgreSQL databases to be read-only for all users. I need it in following scenarios:
A) Planned switch-over from master to slave. We want to minimize impact within the planned switch-overs. So during the process we switch from master to slave, we would like to allow read-only transactions to be run on the original master until the switch-over complete and the new master starts taking user connections (we do the switch with virtual IP mechanism). I didn’t find way to do this on the database server side. Sure, we can utilize the runtime parameter default_transaction_read_only, however, it does not restrict user from changing transaction attribute to non-readonly mode, so is not safe.
B) Blocking writing access when storage constraint is reached. We have massive PostgreSQL instances which are sold to external users with specific storage constraints and prices. When storage constraint for a specific instance is reached, we would rather change the instance to be readonly (then notify user to cleanup data or buy more storage) than shutdown the instance. Our current solution is putting a recovery.conf file to the instance (killing all existing connections) and restart the instance to get it into recovery mode (which is readonly), which is not pretty.
C) Blocking writing access when an instance has expired. Similar with B), when the user’s contract with us expires about his/her instance, we want to firstly block the write access rather than shutdown the instance completely.
Having that said, it would be very nice if there is a command like “SET GLOBAL_ACCESS TO READONLY | READWRITE” which does the job for the whole instance. I guess there could be others who want this feature too.
So, have anyone considered or discussed about adding such a command? Is there anyone working on it (I would like to work on it if no)?
Sincerely,
Guangzhou
