On Tue, 1 Jul 2014, hubert depesz lubaczewski wrote:
That depends. For example - for system that will have 5 users, and requires strict security policies - it would make sense. On the other hand, for website, with thousands of users, putting them all as actual roles in Pg doesn't make much sense, and could potentially cause problem.
Hubert, OK. This is not a Web-based application, but the users could number in the dozens.
When I write apps I tend to create database users per *type* of application that will use it, and then handle application users using table in my database.
There are four roles, each with different privileges. I plan to read about postgres roles today to take advantage of that.
So, for example, I might have "app_dba" account (the one that owns all objects, and is used to create tables/views/functions/...), app_website account (the one used by application to handle web requests), app_cronjob (for general cronjobs, or sometimes specialized app_cron_whatever for every cronjob).
From the user perspective there are four roles: one system 'admin' who adds and deletes users and assigns each to one of the other three roles; 'executive' whose interest is in viewing reports and stored documents (such as permits) but who do not otherwise interact with the application; 'manager' who can add, delete, modify data and view all reports; and 'technician' who can add data, analyze results, and generate reports.
Also, if you're thinking about security - please consider reading http://www.depesz.com/2007/08/18/securing-your-postgresql-database/ .
Will do. Thanks, Rich
