Re: Maintaining user roles and permissions in Postgres - general question

Scott Marlowe wrote:
> On Thu, Jun 18, 2009 at 8:03 PM, Postgres
> User<postgres(dot)developer(at)gmail(dot)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