Re: restrict global access to be readonly

On 2/14/15 3:14 PM, Robert Haas wrote:
> 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.

Throw an error in AssignTransactionId/GetNewTransactionId? I see 4 calls
to Get*TransactionId in logical replication, though arguably if we're
fixing that we should look at doing something special for Slony and the
likes.

Related to this, a lot of people have expressed desire for read only
tables. That would presumably be trickier to accomplish.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com