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

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 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.