On Fri, Feb 13, 2015 at 3:32 AM, happy times <guangzhouzh...@qq.com> wrote: > 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)?
I think this would be a useful feature and have thought about it myself. I suggest that it be spelled like this: ALTER SYSTEM [ READ ONLY | READ WRITE ]; Although I like the idea, it's not clear to me how to implement it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
