PostgreSQL create schema from within function trigger-CodePudding

My goal is to create a schema, based on the uuid of a user. I've therfor wrote a function that gets executed every time a user is created and confirmed.

As the documentation says i may have to write another function that creates a schema because of compatibility.

the 'create_user_schema()' function works in a new query but seems to not work if used in my trigger function. I've tried a lot with casting the uuid to a string but it still don't work.

Did i do something wrong, has this something to do with security and won't work in any case?

CREATE OR REPLACE FUNCTION user_setup() RETURNS trigger AS $user_setup$
    DECLARE
      s_name uuid := NEW.id;
    BEGIN
        -- cutout content that works so far

        SELECT create_user_schema(CAST(s_name AS TEXT));
        RETURN NULL;
    END;
$user_setup$ LANGUAGE plpgsql;

CREATE TRIGGER user_setup AFTER INSERT OR UPDATE ON auth.users
    FOR EACH ROW EXECUTE FUNCTION user_setup();

CREATE OR REPLACE FUNCTION create_user_schema(s_name text) RETURNS void AS $$
    BEGIN
        EXECUTE 'CREATE SCHEMA ' || quote_ident(s_name);
    END;
$$ LANGUAGE plpgsql;

CodePudding user response：

Well it wasn't really a bug and more some missing piece of information in "how postgres works". It was necessary to add a 'Security Definer' keyword to the function to let the trigger have the correct privilges on execution.