Re: Authentication Enhancement Proposal

* Christopher Hotchkiss wrote:

> On Tue, Feb 1, 2011 at 2:49 PM, Christian Ullrich<chris(at)chrullrich(dot)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.

Well, I think if you build with PAM support there is no reason not to
use PAM features to get the group membership information, as long as you
can handle the situation where the PAM stack does not contain the module
you need.

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

So you want to separate authentication and authorization identity, and
use the system user name as the authentication identity, but choose the
authorization identity based on some ruleset applied to the group
memberships of the authenticated system user.

The purpose of pg_ident.conf currently is to declare combinations of
authenticated user name and claimed database user name (from the hello
packet) that are allowed to connect. What you would need is a ruleset
that says "if these conditions are met, the user will be assigned these
roles". The claimed user name from the hello packet would be irrelevant,
and the client would have no control over which identity it would use in
the database, except that it could SET ROLE later.

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

Not exactly. I just remembered there is a CONNECT privilege for
databases, granted by default to PUBLIC.

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

Postgres already has session_user (authenticated user name) and
current_user (what the user last SET ROLE to). This would just add
another one (connected_user?)

--
Christian