agora inbox for [email protected]  

help / color / mirror / Atom feed

From: Jim Nasby <[email protected]>
To: Robert Haas <[email protected]>
To: happy times <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: funnyxj.fxj <[email protected]>
Cc: dingqi.lxb <[email protected]>
Subject: Re: restrict global access to be readonly
Date: Sat, 14 Feb 2015 17:28:38 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+TgmoYDbxFaHt=2dVOZ-Z4FmfTXRhrZjV0usOf+pafgPgTUHQ@mail.gmail.com>
References: <[email protected]>
	<CA+TgmoYDbxFaHt=2dVOZ-Z4FmfTXRhrZjV0usOf+pafgPgTUHQ@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-hackers>

On 2/14/15 3:14 PM, Robert Haas wrote:
> On Fri, Feb 13, 2015 at 3:32 AM, happy times <[email protected]> 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


-- 
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: restrict global access to be readonly
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox