Validating User Login Within Postgres

Discussion:

Rich Shepard

2014-07-01 13:58:09 UTC

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

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

hubert depesz lubaczewski

2014-07-01 14:10:38 UTC

Permalink

Post by Rich Shepard
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.

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.

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.

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

Also, if you're thinking about security - please consider reading
http://www.depesz.com/2007/08/18/securing-your-postgresql-database/ .

Best regards,

depesz

Rich Shepard

2014-07-01 15:03:05 UTC

Permalink

Post by hubert depesz lubaczewski
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.

Post by hubert depesz lubaczewski
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.

Post by hubert depesz lubaczewski
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.

Post by hubert depesz lubaczewski
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

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sim Zacks

2014-07-02 05:36:57 UTC

Permalink

<html style="direction: ltr;">
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<style type="text/css">body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" bgcolor="#FFFFFF"
text="#000000">
<div class="moz-cite-prefix">On 07/01/2014 06:03 PM, Rich Shepard
wrote:<br>
</div>
<blockquote cite="mid:***@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>
requires strict security policies - it would make sense. On the
other
<br>
hand, for website, with thousands of users, putting them 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 (or active directory) you can define the
authentication to use that. See the pg_hba file for details on
various authentication methods. <br>
<br>
Our application is written in python as well (desktop modules in
wxPython and web modules in django)  and we use LDAP authentication.<br>
<br>
sim<br>
</body>
</html>