Re: [HACKERS] Superowners
On Mon, Jan 30, 2017 at 5:33 AM, Simon Riggs wrote: > I would call these "super privileges". > > Peter suggests that we have a much more flexible structure for > super-privileges. > > In Peter's model, Tom's suggestion woud be to grant all of these > automatically to database owners. > GRANT ALL ON ALL TABLES TO $user > GRANT ALL ON ALL SEQUENCES TO $user > GRANT ALL ON ALL FUNCTIONS TO $user > > Either of them would be good for me, as long as we implement the rule > as Tom suggests that this would never apply to objects owned by a > superuser. I like Peter's model better, or more precisely Stephen's suggestion of doing this via some default roles. Tom's model breaks backward compatibility in a security-sensitive way, and it doesn't generalize to things like wanting a user who can read everything but who has no elevated write privileges. The idea of having predefined roles called pg_read_anything, pg_write_anything, etc. seems quite elegant and very powerful, and nobody's existing permissions structure has to change unless they so desire. -- 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] Superowners
On 30 January 2017 at 16:34, Peter Eisentraut wrote: > On 1/30/17 9:04 AM, Simon Riggs wrote: >> all I want in this release is >> super-ownership. > > What exactly is super-ownership, and what problems does it solve? The problem is that there is no easy way for a DBA to have privs on multiple sets of objects, so there is a request for superuser in many cases. Superuser is too strong for most situations, so we are stuck. We need some middle ground where a single user can manage many "normal application objects" (tables, views, sequences, matviews, functions, indexes, triggers) without problem, while not compromising other areas that require higher security. Probably more than 50% of PostgreSQL installs now use services that block superuser accounts, so the majority of PostgreSQL users are affected by these problems. The permissions desirable for logical replication are a good example of this, but not in any sense the only issue. My hope is that we release v10 with a permissions model that allows logical replication to be realistically usable when superuser is not available. This is not a new requirement, but the privilege aspect of the logical replication has been pushed back. While thinking about other problems of access control I've rethought this so I now see the wider problem and would like to solve that rather than just focus on the needs of logical replication. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Superowners
On 30 January 2017 at 16:43, Tom Lane wrote: > Simon Riggs writes: >> Agreed. Let me reiterate: all I want in this release is >> super-ownership. > > While I'm not entirely convinced whether super-ownership is a good idea > or not, I am pretty sure that rushing to get it into v10 is a bad idea. > This is a rather fundamental change in our permissions model and it > might turn out to have undesirable consequences. Agreed. My view is that the current mechanism almost forces people to use superusers for many things and that is definitely undesirable. > Or even more directly: any patch for this would necessarily be landing > in the last v10 commitfest. We have a project policy against major > changes showing up for the first time in the last fest of a cycle, > for good reasons. I understand. > Let's take our time and get it right. So we are able to see what is proposed, I attach a patch. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services db_owner_has_obj_privs.v1.patch Description: Binary data -- 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] Superowners
Simon Riggs writes: > Agreed. Let me reiterate: all I want in this release is > super-ownership. While I'm not entirely convinced whether super-ownership is a good idea or not, I am pretty sure that rushing to get it into v10 is a bad idea. This is a rather fundamental change in our permissions model and it might turn out to have undesirable consequences. Or even more directly: any patch for this would necessarily be landing in the last v10 commitfest. We have a project policy against major changes showing up for the first time in the last fest of a cycle, for good reasons. Let's take our time and get it right. 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] Superowners
On 1/30/17 9:04 AM, Simon Riggs wrote: > all I want in this release is > super-ownership. What exactly is super-ownership, and what problems does it solve? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Superowners
Simon, * Simon Riggs (si...@2ndquadrant.com) wrote: > On 30 January 2017 at 14:43, Stephen Frost wrote: > > > We need to make sure that we're actually talking about the same things > > here, because we've now shifted from ownership-like privileges to those > > privielges which can be GRANT'd, and the two are far from the same. > > Agreed. Let me reiterate: all I want in this release is > super-ownership. Other stuff is way more complex and if it happens at > all it can happen in later release. Alright. > > Would these super-privileges be seen when doing a \dp on a table? > > No, keep it simple, treat like ownership Well, we do list ownership in \d. > > Are > > we going to record the super-privileges into every object which exists > > when the command is called and then also into new objects when they're > > created? > > No. Super privs are implicit and do not need to be added to each new object. Alright, that then involves addiitonal checks happening run-time. I'm not against that, simply pointing it out. > > Or are we going to have an additional catalog lookup for every > > privilege check? > > No, my vision is to replace the superuser() test with a > has_super_privs() check, so the cost is identical to existing > overhead. It's unclear to me why you're suggesting the cost would be identical. We check superuser() first and any change to that check to do something additional (like, look up who the database owner is) would mean an increase in overhead. I'm not saying that such overhead would be an issue, to be clear, but it would be there. That would also be a change from how ownership is done today- in particular, superusers simply *bypass* checks, owners do *not*. Users are able to REVOKE the default rights that the owner has. Of course, the owner can GRANT those back, but it's still not the same as superuser. How is role membership going to be handled with this change also? And what about the information_schema? Surely we will need to report the privileges of these "super-owners" through those views, just as we do for owners today. > I envisage a very small patch. I'll write it so we can see, not in > time for Brussels though. While that would certainly be nice, it doesn't seem very likely to work out that way, unfortunately. > >> * Some super-privileges should exist by default. If super-privileges > >> exist only when granted by superusers and we have no access to > >> superusers then they never will exist, so they will be mostly useless > >> in many cases. > > > > We should at least consider if we really want upgrades to automatically > > set up these super-privileges or not. > > This wouldn't be needed if the database owner has the super-privs > automatically. If the database owner is now a 'super owner' automatically then we certainly do need to consider that we're changing the privilege system for everyone as soon as they start using PG10. I'm not saying that it's necessairly an issue, to be clear, and I believe Tom's argument is a good one, but I do think we need to be sure we consider such a change carefully. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Superowners
On 1/29/17 7:44 PM, Stephen Frost wrote: > I would think we'd either do this with a default role or a role > attribute. That's not how I think about it. I think this would be a separate aclitem[] stored somewhere. The pg_xxx_aclcheck() functions could consult that implicitly. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Superowners
On Fri, Jan 27, 2017 at 05:48:46PM -0500, Peter Eisentraut wrote: > On 1/26/17 1:25 PM, Simon Riggs wrote: > > That should include the ability to dump all objects, yet without > > any security details. And it should allow someone to setup logical > > replication easily, including both trigger based and new logical > > replication. And GRANT ON ALL should work. > > This basically sounds like a GRANT $privilege ON ALL $objecttype TO > $user. So you could have a user that can read everything, for > example. This would still be tremendously useful. So would a cogent, user-facing explanation of DCL in one obvious spot, which I guess I've signed up to do now that I'm complaining about its lack. Best, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Superowners
On 30 January 2017 at 14:43, Stephen Frost wrote: > We need to make sure that we're actually talking about the same things > here, because we've now shifted from ownership-like privileges to those > privielges which can be GRANT'd, and the two are far from the same. Agreed. Let me reiterate: all I want in this release is super-ownership. Other stuff is way more complex and if it happens at all it can happen in later release. > Would these super-privileges be seen when doing a \dp on a table? No, keep it simple, treat like ownership > Are > we going to record the super-privileges into every object which exists > when the command is called and then also into new objects when they're > created? No. Super privs are implicit and do not need to be added to each new object. > Or are we going to have an additional catalog lookup for every > privilege check? No, my vision is to replace the superuser() test with a has_super_privs() check, so the cost is identical to existing overhead. I envisage a very small patch. I'll write it so we can see, not in time for Brussels though. >> * Some super-privileges should exist by default. If super-privileges >> exist only when granted by superusers and we have no access to >> superusers then they never will exist, so they will be mostly useless >> in many cases. > > We should at least consider if we really want upgrades to automatically > set up these super-privileges or not. This wouldn't be needed if the database owner has the super-privs automatically. >> * This model is needed now, or at least change in this area will be >> easier because of PG10.0 > > This doesn't strike me as a terribly good argument. I'm sure we could > do this for PG11 too. > >> Neither of those things make me think deferring this and allowing a >> student to work on it in the future would be a useful outcome. The >> task looks to be a small amount of dev, a medium amount of design and >> a lot of checking and documentation. > > For my 2c, at least, that makes it sound like a very good project for a > student to work on. That said, I'm not against doing it now, if someone > wants to spend the time to do so. > >> So my proposal is to implement what Tom suggests now, which I will now >> describe as automatic super-privileges for database owning roles. And >> we can implement a more flexible scheme for explicit GRANTs to other >> roles in later releases. > > I don't believe doing something quick now will lead us to a place where > we will easily be able to make it more flexible later. I'm much more > concerned that we'll end up painting ourselves into a corner that's > difficult to get out of as privileges are, generally speaking, not > something we can really whack around from release to release. They're > extremely user-visible and any issues will almost certainly lead to > security releases. Which is why we should keep it simple, which is why/what I originally proposed. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Superowners
Simon, * Simon Riggs (si...@2ndquadrant.com) wrote: > On 27 January 2017 at 22:48, Peter Eisentraut > wrote: > > On 1/26/17 1:25 PM, Simon Riggs wrote: > >> That should include the ability to dump all objects, yet without any > >> security details. And it should allow someone to setup logical > >> replication easily, including both trigger based and new logical > >> replication. And GRANT ON ALL should work. > > > > This basically sounds like a GRANT $privilege ON ALL $objecttype TO > > $user. So you could have a user that can read everything, for example. > > > > This kind of thing has been asked for many times, but that quieted down > > when the default privileges feature appeared. I think it would still be > > useful. > > I would call these "super privileges". > > Peter suggests that we have a much more flexible structure for > super-privileges. > > In Peter's model, Tom's suggestion woud be to grant all of these > automatically to database owners. > GRANT ALL ON ALL TABLES TO $user > GRANT ALL ON ALL SEQUENCES TO $user > GRANT ALL ON ALL FUNCTIONS TO $user We need to make sure that we're actually talking about the same things here, because we've now shifted from ownership-like privileges to those privielges which can be GRANT'd, and the two are far from the same. The above model of GRANT ALL ON ALL TABLES TO $user; would *not*, for example, GRANT the right to drop the object, or to ALTER it, as those are not currently GRANT'able privileges. > Either of them would be good for me, as long as we implement the rule > as Tom suggests that this would never apply to objects owned by a > superuser. Things are not quite so simple as this- part of the point of a read-only role would be to allow that role to perform pg_dump-based backups. If the above GRANT system does not apply to catalog tables, then trying to create such a user by using: GRANT SELECT ON ALL TABLES TO readonly; wouldn't cover pg_authid and pg_dumpall would fail. Of course, pg_authid is a shared catalog and we're talking about per-database things here, for the most part, so perhaps that's not quite the same, but what about extensions which are installed by a superuser which include config tables that need to be dumped out? > After thinking about this, the things I care about are > > * That super-privileges can be executed efficiently at run-time. If > they cause major overheads, especially in cases where they have not > been granted then we would have a big problem. This seems to play into a question regarding the implementation which then leads into user expectations a bit, I believe. Would these super-privileges be seen when doing a \dp on a table? Are we going to record the super-privileges into every object which exists when the command is called and then also into new objects when they're created? Or are we going to have an additional catalog lookup for every privilege check? At first blush, I tend to think the answers are: - They exist as an independent catalog - Yes, super-privs are shown in \dp, but through psql doing the necessary lookups/joins to combine the ACL info. - We check normal privileges first and fall back to looking for super-privileges if the caller doesn't have any non-super privileges on the table. This would hopefully minimize the impact of having these additional checks, at least on existing systems. > * Some super-privileges should exist by default. If super-privileges > exist only when granted by superusers and we have no access to > superusers then they never will exist, so they will be mostly useless > in many cases. We should at least consider if we really want upgrades to automatically set up these super-privileges or not. > * This model is needed now, or at least change in this area will be > easier because of PG10.0 This doesn't strike me as a terribly good argument. I'm sure we could do this for PG11 too. > Neither of those things make me think deferring this and allowing a > student to work on it in the future would be a useful outcome. The > task looks to be a small amount of dev, a medium amount of design and > a lot of checking and documentation. For my 2c, at least, that makes it sound like a very good project for a student to work on. That said, I'm not against doing it now, if someone wants to spend the time to do so. > So my proposal is to implement what Tom suggests now, which I will now > describe as automatic super-privileges for database owning roles. And > we can implement a more flexible scheme for explicit GRANTs to other > roles in later releases. I don't believe doing something quick now will lead us to a place where we will easily be able to make it more flexible later. I'm much more concerned that we'll end up painting ourselves into a corner that's difficult to get out of as privileges are, generally speaking, not something we can really whack around from release to release. They're extremely user-visible and any issues will almost certainly lead
Re: [HACKERS] Superowners
On 27 January 2017 at 22:48, Peter Eisentraut wrote: > On 1/26/17 1:25 PM, Simon Riggs wrote: >> That should include the ability to dump all objects, yet without any >> security details. And it should allow someone to setup logical >> replication easily, including both trigger based and new logical >> replication. And GRANT ON ALL should work. > > This basically sounds like a GRANT $privilege ON ALL $objecttype TO > $user. So you could have a user that can read everything, for example. > > This kind of thing has been asked for many times, but that quieted down > when the default privileges feature appeared. I think it would still be > useful. I would call these "super privileges". Peter suggests that we have a much more flexible structure for super-privileges. In Peter's model, Tom's suggestion woud be to grant all of these automatically to database owners. GRANT ALL ON ALL TABLES TO $user GRANT ALL ON ALL SEQUENCES TO $user GRANT ALL ON ALL FUNCTIONS TO $user Either of them would be good for me, as long as we implement the rule as Tom suggests that this would never apply to objects owned by a superuser. After thinking about this, the things I care about are * That super-privileges can be executed efficiently at run-time. If they cause major overheads, especially in cases where they have not been granted then we would have a big problem. * Some super-privileges should exist by default. If super-privileges exist only when granted by superusers and we have no access to superusers then they never will exist, so they will be mostly useless in many cases. * This model is needed now, or at least change in this area will be easier because of PG10.0 Neither of those things make me think deferring this and allowing a student to work on it in the future would be a useful outcome. The task looks to be a small amount of dev, a medium amount of design and a lot of checking and documentation. So my proposal is to implement what Tom suggests now, which I will now describe as automatic super-privileges for database owning roles. And we can implement a more flexible scheme for explicit GRANTs to other roles in later releases. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Superowners
Jim, * Jim Nasby (jim.na...@bluetreble.com) wrote: > On 1/29/17 4:44 PM, Stephen Frost wrote: > >* Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote: > >>On 1/26/17 1:25 PM, Simon Riggs wrote: > >>>That should include the ability to dump all objects, yet without any > >>>security details. And it should allow someone to setup logical > >>>replication easily, including both trigger based and new logical > >>>replication. And GRANT ON ALL should work. > >>This basically sounds like a GRANT $privilege ON ALL $objecttype TO > >>$user. So you could have a user that can read everything, for example. > >> > >>This kind of thing has been asked for many times, but that quieted down > >>when the default privileges feature appeared. I think it would still be > >>useful. > >Agreed. I would think we'd either do this with a default role or a role > >attribute. > > Someone was asking for that on Slack the other day, because their > customer wanted it. Default privs would not fit the bill: they > wanted to grant specific roles the ability to read everything in the > database (or maybe cluster; I don't think the conversation got into > that level of detail). ... eh? If we create a default role called "pg_read_only" which admins can grant to whomever they wish, how does that not "fit the bill"? For my 2c, at least, evaluating the various requests and coming up with some set of default roles and then implementing them would be a good GSoC project.. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Superowners
On 1/29/17 4:44 PM, Stephen Frost wrote: * Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote: On 1/26/17 1:25 PM, Simon Riggs wrote: That should include the ability to dump all objects, yet without any security details. And it should allow someone to setup logical replication easily, including both trigger based and new logical replication. And GRANT ON ALL should work. This basically sounds like a GRANT $privilege ON ALL $objecttype TO $user. So you could have a user that can read everything, for example. This kind of thing has been asked for many times, but that quieted down when the default privileges feature appeared. I think it would still be useful. Agreed. I would think we'd either do this with a default role or a role attribute. Someone was asking for that on Slack the other day, because their customer wanted it. Default privs would not fit the bill: they wanted to grant specific roles the ability to read everything in the database (or maybe cluster; I don't think the conversation got into that level of detail). -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) -- 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] Superowners
* Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote: > On 1/26/17 1:25 PM, Simon Riggs wrote: > > That should include the ability to dump all objects, yet without any > > security details. And it should allow someone to setup logical > > replication easily, including both trigger based and new logical > > replication. And GRANT ON ALL should work. > > This basically sounds like a GRANT $privilege ON ALL $objecttype TO > $user. So you could have a user that can read everything, for example. > > This kind of thing has been asked for many times, but that quieted down > when the default privileges feature appeared. I think it would still be > useful. Agreed. I would think we'd either do this with a default role or a role attribute. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Superowners
On 1/26/17 1:25 PM, Simon Riggs wrote: > That should include the ability to dump all objects, yet without any > security details. And it should allow someone to setup logical > replication easily, including both trigger based and new logical > replication. And GRANT ON ALL should work. This basically sounds like a GRANT $privilege ON ALL $objecttype TO $user. So you could have a user that can read everything, for example. This kind of thing has been asked for many times, but that quieted down when the default privileges feature appeared. I think it would still be useful. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Superowners
On 26 January 2017 at 17:37, Peter Eisentraut wrote: > On 1/24/17 8:19 AM, Tom Lane wrote: >> What about just saying that the database owner has those privileges? >> After all, the ultimate privilege of an owner is to drop the object >> (and then remake it as she pleases), and the DB owner has that option >> w.r.t. the whole database. So I'm not sure we need to invent a new >> concept. > > A database owner does not necessarily have the permission to create a > new database. So the concept I was looking for is already there: on pg_database the database owner is referred to as the DBA. I'd like to be able to give permision to someone to control all user-owned objects in the database, yet not be allowed to do anything that touches filesystem or creates potentially unsafe functions. This allows a separation of duty between people that run a service and people that use it. That should include the ability to dump all objects, yet without any security details. And it should allow someone to setup logical replication easily, including both trigger based and new logical replication. And GRANT ON ALL should work. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Superowners
On Thursday, January 26, 2017, Michael Banck wrote: > On Thu, Jan 26, 2017 at 12:37:44PM -0500, Peter Eisentraut wrote: > > On 1/24/17 8:19 AM, Tom Lane wrote: > > > What about just saying that the database owner has those privileges? > > > After all, the ultimate privilege of an owner is to drop the object > > > (and then remake it as she pleases), and the DB owner has that option > > > w.r.t. the whole database. So I'm not sure we need to invent a new > > > concept. > > > > A database owner does not necessarily have the permission to create a > > new database. > > Right. > > Would a "TRUNCATE ;" (i.e. empty the database, but don't > delete it) make sense/be useful for that maybe? > > Conceptually we might already have that. https://www.postgresql.org/docs/current/static/sql-drop-owned.html Drop Owned ... Though I suspect there might be caveats since indications are it wasn't designed with this particular use case in mind, David J.
Re: [HACKERS] Superowners
On Thu, Jan 26, 2017 at 12:37:44PM -0500, Peter Eisentraut wrote: > On 1/24/17 8:19 AM, Tom Lane wrote: > > What about just saying that the database owner has those privileges? > > After all, the ultimate privilege of an owner is to drop the object > > (and then remake it as she pleases), and the DB owner has that option > > w.r.t. the whole database. So I'm not sure we need to invent a new > > concept. > > A database owner does not necessarily have the permission to create a > new database. Right. Would a "TRUNCATE ;" (i.e. empty the database, but don't delete it) make sense/be useful for that maybe? Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.ba...@credativ.de credativ GmbH, HRB Mönchengladbach 12080 USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer -- 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] Superowners
On 1/24/17 8:19 AM, Tom Lane wrote: > What about just saying that the database owner has those privileges? > After all, the ultimate privilege of an owner is to drop the object > (and then remake it as she pleases), and the DB owner has that option > w.r.t. the whole database. So I'm not sure we need to invent a new > concept. A database owner does not necessarily have the permission to create a new database. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Superowners
Simon, * Simon Riggs (si...@2ndquadrant.com) wrote: > So I was thinking about various annoying admin/security issues > recently, so I came up with this: a new type of user called a > “superowner”. It’s somewhere between a superuser and a normal user. I like the general idea, but I'm not really thrilled with the name (too easy to confuse with superuser and people might, reasonably, assume 'superowner' can do more than 'superuser'), nor the proposed grammer- why have only one superowner? How does role membership play into this? > Superowner would own all objects defined by users, so it would do > useful things in contexts where superuser is not available. I also don't really like the idea of tossing out ownership-level rights for regular users. I would *much* rather have two independent concepts: - role attribute which makes a user be treated as if they own all normal-user-owned database objects (or perhaps as if they are a member of every role, which people have asked me for multiple times). - Managmenet of object ownership; as in, have a way to set a DEFAULT OWNER, or perhaps a FORCED OWNER. Setting DEFAULT OWNER would have the same requirments that ALTER TABLE ... OWNER TO requires today and we would re-check those rights at every object creation and error if we are unable to set the ownership requested (alternatively, we could allow it and just throw a warning, similar to what GRANT does when you try to GRANT something and GRANT doesn't end up actually doing anything). * Tom Lane (t...@sss.pgh.pa.us) wrote: > Simon Riggs writes: > > So I was thinking about various annoying admin/security issues > > recently, so I came up with this: a new type of user called a > > “superowner”. It’s somewhere between a superuser and a normal user. > > Superowner would own all objects defined by users, so it would do > > useful things in contexts where superuser is not available. > > What about just saying that the database owner has those privileges? I'm not thrilled with suddenly changing how a database owner works, though I'll admit that *new* users tend to assume things work more in this way. Thinking through it a bit more though, I can see why Simon is proposing that it be database-specific. That would seem to imply that what's needed is a per-database set of role attributes, ala pg_db_authid (not a great name, but the name can come later). Generally speaking, I like that idea as I can see other possible use-cases, in particular the question continues to come up about having a read-only user and being able to set that for a given role in a given database would be great (though I think it would also be nice to have one cluster-wide, but that goes for Simon's proposed feature here too, imv). > After all, the ultimate privilege of an owner is to drop the object > (and then remake it as she pleases), and the DB owner has that option > w.r.t. the whole database. So I'm not sure we need to invent a new > concept. I can see some overlap with that. > With or without it being a separate property, there's a point I think > you missed: this should only extend to objects owned by normal users, > not by superusers. Otherwise there are all sorts of security issues. Agreed. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Superowners
On 24 January 2017 at 13:19, Tom Lane wrote: > Simon Riggs writes: >> So I was thinking about various annoying admin/security issues >> recently, so I came up with this: a new type of user called a >> “superowner”. It’s somewhere between a superuser and a normal user. >> Superowner would own all objects defined by users, so it would do >> useful things in contexts where superuser is not available. > > What about just saying that the database owner has those privileges? > After all, the ultimate privilege of an owner is to drop the object > (and then remake it as she pleases), and the DB owner has that option > w.r.t. the whole database. So I'm not sure we need to invent a new > concept. Thinking about it, I've not seen dbowner != superuser in most cases, so that works for me. I guess I was expecting push back from people for backwards compatibility, but it is 10.0 > With or without it being a separate property, there's a point I think > you missed: this should only extend to objects owned by normal users, > not by superusers. Otherwise there are all sorts of security issues. Sure. That sounds like the right definition of what I was trying to specify. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Superowners
Simon Riggs writes: > So I was thinking about various annoying admin/security issues > recently, so I came up with this: a new type of user called a > “superowner”. It’s somewhere between a superuser and a normal user. > Superowner would own all objects defined by users, so it would do > useful things in contexts where superuser is not available. What about just saying that the database owner has those privileges? After all, the ultimate privilege of an owner is to drop the object (and then remake it as she pleases), and the DB owner has that option w.r.t. the whole database. So I'm not sure we need to invent a new concept. With or without it being a separate property, there's a point I think you missed: this should only extend to objects owned by normal users, not by superusers. Otherwise there are all sorts of security issues. 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
[HACKERS] Superowners
So I was thinking about various annoying admin/security issues recently, so I came up with this: a new type of user called a “superowner”. It’s somewhere between a superuser and a normal user. Superowner would own all objects defined by users, so it would do useful things in contexts where superuser is not available. It seems easiest to make this a property at database level. There is no default. ALTER DATABASE name SUPEROWNER TO { new_owner | CURRENT_USER | SESSION_USER } CREATE DATABASE name SUPEROWNER [=] new_owner Changes to various commands highlighted by ** **, in existing text from docs. Changes to GRANT - Roles having CREATEROLE privilege can grant or revoke membership in any role that is not a superuser **or superowner**. It should be noted that database superusers can access all objects regardless of object privilege settings. This is comparable to the rights of root in a Unix system. As with root, it's unwise to operate as a superuser except when absolutely necessary. **Superowners for a database can access any table, view, materialized view, index or sequence in that database regardless of object privilege settings. Superowners cannot create untrusted procedures, administer catalog tables or schemas or perform internal operations reserved only for superusers .** If a superuser **or superowner** chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. In particular, privileges granted via such a command will appear to have been granted by the object owner. Changes to ALTER ROLE - Superusers can change anyone's session defaults. Roles having CREATEROLE privilege can change defaults for non-superuser roles. **Superowners of a database can change defaults for that role on their database only.** Ordinary roles can only set defaults for themselves. Certain configuration variables cannot be set this way, or can only be set if a superuser issues the command. Only superusers can change a setting for all roles in all databases. Any issues, additions or changes? -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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