Re: Authentication Enhancement Proposal

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

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

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

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

--
Christian