Re: [HACKERS] db_user_namespace a temporary measure
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
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
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
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
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
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
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
* 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
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
* 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
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
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
* 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
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
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
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
* 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
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
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
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
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
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
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
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
* 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
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
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
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
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
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
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
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
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
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
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
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
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
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
[HACKERS] db_user_namespace a temporary measure
Hi, I've noticed that db_user_namespace has had the following note attached to it since 2002: This feature is intended as a temporary measure until a complete solution is found. At that time, this option will be removed. It will be 12 years this year since this temporary measure was added. I'm just wondering, is there any complete solution that anyone had in mind yet? Or should this just be deprecated? Thanks Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
