Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>To achieve what you <strong><em>actually asked</em></strong>:</p> <blockquote> <p>only certain users should be able to modify column A<br> ...<br> other users should be able to update the column if ... it is NULL.</p> </blockquote> <p>Revoke <code>UPDATE</code> (and <code>DELETE</code> ?!?) from public and everybody else who should have the privilege.</p> <pre><code>REVOKE UPDATE ON TABLE tbl FROM public; REVOKE UPDATE ON TABLE tbl FROM ... </code></pre> <p>Create a (group-)role <code>some_users</code> that is allowed to update the <code>col_a</code> (and nothing else):</p> <pre><code>CREATE ROLE some_users; GRANT SELECT, UPDATE (col_a) ON TABLE tbl TO some_users; </code></pre> <p>Why the <code>SELECT</code>? Consider this quote from <a href="http://www.postgresql.org/docs/current/interactive/sql-grant.html" rel="nofollow noreferrer">the manual ON <code>GRANT</code></a>:</p> <blockquote> <p>In practice, any nontrivial <code>UPDATE</code> command will require <code>SELECT</code> privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.</p> </blockquote> <p>This way, you have a <em>single point</em> where you dole out privileges for the column.<br> Create another (group-)role <code>certain_users</code> that can do everything <code>some_users</code> can (plus some more):</p> <pre><code>CREATE ROLE certain_users; GRANT some_users TO certain_users; </code></pre> <p>Grant membership in these roles to user-roles as needed:</p> <pre><code>GRANT some_users TO lowly_user; GRANT certain_users TO chief_user; </code></pre> <p>Create a conditional trigger, similar to what <a href="https://stackoverflow.com/a/15921276/939860">@Daniel provided</a>, but with another condition using <a href="http://www.postgresql.org/docs/current/interactive/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE" rel="nofollow noreferrer"><code>pg_has_role()</code></a>:</p> <pre><code>CREATE TRIGGER tbl_value_trigger BEFORE UPDATE ON tbl FOR EACH ROW WHEN (OLD.col_a IS NOT NULL AND NOT pg_has_role('some_users', 'member')) EXECUTE PROCEDURE always_fail(); </code></pre> <p>Using the trigger function:</p> <pre><code>CREATE FUNCTION always_fail() RETURNS trigger AS $func$ BEGIN -- Fail with an exception, rolling back the whole transaction. -- RAISE EXCEPTION 'value is not null'; -- Do nothing instead, letting the rest of the transaction commit. -- Requires BEFORE trigger. RAISE WARNING 'col_a IS NOT NULL. User % is too ambitious!', current_user; RETURN NULL; -- effectively aborts UPDATE END $func$ LANGUAGE plpgsql; </code></pre> <h3>Now:</h3> <ul> <li>The general public is restricted from updating the table completely.</li> <li><code>some_users</code> and <code>certain_users</code> are allowed to update the column <code>col_a</code> (and nothing else),</li> <li>Only changes from <code>certain_users</code> go through once <code>col_a is NOT NULL</code>.</li> </ul> <p><strong>Note</strong> that <strong>superusers</strong> are member of any group automatically. So the form <code>NOT pg_has_role(...)</code> <em>enables</em> superusers, while an inverse logic would potentially prohibit superusers from updating the column.</p>
    singulars
    1. This table or related slice is empty.
    1. POEnsure that a column can only be modified if it IS NULL and only by some users
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USErwin Brandstetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POEnsure that a column can only be modified if it IS NULL and only by some users
      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. VTAcceptedByOriginator
    1. COthank you. especially for the sample code.
      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