Re: restrict global access to be readonly

On Fri, Feb 13, 2015 at 3:32 AM, happy times <guangzhouzhang(at)qq(dot)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