Re: db_user_namespace a "temporary measure"

On 03/12/2014 11:25 AM, Stephen Frost wrote:
> * Josh Berkus (josh(at)agliodbs(dot)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.

>> 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.

>> 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.

>> 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. Assuming, of course, that we had
local users in the first place.

>> 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.

> 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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com