To All,
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 mapped to a role using the
existing regular expression support that exists today. This would also allow
the offloading of the creation of 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.

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.

For example:
User Bob is a sales clerk and needs to login to the database... the database
client uses his existing login credentials (sspi, gssapi, kerberos or pam)
and attempts authentication to the database. The database takes the incoming
user name appends the groups Bob belongs to and finds a user map that maps
him to a generic role for access privileges into the database.

Changes Needed:
- Add support for an option "append_groups" to the sspi, gssapi, kerberos
and pam authentication methods in pg_hba.conf
- After the authentication process if append_groups is enabled, use the apis
for those authentication methods to append all groups for the user in the
following format
- - USERNAME -> USERNAME:[GROUP][,GROUP]...
- Add another session variable similar to session_user and current_user that
stores the username + group that cannot be reset without superuser
privileges.

How does this proposal sound?
--
Christopher Hotchkiss "chotchki"
http://www.chotchki.us

Search Discussions

  • Tom Lane at Feb 1, 2011 at 5:39 pm

    Christopher Hotchkiss writes:
    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.
    Um ... there isn't any real distinction between users and groups
    anymore, they're all roles. So it's not clear to me what you're
    proposing that doesn't work now. Or at least could be made to work,
    possibly not in quite the way you're thinking, but using the already
    existing features.

    regards, tom lane
  • Christopher.Hotchkiss at Feb 1, 2011 at 7:13 pm

    On Feb 1, 2011, at 12:39 PM, Tom Lane wrote:

    Christopher Hotchkiss <christopher.hotchkiss@gmail.com> writes:
    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.
    Um ... there isn't any real distinction between users and groups
    anymore, they're all roles. So it's not clear to me what you're
    proposing that doesn't work now. Or at least could be made to work,
    possibly not in quite the way you're thinking, but using the already
    existing features.

    regards, tom lane
    I'm sorry I wasn't clear Tom. I was referring to allowing the mapping of operating system users/groups to postgres roles. Today as far as I can tell only os users are mappable not the groups. Thoughts?

    Thanks,
    Christopher Hotchkiss
  • Christian Ullrich at Feb 1, 2011 at 8:16 pm

    * 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
  • Christopher Hotchkiss at Feb 1, 2011 at 8:51 pm

    On Tue, Feb 1, 2011 at 2:49 PM, Christian Ullrich 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
  • Christian Ullrich at Feb 1, 2011 at 10:17 pm

    * Christopher Hotchkiss wrote:

    On Tue, Feb 1, 2011 at 2:49 PM, Christian Ullrichwrote:
    * 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedFeb 1, '11 at 2:26a
activeFeb 1, '11 at 10:17p
posts6
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase