Thread: One Role, Two Passwords
Hello list, I wanted to test the waters on how receptive people might be to an extension that would allow Postgres to support two passwords for a given role. I have recently encountered a case where this would be highly useful when performing rolling password upgrades across many client applications and/or application instances. It is possible (as far as I know) to get around some of the sticker parts of this with some teeth gnashing, using some CREATE ROLE ... IN ROLE dancing, but I wanted to see if there was any interest in supporting this "for real." This design is not uncommon, one example is Amazon Web Services (e.g. EC2, S3), whereby one identification key can have many, independently revokable secret keys. I haven't given much thought to the mechanism yet, rather, I am just trying to assess gut reactions on the principle. -- fdr
On 01/20/2011 05:28 PM, Daniel Farina wrote: > Hello list, > > I wanted to test the waters on how receptive people might be to an > extension that would allow Postgres to support two passwords for a > given role. I have recently encountered a case where this would be > highly useful when performing rolling password upgrades across many > client applications and/or application instances. > > It is possible (as far as I know) to get around some of the sticker > parts of this with some teeth gnashing, using some CREATE ROLE ... IN > ROLE dancing, but I wanted to see if there was any interest in > supporting this "for real." > > This design is not uncommon, one example is Amazon Web Services (e.g. > EC2, S3), whereby one identification key can have many, independently > revokable secret keys. > > I haven't given much thought to the mechanism yet, rather, I am just > trying to assess gut reactions on the principle. Have you thought of trying to use an external auth source like LDAP for such a scheme? cheers andrew
On Thu, Jan 20, 2011 at 2:45 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > Have you thought of trying to use an external auth source like LDAP for such > a scheme? I have thought about that, although LDAP is the only one that came to mind (I don't know a whole lot of systems in detail, only by name...so suggestions welcome for low-administrative-overhead variants). I also briefly considered investigating what hooks I could exploit for auth & auth; I do not know these very well right now. It would be ideal to not have to run another full bore set of services to support phased password rotation, though -- in this case it would still appear be better, but frustrating to use the CREATE ROLE ... IN ROLE dance. -- fdr
Daniel Farina <drfarina@acm.org> writes:
> I wanted to test the waters on how receptive people might be to an
> extension that would allow Postgres to support two passwords for a
> given role.
Not very. Why don't you just put two roles in the same group?
regards, tom lane
On Thu, Jan 20, 2011 at 3:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Daniel Farina <drfarina@acm.org> writes: >> I wanted to test the waters on how receptive people might be to an >> extension that would allow Postgres to support two passwords for a >> given role. > > Not very. Why don't you just put two roles in the same group? How does this work with newly created objects? Is there a way to have them default objects to a different owner, the parent of the two roles? It is highly desirable that no ALTER <OBJECT> statements should need issuing after the password transition is complete. As-is, though, I don't understand how that would be possible. It would also be nice to be able to change a password without changing the role name. In the case of password rotation, the goal would be to drop the old password after all clients have had reasonable chance to get an update. One could work around by generating new username+password pairs constantly, but there are conveniences to having a stable public-identifier for a role in addition to a private secret used to authenticate it (or, as is the case with this proposal, more than one acceptable private secrets). Changing the username all the time to facilitiate this basically renders it part of a unstable, two-part secret key, and the job of having a stable, public identifier is pushed up the application stack. -- fdr
> How does this work with newly created objects? Is there a way to have
> them default objects to a different owner, the parent of the two
> roles?
No, but you could easily assign default permissions.
> In the case of password rotation, the goal would be to
> drop the old password after all clients have had reasonable chance to
> get an update. One could work around by generating new
> username+password pairs constantly, but there are conveniences to
> having a stable public-identifier for a role in addition to a private
> secret used to authenticate it
I guess I don't really understand what the real-world use case for this is.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
On Thu, Jan 20, 2011 at 4:35 PM, Josh Berkus <josh@agliodbs.com> wrote: > >> How does this work with newly created objects? Is there a way to have >> them default objects to a different owner, the parent of the two >> roles? > > No, but you could easily assign default permissions. > >> In the case of password rotation, the goal would be to >> drop the old password after all clients have had reasonable chance to >> get an update. One could work around by generating new >> username+password pairs constantly, but there are conveniences to >> having a stable public-identifier for a role in addition to a private >> secret used to authenticate it > > I guess I don't really understand what the real-world use case for this is. Here's one: running a cluster with dynamic resource provisioning and diverse applications, whereby one has the following constraints: * Ensure all existing open database sessions operate as before without interruption * Not be able to ensure after any one point that all *new* connection attempts will be with the new set of credentials * Ensure that all database objects created using new or old credentials are indistinguishable * Eventual Retirement of old credentials without having to issue ALTER statements (or really statements of any kind...) against application schema objects. I don't see precisely how I can do this. -- fdr
> * Eventual Retirement of old credentials without having to issue ALTER
> statements (or really statements of any kind...) against application
> schema objects.
OK, that's a different goal. You want to be able to expire passwords
with an overlap period. That's quite different from wanting an
indefinfite number of passwords per role.
Mind you, the main way to do this right now ... and where you're going
to get pushback ... is using LDAP, ActiveDirectory or similar. At a
certain point we have to draw the line and say "PostgreSQL is not an
authtenication server". I don't know exactly where that line is, but
recognize that you're arguing about where to draw it.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
On Thu, Jan 20, 2011 at 5:32 PM, Josh Berkus <josh@agliodbs.com> wrote: > >> * Eventual Retirement of old credentials without having to issue ALTER >> statements (or really statements of any kind...) against application >> schema objects. > > OK, that's a different goal. You want to be able to expire passwords > with an overlap period. That's quite different from wanting an > indefinfite number of passwords per role. Correct; although I don't see a reason to strictly abide by two. Still, it would get most jobs done. > Mind you, the main way to do this right now ... and where you're going > to get pushback ... is using LDAP, ActiveDirectory or similar. At a > certain point we have to draw the line and say "PostgreSQL is not an > authtenication server". I don't know exactly where that line is, but > recognize that you're arguing about where to draw it. Quite correct, as I conceded to Andrew initially. PAM may also be an option to work around. The problem is that running a reliable, centralized LDAP service is not justifiable as compared to role mangling on a per-node level, and the role mangling seems has some shortcomings that are negotiable with gritted teeth. A goldilocks case so far of "too hot" and "too cold" I think is exhibited here. I do not think the problem unreasonable and it will become increasingly common on larger and more diverse Postgres deployments, especially on hosted (do I need to say cloud?) infrastructure which cannot make as many consistency guarantees about processes starting all over the place. For that reason I have brought it to -hackers. -- fdr
* Daniel Farina (drfarina@acm.org) wrote:
> I have thought about that, although LDAP is the only one that came to
> mind (I don't know a whole lot of systems in detail, only by name...so
> suggestions welcome for low-administrative-overhead variants).
My preference is Kerberos and I find that it works quite well and isn't
too much overhead, once you understand it. :)
Thanks,
Stephen
On Thu, Jan 20, 2011 at 8:32 PM, Josh Berkus <josh@agliodbs.com> wrote: > >> * Eventual Retirement of old credentials without having to issue ALTER >> statements (or really statements of any kind...) against application >> schema objects. > > OK, that's a different goal. You want to be able to expire passwords > with an overlap period. That's quite different from wanting an > indefinfite number of passwords per role. > > Mind you, the main way to do this right now ... and where you're going > to get pushback ... is using LDAP, ActiveDirectory or similar. At a > certain point we have to draw the line and say "PostgreSQL is not an > authtenication server". I don't know exactly where that line is, but > recognize that you're arguing about where to draw it. Bingo. I think it would be great to integrate with some external authentication solution that would support this, but I'm not that keen on supporting it in the server - not because I don't think it's useful, but because I think there are 20 other equally weird, equally useful things that someone might want to do in the alternative, and I think it'll be unmanageable to try to support them all. And next year someone will think of another 20. It strikes me that it would be useful to have a GUC that sets the owner of any new objects you create (much as you can control their default schemas using search_path). Obviously, you'd need to restrict it so that it wouldn't allow you to create an object owned by a role to which you couldn't have given an object owned by yourself. But this is what Florian was trying to get at with his much-maligned ALTER DATABASE .. SET ROLE, I think, and it seems to me that it would help with this case, too. It's always struck me that using multiple database logins would create all sorts of inconsistencies with different objects ending up owned by different users, but I guess until recently I was under the impression I was the only one who had an issue with that. It seems not. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
* Robert Haas (robertmhaas@gmail.com) wrote:
> It strikes me that it would be useful to have a GUC that sets the
> owner of any new objects you create (much as you can control their
> default schemas using search_path).
There was a great deal of discussion along these lines over the summer
of '09 (iirc) with regard to default owners and with the default
privileges patch. I encourage you to try and make it happen though.
> It's always struck me that using multiple
> database logins would create all sorts of inconsistencies with
> different objects ending up owned by different users, but I guess
> until recently I was under the impression I was the only one who had
> an issue with that. It seems not.
Uh, no, you're definitely not alone. It's a huge pain in the ass,
imv.
Thanks,
Stephen
On Thu, Jan 20, 2011 at 9:07 PM, Stephen Frost <sfrost@snowman.net> wrote: > * Robert Haas (robertmhaas@gmail.com) wrote: >> It strikes me that it would be useful to have a GUC that sets the >> owner of any new objects you create (much as you can control their >> default schemas using search_path). > > There was a great deal of discussion along these lines over the summer > of '09 (iirc) with regard to default owners and with the default > privileges patch. I encourage you to try and make it happen though. I'm not likely to write a patch for it, but if someone else writes one I would be willing to (a) support it and (b) subject to consensus, commit it. To 9.2. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Thu, Jan 20, 2011 at 6:12 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Thu, Jan 20, 2011 at 9:07 PM, Stephen Frost <sfrost@snowman.net> wrote: >> * Robert Haas (robertmhaas@gmail.com) wrote: >>> It strikes me that it would be useful to have a GUC that sets the >>> owner of any new objects you create (much as you can control their >>> default schemas using search_path). >> >> There was a great deal of discussion along these lines over the summer >> of '09 (iirc) with regard to default owners and with the default >> privileges patch. I encourage you to try and make it happen though. > > I'm not likely to write a patch for it, but if someone else writes one > I would be willing to (a) support it and (b) subject to consensus, > commit it. Wouldn't this require a client application to issue the GUC setting? Or could I somehow tell a role "You create objects as this user, and you cannot change this." -- fdr
On Jan21, 2011, at 03:03 , Robert Haas wrote: > It strikes me that it would be useful to have a GUC that sets the > owner of any new objects you create (much as you can control their > default schemas using search_path). Obviously, you'd need to restrict > it so that it wouldn't allow you to create an object owned by a role > to which you couldn't have given an object owned by yourself. We could simply refuse to set default_owner to a rule the current user don't inherit from. If set via an ALTER DATABASE SET ROLE the setting would then simply be (silently) ignored - or at least this is how it work for ALTER DATABASE SET ROLE. > But > this is what Florian was trying to get at with his much-maligned ALTER > DATABASE .. SET ROLE, I think, and it seems to me that it would help > with this case, too. It's *precisely* what I was trying to get at! Great idea! It seems to avoid most of the issues people had with my ALTER DATABASE SET ROLE trick, too. > It's always struck me that using multiple > database logins would create all sorts of inconsistencies with > different objects ending up owned by different users, but I guess > until recently I was under the impression I was the only one who had > an issue with that. It seems not. Certainly not :-) best regards, Florian Pflug
On Jan21, 2011, at 03:14 , Daniel Farina wrote: > On Thu, Jan 20, 2011 at 6:12 PM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Thu, Jan 20, 2011 at 9:07 PM, Stephen Frost <sfrost@snowman.net> wrote: >>> * Robert Haas (robertmhaas@gmail.com) wrote: >>>> It strikes me that it would be useful to have a GUC that sets the >>>> owner of any new objects you create (much as you can control their >>>> default schemas using search_path). >>> >>> There was a great deal of discussion along these lines over the summer >>> of '09 (iirc) with regard to default owners and with the default >>> privileges patch. I encourage you to try and make it happen though. >> >> I'm not likely to write a patch for it, but if someone else writes one >> I would be willing to (a) support it and (b) subject to consensus, >> commit it. > > Wouldn't this require a client application to issue the GUC setting? > Or could I somehow tell a role "You create objects as this user, and > you cannot change this." You could do ALTER ROLE SET default_owner TO <whatever>. Nothing would prevent the user from resetting default_owner, though - but do you really need to protect against that? best regards, Florian Pflug
* Daniel Farina (drfarina@acm.org) wrote:
> On Thu, Jan 20, 2011 at 6:12 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> > I'm not likely to write a patch for it, but if someone else writes one
> > I would be willing to (a) support it and (b) subject to consensus,
> > commit it.
>
> Wouldn't this require a client application to issue the GUC setting?
> Or could I somehow tell a role "You create objects as this user, and
> you cannot change this."
Errr, well, ok, this is curious.
gis=> alter user sfrost set role gis;
ALTER ROLE
gis=> ^D\q
beren:/home/sfrost> psql --cluster 8.4/main -d gis
psql (8.4.5)
Type "help" for help.
gis=> show role;role
------gis
(1 row)
I'm trying to figure out if that'd help you or not, but it might. The
problem is that you'd have to change both the login userid and the
password, but you could make everything both those login roles do be
'as' some third role that's the 'real' role for the user.
I'm still looking for 'default owner for schema', but I guess that's
just me.
Thanks,
Stephen
On Thu, Jan 20, 2011 at 08:55:42PM -0500, Daniel Farina wrote: > The problem is that running a reliable, > centralized LDAP service is not justifiable as compared to role > mangling on a per-node level, and the role mangling seems has some > shortcomings that are negotiable with gritted teeth. Wandering slightly off-topic here... I often find RADIUS is an easier way to setup a highly available (and perhaps too flexible) AuthN/AuthZ service, but I've never used PG's RADIUS implementation. I was curious and I took a look at the documentation... I can't tell if PG supports querying a secondary RADIUS server? I don't see how I would do it with the syntax here ... http://developer.postgresql.org/pgdocs/postgres/auth-methods.html Are multiple servers not supported? Garick
On Fri, 2011-01-21 at 11:00 -0500, Garick Hamlin wrote: > I can't tell if PG supports querying a secondary RADIUS server? > > I don't see how I would do it with the syntax here ... > http://developer.postgresql.org/pgdocs/postgres/auth-methods.html > > Are multiple servers not supported? > > Garick From a quick look at the source code it does not look like it can. Andreas
On Thu, Jan 20, 2011 at 6:19 PM, Stephen Frost <sfrost@snowman.net> wrote: > Errr, well, ok, this is curious. > > gis=> alter user sfrost set role gis; > ALTER ROLE > gis=> ^D\q > beren:/home/sfrost> psql --cluster 8.4/main -d gis > psql (8.4.5) > Type "help" for help. > > gis=> show role; > role > ------ > gis So far, I think this does exactly what you think it would... This is how I think a mostly-transparent one-role-two-password (ish) system would look like using this property, using a group, as Tom suggested: -- Note: This role does not have LOGIN set CREATE ROLE public_facing_name; -- The first set of credentials CREATE ROLE first_arbitrary_name IN ROLE public_facing_name LOGIN PASSWORD 'first_secret'; ALTER ROLE first_arbitrary_name SET ROLE public_facing_name; -- The second set of credentials CREATE ROLE second_arbitrary_string IN ROLE public_facing_neme LOGIN PASSWORD 'second_secret'; ALTER ROLE second_arbitrary_name SET ROLE public_facing_name; When one logs in as one_arbitrary_name or second_arbitrary_name, all CREATE statements -- and indeed, all privilege checks -- will take place against public_facing_name. No objects should ever get created under the roles "first_arbitrary_name" or "second_arbitrary_name," and no ownership ever assigned to them, unless the client runs SET ROLE to un-do what was done. This would be annoying (when dropping sets of credentials, or when ownership-specific privileges apply to one set of credentials but not the other) but may not occur much at large. Thoughts? -- fdr