Обсуждение: restrict global access to be readonly

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 2/14/15 3:14 PM, Robert Haas wrote:
>> Although I like the idea, it's not clear to me how to implement it.

> Throw an error in AssignTransactionId/GetNewTransactionId?

A whole lot depends on what you choose to mean by "read only".  If it
means the same thing as "all transactions are READ ONLY", that would be
useful for some purposes, and it would have the advantage of having a
visible relationship to a long-established feature with the same name.
If you want it to mean "no writes to disk at all", that's something
totally different, and possibly not all that useful (eg, do you really
want to make sorts fail if they'd spill to disk? How about hint bit
updates?).  Jim's suggestion above would be somewhere in the middle,
as it would successfully block use of temp tables but not eg. VACUUM.
Another possibility that would be attractive for replication-related
use-cases would be "nothing that generates WAL thank you very much".

I'm inclined to think that we should agree on the desired semantics
before jumping to implementation.
        regards, tom lane

happy times wrote
> 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. 

ISTM that implementing a means to make this setting only super-user
changeable would be a quick(er) solution to the problem - the decision as to
what to disallow is already decided.

It seems like it would have to be a separate GUC but it would require any
new SQL but would simply leverage the existing settings system to setup
database-user values that only a super-user can change.

David J.




--
View this message in context:
http://postgresql.nabble.com/restrict-global-access-to-be-readonly-tp5837818p5838021.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Feb15, 2015, at 10:13 , David G Johnston <david.g.johnston@gmail.com> wrote:
> happy times wrote
>> 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.
>
> ISTM that implementing a means to make this setting only super-user
> changeable would be a quick(er) solution to the problem - the decision as to
> what to disallow is already decided.
>
> It seems like it would have to be a separate GUC but it would require any
> new SQL but would simply leverage the existing settings system to setup
> database-user values that only a super-user can change.

I've wished for a way prevent regular users for changing specific settings
in the past. Maybe we could have
 ALTER DATABASE <database> FORCE <parameter> TO <value> ALTER ROLE <role> [ IN DATABASE <db> ] FORCE <parameter> TO
<value>

In postgresql.conf, we could use a syntax like
 parameter =!= value

to indicate that the parameter value can only be changed by super-users.

We' have to figure out what happens if a database- or role-specific
FORCEd setting attempts to override a value already FORCEd in postgresql.conf.

Ideally, we'd allow database- or role-specific settings created by super-users
to override previously FORCEd values, but that would require us to store the
role that creates such settings in pg_db_role_setting. For SET clauses attached
to functions, we'd complain if they attempt to change a FORCEd value, unless
they are called by a super-user, or are marked SECURITY DEFINER and owned by
a super-user.

best regards,
Florian Pflug

On 2015-02-14 17:28:38 -0600, Jim Nasby wrote:
> 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.

I don't think there are any xid assignments in the logical decoding
code. There's a couple error checks erroring out if an xid has been
assigned, but those use GetTopTransactionIdIfAny(), i.e. don't assign an
id.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

On 2/14/15 7:24 PM, Tom Lane wrote:
> Another possibility that would be attractive for replication-related
> use-cases would be "nothing that generates WAL thank you very much".

This would be useful, as it essentially simulates a hot standby.

<br />>‍Jim Nasby writes:<br />> On 2/14/15 3:14 PM, Robert Haas wrote:<br
/>>> Although I like the idea, it's not clear to me how to implement it.<br/><br
/>> Throw an error in AssignTransactionId/GetNewTransactionId?<br/><br
/>>A whole lot depends on what you choose to mean by "read only".  If it<br
/>>‍means the same thing as "all transactions are READ ONLY", that would be<br
/>>‍useful for some purposes, and it would have the advantage of having a<br
/>>‍visible relationship to a long-established feature with the same name.<br
/>>‍If you want it to mean "no writes to disk at all", that's something<br
/>>‍totally different, and possibly not all that useful (eg, do you really<br
/>>‍want to make sorts fail if they'd spill to disk? How about hint bit<br
/>>‍updates?).  Jim's suggestion above would be somewhere in the middle,<br
/>>‍as it would successfully block use of temp tables but not eg. VACUUM.<br
/>>‍Another possibility that would be attractive for replication-related<br
/>>‍use-cases would be "nothing that generates WAL thank you very much".<br/><br
/>>‍I'm inclined to think that we should agree on the desired semantics<br
/>>‍before jumping to implementation.<br/><br />>‍regards, tom lane<br /><br
/> The first choice Tom pointed makes sense to me: adding this as eqivalent to setting all subsequent transactions as read only. It is useful enough in the scenarios where disk limit for the instance is reached, we want to block all write access(this limit is typically soft limit and vacuum logs or sort spills could be permitted).<br
/><br
/>I previously thought of the choice of "not generating any WAL" semantics, but now doubt if thats practically useful. We are forced to restart the old master with recovery mode during switching roles of master-slave, which would make it into the state of not generating any WAL.<br
/><br
/>And for logical replication, seems setting transactions as readonly could do the job to avoid logs to be shipped to slave.<br
/><br
/>One other thing to consider is the user to be blocked. I expect this command to prevent write access even for the superusers, since there may be other internal apps that connect as superuser and do writes, they are expected to be blocked too. And sometime we may use this command to avoid any unexpected write operation. <br
/><br
/>Last thing is when the command returns. I expected it to return immediately and not waiting for existing active transactions to finish. This is to avoid existing long running transactions to block it and let the user to decide whether to wait or kill existing transactions.

On Tue, Feb 17, 2015 at 4:40 AM, happy times <guangzhouzhang@qq.com> wrote:
>  The first choice Tom pointed makes sense to me: adding this as eqivalent to
> setting all subsequent transactions as read only. It is useful enough in the
> scenarios where disk limit for the instance is reached, we want to block all
> write access(this limit is typically soft limit and vacuum logs or sort
> spills could be permitted).
>
> I previously thought of the choice of "not generating any WAL" semantics,
> but now doubt if thats practically useful. We are forced to restart the old
> master with recovery mode during switching roles of master-slave, which
> would make it into the state of not generating any WAL.
>
> And for logical replication, seems setting transactions as readonly could do
> the job to avoid logs to be shipped to slave.
>
> One other thing to consider is the user to be blocked. I expect this command
> to prevent write access even for the superusers, since there may be other
> internal apps that connect as superuser and do writes, they are expected to
> be blocked too. And sometime we may use this command to avoid any unexpected
> write operation.
>
> Last thing is when the command returns. I expected it to return immediately
> and not waiting for existing active transactions to finish. This is to avoid
> existing long running transactions to block it and let the user to decide
> whether to wait or kill existing transactions.

The use cases I can think of are:

- Opening a possibly-damaged database strictly read-only so you can
inspect it without risking further damage; or forcing a damaged
database that is already up and running to go read-only to prevent
further damage.  In this case, you'd want to prohibit all writes to
the data files, even hint bit changes, but the use of temporary files
for sorts or hashes would be fine.

- Forcing a master into a read-only state in preparation for a
controlled switchover.  If you can completely stop WAL generation on
the master, replay all WAL generated on the master prior to the
switchover on the standby, and then switch over, you could make the
old master a slave of the new master.  I think the requirements here
are similar to the previous case.  I'm not 100% sure that we need to
prevent hint bits getting set in this case, but it probably wouldn't
hurt. Temp file writes are, again, OK.

- Taking a copy of the database for backup purposes.  Same thing again.

- Accessing a cluster stored on a read-only medium, like a CD or DVD.
In this case, even temporary file writes are no good.

- Your proposed use case of preventing the disk from being filled up
is a little different.  There's no real problem if the data files fill
up the disk; at a certain point, users will get errors, but forcing
the database read only is going to do that anyway (and sooner).
There's a big problem if the xlog partition fills up, though.  You
could want a few different things here depending on the details of
your use case, but preventing all WAL generation is one of them.

Based on the above, I'm inclined to think that making the system read
only should (1) prevent all WAL generation and (2) prevent all data
file modifications, but (3) still allow the use of temporary files.

--

It's also worth taking a look at what other systems support.  SQL
server support something like this feature using this (ugly) syntax:

ALTER DATABASE [whatever] SET  READ_ONLY WITH NO_WAIT

I'm not sure what the semantics are, exactly.

In Oracle, you can open a database read-only:

ALTER DATABASE whatever OPEN READ ONLY;

It's not clear to me whether you can use this to force an
already-read-write database back to read only mode.  Oracle also lets
you make a tablespace read-only if there are no open transactions,
running hot backups, etc anywhere in the system.  That syntax is just:

ALTER TABLESPACE whatever READ ONLY;

That forbids all future data file modifications.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company