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
