Re: [HACKERS] db_user_namespace a temporary measure

2014-03-13 Thread Andres Freund
On 2014-03-12 20:54:36 -0400, Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
  On Wed, Mar 12, 2014 at 9:19 AM, Andres Freund and...@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


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Magnus Hagander
On Mar 12, 2014 1:46 AM, Josh Berkus j...@agliodbs.com wrote:

 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.


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?

/Magnus


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Alvaro Herrera
Tom Lane wrote:
 Andrew Dunstan and...@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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Andres Freund
On 2014-03-12 10:03:42 -0300, Alvaro Herrera wrote:
 Tom Lane wrote:
  Andrew Dunstan and...@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


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Tom Lane
Jaime Casanova ja...@2ndquadrant.com writes:
 On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane t...@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


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Tom Lane
Magnus Hagander mag...@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


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Magnus Hagander
On Wed, Mar 12, 2014 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Magnus Hagander mag...@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.


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.


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.

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

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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Stephen Frost
* Magnus Hagander (mag...@hagander.net) wrote:
 On Wed, Mar 12, 2014 at 3:52 PM, Tom Lane t...@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


signature.asc
Description: Digital signature


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes:
 On Wed, Mar 12, 2014 at 3:52 PM, Tom Lane t...@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


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote:
 Magnus Hagander mag...@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


signature.asc
Description: Digital signature


Re: [HACKERS] db_user_namespace a temporary measure

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

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


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Andrew Dunstan


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



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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Stephen Frost
* Josh Berkus (j...@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.

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


signature.asc
Description: Digital signature


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Josh Berkus
On 03/12/2014 11:25 AM, Stephen Frost wrote:
 * Josh Berkus (j...@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


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Tom Lane
Andrew Dunstan and...@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


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Josh Berkus
On 03/12/2014 11:40 AM, Tom Lane wrote:
 Andrew Dunstan and...@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


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


Re: [HACKERS] db_user_namespace a temporary measure

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


signature.asc
Description: Digital signature


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Robert Haas
On Wed, Mar 12, 2014 at 9:19 AM, Andres Freund and...@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


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-12 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Wed, Mar 12, 2014 at 9:19 AM, Andres Freund and...@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


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Magnus Hagander
On Sun, Mar 9, 2014 at 9:00 PM, Thom Brown t...@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/


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes:
 On Sun, Mar 9, 2014 at 9:00 PM, Thom Brown t...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Magnus Hagander
On Tue, Mar 11, 2014 at 2:40 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Magnus Hagander mag...@hagander.net writes:
  On Sun, Mar 9, 2014 at 9:00 PM, Thom Brown t...@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/


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes:
 On Tue, Mar 11, 2014 at 2:40 PM, Tom Lane t...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Andrew Dunstan


On 03/11/2014 09:57 AM, Tom Lane wrote:

Magnus Hagander mag...@hagander.net writes:

On Tue, Mar 11, 2014 at 2:40 PM, Tom Lane t...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Stephen Frost
* Andrew Dunstan (and...@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


signature.asc
Description: Digital signature


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Andrew Dunstan


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Tom Lane
Josh Berkus j...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Tom Lane
Andrew Dunstan and...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Andrew Dunstan


On 03/11/2014 07:41 PM, Tom Lane wrote:

Andrew Dunstan and...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Josh Berkus
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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Andrew Dunstan


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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Tom Lane
Andrew Dunstan and...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Aidan Van Dyk
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 schemaEvery 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 mag...@hagander.netwrote:

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

 Magnus Hagander mag...@hagander.net writes:
  On Sun, Mar 9, 2014 at 9:00 PM, Thom Brown t...@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,
ai...@highrise.ca   command like a king,
http://www.highrise.ca/   work like a
slave.


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Andrew Dunstan


On 03/11/2014 11:06 PM, Tom Lane wrote:

Andrew Dunstan and...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Jaime Casanova
On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane t...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Andrew Dunstan


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

On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane t...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread Jaime Casanova
On Tue, Mar 11, 2014 at 10:52 PM, Andrew Dunstan and...@dunslane.net wrote:

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

 On Tue, Mar 11, 2014 at 10:06 PM, Tom Lane t...@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


Re: [HACKERS] db_user_namespace a temporary measure

2014-03-11 Thread David Johnston
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