Re: db_user_namespace a "temporary measure"

On 03/12/2014 12:22 AM, Magnus Hagander wrote:
> On Mar 12, 2014 1:46 AM, "Josh Berkus" <josh(at)agliodbs(dot)com> wrote:
>> Yeah, what we really need is encapsulated per-DB users and local
>> superusers. I think every agrees that this is the goal, but nobody
>> wants to put in the work to implement a generalized solution.
>>
>
> Encapsulated would probably be the doable part. But local superuser? Given
> that a superuser can load and run binaries, how would you propose you
> restrict that superuser from doing anything they want? And if you don't
> need that functionality, then hows it really different from being the
> database owner?

Well, if you really want my "I want a pony" list:

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
2 load extensions from a predefined .so directory / list
3 create/modify untrusted language functions
4 create per-database users and change their settings
5 change database settings (SET stuff)
6 NOT change their own user settings
7 NOT change any global users
8 NOT run SET PERSISTENT or other commands with global effect

The above is fairly similar to what Amazon currently offers for Postgres
RDS, except that they only have 1 database per instance, so clearly they
haven't worked out some of the security issues.

Now, obviously permission (3) could be used to escalate a local
superuser to global superuser permissions, so local superusers aren't
really a secure concept, unless you don't add any untrusted languages to
the list of allowed extensions. Alternately, we could drop (3) from the
list of features.

Hmmmm. On the other foot, though: all of 1,2,4 and 5 could conceivably
be done via a set of Security Definer functions loaded into the
database, with a lot less complexity and security risk. So if we're
sacrificing untrusted languages, then we really don't need a local
superuser at all; just a bunch of variadic SD functions which can handle
user creation and permissions issues. It would limit the permissions
syntax we can express, but not critically so in my opinion. This could
all be done as an extension, and would probably be the better for it.

Assuming we have db-local users in the first place, of course.

On 03/11/2014 09:39 PM, David Johnston wrote:

> So if dave is already a user in db1 only that specific dave can be made a
> global user - any other dave would be disallowed.

Correct. Well, unless the other dave was promoted first. However, I
personally don't see any reason why we should even support promoting
users from local to global. It adds complexity to the concept, and the
value of it eludes me.

>
> Would "user - password" be a better PK? Even with the obvious issue that
> password part of the key can change. "user-password to database" is a
> properly many-to-many relationship. Or see next for something simpler.

I have no idea where you're going with this.

> A simple implementation would simply have the global users copied into
each
> database as it is constructed. There would also be a link from each
of the
> database-specific users and the global master so that a password change
> issued against the global user propagates to all the database-specific
> versions.

That's called "massive backwards compatibility failure", and I don't
think we'll go any further with your concept.

> Be nice if all users could be "global" and there would be some way to give
> them permissions on databases.

Um, there is:
http://www.postgresql.org/docs/9.3/static/sql-grant.html

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