Level 63
Storing a password in plain text is really not a good idea, it's even a very bad idea.
Furthermore it's better to not use the .env file in production, it's better to write all credentials in a configuration file.
Jan 18, 2023
8
Level 4
Storing multi-tenant database connection info
Hi all, just curious on what the general consensus is for storing DB credentials (username/password) in a multiple database multi-tenant application (credentials per tenant DB). I’ve been looking around and there’s a few posts, but the info seems pretty limited. These options seem to be the most popular:
-
Store the username/password in plain text in a DB table column (most tutorials seem to do this, or brush over storing credentials at all. Obviously not ideal - seems trivial right?)
-
Encrypt credentials before storing them in the DB table (seems to be the ‘best’ way so far, Tenancy for Laravel has an approach for doing this, though not enabled by default for some reason). Another random gist hashed the password based on the APP_KEY, so a similar thing. Another just does a
Hash::make($password)before storing. -
Store the credentials in your .env file (or make some script that creates a .env/similar file per tenant and modifies the DB connection to read these). - Doesn’t seem that practical/scalable.
I’ve even come across a tutorial that claimed “there is a better way” than storing the password in the DB, but never showed it.
Anyone know of a way of getting around this? Storing this info in the central/landlord DB just doesn’t seem right for security, but I guess that might be the compromise for convenience.
Also, trying not to ignite a war between the single and multi-database approaches. Each have their own pros/cons, so I’ll leave it at that. Just curious. Thanks.
Level 4
@vincent15000 Yep I'm aware. Just reaching out to see what other people do, as it's more common than not to find this when looking for more info on this subject.
1 like
Level 80
@weblifted If you’re worried about scaling, then storing tenants’ database credentials in a database isn’t the best solution.
Think about it: for every request now you’re going to be:
- Connecting to the “landlord” database
- Identift the tenant from some form of request parameter (hostname, subdomain, etc)
- Establishing a second database connection to the tenant database
- Now actually handling whatever the request is
The visitor now has to wait until two database connections are made, and those connections can only ever happen one after the other. So you’re building in latency by having tenants have their own databases, whose credentials need to be looked up at runtime in order to establish a connection to a tenant’s database.
If you really need to have tenant data separated into separate data for whatever legal reason, then it’s probably a better idea to store the database credentials in environment variables for each site. So if tenants are identified by hostname, when you create the Apache or nginx configuration for that host, define and pass the credentials and pass them to the Apache/nginx process so that the Laravel app can read them like normal.
1 like
Level 4
@martinbean Thanks. Yeah, good points. Furthermore, you would have at least one query (maybe more) to maintain the tenant DB connection per request, so not ideal. The main approach I've seen is to store the credentials in a database, which is why I was reaching out for any other potential options.
Could you expand a little bit on the passing of credentials to the Apache/nginx process? I don't think I've seen that done before.
1 like
Level 80
@weblifted You can set environment variables in server definitions in both Apache and nginx.
With Apache you’d use the SetEnv directive in your VirtualHost block. In nginx you’d use the fastcgi_param directive.
1 like
Level 4
@martinbean Ah right, interesting. I noticed that Laravel lets you set the APP_ENV env. variable before the Laravel app loads. Would you have a .env file per tenant with their DB info and load that in through the APP_ENV on the server definition variables (something like this), or are you more referring to passing each DB credential (username, password, etc.) through as their own server definition variable?
It seems like that could work by bypassing the need to query the "landlord" on every request, and it's not stored in a single DB which seems like a better mindset. And it's set from the beginning so it's not reliant on changing DB connections.
Though, that might become problematic if you were to have an app on a single server and used path-based tenant identification (where the server configuration is shared across tenants, just with differing databases) which would defeat the purpose of this approach.
1 like
Level 80
@weblifted I just don’t use different databases per tenant to be honest. It just seems like a pain. If I have a 1,000 customers, I don’t want to be managing 1,001 databases, or migrating 1,000 tenant databases when I add a column to a tenant schema, etc.
1 like
Level 4
@martinbean That's fair, it was more a hypothetical. I'd only use multi databases if there was a requirement to keep the data separated, but still interested in how people are storing their credentials in case the situation pops up.
If anyone else has experience with this, feel free to chime in. Would love to hear any other approaches.
1 like
Please or to participate in this conversation.