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
