Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <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> </blockquote> <p>You probably misread this, or read it in a highly biased location. A more balanced view is (hopefully) this:</p> <ul> <li><p>Managing perms (ACL or RBAC or other) within the database is a bloody mess and hard to get right. It can cripple performance, too, if done improperly (think: "select * from table join perms where convoluted_permission_scenario".) Depending on who you ask, you'll get more or less extreme viewpoints, e.g. here's (the very controversial) Zed Shaw: <a href="http://vimeo.com/2723800" rel="nofollow">http://vimeo.com/2723800</a>.</p></li> <li><p>Managing perms at the DB level is just as much of a bloody mess. Not all engines implement row-level permissions, and even then there occasionally are leaks. For instance, calling a function in a where clause could (can?) leak rows in Postgres (until a recent version?) if <code>raise</code> gets called. And frankly, if you go past a superficial analysis of what is going on, it basically amounts to the former — just standardized and (usually) in C.</p></li> <li><p>Managing perms at the app level <em>without</em> a database is <em>also</em> a bloody mess. It'll cripple performance no matter what you do from the moment where you need to join outside of SQL, unless you're dealing with trivial amounts of data. If you try it, you'll do fine… until your database grows too large and you basically don't.</p></li> </ul> <p>So, in short: it's a bloody mess no matter where you manage it. Because permissions <em>are</em> a mess. In addition to the casual and idealistic "Joe needs write access to this set of nodes", you also need to cope with more down to earth scenarios such as "John is going off on vacation for Christmas and needs to temporarily delegate his write permissions on this set of nodes to his assistant Jane". Moreover, whichever scenario you do pick, you <em>need</em> to manage read access (which is usually the most frequent) in such a way that it's fast so you can scale. There's no silver bullet.</p> <p>Moreover, even in the first and last of the above scenarios, it's ideal to have three DB users. One for reads, one for read/writes, and one for schema changes. Most apps don't, because it's yet another bloody mess to configure your ORM that way, hence the typical one DB user per app.</p> <p>Anyway, getting back to your question: what people do in practice is one or two database users (read vs read/write/modify), implement RBAC or ACL within the database itself, and avoid access restriction logic like the plague on public-facing pages for performance reasons.</p>
    singulars
    1. This table or related slice is empty.
    1. POAre "best practices" regarding connection handle re-use and database user design mutually exclusive?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USDenis de Bernardy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POAre "best practices" regarding connection handle re-use and database user design mutually exclusive?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks, that's informative. I've worked around row-level permissions using views with simple where clauses--most tables have a "my_tablename" view that only shows rows created by the user (created_by_column = current_user::text::integer). I guess such an approach, which I thought was clever at the time, is not meant to be used in LAPP land.
      singulars
      1. PO
      1. USKev
    2. COThat scenario is clever but it wouldn't get you very far in a sales application where each of payee, deliveree, sales staff, sales managers, support staff, support managers, senior execs and occasionally their secretaries, as well as their cats, dogs, ponies and unicorns, you name it, need a potentially separate set of permissions on a particular node.
      singulars
      1. PO
      1. USDenis de Bernardy
    3. CODenis described a public-facing web scenario accurately. Databases that have relatively little web access--possibly only to remote offices of the same company--usually use more database-level permissions and fewer application-level permissions.
      singulars
      1. PO
      1. USMike Sherrill 'Cat Recall'
 

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