Allow some roles to update a column, and some others if it IS NULL

Question

I have a column A (type int) in a table for which data is not available at insert time of the other values. I do not want to split the table, as there is no other real reason to do so. I am implementing privilege separation at the database level.

Only certain users (who belong to category A) should be able to modify column A at any time. But other users (in category B, not necessarily mutually exclusive to category A) should be able to update the column if its value is not set already, ie. if it is NULL.
I am using PostgreSQL 9.2.4.

How can I accomplish this? Triggers? Rules? Something else?

Answer 1

only certain users should be able to modify column A
...
other users should be able to update the column if ... it is NULL.

Revoke UPDATE (and DELETE ?!?) from public and everybody else who should not have the privilege.

REVOKE UPDATE ON TABLE tbl FROM public;
REVOKE UPDATE ON TABLE tbl FROM ...

Create a (group-)role some_users that is allowed to update col_a (and nothing else):

CREATE ROLE some_users;
GRANT SELECT, UPDATE (col_a) ON TABLE tbl TO some_users;

Why the SELECT? The manual on GRANT:

In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.

This way, you have a single point where you dole out privileges for the column.
Create another (group-)role certain_users that can do everything some_users can (plus some more):

CREATE ROLE certain_users;
GRANT some_users TO certain_users;

Grant membership in these roles to user-roles as needed:

GRANT some_users TO lowly_user;
GRANT certain_users TO chief_user;

Create a conditional trigger, similar to what @Daniel provided, but with another condition using pg_has_role():

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();

Using the trigger function:

CREATE FUNCTION always_fail()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- To fail with exception, rolling back the whole transaction
   -- use this instead:
   -- 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$;

Now:

• The general public is restricted from updating the table completely.
• some_users and certain_users are allowed to update the column col_a (and nothing else),
• Only changes from certain_users go through once col_a is NOT NULL.

Note that superusers are member of any group automatically. So the form NOT pg_has_role(...) enables superusers, while an inverse logic would potentially prohibit superusers from updating the column.