Trigger to Create User in PostgreSQL

Question

Well, after insert a user in systemuser, i like to create a ROLE for PostgreSQL, using the password of new user. But when i try create the user, this error appears:

Blockquote ERROR: syntax error at or near "NEW" LINE 24: ALTER USER NEW.login ENCRYPTED PASSWORD NEW.password...

CREATE OR REPLACE FUNCTION trg_insertuser() 
RETURNS TRIGGER AS $trg_insertuser$
DECLARE
BEGIN
    IF (TG_OP = 'DELETE') THEN
        DROP USER OLD.login;
    ELSIF (TG_OP = 'UPDATE') THEN
        ALTER USER OLD.login ENCRYPTED PASSWORD NEW.password;
    ELSIF (TG_OP = 'INSERT') THEN       
        CREATE USER NEW.login ENCRYPTED PASSWORD NEW.password CREATEDB IN GROUP group_user;
    END IF;
    RETURN NULL;
END;
$trg_insertuser$ LANGUAGE plpgsql;

What's happend?

maniek · Accepted Answer · 2012-10-02 14:47:38Z

2

use execute.

execute 'drop user ' || quote_ident(OLD.login);

etc...

answered Oct 2, 2012 at 14:47

maniek

7,3252 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trigger to Create User in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related