Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Never provide authorization from a client application, which is run on uncontrolled environment. And every device, that a user has physical access to, is an uncontrolled environment. This is security through obscurity &mdash; a user can simply use a debugger to get a database access credentials from client program memory and just use <code>psql</code> to do anything.</p> <p>Use roles.</p> <p>When I was developing an C++/PostgreSQL desktop application I've chosen to disallow all users access to modify all tables and I've created an API using Pl/PgSQL functions with <code>VOLATILE SECURITY DEFINER</code> options. But I think it wasn't a best approach, as it's not natural and error prone to use for example:</p> <pre><code>select add_person(?,?,?,?,?,?,?,?,?,?,?,?); </code></pre> <p>I think a better way would be to allow modifications to tables which a user needs to modify and, when needed, enforce authorization using BEFORE triggers, which would throw an error when current_user does not belong to a proper role.</p> <p>But <a href="http://www.postgresql.org/docs/current/static/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY" rel="nofollow">remember to use <code>set search_path=...</code> option in all functions that have anything to do with security</a>.</p> <hr> <p>If you want to authorize read-only access to some tables then it gets even more complicated. Either you'd need to disable select privilege for these tables and create API using security definer functions for accessing all data. This would be a monster size API, extremely ugly and extremely fragile. Or you'd need to disable select privilege for these tables and create views for them using <code>create view with (security_barrier)</code>. Also not pretty.</p>
    singulars
    1. This table or related slice is empty.
    1. POmulti user desktop application with privilege separaion
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USTometzky
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for the answer Tometzky. I have a few questions: 1. What if users are not that technically oriented? And it is highly unlikely that they use the debugger approach?
      singulars
      1. PO
      1. USRichard
    2. CO2. Another approach I can think of is, to use postgres roles. Use roles (no login rights) to define privileges on tabbles (like linux groups). Create a new role with login rights and required combination of privileges (implemented by inheriting the earlier group roles). Now, create a users tables that maps actual users to these postgres users (user roles). Naturailly, some users (admins/managers at the organization) would be granted privileges to create additional user roles, or group roles as they see fit.
      singulars
      1. PO
      1. USRichard
    3. COAnother question, can we not use another secure authentication method? I am not aware of all of them, neither well informed on how they work, but I believe (I have no research to support this) there must be another authentication method that can be used by the application to authenticate itself to the server, which cannot be duplicated by a malicious user. What are your thoughts?
      singulars
      1. PO
      1. USRichard
 

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