StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAre "best practices" regarding connection handle re-use and database user design mutually exclusive?
text
Body
copied!<p>SO says this may be subjective. I'm hoping not--I just can't seem to understand how this works in practice, and it seems like a specific enough technical question with I hope a definitive answer.</p> <p><strong>Context:</strong> LAPP stack.</p> <ol> <li><p>I've read that using a single database user as the login for all connections to the database, and handling security yourself from there, is a bad idea. Databases have sufficient security models and it makes sense to use them.</p></li> <li><p>Database handles have some resource cost associated with them, hence the existence of Apache::DBI, DBIx::Connector, and DBI::connect_cached(), to re-use a recent connection to a database. Making use of them should make a web app faster by avoiding the cost of connecting to a database.</p></li> </ol> <p>The reason these seem to be mutually exclusive best practices is that, in my understanding, #1 implies that any database connection will be made with separate per-user credentials, which implies (as <a href="https://stackoverflow.com/questions/20475956/should-i-disconnect-if-im-using-apachedbis-connect-cached">Apache::DBI documents</a>) that re-using such connections will likely quickly cause your database backend to run out of connections.</p> <p>The default maximum number of connections for PostgreSQL is 100.</p> <p>The default numbers of servers and multiplied by subprocesses allowed for each, for Apache 2 running with the prefork MPM, far exceeds that, so it seems Apache::DBI's docs are right.</p> <p><strong>Thus the question</strong>: What do people do then, in practice?</p> <p>Does this mean people using a LAPP stack generally connect using a single database user, and implement their own security/permissions model? Or does it mean they don't pool connections? Or do they choose between these two strategies based on speed vs security needs if they go with a LAPP stack, and if they need both, go with a desktop app or some other connection model?</p> <p>Or if these are not, in fact, mutually exclusive strategies, what am I missing in my understanding here?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload