0

I have a Postgres trigger that I have written. It works 100%, I've tested it thoroughly. However, when my application writes to that database table, it fails. I'll put the code below, then I'll explain the failure.

CREATE OR REPLACE FUNCTION validate_client_user_role()
    RETURNS trigger AS
    $BODY$

    DECLARE role_has_client INT;
    DECLARE user_has_client INT;

    BEGIN
        IF NEW.client_id IS NULL THEN
            RAISE EXCEPTION 'client_id cannot be null';
        END IF;

        IF NEW.user_id IS NULL THEN
            RAISE EXCEPTION 'user_id cannot be null';
        END IF;

        IF NEW.role_id IS NULL THEN
            RAISE EXCEPTION 'role_id cannot be null';
        END IF;

        SELECT COUNT(*)
        INTO role_has_client
        FROM roles
        WHERE id = NEW.role_id
        AND client_id = NEW.client_id;

        SELECT COUNT(*)
        INTO user_has_client
        FROM client_users
        WHERE user_id = NEW.user_id
        AND client_id = NEW.client_id;

        IF role_has_client = 0 THEN
            RAISE EXCEPTION 'Role is not allowed by client';
        END IF;

        IF user_has_client = 0 THEN
            RAISE EXCEPTION 'User is not allowed by client';
        END IF;

        RETURN NEW;
    END
    $BODY$ LANGUAGE plpgsql;

CREATE TRIGGER client_user_role_validation
    BEFORE INSERT OR UPDATE
    ON client_user_roles
    FOR EACH ROW
    EXECUTE PROCEDURE validate_client_user_role();

So, my application database has multiple schemas, one for dev, qa, prod, etc. When I first write this DDL statement, I run this first:

set search_path to dev;

This then adds it properly to the "dev" schema. As long as I'm in my DB query console, I can validate this trigger is working perfectly.

However, when my application tries to write to the tables, the trigger fails, saying the relations (roles, client_users) that it tries to do selects from don't exist. I can fix this by modifying the function code to explicitly reference the "dev" schema for each table. I don't want to do this, though. I would prefer code that I can execute as a DDL statement for dev/qa/prod without needing to replace all the schema references or keep multiple copies of it.

Is there some way to do this? Obviously the trigger is not inheriting from the schema it is assigned to when I execute it. Is there some Postgres trick to make this work?

1 Answer 1

2

You can use the TG_TABLE_SCHEMA variable and set_config() with IS_LOCAL = true to accomplish this:

CREATE OR REPLACE FUNCTION validate_client_user_role()
    RETURNS trigger AS
    $BODY$

    DECLARE role_has_client INT;
    DECLARE user_has_client INT;

    BEGIN
        IF NEW.client_id IS NULL THEN
            RAISE EXCEPTION 'client_id cannot be null';
        END IF;

        IF NEW.user_id IS NULL THEN
            RAISE EXCEPTION 'user_id cannot be null';
        END IF;

        IF NEW.role_id IS NULL THEN
            RAISE EXCEPTION 'role_id cannot be null';
        END IF;

        PERFORM set_config('search_path', TG_TABLE_SCHEMA, true);  -- <-- This line

        SELECT COUNT(*)
        INTO role_has_client
        FROM roles
        WHERE id = NEW.role_id
        AND client_id = NEW.client_id;

        SELECT COUNT(*)
        INTO user_has_client
        FROM client_users
        WHERE user_id = NEW.user_id
        AND client_id = NEW.client_id;

        IF role_has_client = 0 THEN
            RAISE EXCEPTION 'Role is not allowed by client';
        END IF;

        IF user_has_client = 0 THEN
            RAISE EXCEPTION 'User is not allowed by client';
        END IF;

        RETURN NEW;
    END
    $BODY$ LANGUAGE plpgsql;
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you, that was it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.