Re: [HACKERS] Superowners

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