Thread: Automatically assuming a specific role after connecting to pg

I'd like to be able to connect to postgres, and automatically assume a specific role.

I imagine something like
create role myuser ;
create role dev noinherit ;
create role admin noinherit superuser ;
grant dev to myuser ;
grant admin to myuser ;

Now, I'd like a connect as "myuser/admin" to be equivalent to a connect as "myuser"
and then issuing "set role admin". The same should be true for "myuser/dev".

Would it be hard to patch postgres to do that? And would such a patch have a chance
of being accepted into 8.2 (or 8.3)

greetings, Florian Pflug

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

            regards, tom lane

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

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

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.

    Thanks,

        Stephen

* Florian G. Pflug (fgp@phlo.org) wrote:
> I'd like to be able to connect to postgres, and automatically assume a
> specific role.

Couldn't you just have 'SET ROLE <whatever>' in the user's .psqlrc?  At
least, if that's how they're connecting I think that'd work...

    Enjoy,

        Stephen

Stephen Frost wrote:
> * Florian G. Pflug (fgp@phlo.org) wrote:
>
>>I'd like to be able to connect to postgres, and automatically assume a
>>specific role.
>
> Couldn't you just have 'SET ROLE <whatever>' in the user's .psqlrc?  At
> least, if that's how they're connecting I think that'd work...
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.

greetings, Florian Pflug

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

* 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

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

Florian G. Pflug wrote:
>
> Do you see any other way via which I could archive my desired result?
> (Apart from modifying every client in existence)

Could you modify pgpool to act as a wrapper for this?

--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:
> Florian G. Pflug wrote:
>>
>> Do you see any other way via which I could archive my desired result?
>> (Apart from modifying every client in existence)
>
> Could you modify pgpool to act as a wrapper for this?
Hm.. should be possible.. I'll look into this. Thanks for the hint.

greetings, Florian Pflug