Обсуждение: db_user_namespace a "temporary measure"

Hi,

I've noticed that "db_user_namespace" has had the following note
attached to it since 2002:

"This feature is intended as a temporary measure until a complete
solution is found. At that time, this option will be removed."

It will be 12 years this year since this "temporary measure" was
added.  I'm just wondering, is there any "complete solution" that
anyone had in mind yet?  Or should this just be deprecated?

Thanks

Thom

Magnus Hagander <magnus@hagander.net> writes:
> On Sun, Mar 9, 2014 at 9:00 PM, Thom Brown <thom@linux.com> wrote:
>> It will be 12 years this year since this "temporary measure" was
>> added.  I'm just wondering, is there any "complete solution" that
>> anyone had in mind yet?  Or should this just be deprecated?

> I'd say +1 to remove it. That would also make it possible to get id of
> "password" authentication...

If we remove it without providing a substitute feature, people who are
using it will rightly complain.

Are you claiming there are no users, and if so, on what evidence?
        regards, tom lane

Magnus Hagander <magnus@hagander.net> writes:
> On Tue, Mar 11, 2014 at 2:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Are you claiming there are no users, and if so, on what evidence?

> I am claiming that I don't think anybody is using that, yes.

> Based on the fact that I have *never* come across it on any system I've
> come across since, well, forever. Except once I think, many years ago, when
> someone had enabled it by mistake and needed my help to remove it...

A slightly more scientific basis for that would be to ask on
pgsql-general.

> Or if someone wants to fix it properly of course :)

Yeah, that's what we've been hoping for for 12 years.  I stopped holding
my breath awhile ago.

Mind you, I wouldn't be unhappy to see it go away; it's a kluge and always
has been.  I'm just expecting lots of push-back if we try.  And it's kind
of hard to resist push-back when you don't have a substitute to offer.
        regards, tom lane

On 03/11/2014 09:57 AM, Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> On Tue, Mar 11, 2014 at 2:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Are you claiming there are no users, and if so, on what evidence?
>> I am claiming that I don't think anybody is using that, yes.
>> Based on the fact that I have *never* come across it on any system I've
>> come across since, well, forever. Except once I think, many years ago, when
>> someone had enabled it by mistake and needed my help to remove it...
> A slightly more scientific basis for that would be to ask on
> pgsql-general.
>
>> Or if someone wants to fix it properly of course :)
> Yeah, that's what we've been hoping for for 12 years.  I stopped holding
> my breath awhile ago.
>
> Mind you, I wouldn't be unhappy to see it go away; it's a kluge and always
> has been.  I'm just expecting lots of push-back if we try.  And it's kind
> of hard to resist push-back when you don't have a substitute to offer.
>
>             


Or we try to make it work. I don't think the idea is inherently bad, and 
I know there are people (like ISPs) who would like to have it work 
properly. Maybe in these days when most people are on dedicated VMs this 
matters less, but I don't think shared database servers are totally dead 
yet.

The docs say:
   db_user_namespace causes the client's and server's user name   representation to differ. Authentication checks are
alwaysdone with   the server's user name so authentication methods must be configured   for the server's user name, not
theclient's. Because md5 uses the   user name as salt on both the client and server, md5 cannot be used   with
db_user_namespace.

Is that the only major issue? Why not have the server strip out the @db 
part if this is on? If we made this an initdb-time setting rather than a 
GUC then we'd remove the problems caused by turning this on and off. I'm 
not sure what other problems that might cause, but it doesn't seem 
totally intractable at first glance.

cheers

andrew

* Andrew Dunstan (andrew@dunslane.net) wrote:
> Or we try to make it work. I don't think the idea is inherently bad,
> and I know there are people (like ISPs) who would like to have it
> work properly. Maybe in these days when most people are on dedicated
> VMs this matters less, but I don't think shared database servers are
> totally dead yet.

Agreed.  There are certainly pretty big hosting companies out there
which are already doing multi-tenant PG, but they're using their own
approaches instead of anything we provide (because what we provide
sucks, basically..).

> The docs say:
>
>    db_user_namespace causes the client's and server's user name
>    representation to differ. Authentication checks are always done with
>    the server's user name so authentication methods must be configured
>    for the server's user name, not the client's. Because md5 uses the
>    user name as salt on both the client and server, md5 cannot be used
>    with db_user_namespace.
>
> Is that the only major issue? Why not have the server strip out the
> @db part if this is on? If we made this an initdb-time setting
> rather than a GUC then we'd remove the problems caused by turning
> this on and off. I'm not sure what other problems that might cause,
> but it doesn't seem totally intractable at first glance.

Isn't the other issue for ISPs essentially that we don't have row-level
security for our global catalogs?  as in- we can't limit what's in
pg_authid to only those entries a given user should be able to see?  I
don't think db_user_namespace addresses that issue (but I didn't go look
either).
Thanks,
    Stephen

On 03/11/2014 12:37 PM, Stephen Frost wrote:

> Isn't the other issue for ISPs essentially that we don't have row-level
> security for our global catalogs?  as in- we can't limit what's in
> pg_authid to only those entries a given user should be able to see?  I
> don't think db_user_namespace addresses that issue (but I didn't go look
> either).
>
>     


Possibly, but we don't have to solve every problem at once. As the 
Sermon on the Mount says, "Sufficient unto the day is the evil thereof."

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:
> The docs say:

>     db_user_namespace causes the client's and server's user name
>     representation to differ. Authentication checks are always done with
>     the server's user name so authentication methods must be configured
>     for the server's user name, not the client's. Because md5 uses the
>     user name as salt on both the client and server, md5 cannot be used
>     with db_user_namespace.

> Is that the only major issue?

That's one major issue.

Another one is the hacky way of distinguishing global from per-db users
(ie, user must append @ to log in as a global user).  I'd love to get rid
of that requirement, but not sure how.  Would it be all right for the
server to first probe for user@db and then for just user, or vice versa?
How would this integrate with pg_hba.conf?

> Why not have the server strip out the @db part if this is on?

Hmm ... that's a thought.  IIRC, the client doesn't actually know that
this is going on, it just sends the user name, and hashes against that
too.  If the server remembered the bare user name it could hash against
that as well.

At least, that would work for db-local usernames.  It *doesn't* work for
global names, because the client side has no idea that the @ ought to not
be counted for hashing.  Again, if we could get rid of that convention,
it'd be far less messy.

A possible objection would be that changing the definition of what to hash
would invalidate existing MD5 password hashes; but since MD5 passwords
have never been allowed with db_user_namespace names anyway, that doesn't
seem very forceful.

> If we made this an initdb-time setting rather than a 
> GUC then we'd remove the problems caused by turning this on and off.

Why do we need to restrict that?
        regards, tom lane

On 03/11/2014 07:41 PM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> The docs say:
>>      db_user_namespace causes the client's and server's user name
>>      representation to differ. Authentication checks are always done with
>>      the server's user name so authentication methods must be configured
>>      for the server's user name, not the client's. Because md5 uses the
>>      user name as salt on both the client and server, md5 cannot be used
>>      with db_user_namespace.
>> Is that the only major issue?
> That's one major issue.
>
> Another one is the hacky way of distinguishing global from per-db users
> (ie, user must append @ to log in as a global user).  I'd love to get rid
> of that requirement, but not sure how.  Would it be all right for the
> server to first probe for user@db and then for just user, or vice versa?

user@db first, I think. I guess that means don't name your global users 
the same as db-specific users. Maybe we should prevent a conflict? Or if 
we allow a conflict then only require user@ in conflicting cases.


> How would this integrate with pg_hba.conf?

Not sure.

>
>> Why not have the server strip out the @db part if this is on?
> Hmm ... that's a thought.  IIRC, the client doesn't actually know that
> this is going on, it just sends the user name, and hashes against that
> too.  If the server remembered the bare user name it could hash against
> that as well.
>
> At least, that would work for db-local usernames.  It *doesn't* work for
> global names, because the client side has no idea that the @ ought to not
> be counted for hashing.  Again, if we could get rid of that convention,
> it'd be far less messy.


Right.

>
> A possible objection would be that changing the definition of what to hash
> would invalidate existing MD5 password hashes; but since MD5 passwords
> have never been allowed with db_user_namespace names anyway, that doesn't
> seem very forceful.

Agreed.

>
>> If we made this an initdb-time setting rather than a
>> GUC then we'd remove the problems caused by turning this on and off.
> Why do we need to restrict that?
>
>             


Oh, probably a thinko in my part. I thought the setting would affect the 
stored password, but now I think about it some more I see it doesn't.


I think we might be on the track of something useful here.


cheers

andre

On 03/11/2014 06:57 AM, Tom Lane wrote:
> Mind you, I wouldn't be unhappy to see it go away; it's a kluge and always
> has been.  I'm just expecting lots of push-back if we try.  And it's kind
> of hard to resist push-back when you don't have a substitute to offer.

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.

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

Josh Berkus <josh@agliodbs.com> writes:
> On 03/11/2014 06:57 AM, Tom Lane wrote:
>> Mind you, I wouldn't be unhappy to see it go away; it's a kluge and always
>> has been.  I'm just expecting lots of push-back if we try.  And it's kind
>> of hard to resist push-back when you don't have a substitute to offer.

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

Well ... you know, how hard would it really be?  Extend the primary
key of pg_authid to be (username, database_oid) with the convention
of database_oid = 0 for a globally known user.  Add some syntax to
CREATE USER to indicate whether you're creating a global user
(the default) or one known only within the current database.  I'm
not quite sure what to do with local users for pg_dump/pg_dumpall;
perhaps pg_dump should get the responsibility of creating local users?
But otherwise it seems like there are no issues that we'd not have to
solve anyway if we wanted to make db_user_name less of a crock.

In particular, I'd like to see an exclusion that prevents local users
from having the same name as any global user, so that we don't have
ambiguity in GRANT and similar commands.  This doesn't seem simple to
enforce (if we supported partial indexes on system catalogs, it would
be ...) but surely this representation is more amenable to enforcing it
than the existing one.
        regards, tom lane

On 03/11/2014 09:37 PM, Tom Lane wrote:

>
> In particular, I'd like to see an exclusion that prevents local users
> from having the same name as any global user, so that we don't have
> ambiguity in GRANT and similar commands.  This doesn't seem simple to
> enforce (if we supported partial indexes on system catalogs, it would
> be ...) but surely this representation is more amenable to enforcing it
> than the existing one.
>
>             

Should be workable if you're creating a local name - just check against 
the list of global roles. For creating global names, maybe we could keep 
an invisible shared catalog (or maybe only visible to global superusers) 
of local names / db oids. New global names would be checked against that 
catalog. Something like that.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:
> On 03/11/2014 09:37 PM, Tom Lane wrote:
>> In particular, I'd like to see an exclusion that prevents local users
>> from having the same name as any global user, so that we don't have
>> ambiguity in GRANT and similar commands.  This doesn't seem simple to
>> enforce (if we supported partial indexes on system catalogs, it would
>> be ...) but surely this representation is more amenable to enforcing it
>> than the existing one.

> Should be workable if you're creating a local name - just check against 
> the list of global roles.

Concurrent creations won't be safe without some sort of locking scheme.
A unique index would be a lot better way of plugging that hole than a
system-wide lock on user creation.  But not sure how to define a unique
index that allows (joe, db1) to coexist with (joe, db2) but not with
(joe, 0).
        regards, tom lane

On 03/11/2014 11:06 PM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> On 03/11/2014 09:37 PM, Tom Lane wrote:
>>> In particular, I'd like to see an exclusion that prevents local users
>>> from having the same name as any global user, so that we don't have
>>> ambiguity in GRANT and similar commands.  This doesn't seem simple to
>>> enforce (if we supported partial indexes on system catalogs, it would
>>> be ...) but surely this representation is more amenable to enforcing it
>>> than the existing one.
>> Should be workable if you're creating a local name - just check against
>> the list of global roles.
> Concurrent creations won't be safe without some sort of locking scheme.
> A unique index would be a lot better way of plugging that hole than a
> system-wide lock on user creation.  But not sure how to define a unique
> index that allows (joe, db1) to coexist with (joe, db2) but not with
> (joe, 0).
>
>             


Create (joe, db1), (joe, db2) ... for each global user? Might get a tad 
ugly if you have lots of global users or lots of databases.

Just trying to be a bit creative :-)

cheers

andrew

On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> But not sure how to define a unique
> index that allows (joe, db1) to coexist with (joe, db2) but not with
> (joe, 0).
>

and why you want that restriction? when you login you need to specify
the db, right? if you don't specify the db then is the global 'joe'
that want to login

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566         Cell: +593 987171157

On 03/11/2014 11:50 PM, Jaime Casanova wrote:
> On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> But not sure how to define a unique
>> index that allows (joe, db1) to coexist with (joe, db2) but not with
>> (joe, 0).
>>
> and why you want that restriction? when you login you need to specify
> the db, right? if you don't specify the db then is the global 'joe'
> that want to login
>

You can't login without specifying a db. Every session is connected to a db.

cheers

andrew

On Tue, Mar 11, 2014 at 10:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> On 03/11/2014 11:50 PM, Jaime Casanova wrote:
>>
>> On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>
>>> But not sure how to define a unique
>>> index that allows (joe, db1) to coexist with (joe, db2) but not with
>>> (joe, 0).
>>>
>> and why you want that restriction? when you login you need to specify
>> the db, right? if you don't specify the db then is the global 'joe'
>> that want to login
>>
>
> You can't login without specifying a db. Every session is connected to a db.
>

then, what's the problem with global users?

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566         Cell: +593 987171157

Andrew Dunstan wrote
> On 03/11/2014 11:50 PM, Jaime Casanova wrote:
>> On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane <

> tgl@.pa

> > wrote:
>>> But not sure how to define a unique
>>> index that allows (joe, db1) to coexist with (joe, db2) but not with
>>> (joe, 0).
>>>
>> and why you want that restriction? when you login you need to specify
>> the db, right? if you don't specify the db then is the global 'joe'
>> that want to login
>>
> 
> You can't login without specifying a db. Every session is connected to a
> db.
> 
> cheers
> 
> andrew

Random Thoughts:

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.

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.

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.

Construction of a new global user would cause an immediate copy-over; which
can fail if the simple name is already in use in any of the existing
databases.  "Promoting" becomes a problem that would ideally have a solution
- but then you are back to needing to distinguish between "dave-db1" and
"dave-db2"...

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

Or go the opposite extreme and all users are local and the concept of
"global" would have to be added by those desiring it.  Basically move
user-management at the cluster level to a cluster support application and
leave databases free-standing.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/db-user-namespace-a-temporary-measure-tp5795305p5795609.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

<p dir="ltr"><br /> On Mar 12, 2014 1:46 AM, "Josh Berkus" <<a
href="mailto:josh@agliodbs.com">josh@agliodbs.com</a>>wrote:<br /> ><br /> > On 03/11/2014 06:57 AM, Tom Lane
wrote:<br/> > > Mind you, I wouldn't be unhappy to see it go away; it's a kluge and always<br /> > > has
been. I'm just expecting lots of push-back if we try.  And it's kind<br /> > > of hard to resist push-back when
youdon't have a substitute to offer.<br /> ><br /> > Yeah, what we really need is encapsulated per-DB users and
local<br/> > superusers.  I think every agrees that this is the goal, but nobody<br /> > wants to put in the work
toimplement a generalized solution.<br /> ><p dir="ltr">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
doinganything they want? And if you don't need that functionality, then hows it really different from being the
databaseowner? <p dir="ltr">/Magnus  

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
> > On 03/11/2014 09:37 PM, Tom Lane wrote:
> >> In particular, I'd like to see an exclusion that prevents local users
> >> from having the same name as any global user, so that we don't have
> >> ambiguity in GRANT and similar commands.  This doesn't seem simple to
> >> enforce (if we supported partial indexes on system catalogs, it would
> >> be ...) but surely this representation is more amenable to enforcing it
> >> than the existing one.
> 
> > Should be workable if you're creating a local name - just check against 
> > the list of global roles.
> 
> Concurrent creations won't be safe without some sort of locking scheme.
> A unique index would be a lot better way of plugging that hole than a
> system-wide lock on user creation.  But not sure how to define a unique
> index that allows (joe, db1) to coexist with (joe, db2) but not with
> (joe, 0).

Isn't this just a case of creating a suitable operator and an exclusion
constraint?  Defining the constraint in BKI might require extra
infrastructure, but it should be possible.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2014-03-12 10:03:42 -0300, Alvaro Herrera wrote:
> Tom Lane wrote:
> > Andrew Dunstan <andrew@dunslane.net> writes:
> > > On 03/11/2014 09:37 PM, Tom Lane wrote:
> > >> In particular, I'd like to see an exclusion that prevents local users
> > >> from having the same name as any global user, so that we don't have
> > >> ambiguity in GRANT and similar commands.  This doesn't seem simple to
> > >> enforce (if we supported partial indexes on system catalogs, it would
> > >> be ...) but surely this representation is more amenable to enforcing it
> > >> than the existing one.
> > 
> > > Should be workable if you're creating a local name - just check against 
> > > the list of global roles.
> > 
> > Concurrent creations won't be safe without some sort of locking scheme.
> > A unique index would be a lot better way of plugging that hole than a
> > system-wide lock on user creation.  But not sure how to define a unique
> > index that allows (joe, db1) to coexist with (joe, db2) but not with
> > (joe, 0).
> 
> Isn't this just a case of creating a suitable operator and an exclusion
> constraint?  Defining the constraint in BKI might require extra
> infrastructure, but it should be possible.

Except that we don't have the infrastructure to perform such checks
(neither partial, nor expression indexes, no exclusion constraints) on
system tables atm. So it's not a entirely trivial thing to do.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services

Jaime Casanova <jaime@2ndquadrant.com> writes:
> On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> But not sure how to define a unique
>> index that allows (joe, db1) to coexist with (joe, db2) but not with
>> (joe, 0).

> and why you want that restriction?

So that if I say "GRANT SELECT ON mytable TO joe", it's unambiguous which
user I'm granting to.  There should be at most one "joe" that can access
any given database.

If we don't have such a restriction, we'll need syntax kluges in GRANT,
ALTER OWNER, and probably other commands to disambiguate whether a local
or global user is meant.  Right now with the db_user_namespace mechanism,
you have to say GRANT ... TO "joe@db1" if you were granting to a local
user in db1.  That's unambiguous all right, but it has little else to
recommend it.
        regards, tom lane

Magnus Hagander <magnus@hagander.net> writes:
>> 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?

A local user with the superuser privilege would not be able to log into
another database, because superuser doesn't give you any extra privilege
until you've logged in.

Yeah, as superuser you could still break things as much as you pleased,
but not through SQL.

I share your doubts as to how useful such a concept actually is, but
it'd work if we had real local users.
        regards, tom lane

* Magnus Hagander (magnus@hagander.net) wrote:
> On Wed, Mar 12, 2014 at 3:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I share your doubts as to how useful such a concept actually is, but
> > it'd work if we had real local users.
>
>
> It can also do interesting things like ALTER SYSTEM, replication, backups,
> etc. All of which could be used to escalate privileges beyond the local
> database.

Probably DROP ROLE for global users too.

> So you'd have to somehow restrict those, at which point what's the point of
> the property in the first place?

We've been asked quite often for a not-quite-superuser, as in, one which
can bypass the normal GRANT-based permission system but which can't do
things like create untrusted functions or do other particularly bad
activities.  I can certainly see value in that.  Another oft-requested
option is a read-only role which pg_dump or an auditor could use.

Anyway, this is getting a bit far afield from the original discussion,
which looked like it might actually be heading somewhere interesting..
Thanks,
    Stephen

Magnus Hagander <magnus@hagander.net> writes:
> On Wed, Mar 12, 2014 at 3:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> A local user with the superuser privilege would not be able to log into
>> another database, because superuser doesn't give you any extra privilege
>> until you've logged in.
>> 
>> Yeah, as superuser you could still break things as much as you pleased,
>> but not through SQL.

> You could COPY over the hba file or sometihng like that :)  Or just
> pg_read_binary_file() on the files in another database, which is accessible
> through SQL as well.

More directly, he could alter pg_authid to make himself a not-local user.
But I don't see that it's our responsibility to prevent that.  As long as
the combination of features works in a straightforward way, I'm happy
with it --- and it would, AFAICS.
        regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Magnus Hagander <magnus@hagander.net> writes:
> > You could COPY over the hba file or sometihng like that :)  Or just
> > pg_read_binary_file() on the files in another database, which is accessible
> > through SQL as well.
>
> More directly, he could alter pg_authid to make himself a not-local user.
> But I don't see that it's our responsibility to prevent that.  As long as
> the combination of features works in a straightforward way, I'm happy
> with it --- and it would, AFAICS.

That depends on exactly what you mean by 'those features'.  There's
quite a difference between "you can set the superuser flag on a local
user and then that user will be a superuser" and "a local user with
superuser flag will only be able to impact the database they are local
to".  I agree that there's nothing stopping us from having a "local"
user which is marked as a superuser from a technical level.

What Magnus and I are worried about is the *implication* of such a
configuration is and what the user will think it means.  Specifically,
there will be an assumption that "local" users can only access or impact
the databases which they have access to, which wouldn't be accurate for
a "local" user who is a superuser.  Certainly, documenting this would
help with that but with as many warnings as we'd have to put up about
that being dangerous and that it isn't actually going to prevent that
superuser from accessing the other databases if they really wanted to,
or prevent them from making a global superuser account, etc, I'm just
not convinced that it's worth it for the "feature" of allowing a "local"
account to be set as superuser- I don't see a huge use-case there.
Thanks,
    Stephen

On 03/12/2014 12:22 AM, Magnus Hagander wrote:
> On Mar 12, 2014 1:46 AM, "Josh Berkus" <josh@agliodbs.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

On 03/12/2014 02:09 PM, Josh Berkus wrote:
> On 03/12/2014 12:22 AM, Magnus Hagander wrote:
>> On Mar 12, 2014 1:46 AM, "Josh Berkus" <josh@agliodbs.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

Item 3 gives away the store. AFAIK Amazon doesn't load untrusted 
languages, period.

cheers

andrew

* 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?

> 2 load extensions from a predefined .so directory / list

This would obviously have to be a curated list that avoids things like
'adminpack'...

> 3 create/modify untrusted language functions

Uhh, I don't believe RDS allows you to do this..?

> 4 create per-database users and change their settings

Presumably just for the 'local' DB?

> 5 change database settings (SET stuff)

This can be done by the database-owner already, no?

> 6 NOT change their own user settings

Don't think this is quite that simple (passwords?).

> 7 NOT change any global users

What about role membership, wrt local vs. global roles?

> 8 NOT run SET PERSISTENT or other commands with global effect

Indeed, or use 'COPY'..

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

That'd certainly be the main issue that I see with this proposal.  Doing
the rest but allowing untrusted languages would just get the naive in
trouble and not help those of us who want this, as we wouldn't be able
to use it.

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

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.

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

Agreed.
Thanks,
    Stephen

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.

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

Andrew Dunstan <andrew@dunslane.net> writes:
> On 03/12/2014 02:09 PM, Josh Berkus wrote:
>> 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

> Item 3 gives away the store.

Indeed.  If you can do (3), you can break out of any of the other
constraints.  I suspect even (1) and/or (5) would be enough to mount
trojan-horse attacks against real superusers who visit your database.

I do not put any stock in the notion of "constrained superuser".
        regards, tom lane

On 03/12/2014 11:40 AM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> On 03/12/2014 02:09 PM, Josh Berkus wrote:
>>> 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
> 
>> Item 3 gives away the store.
> 
> Indeed.  If you can do (3), you can break out of any of the other
> constraints.  I suspect even (1) and/or (5) would be enough to mount
> trojan-horse attacks against real superusers who visit your database.

... nobody reads my whole post, except Stephen.  :-(

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

* 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

On Wed, Mar 12, 2014 at 9:19 AM, Andres Freund <andres@2ndquadrant.com> wrote:
>> Isn't this just a case of creating a suitable operator and an exclusion
>> constraint?  Defining the constraint in BKI might require extra
>> infrastructure, but it should be possible.
>
> Except that we don't have the infrastructure to perform such checks
> (neither partial, nor expression indexes, no exclusion constraints) on
> system tables atm. So it's not a entirely trivial thing to do.

I'm probably woefully underinformed here, but it seems like getting
exclusion constraints working might be simpler than partial indexes or
expression indexes, because both of those involve being able to
evaluate arbitrary predicates, whereas exclusion constraints just
involve invoking index access methods to look for conflicting rows via
smarts built into your index AM.  The latter seems to involve less
risk of circularity (but I might be wrong).

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Mar 12, 2014 at 9:19 AM, Andres Freund <andres@2ndquadrant.com> wrote:
>> Except that we don't have the infrastructure to perform such checks
>> (neither partial, nor expression indexes, no exclusion constraints) on
>> system tables atm. So it's not a entirely trivial thing to do.

> I'm probably woefully underinformed here, but it seems like getting
> exclusion constraints working might be simpler than partial indexes or
> expression indexes, because both of those involve being able to
> evaluate arbitrary predicates, whereas exclusion constraints just
> involve invoking index access methods to look for conflicting rows via
> smarts built into your index AM.  The latter seems to involve less
> risk of circularity (but I might be wrong).

You might be right.  I don't think anyone's ever looked at what it
would take to support that particular case.  We have looked at the
other cases and run away screaming ... but I think that was before
exclusion constraints existed.
        regards, tom lane

On 2014-03-12 20:54:36 -0400, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Wed, Mar 12, 2014 at 9:19 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> >> Except that we don't have the infrastructure to perform such checks
> >> (neither partial, nor expression indexes, no exclusion constraints) on
> >> system tables atm. So it's not a entirely trivial thing to do.
> 
> > I'm probably woefully underinformed here, but it seems like getting
> > exclusion constraints working might be simpler than partial indexes or
> > expression indexes, because both of those involve being able to
> > evaluate arbitrary predicates, whereas exclusion constraints just
> > involve invoking index access methods to look for conflicting rows via
> > smarts built into your index AM.  The latter seems to involve less
> > risk of circularity (but I might be wrong).

Exclusion constraints support being partial... But I guess we could
forbid using that.

> You might be right.  I don't think anyone's ever looked at what it
> would take to support that particular case.  We have looked at the
> other cases and run away screaming ... but I think that was before
> exclusion constraints existed.

Hm. Is it actually that complicated to support checking predicates and
computing expressions for system catalogs during index insertions? If
we'd only create those indexes once the the basic bootstrap is over, I
don't see too much problems with circularity? Creating indexes on shared
catalogs after the immediate bootstrap isn't entirely trivial, but
should be doable.
I've searched for "running away screaming", but even with extending the
search critera a bit I unfortunately came up empty.

I don't really see much need for expression indexes on catalogs, but
partial unique constraints would surely be useful.

Now, what I *do* see problems with would be to try to evaluate
predicates/expressions when filling system caches. But it looks to be me
like the primary interest at least here is partial unique constraints?

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services