On Tue, Feb 1, 2011 at 2:49 PM, Christian Ullrich ch...@chrullrich.net wrote:
* Christopher Hotchkiss wrote:
I would like to propose (and volunteer to do if its considered to be a
decent idea) to extend the mapping of users to roles in the
pg_ident.conf to incorporate groups. This would allow any user who
belonged to a particular group in certain authentication systems to be
Be aware that of the ten authentication mechanisms PostgreSQL supports
today, only SSPI (yes, really) can provide you a group list directly from
the authentication result. For everything else, you would have to have a
hook for plugging in system-specific code for determining the group
memberships.
My environment is a SSPI environment and its api is where I got the
idea from. I guess keeping Postgres portable would precluding using
pam_groups (or another system specific method) get the same
information out of the unix world.
mapped to a role using the existing regular expression support that
exists today. This would also allow the offloading of the creation of
So this would still result in only one active role? How about taking all the
groups and using them as roles, without considering pg_auth_members at all?
I was planning to use a set of roles setup in postgres that could map
to the os/sspi groups. Those roles would hold the grant information
for the tables and functions.
new users for the system to an external mechanism instead of needing to
create a new role in the database for each person. At the same time by
allowing the mapping to match based off of groups the offloading of
authentication would still allow for restrictions of who could connect
to the database.
How? If you delegate the decision on what is a valid user to the external
mechanism and take pg_authid out of the picture, then everyone must be let
in, and have the privileges assigned to PUBLIC at least. Sure, pg_hba.conf
would still apply, but in practice everybody would end up with all users.
Look at what SQL Server does. I'm sure they would rather get rid of their
own user management and leave it all to the OS. Yet even though they can
grant privileges to OS groups, that still only works by explicitly mapping
them to database-internal authentication IDs. I think this may well be the
reason for that.
Today as far as I can tell, when you setup SSPI if you have a valid
user account the only way to restrict access to postgres is via the
pg_hba or pg_ident files. This requires either configuring each user
as a postgres role or mapping to a generic account (or many). This is
driven entirely off of the user name and which makes administration
painful since you have to tweak the pg_hba.conf or pg_ident.conf file
for each user or you encode authorization information into the
username which makes user removal equally painful.
A second enhancement that would be useful would be despite what role the
database logs the user in as the server sets a read only session
variable similar to application_name could store the system username or
username plus groups for use in audit triggers.
This rules out the use of connection pools, except if they reproduce the
entire group mapping logic and collect client sessions based on what role
they would end up in the database.
Thats true, in that case having the client set application_name
would probably be a better route to communicate to the server the real
user of the application. That approach makes sense for web
applications where you can trust the code that is connecting to the
database to communicate user information correctly. For a thick client
however the user is logging into the system and could create a
secondary database connection with a maliciously set username to fool
an audit system. Thats why I thought this would be a useful
enhancement.
Thanks!
--
Christopher Hotchkiss chotchki
http://www.chotchki.us
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers