Re: Automatically assuming a specific role after connecting

* 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...

> 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.

> >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...).

    Thanks,

        Stephen