Thread: Maintaining user roles and permissions in Postgres - general question
Does anyone have a recommendation for maintaining user permissions on a changing database? The lack of an option to grant specific rights to all objects of a given type within a Postgres db obviously places the burden on the administrator to keep roles updated as objects are added and dropped from a given database. Unfortunately for us, we don't have a dedicated db admin, so this task falls into the hands of developers who are probably less adapt at this kind of task ;) Is there a utility or set of scripts out there that helps a db owner with permissions admin? Thanks
Postgres User wrote: > Does anyone have a recommendation for maintaining user permissions on > a changing database? The lack of an option to grant specific rights > to all objects of a given type within a Postgres db obviously places > the burden on the administrator to keep roles updated as objects are > added and dropped from a given database. > > Unfortunately for us, we don't have a dedicated db admin, so this task > falls into the hands of developers who are probably less adapt at this > kind of task ;) > > Is there a utility or set of scripts out there that helps a db owner > with permissions admin? > your question is just too vague to even begin to know what it is you want ? there are scripts for GRANTALL like functionality. But, if you use nested roles for your permissions, these things just don't come up. Or if the database is owned by the account that creates the tables and accesses it, again, they don't come up at all.
On Thu, Jun 18, 2009 at 8:03 PM, Postgres User<postgres.developer@gmail.com> wrote: > Does anyone have a recommendation for maintaining user permissions on > a changing database? The lack of an option to grant specific rights > to all objects of a given type within a Postgres db obviously places > the burden on the administrator to keep roles updated as objects are > added and dropped from a given database. > > Unfortunately for us, we don't have a dedicated db admin, so this task > falls into the hands of developers who are probably less adapt at this > kind of task ;) > > Is there a utility or set of scripts out there that helps a db owner > with permissions admin? It's easy enough to write scripts to do this, HOWEVER, down that road may lie madness. Let's say you've got 100 different users who need access to various parts of your database. If you start assigning all kinds of permissions to each user, you're gonna go insane. What works better is to assign roles the proper permissions. So, hr_admin role can change records in hr tables, hr_user can read records in hr tables and only change one or two, and so on. Then when someone comes on as an HR user, you just grant them the role. They leave the HR group, you revoke the role. ding, job done. You only ever need to assign the rights once really, to the main role, and from then on it's just one assignment / revocation to a user or users.
Scott Marlowe wrote: > On Thu, Jun 18, 2009 at 8:03 PM, Postgres > User<postgres.developer@gmail.com> wrote: >> Does anyone have a recommendation for maintaining user permissions on >> a changing database? The lack of an option to grant specific rights >> to all objects of a given type within a Postgres db obviously I think we're answering a different question to the one being asked. >> places >> the burden on the administrator to keep roles updated as objects are >> added and dropped from a given database. "Postgres User" is talking about adding new functions/tables etc. to an existing database. > What works better is to assign roles the proper permissions. So, > hr_admin role can change records in hr tables, hr_user can read > records in hr tables and only change one or two, and so on. Then when > someone comes on as an HR user, you just grant them the role. They > leave the HR group, you revoke the role. ding, job done. We're talking about changing the users who fill various roles. Correct (and useful) info but not quite on-target for the original question. This is in fact the main benefit of the various "grant all" scripts (easy enough to google for). Combined with Scott's advice you keep your life simple by: 1. Script revokes all permissions 2. Script applies minimum permissions required (e.g. read only on Scott's hr tables) 3. Script applies privileged permissions to specific roles/objects as required Re-run the script after every update to the database schema on your test DB. If the developers need to update the script but forget, they'll notice straight away when they can't access their new table/function. Keep the script under version-control and you can quickly identify the permission changes to make to the live system when you apply the schema changes there. -- Richard Huxton Archonet Ltd