Per database users/admins, handy for database virtual hosting...

On 25-Mar-04, at 8:18 PM, Sean Chittenden wrote:

I haven't read much in the last few months, but archives from 2002
suggested there wasn't much on the table in terms of making this
happen beyond adding a function that runs as a DBA to create users
(which I've done).

Well, the db_user_namespace GUC var has been implemented, but it is a
hack.

-Neil

Sean Chittenden <sean@chittenden.org> writes:

What's the feasibility of augmenting the system catalogs so that
something similar to the following is possible:

CREATE VIEW pg_catalog.pg_shadow AS
SELECT usename, usesysid, usecreatedb, usesuper,
usecatupd, passwd, valuntil, useconfig
FROM pg_catalog.pg_shadow_cluster
UNION ALL
SELECT usename, usesysid, usecreatedb, usesuper,
usecatupd, passwd, valuntil, useconfig
FROM pg_catalog.pg_shadow_db;

The main problem I can see is usesysid conflicts. For example suppose
userid 42 is created in database A, and then someone in database B
decides to create a global user with id 42. The latter someone can't
even see that he's causing a problem in database A :-(

I'd be in favor of this if we could find an answer to that one.

Maybe something dirty like reserving separate ranges of sysid for local
and global users would get the job done, but I haven't spent any time
trying to poke holes in that idea...

Come to think of it, the same risk of conflict applies for user *names*,
and we can't easily make an end-run around that.

And then set up RULEs to direct INSERTs, UPDATEs, DELETEs from
pg_shadow to pg_shadow_db. CREATE USER/ALTER USER operates on
pg_catalog_db, then CREATE CLUSTER USER/ALTER CLUSTER USER operates on
pg_catalog_cluster.

Nope, other way round, default behavior for backwards compatibility must
be to create cluster-wide users. CREATE LOCAL USER is what to add.

regards, tom lane

What's the feasibility of augmenting the system catalogs so that
something similar to the following is possible:

CREATE VIEW pg_catalog.pg_shadow AS
SELECT usename, usesysid, usecreatedb, usesuper,
usecatupd, passwd, valuntil, useconfig
FROM pg_catalog.pg_shadow_cluster
UNION ALL
SELECT usename, usesysid, usecreatedb, usesuper,
usecatupd, passwd, valuntil, useconfig
FROM pg_catalog.pg_shadow_db;

The main problem I can see is usesysid conflicts. For example suppose
userid 42 is created in database A, and then someone in database B
decides to create a global user with id 42. The latter someone can't
even see that he's causing a problem in database A :-(

Hrm... that's true. The UID sequence would be shared, but that
doesn't prevent someone from forcing a DBA from having a non-sequential
UID. Here's a list of the scenarios that I can think of:

LOCAL USERs, as you suggest later, are bound to a given database, who
cares if the local DBA mucks with the UID of the user? They're still
confined to their local database and there's no risk to the integrity
of the system. A local DBA shouldn't be able to muck with
pg_shadow_cluster anyway, so no harm should be possible.

Let's say a local DBA creates a user with UID that conflicts with a
cluster wide user. What's the worst that could happen? The UID of the
cluster wide user inherits perms of the local user with the same UID.
In most deployment scenarios where system admins would deploy
PostgreSQL and use LOCAL USERS, the CLUSTER USERS are probably a DBAs
with his/her pg_catalog.pg_shadow_cluster.usesuper = TRUE, so
inheriting privs is of little consequence. If a CLUSTER USER is just a
normal user, then the permissions could get wonky. It may be worth
while logging a UID conflict and closing the connection for security
reasons if usesuper = FALSE. It seems like it'd be possible to have
TRIGGERs on pg_shadow_db that'd check to make sure the UID wasn't
already in use and make a stink if it were already in use in the
cluster's catalog. You can only protect people from wandering off the
range so far...

Beyond a database picking up problems, I can't think of any other
consequences... at least not that'd affect the entire cluster... but
I'm still a bit new to the problem and may have missed something.

Maybe something dirty like reserving separate ranges of sysid for local
and global users would get the job done, but I haven't spent any time
trying to poke holes in that idea...

Well, it's reasonably tried and true in the *NIX world with reserved
UIDs being the only ones allowed to bind to ports less than 1024.
Beyond being arbitrary limits, it seems to have worked well to date.

Reserving the lower 10K UIDs for cluster users isn't a bad idea...
going further, given that I haven't heard of a database with more than
1B users... use 2^30 through 2^31 as the UID range for local users and
0 through (2^30 - 1) as the range for cluster wide UIDs. If someone
gripes about having only 1B UIDs for cluster wide/local admin
purposes...

Come to think of it, the same risk of conflict applies for user
*names*,
and we can't easily make an end-run around that.

That's why I used UNION ALL in my example. Reserved usernames that are
in the cluster should be just as valid as usernames that are in the
local database table. I'm not sure how the authentication bit works
internally, but that seems like a matter of changing the routine to do:

SELECT TRUE FROM pg_catalog.pg_shadow WHERE usename = :username AND
password = :pw;

and checking to see if the query returns at least one row.

And then set up RULEs to direct INSERTs, UPDATEs, DELETEs from
pg_shadow to pg_shadow_db. CREATE USER/ALTER USER operates on
pg_catalog_db, then CREATE CLUSTER USER/ALTER CLUSTER USER operates on
pg_catalog_cluster.

Nope, other way round, default behavior for backwards compatibility
must
be to create cluster-wide users. CREATE LOCAL USER is what to add.

Ah, good point. -sc

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:

Come to think of it, the same risk of conflict applies for user
*names*, and we can't easily make an end-run around that.

That's why I used UNION ALL in my example. Reserved usernames that are
in the cluster should be just as valid as usernames that are in the
local database table.

I don't follow. You can't think that allowing the same name to appear
globally and locally is a good idea. If I say "GRANT TO foo", who am
I granting privileges to? And I don't want to say that there is no
difference because they are the same user. That will open up some nasty
security holes, eg, being able to pretend that you are the global
postgres superuser if you can set the password for a local user by the
same name.

regards, tom lane

You can't think that allowing the same name to appear
globally and locally is a good idea.

Actually, I do think it is a good idea.

If I say "GRANT TO foo", who am
I granting privileges to?

SET username_precedence TO LOCAL,GLOBAL; -- I like GLOBAL more than
CLUSTER
GRANT TO foo;
SET username_precedence TO GLOBAL,LOCAL;
GRANT TO foo;

And I don't want to say that there is no
difference because they are the same user.

Agreed, they should be the same user.

That will open up some nasty
security holes, eg, being able to pretend that you are the global
postgres superuser if you can set the password for a local user by the
same name.

Agreed, but if a cluster is using LOCAL USERs, I doubt highly that
CLUSTER/GLOBAL users would be in use much beyond super users. -sc

--
Sean Chittenden

I haven't read much in the last few months, but archives from 2002
suggested there wasn't much on the table in terms of making this
happen beyond adding a function that runs as a DBA to create users
(which I've done).

Well, the db_user_namespace GUC var has been implemented, but it is a
hack.

And it doesn't handle the case of letting the local database admin
create users (without giving them access to the rest of the database),
which is what I'm after. -sc

--
Sean Chittenden

On Thu, Mar 25, 2004 at 08:24:59PM -0800, Sean Chittenden wrote:

You can't think that allowing the same name to appear
globally and locally is a good idea.

Actually, I do think it is a good idea.

If I say "GRANT TO foo", who am
I granting privileges to?

SET username_precedence TO LOCAL,GLOBAL; -- I like GLOBAL more than
CLUSTER
GRANT TO foo;
SET username_precedence TO GLOBAL,LOCAL;
GRANT TO foo;

Yes, it possible, but I not sure if this commands dependence is
something wanted and nice. You can use "GRANT TO LOCAL foo" rather than
connect more commands together.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

Sean Chittenden <sean@chittenden.org> writes:

You can't think that allowing the same name to appear
globally and locally is a good idea.

Actually, I do think it is a good idea.

If I say "GRANT TO foo", who am
I granting privileges to?

SET username_precedence TO LOCAL,GLOBAL; -- I like GLOBAL more than
CLUSTER
GRANT TO foo;
SET username_precedence TO GLOBAL,LOCAL;
GRANT TO foo;

And I don't want to say that there is no
difference because they are the same user.

Agreed, they should be the same user.

What? You are contradicting yourself. That "precedence" hack makes
sense only if there is a difference.

That will open up some nasty
security holes, eg, being able to pretend that you are the global
postgres superuser if you can set the password for a local user by the
same name.

Agreed, but if a cluster is using LOCAL USERs, I doubt highly that
CLUSTER/GLOBAL users would be in use much beyond super users. -sc

Exactly my point.  I think that it might be possible for a
locally-privileged DBA to give himself superuser privileges by skating
on this confusion between who is whom.  Once he creates a local user
with the same name as the global superuser, the door is open to problems
--- not only possible bugs in our own code, but plain old human error on
the part of the real superuser.

In short, I say it's a bad idea with no redeeming social value. I can't
see any positive use-case for having local usernames that conflict with
global ones.

regards, tom lane

On Friday 26 March 2004 15:09, Tom Lane wrote:

Sean Chittenden <sean@chittenden.org> writes:

Agreed, but if a cluster is using LOCAL USERs, I doubt highly that
CLUSTER/GLOBAL users would be in use much beyond super users. -sc

Exactly my point.  I think that it might be possible for a
locally-privileged DBA to give himself superuser privileges by skating
on this confusion between who is whom.  Once he creates a local user
with the same name as the global superuser, the door is open to problems
--- not only possible bugs in our own code, but plain old human error on
the part of the real superuser.

Maybe it's me being slow, but are we not being over-complicated here? What's
wrong with saying "database D1 looks up users in local table, D2 in the
global table". If you are connected to D1, then no-one can see the global
userlist.

The global user "richard" cannot log into D1, and the local user "richard" can
log only into D1.

In short, I say it's a bad idea with no redeeming social value. I can't
see any positive use-case for having local usernames that conflict with
global ones.

In a shared-hosting situation, I can see "local super-users" both wanting to
create users called (e.g.) "plone".

--
Richard Huxton
Archonet Ltd

Richard Huxton <dev@archonet.com> writes:

Maybe it's me being slow, but are we not being over-complicated here? What's
wrong with saying "database D1 looks up users in local table, D2 in the
global table". If you are connected to D1, then no-one can see the global
userlist.

Hmm. That would amount to saying that there are no global superusers
for D1, which might be a bit of a problem --- if local DBA paints
himself into a corner, you can't get him out. Backing up a cluster that
has not got global superusers would be a PITA too.

Still, I think you are right that we gotta think outside the box if
we're going to find a way to do this.

regards, tom lane

You can't think that allowing the same name to appear
globally and locally is a good idea.

Actually, I do think it is a good idea.

If I say "GRANT TO foo", who am
I granting privileges to?

SET username_precedence TO LOCAL,GLOBAL; -- I like GLOBAL more than
CLUSTER
GRANT TO foo;
SET username_precedence TO GLOBAL,LOCAL;
GRANT TO foo;

And I don't want to say that there is no
difference because they are the same user.

Agreed, they should be the same user.

^
n't

What? You are contradicting yourself. That "precedence" hack makes
sense only if there is a difference.

Ack, brain-o, you're right: what a difference an "n't" makes.

That will open up some nasty
security holes, eg, being able to pretend that you are the global
postgres superuser if you can set the password for a local user by
the
same name.

Agreed, but if a cluster is using LOCAL USERs, I doubt highly that
CLUSTER/GLOBAL users would be in use much beyond super users. -sc

Exactly my point. I think that it might be possible for a
locally-privileged DBA to give himself superuser privileges by skating
on this confusion between who is whom.

I don't think that's possible though... let's say there are two
databases, hostingco and customer1. That gives us two different
pg_shadow_db tables. On top of that, there is a central
pg_shadow_cluster table that is shared among all databases. Let's
suppose there is:

1) a superuser 'dba' in pg_shadow_cluster (password 'foo');
2) a superuser 'dba' in pg_shadow_db in the hostingco database
(password 'bar'); and,
3) a normal user 'dba' in pg_shadow_db in the customer1 database
(password 'baz').

Here are the scenarios with the UNION example I gave:

pg_shadow_cluster 'dba' case:

*) the 'dba' account in pg_shadow_cluster (dba/cluster) could log in to
all of the databases.
*) the dba/cluster account could only be logged into if someone had the
right password.
*) the UID for the dba/cluster account is irrelevant because any perms
the UID has won't prevent him/her from walking through the entire
database.
*) if a database creates a user with the same UID as the dba/cluster,
the local database admin doesn't gain anything if the dba/cluster
account modifies the database/does work. If the dba/cluster account
does do work in the local database with a shared UID, the UID will
resolve to the local database first preventing the local account with
the shared UID from gaining cluster wide privs (only accounts with
cluster superuser privs should be able to change the resolution from
LOCAL,GLOBAL to GLOBAL,LOCAL).

hostingco 'dba' case:

*) The 'dba' account in pg_shadow_db (dba/hostingco) could log in to
only the local database housing the pg_shadow_db table.

*) The dba/hostingco account behaves identically to the dba/cluster...
I don't think there's a need to even prevent this account from changing
the username resolution because changing databases requires a new
connection where GUC settings are reset.

*) The dba/hostingco account can't log into any other database because
the dba/hostingco account only lives in the database specific
pg_shadow_db table.

*) If the dba/cluster admin logs into the hostingco database, the UID
resolution would be GLOBAL,LOCAL instead of LOCAL,GLOBAL. If there is
a shared UID, the local admin who created the shared UID account only
stands to loose, but can't gain elevated privs.

customer1 'dba' case:

*) Identical to the dba/hostingco case except the account isn't a
superuser.

Have I missed a case? As for the reason for the usefulness of having
shared usernames, the 'www', 'dba', 'admin', 'web', 'php', or
[commoon_application_name] accounts are very popular names for logging
in and I'd like to not deprive customers of ease of use because they're
in a hosted environment instead of a dedicated environment.

Another solution would be to have CREATE USER done by a local admin
create users in the form of 'username@database'. This prevents
duplicate usernames and allows us to use the current hack of local
database users.

Once he creates a local user
with the same name as the global superuser, the door is open to 
problems
--- not only possible bugs in our own code, but plain old human error 
on
the part of the real superuser.

How so? Can you give a scenario where this'd make a difference? I
think putting a trigger on pg_shadow_db to prevent users from mucking
with the UID would be a sufficient anti-foot shooting measure.

-sc

--
Sean Chittenden

Richard Huxton <dev@archonet.com> writes:

Maybe it's me being slow, but are we not being over-complicated here?
What's
wrong with saying "database D1 looks up users in local table, D2 in the
global table". If you are connected to D1, then no-one can see the
global
userlist.

Hmm. That would amount to saying that there are no global superusers
for D1, which might be a bit of a problem --- if local DBA paints
himself into a corner, you can't get him out. Backing up a cluster that
has not got global superusers would be a PITA too.

So you write a script to add a local superuser when you create the
database. Or, we could do it in the createdb/CREATE DATABASE code - just
clone the "postgres" user. Last resort, I'm sure the files themselves
could be hacked if you had to. If people are running a shared environment,
it's fair to assume they know a little of what they're doing.

Still, I think you are right that we gotta think outside the box if
we're going to find a way to do this.

More a case of thinking under the box here.

Sean Chittenden <sean@chittenden.org> writes:

Another solution would be to have CREATE USER done by a local admin
create users in the form of 'username@database'. This prevents
duplicate usernames and allows us to use the current hack of local
database users.

Yeah, I think it would be reasonable to leave that facility as-is and
invent a category of user privileges that only allows creation/deletion
of local usernames. We'd have to think about how that should interact
with other superuser attributes such as the ability to bypass
privileges. But breaking down "superuserness" into finer-grained
privileges has been on the wish list for awhile.

Once he creates a local user with the same name as the global
superuser, the door is open to problems --- not only possible bugs in
our own code, but plain old human error on the part of the real
superuser.

How so? Can you give a scenario where this'd make a difference?

Well, it's the standard sort of attack where you try to trick a user
with more privs than you into doing something he'd not do if he was
aware of who/what he was really doing it to. Something like granting
privileges to a local user when he thought he was granting to a global
user, or vice versa. Or making the wrong user a member of a group.

I'm not sure that this is actually very probable, if the usage scenario
is that global users are always superusers --- there'd seldom be any
reason to go granting them any additional privileges. But if we are
also thinking of having multiple categories of user privileges then it's
less far-fetched.

Other possible problems include resetting the password of the wrong
user.  This would be particularly bad if a database's local superuser
can choose the setting of the "encrypt passwords by default" GUC switch
--- he might have the opportunity to see the cleartext password of some
global user.  Obviously that hole can be plugged now that I've pointed
it out, but what other ones are there?

regards, tom lane

Another solution would be to have CREATE USER done by a local admin
create users in the form of 'username@database'. This prevents
duplicate usernames and allows us to use the current hack of local
database users.

Yeah, I think it would be reasonable to leave that facility as-is and
invent a category of user privileges that only allows creation/deletion
of local usernames. We'd have to think about how that should interact
with other superuser attributes such as the ability to bypass
privileges. But breaking down "superuserness" into finer-grained
privileges has been on the wish list for awhile.

How about splitting usesuper into just the following two privileges:

*) createuser
*) usesuper

A normal user can be the owner of a database so there's no need for an
"admin" like priv. createuser allows the user to create other users
with the same or lesser privs, and usesuper is basically exactly what
we've got now. How's that? It doesn't give us database local
usernames, but it's a big first step toward virtual hosting.

Once he creates a local user with the same name as the global
superuser, the door is open to problems --- not only possible bugs in
our own code, but plain old human error on the part of the real
superuser.

How so? Can you give a scenario where this'd make a difference?

Well, it's the standard sort of attack where you try to trick a user
with more privs than you into doing something he'd not do if he was
aware of who/what he was really doing it to. Something like granting
privileges to a local user when he thought he was granting to a global
user, or vice versa. Or making the wrong user a member of a group.

I'm not sure that this is actually very probable, if the usage scenario
is that global users are always superusers --- there'd seldom be any
reason to go granting them any additional privileges. But if we are
also thinking of having multiple categories of user privileges then
it's
less far-fetched.

Eh, yes and no. The cluster database admin is the last line of
defense... the local dba is who users would talk to and even then, the
cluster database admin should only restore local admin privs to the
local dba. Beyond that, the cluster dba shouldn't be involved with
much... separating the UID ranges for global vs local users wouldn't be
a bad idea the more I think about it, even though I don't think there's
much of a risk except for possibly to the local database integrity.

Other possible problems include resetting the password of the wrong
user.  This would be particularly bad if a database's local superuser
can choose the setting of the "encrypt passwords by default" GUC switch
--- he might have the opportunity to see the cleartext password of some
global user.  Obviously that hole can be plugged now that I've pointed
it out, but what other ones are there?

Other holes... none that I can think of beyond:

*) changing privs - accidentally elevating a global user's privs when
thinking it was a local user that was getting its privs elevated.

*) changing the wrong password - same prob as changing the privs

*) granting privs to structures to the wrong UID because of incorrect
username resolution

Beyond leaving admins with possible land mines that needs to be well
documented... but that's the trade off for the flexibility, IMHO.
Proxy authentication has the same pit falls in kerberos or SASL but
that hasn't stopped the security community from adopting kerberos in
high security installations.... the pit falls just need to be
documented.

A lot of this could be subverted if pg_hba.conf was also stored in the
database and could be used to limit what users could connect to what
databases and from what IPs. :)

BTW, has it been discussed to add a way in pg_hba.conf to limit the
number of connections per IP address or per subnet mask? 2 connections
per /32 or 4 connections per /30?

-sc

--
Sean Chittenden