Allow insertion only from within a trigger

Question

I'm new to SQL programming, and I couldn't find an answer to this question online.

I'm working with pl/pgsql and I wish to achieve the following result:

I have a table A with certain attributes. I am supposed to keep this table updated at any time - thus whenever a change was made that can affect A's values (in other tables B or C which are related to A) - a trigger is fired which updates the values (in the process - new values can be inserted into A, as well as old values can be deleted). At the same time, I want to prevent from someone insert values into A.

What I want to do is to create a trigger which will prevent insertion into A (by returning NULL) - but I don't want this trigger to be called when I'm doing the insertion from another Trigger - so eventually - insertion to A will only be allowed from within a specific trigger.

As I said before, I'm new to SQL, and I don't know if this is even possible.

Answer 1

Yes, totally possible.

1. Generally disallow UPDATE to A

I would operate with privileges:

REVOKE ALL ON TABLE A FROM public;  -- and from anybody else who might have it

That leaves superusers such as postgres who ignore these lowly restrictions. Catch those inside your trigger-function on A with pg_has_role():

IF pg_has_role('postgres', 'member') THEN
   RETURN NULL;
END IF;

Where postgres is an actual superuser. Note: this catches other superusers as well, since they are member of every role, even other superusers.

You could catch non-superusers in a similar fashion (alternative to the REVOKE approach).

2. Allow UPDATE for daemon role

Create a non-login role, which is allowed to update A:

CREATE ROLE a_update NOLOGIN;
-- GRANT USAGE ON SCHEMA xyz TO a_update;  -- may be needed, too
GRANT UPDATE ON TABLE A TO a_update;

Create trigger functions on tables B and C, owned by this daemon role and with SECURITY DEFINER. Details:

• Is there a way to disable updates/deletes but still allow triggers to perform them?

Add to the trigger function on A:

IF pg_has_role('postgres', 'member') THEN
   RETURN NULL;
ELSIF pg_has_role('a_update', 'member') THEN
   RETURN NEW;
END IF;

For simple 1:1 dependencies, you can also work with foreign key constraints (additionally) using ON UPDATE CASCADE.