Re: Validating User Login Within Postgres

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