Read only postgres table, except by trigger

Question

Suppose the following situation:

I have a product_stock table and a stock_transaction_log table. Currently whenever there is an increase/decrease in a product's stock, I update the product_stock table increasing or decreasing the quantity and also insert an entry in the transaction log table, for auditing. This is done in the application code.

I am considering changing the update of the product_stock table to be done by a trigger on the transaction log table (instead of doing it directly in my application code), but I would like to add a reinforcement that the product_stock table would never be updated directly, and only the trigger would be authorized to update it.

Is there anyway to do this in postgresql (currently using 9.1)

• shx: Could you show the table schema of both tables and queries (UPDATE and INSERT)?

Answer 1

The solution proposed by Ziggy will work, but if you don't want to go that deep by having to create a privileged user just to execute the trigger, there's another solution you can try: Create a validation trigger on the product_stock table that checks if the update is coming from the stock_transaction_log trigger. To do that, we can create a temporary table to serve as a "global variable".

CREATE OR REPLACE FUNCTION validate_product_stock() RETURNS trigger AS $$
DECLARE
 count_ int;
BEGIN
 BEGIN
 SELECT COUNT(1) INTO count_ FROM __inside_stl_trigger WHERE note = 'inside trigger';
 EXCEPTION WHEN undefined_table THEN
 count_ := 0;
 END;
 IF count_ = 0 THEN
 RAISE EXCEPTION 'This table is automatically updated by stock_transaction_log'
 END IF;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER validate_product_stock_trigger
 BEFORE INSERT OR UPDATE ON product_stock
 FOR EACH ROW
 EXECUTE PROCEDURE validate_product_stock();
CREATE OR REPLACE FUNCTION update_product_stock() RETURNS trigger AS $$
BEGIN
 CREATE TEMPORARY TABLE __inside_stl_trigger (note text NOT NULL);
 INSERT INTO __inside_stl_trigger (note) VALUES ('inside trigger');
 -- Update the product_stock table here
 DROP TABLE __inside_stl_trigger;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_product_stock_trigger
 BEFORE INSERT OR UPDATE ON stock_transaction_log
 FOR EACH ROW
 EXECUTE PROCEDURE update_product_stock();

Obviously, one could create that temporary table by hand, insert a row on it and do the update, but at least you will make it harder.

Answer 2

You can use SECURITY DEFINER in the function trigger, and create it with a privileged user. Give the user privileges for inserting into stock_transaction_log and executing the trigger, but not for inserting into product_stock.

With PostgreSQL 9.3+, if you don't expect very high frequency of insertions in stock_transaction_log you can also create the table product_stock as a MATERIALIZED VIEWs with a unique index, and call REFRESH MATERIALIZED VIEW CONCURRENTLY product_stock from within the trigger after insertion in the log table.