Message-ID: <54DFDA26.9070502@BlueTreble.com>
Date: Sat, 14 Feb 2015 17:28:38 -0600
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:31.0) Gecko/20100101 Thunderbird/31.4.0
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>, happy times <guangzhouzhang@qq.com>
CC: pgsql-hackers <pgsql-hackers@postgresql.org>, funnyxj.fxj
 <funnyxj.fxj@alibaba-inc.com>, dingqi.lxb <dingqi.lxb@taobao.com>
Subject: Re: restrict global access to be readonly
References: <tencent_52390E301F51636C42CD9500@qq.com>
 <CA+TgmoYDbxFaHt=2dVOZ-Z4FmfTXRhrZjV0usOf+pafgPgTUHQ@mail.gmail.com>
In-Reply-To: 
 <CA+TgmoYDbxFaHt=2dVOZ-Z4FmfTXRhrZjV0usOf+pafgPgTUHQ@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 8bit
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 14 Feb 2015 23:28:49.8651 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR11MB0347
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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

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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers