Re: db_user_namespace a "temporary measure"

* Josh Berkus (josh@agliodbs.com) wrote:
> On 03/12/2014 11:25 AM, Stephen Frost wrote:
> > * Josh Berkus (josh@agliodbs.com) wrote:
> >> Local superusers (maybe this concept needs another name) would be able
> >> to do the following things in a *single* database:
> >>
> >> 1 change permissions for other users on that database and its objects
> >
> > What about "bypass" permissions, ala what superuser does today?  Or are
> > you saying we'd only need to allow this new kind of role to bypass the
> > checks in the GRANT/REVOKE system?
>
> More like what we have for the database owner role today.

eh?  Can you clarify what you think the database owner role can do today
that actually matches what you're asking for above, or even what I'm
suggesting?

> >> 2 load extensions from a predefined .so directory / list
> >
> > This would obviously have to be a curated list that avoids things like
> > 'adminpack'...
>
> It would need to be a list created by the global superuser.  By default,
> nothing would be on it.

Hmm, so this would be independent from what's installed on the system at
a file level.  That's an interesting idea.

> >> 4 create per-database users and change their settings
> >
> > Presumably just for the 'local' DB?
>
> Right.
>
> >> 5 change database settings (SET stuff)
> >
> > This can be done by the database-owner already, no?
>
> Oh, point.
>
> >> 6 NOT change their own user settings
> >
> > Don't think this is quite that simple (passwords?).
>
> Well, we already limit what things users can change about themselves;
> they can't promote themselves to superuser, for example.

Just saying that we need to be more specific on this point.

> >> 7 NOT change any global users
> >
> > What about role membership, wrt local vs. global roles?
>
> Allowing global users to join local ROLEs is its own can'o'worms that
> would merit an entire other thread.

What would the caveats here be then..?  Local roles can join global
ones, but global roles can't join local ones?  I'm not sure how much of
an issue this will really be if they're all sharing one catalog with
different namespaces (eg: the '@db' stuff).

> Assuming, of course, that we had
> local users in the first place.

That's what this thread is about, isn't it..?

> >> 8 NOT run SET PERSISTENT or other commands with global effect
> >
> > Indeed, or use 'COPY'..
>
> Yeah.  In theory, we should allow the local superuser to use COPY; in
> practice, nobody will care because they'll be using client-side COPY
> since the entire use-case for this is cloud-hosted DBs anyway.

How could we allow the local "superuser" to use COPY?  They've be able
to overwrite files in other databases, or possibly modify
postgresql.conf, etc, depending on the overall system environment.

> > For my part- I don't see having everyone write their own set of SECURITY
> > DEFINER functions as being either less complex or less risk.  They're
> > also a lot less convenient to use.  That's not what RDS did, is it?  No,
> > and I agree with them on that part.
>
> I was thinking of having an extension in contrib, actually.  That is, a
> canonical set of security definer functions.  But if you think it's
> easier to actually implement the permissions restrictions in the actual
> utility functions, I wouldn't argue.

Having it in contrib would be better than nothing, but I'd advocate
putting this in as part of our actual permissions model, along with the
read-only/auditor-type option.  Having this not-quite-superuser which is
able to read all tables, possibly modify any of them, without having to
change the permissions system could be extremely useful and would be
rather painful to implement through SD functions.
Thanks,
    Stephen