1

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)

1
  • Could you show the table schema of both tables and queries (UPDATE and INSERT)?
    – shx
    Mar 30 '16 at 1:43
2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.