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