Website login using database credentials

Question

Is it secure to login to a website by using database usernames, for each user? Is it a bad practice? What can be the issues?

The idea is to create the users and permissions at database level.

EDIT: Any user accessing through my website would have INSERT, DELETE, UPDATE an SELECT permissions in specific tables.

At code level I would block any login to specific users with higher permission levels (like admin).

Only my company staff will be accessing my website. So, there will be no account management through the website, nor creation of new users. I would do it locally.

EDIT2: I will be using PostgreSQL, and my users should be able to access the site over the internet (not the database).

One of the reasons I ask this is because I saw a similar comercial desktop application, which does just that: you login inside the application, with a database username. I know that type of software is intended to be run inside a local network, but can easily be configured to work outside.

Answer 1

Edit: I made a lot of assumptions in this answer that were not necessarily true. I just found out that the DB in question is Postgres. My answer is more tailored towards SQL Server, which until version 2016 did not have Row-Level Security. I'll leave my answer here for information purposes.

What you are suggesting is certainly not best practice, for many reasons. That doesn't mean you can't do it, but you must be willing to concede all of these points if you don't:

With your model, any user can access the database directly without using the website at all. (Edit: this is assuming you are in a local intranet and your users can access the DB directly using another tool outside of the website).

Because of this:

1. Any user can add/edit/view/delete information for all users, not just their own information. (Note: this is not necessarily true if your DB supports Row-Level Security, and if you use it.)
2. If any one user's credentials are compromised, all of the data is potentially at risk. (Note: this is not necessarily true if your DB supports Row-Level Security, and if you use it.)
3. A user accessing the DB directly could incidentally run queries that do something they didn't intend. They can also unknowingly lock up the DB and make it unusable for others.

There are other reasons to not do it, but let's talk about the "norm" instead. Typically you manage your users from the website, and then give the website access to the DB with a single user that has the minimum set of permissions needed. If you ever suspect the DB password has been compromised, you can simply change that one password, and then let the website know about the new password (usually in an encrypted file of some sort that is not web accessible). This also makes it easier to provide different levels of permissions to different users, simply by controlling their access in the website code. You can also get much more granular, for example, you could allow certain users to only update a particular column of a particular table, if you wanted to.

Fortunately, there are many free out-of-the-box solutions that manage users and roles on virtually every popular web platform. I can't really think of good reason to venture away from the norm for connecting to a database via a website.

Edit: even though it may be the case that Postgres best practices differ from my suggestions in this answer, I still am not convinced that providing direct access to the DB is better than my suggestions. Without knowing more about Postgres, I would still prefer this model. But that could simply be because this is what I'm used to.

Answer 2

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:

1. Connect as a power user and add "real" userid info to your SQL statements (using parameters of course!)
2. Connect as the real db user (this kills connection pooling)
3. Connect as a limited user and impersonate the real user using set role

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 current_user

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.