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