Stephen Frost wrote:
* Florian G. Pflug (fgp@xxxxxxxx) 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
