Re: Automatically assuming a specific role after connecting - Mailing list pgsql-general

Stephen Frost wrote:
> * Florian G. Pflug (fgp@phlo.org) wrote:
>> Stephen Frost wrote:
>>> 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.
>
> ehhh, I'm not so sure this is a good idea.  For one thing, I'm not sure
> how well it would interact with Kerberos and SASL which support having
> /'s in the username too but not quite in the same way...
Well, "/" could be replaces with something else, or even made configurable.

>> 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.
>
> Your approach would have to be handled cleanly by all the different
> authentication mechanisms too (krb5, ident, etc, not just md5 or
> password), some of which use external libraries and might not do what
> you want.  I'm not really sure I see much advantage to changing the
> server for this case.
The logic described above is pretty much orthogonal to any
authentication scheme. The postmaster would just have to try to
authenticate a user, and if this fails it would retry with the "/role"
part stripped off. If that succeeds, it'd have to somehow tell the
backend to execute "set role <role" upon startup.

>>> 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.
>
> You're really just trying to overload one of the existing, defined
> methods to also do this which could *break* some applications (such as
> something which expects to know the username after connection and does
> things based on it would be confused when suddenly the user is "abc"
> instead of "abc/xyz" like it expected...).
I'd consider the chance of breakage to be relatively small - and nobody
would be forced to use that feature (It could be turned on by a switch
in postgresql.conf).

Do you see any other way via which I could archive my desired result?
(Apart from modifying every client in existence)

greetings, Florian Pflug