Re: Validating User Login Within Postgres

On Tue, Jul 1, 2014 at 3:58 PM, Rich Shepard <rshepard(at)appl-ecosys(dot)com>
wrote:

> 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