Read only postgres table, except by trigger 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)
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.