Re: One Role, Two Passwords

On Thu, Jan 20, 2011 at 6:19 PM, Stephen Frost  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