I designed a Java web application. The persistence layer is a
PostgreSQL database. The application needs user authentication.
I think it's a good choice to implement this authentication mechanism
via PostgreSQL login roles. So I can create several database login
roles and set the database permissions to this login roles. This is my
first project with the postgres database, so I don't know how I can
validate a login from the website. Is there a best practice to do this
or does PostgreSQL offers a stored procedure like
'authenticateUser(String username, String password)'?
Keep in mind that this might interact badly with very desirable features
like :
- persistent connections
(opening a postgres connection takes a lot longer than a simple SELECT,
so if you must reopen connections all the time your performance will suck)
- connection pooling
(what happens when a user gets the admin's connection out of the pool ?)
Since you use an object-relational mapper I believe it is better, and
more flexible to have your objects handle their own operations.
On a very basic level your objects can have a .isReadOnly() method which
is checked in your application before any writing takes place, for
instance.
