Hi Dmitriy, On 10/21/10 4:21 PM, Dmitriy Igrishin wrote: > IMO, you are trying to reinvent the wheel. Although, you may do it just for > fun. :-) Surely I am, but I think it made sense at the time. It doesn't make as much sense now that I need to audit every insert/update/delete in the database. > Why not just create "groups" via CREATE ROLE User ... and grants this > roles to the "users" (created via CREATE USER or CREATE ROLE ... LOGIN)Â ? The reason I shied away from this initially was the overhead of having to maintain user info in two places (the pg_catalog schema for postgres users, and in my application schema, with the user's real name, application preferences, etc.) It also seemed like the role information wasn't very accessible in the system catalogs -- I had noticed that the pg_group view was deprecated, and the query to get group information out of the pg_auth_members and pg_roles tables started to look very ugly, when I could just do a quick "is the user an administrator" check via a boolean flag in my app user's table. With my new requirements for auditing, using the database's roles makes more sense, but I still see some problems with it, even if I can solve the connection pooling problem by using persistent connections as you suggest. For one thing, in this app, all higher permissions include the lower permissions -- all administrators are auditors and regular users, and all auditors are regular users. So, my normal instinct would be to set it up like this: GRANT g_user TO g_auditor WITH ADMIN OPTION; GRANT g_auditor TO g_admin WITH ADMIN OPTION; Then, in theory, I could grant administrators the g_admin group, auditors the g_auditor group, etc. and they could do all the things the lower groups can. BUT, in my app, to check for access to audit functions, I can't do a simple query to see if the user is in the "g_auditor" group, because administrators aren't explicitly granted this group -- they get those permissions implicitly, but how do I know this from my application? Is there some kind of query I can do to get back all the groups a role is a member of? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
