Обсуждение: [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 Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Simon Riggs <simon@2ndquadrant.com> 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

On 24 January 2017 at 13:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndquadrant.com> 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 Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Simon,

* Simon Riggs (simon@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
theyare 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
OWNERwould have the same requirments that ALTER TABLE ... OWNER TO requires today and we would re-check those rights at
everyobject creation and error if we are unable to set the ownership requested (alternatively, we could allow it and
justthrow a warning, similar to what GRANT does when you try to GRANT something and GRANT doesn't end up actually doing
anything).


* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Simon Riggs <simon@2ndquadrant.com> 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

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

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 <database>;" (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.banck@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

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 <database>;" (i.e. empty the database, but don't
delete it) make sense/be useful for that maybe?

On 26 January 2017 at 17:37, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> 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 Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

* Peter Eisentraut (peter.eisentraut@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

On 1/29/17 4:44 PM, Stephen Frost wrote:
> * Peter Eisentraut (peter.eisentraut@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)

Jim,

* Jim Nasby (Jim.Nasby@BlueTreble.com) wrote:
> On 1/29/17 4:44 PM, Stephen Frost wrote:
> >* Peter Eisentraut (peter.eisentraut@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

On 27 January 2017 at 22:48, Peter Eisentraut
<peter.eisentraut@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.

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 Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Simon,

* Simon Riggs (simon@2ndquadrant.com) wrote:
> On 27 January 2017 at 22:48, Peter Eisentraut
> <peter.eisentraut@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.
>
> 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-superprivileges on the table.  This would hopefully minimize the impact of having these additional checks, at least
onexisting 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 to
security releases.

Thanks!

Stephen

On 30 January 2017 at 14:43, Stephen Frost <sfrost@snowman.net> 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 Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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 <david(at)fetter(dot)org> 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

Simon,

* Simon Riggs (simon@2ndquadrant.com) wrote:
> On 30 January 2017 at 14:43, Stephen Frost <sfrost@snowman.net> 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

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

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

Simon Riggs <simon@2ndquadrant.com> 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

On 30 January 2017 at 16:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndquadrant.com> 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 Riggs                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

On 30 January 2017 at 16:34, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> 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 Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, Jan 30, 2017 at 5:33 AM, Simon Riggs <simon@2ndquadrant.com> 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