I am making an app which connects to a DB, and the app will have plenty different userslogins.
Is it a good design choice to have a specific DB login/password for the application, using which the application will connect to the DB, and then verify userlogin/hashed+saltedpassword which are stored in a TABLE? (and for PostgreSQL to then SET ROLE to a user with necessary privileges for different TABLES/SCHEMAS)
My problem with this is I don't fancy the idea of having to store any credentials in the application, as if it's "reverse engineered" and the source code is found the hacker would know the credentials to access the database, and knowing the logic of the application he could then use "SET ROLE" himself and get access to any user he knows.
I just don't know how to access the DB without storing credentials in the application itself. I can make the user credentials = DB credentials, then no credentials would be stored but then the user could access the DB directly. So do we have to store the DB credentials in our source code?
So it's like this:
User -> inputs his credentials into the app
App -> has login/password stored in the source code to access the DB, it connects to the DB and does
SELECT hash, salt FROM login_table WHERE userlogin = *given user login*
The app uses the given password and hashes it with the salt it found in the login_table, then it tries to match the just hashed password with the hash from login_table. If those match - the application does
SET ROLE *given user login*
(this is specific to PostgreSQL) and then does all the other stuff using the established connection.
But while this seems great, the app needs to connect to the DB in the first place, so do I store it's DB login&password in the source code?