Thread: Validating User Login Within Postgres

   I'm developing a new application and want to take advantage of postgres
features such as triggers and stored procedures and put as much of the
middleware 'business logic' into the database engine as is practical.

   Is it possible, or practical, to validate a potential user login within
the database? What I've done in the past is search the User table for that
name and password, return it to the application, and have a Python script
see if the entered username and password match that stored in the table.

   All pointers and suggestions welcome.

Rich

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

<div class="moz-cite-prefix">On 07/01/2014 06:03 PM, Rich Shepard wrote:<br /></div><blockquote
cite="mid:alpine.LNX.2.11.1407010757070.21028@localhost"type="cite">On Tue, 1 Jul 2014, hubert depesz lubaczewski
wrote:<br /><br /><blockquote type="cite">That depends. For example - for system that will have 5 users, and <br />
requiresstrict security policies - it would make sense. On the other <br /> hand, for website, with thousands of users,
puttingthem all as actual <br /> roles in Pg doesn't make much sense, and could potentially cause problem. <br
/></blockquote><br/> Hubert, <br /><br />   OK. This is not a Web-based application, but the users could number in the
<br/> dozens. <br /></blockquote> Using database user authentication is great for corporate use. If you have an LDAP
(oractive directory) you can define the authentication to use that. See the pg_hba file for details on various
authenticationmethods. <br /><br /> Our application is written in python as well (desktop modules in wxPython and web
modulesin django)  and we use LDAP authentication.<br /><br /> sim<br />