Re: [HACKERS] restrict global access to be readonly
On Tue, Feb 17, 2015 at 4:40 AM, happy times 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] restrict global access to be readonly
>?6?9Jim Nasby 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 >?6?9means the same thing as "all transactions are READ ONLY", that would be >?6?9useful for some purposes, and it would have the advantage of having a >?6?9visible relationship to a long-established feature with the same name. >?6?9If you want it to mean "no writes to disk at all", that's something >?6?9totally different, and possibly not all that useful (eg, do you really >?6?9want to make sorts fail if they'd spill to disk? How about hint bit >?6?9updates?). Jim's suggestion above would be somewhere in the middle, >?6?9as it would successfully block use of temp tables but not eg. VACUUM. >?6?9Another possibility that would be attractive for replication-related >?6?9use-cases would be "nothing that generates WAL thank you very much". >?6?9I'm inclined to think that we should agree on the desired semantics >?6?9before jumping to implementation. >?6?9regards, tom lane 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.
Re: [HACKERS] restrict global access to be readonly
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] restrict global access to be readonly
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] restrict global access to be readonly
On Feb15, 2015, at 10:13 , David G Johnston 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 FORCE TO ALTER ROLE [ IN DATABASE ] FORCE TO 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] restrict global access to be readonly
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] restrict global access to be readonly
Jim Nasby 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 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
Re: [HACKERS] restrict global access to be readonly
On Fri, Feb 13, 2015 at 3:32 AM, happy times 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] restrict global access to be readonly
Hi PG Developers, 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)? Sincerely, Guangzhou
