I need an advice about the possible approach for handling database credentials with Shiny app as a front end.
Here is my problem:
I developed Shiny application that connects to Postgres database.
R server and Postgres are located on two different AWS EC2 virtual image Linux servers, one has AWS Aurora flavor of Postgres, the other has R server.
I use shinyauthr library; when application launches it shows login dialog with fields for username and password. Input values are transferred to dbPool() ;
So credentials are entered by user manually and transferred to Postgres server; if dbPool() does not return an error (that is, if server accepted credentials and DB session is established), then I assign TRUE to reactive value "isConnected", and server part
output$ui <- renderUI({
has in the first line
req(isConnected)
So if isConnected=TRUE then all rendering starts; if not - just nothing happens and all application widgets are not rendered.
With this approach Postgres server handles all credentials, and depending of connected user (role) Postgres allows transactions or works in read-only mode.
But our team manager insists on implementing another approach:
In the database they created a table EMPLOYEES that contain user names, passwords(in plain text) and employee role (they are not Postgres roles).
Next, they created a generic Postgres 'superuser'(role) that has full access to all application tables.
That user name and password must be stored (hardcoded) in Shiny application, or in some file/env variable on R server machine;
So when user enters his name and password at the application start, application actually uses hardcoded credentials of 'superuser' and establishes connection with Postgres under these credentials;
Next, application runs a query for EMPLOYEES table and retrieves password and role for the user name that was entered at login. If password from EMPLOYEES table matches the value that was entered at login, then this user is considered authenticated and application controls related to transactions - UPDATE/INSERT/DELETE - are modified (enabled/disabled) depending on that employee role stored in EMPLOYEES table.
I strongly objected this approach; I am not a specialist in software security, but I am convinced that database password by no means should be stored anywhere in application code (regardless of framework/language), or in any other way (like, environment variable, OS file etc). AFAIK the only way of storing and verifying passwords is using hash values, this is what Postgres or Oracle does, is that right? Like, DB server receives password string, calculates its hash value and compares this calculated value with the one stored in some table (or file). (well, I am aware this is way more complicated, but the generic idea of hashing is still valid, at least in non-critical applications, I presume)
I want to clarify: my concern is NOT about authenticating user by comparing manual input with plain text password from the DB table;
My concern is that application connects with DB (even when user entered wrong credentials), and this connection uses a password hardcoded in application or OS file.
I would like to know an opinion of community regarding the above approaches.
I cannot disobey my manager orders regarding handing credentials and I will implement his requirements.
Not sure yet where and how to store 'superuser' password, I will consider 'keyring' package, or maybe some OS file.