There are actually two questions here: how to authenticate a user against a web server, and which user to use to connect to the db as.
The real question is, should database security be up to the DBA or the web programmer?
Database servers are long-lived and are often shared by multiple applications.
Ideally, you would authenticate your (LAN) webapp users against an LDAP (aka ActiveDirectory) server, via Kerberos + SPNEGO. This gives you centralized authentication and single sign-on across many apps.
You could also authenticate webapp users directly against db users, example here . If that's where all your user authn info is, and you don't use LDAP/Kerberos, might as well use that.
The next question is which db user to use when connecting from the web server:
- Connect as a power user and add "real" userid info to your SQL statements (using parameters of course!)
- Connect as the real db user (this kills connection pooling)
- Connect as a limited user and impersonate the real user using
ONE is the most common, not because it's a good idea, but because people wanted to use connection pooling in the past and didn't understand impersonation. This kicks the Principle of Least Privilege directly in the crotch.
There are incredibly common, career-ending vulnerabilities made more likely with option 1: SQL Injection and Insecure Object References:
Web server connects to db as
powerUser, Malice logs in to website as
malice and finds a SQL injection and drops your
payments table, as
powerUser has that authority.
Your web developers append the userid to the sql for the
/bank-accounts URL, but are stupid and forget to append the userid for individual bank account URLs (like
/bank-accounts/999). Malice logs in as
malice and views
bobs bank balance.
These risks can be minimized by using impersonation and database row security (which essentially requires impersonation). PG 9.5 supports row security. You can mimic it in earlier versions with
security_barrier check option views and triggers.
It also makes data auditing easier, as you have the real username right in your db as
I would say a database like Postgres has better security than most web security frameworks, as it does Row Security, Column Security and has a nice hierarchical role system.
I've seen web security frameworks that select all rows for a given query, then discard everything that doesn't belong to user X. This totally screws up pagination and is terrible for performance. I've also never seen one that does Column/Field security right.