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