Databases
StackOverflow2013
Postsdbo
POAre "best practices" regarding connection handle re-use and database user design mutually exclusive?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAre "best practices" regarding connection handle re-use and database user design mutually exclusive?
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USKev
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAre "best practices" regarding connection handle re-use and database user design mutually exclusive?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAre "best practices" regarding connection handle re-use and database user design mutually exclusive?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think you are getting application ID's and user ID's confused. For example a Application might have other security restrictions that cannot be modelled on the database. I would create a application ID that I can enforce SQL related permissions on and also use in my custom scheme if needs be. I would then create user id for users that needs to access the database directly such as ETL, Admin and other workers. Having a single application ID does make auditing easier and ensures that the right permissions are used. Using one super id IS a bad idea.
      singulars
      1. POAre "best practices" regarding connection handle re-use and database user design mutually exclusive?
      1. USNamphibian
    2. COSounds probable. So a "database user" is actually an application, and an actual real-life user using my application should not be a "database user", even if I only have one application? If so, that's unfortunate: I was finding it very convenient to have users' and groups' permissions built into tables, views, and functions. It had seemed natural.
      singulars
      1. POAre "best practices" regarding connection handle re-use and database user design mutually exclusive?
      1. USKev
    3. CO@Kev: it seems natural until you need to need to manage an unusual scenario, e.g.: grant this person access to my stuff on the 1st of each month if this condition is fulfilled.
      singulars
      1. POAre "best practices" regarding connection handle re-use and database user design mutually exclusive?
      1. USDenis de Bernardy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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