Re: Automatically assuming a specific role after connecting

Tom Lane wrote:
> "Florian G. Pflug" <fgp@phlo.org> writes:
>
>>I'd like to be able to connect to postgres, and automatically assume a specific role.
>
> Why don't you just connect as that role to begin with?  This seems like
> a pretty low-value frammish.

Because I want each user to have his or her own password. If everyone connects as the same
role, they'll all have to know the same password, and this password will have to be changed
every time a user (which are employees of a company) leaves the company.

I'd like to authenticate all postgres servers against a ldap directory (using pam). But if everyone
works as their own user in the database, it won't be possible for user B to e.g. truncate a
table created by A, because the table is owned by A. I solved that for now by creating a "dev" role,
and doing "alter user myuser set role dev".

Now, in theory if the need administrative access, they'd just need to issue a "set role admin" after
connecting - but some clients like pgadmin don't support that. Instead of fixing all clients, I wanted
a solution that works with existing clients, which lead to the "user/role" idea.

greetings, Florian Pflug