Re: Automatically assuming a specific role after connecting

Stephen Frost wrote:
> * Florian G. Pflug (fgp@phlo.org) wrote:
>> I can do that with "alter user <user> set role <whatever>" too...
>> But I'd like my users to be able to connect as either role "dev" or
>> role "admin", depending on the task they want to do.
>
> Alright, can you describe *exactly* what you'd want to see then?  Is
> this a new command-line option to psql (perhaps something like -v?)?  Or
> do you need it to be supported by libpq through a new connect-string
> option (for, say, ODBC, or DBD/DBI in perl, etc.)?  Both?

I imagine the following behaviour:
When a new connection to postgres is opened, passing the username
"user/role", then the postmaster
1) Checks if there is a user
named "user/role" (literally). If such a user exists than the user is
authenticated is the same way as it is now.
2) Otherwise, the "/role" part is split of, and postgres check for the
existance of just "user". If it exists, and can be authenticated via
whatever means are configure in pg_hba.conf, then a new session is
started for the user "user", just as if the user had just users "user"
(instead of "user/role") is his username. But, as an additional step
after creating a session for the user, "set role <role>" is executed in
the new session.

This would allow all developers in the company I work for to connect
to the DB as role "dev" - which guarantees that everyone has the same
permissions on all db objects, no matter how created them (because
they'll all have owner "dev). But still, every developer has his own
user _with_his_own_password_. If a developer quits, his user is deleted
from the central ldap repository, and he instantly looses access to all
databases. If, on the other hand, all those developers directly
connected as role "dev" (as tom lane suggested), then the password of
this role would need to be changed whenever a developer leaves the company.

The same effect could, of course, be reached by implementing an option
to set variables upon login in every client. But this would mean
changing every client (psql, pgadmin, pgodbc, ....) while my approach
would take care of this on the server.

> A generic "set this SQL variable after connecting" might not be a bad
> option for psql to have.  I know I'd like to see something like that for
> pg_dump and pg_restore so I can "set role" before dumping or restoring.
For the special case of pg_restore, being able to specify a "predump sql
snipped", and a "postdump sql snippet" would be nice. I'd e.g. allow one
to wrap the restoration in a transaction with predump="begin" and
postdump="commit".

But, for the reasons stated above, I'd prefer a server-side approach for
setting the initial role.

greetings, Florian Pflug