db_user_namespace a "temporary measure"

On Sun, Mar 9, 2014 at 9:00 PM, Thom Brown <thom@linux.com> wrote:

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?

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

But we might want to officially deprecate it for 9.4, and then remove it
later?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Mar 11, 2014 at 2:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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?

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

But we should absolutely deprecate it first in that place. Preferrably
visibly (e.g. with a log message when people use it). That could at least
get those people who use it to let us know they do, to that way figure out
if they do - and can de-deprecate it.

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

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

So I'll admit to using it, only in "toy" setups...

I use it with trust and ident, on local connections though, not password....

I try to keep my laptops clean of mysqld, and I use PG. And only on my
laptop/PC, I make a database for every user... And every "app" get's a
userid, and a schema.... Every user get's passwordless access to their
database. And the "userid" associates the app, and the defaults that get
used on their connections.

So, I think it's "neat", but wouldn't be put out if it was removed ...

On Tue, Mar 11, 2014 at 9:47 AM, Magnus Hagander <magnus@hagander.net>wrote:

On Tue, Mar 11, 2014 at 2:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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?

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

But we should absolutely deprecate it first in that place. Preferrably
visibly (e.g. with a log message when people use it). That could at least
get those people who use it to let us know they do, to that way figure out
if they do - and can de-deprecate it.

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

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

--
Aidan Van Dyk Create like a god,
aidan@highrise.ca command like a king,
http://www.highrise.ca/ work like a
slave.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andrew Dunstan wrote

On 03/11/2014 11:50 PM, Jaime Casanova wrote:

On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane &lt;

tgl@.pa

&gt; 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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers