Add user to database in postgresql

Question

I am currently trying to add a user to the database when a new comercial register is inserted to the comercial table.

CREATE OR REPLACE FUNCTION anade_usuario() RETURNS TRIGGER AS $$
DECLARE
nom TEXT;
BEGIN
    /* SELECT NEW.dni INTO nom;  */
    nom := NEW.dni;
    CREATE USER nom WITH PASSWORD 'pass';
    GRANT ALL PRIVILEGES ON DATABASE "fira" TO nom;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';


\echo Creating trigger number 3.
\echo --------------------------
CREATE TRIGGER trigger3 AFTER INSERT ON comercial
FOR EACH ROW EXECUTE PROCEDURE anade_usuario();

But the script seems to add the user 'nom' instead of the user with the id NEW.dni and so at the second insert I receive an error. What should I change?

Whenever you say "I receive an error", you should cut & paste the entire error so that we know what the error says. There are many different errors that are possible, and seeing the error message will help us with your problem. — Andy Lester
– Andy Lester, Commented May 27, 2015 at 20:39

Wolph · Accepted Answer · 2015-05-27 20:34:27Z

1

You need to execute it as a dynamic command: http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Something along the lines of this:

EXECUTE 'CREATE USER "' || nom || '" WITH ... '
INTO user
USING checked_user, checked_date;

edited May 27, 2015 at 20:34

answered May 27, 2015 at 16:17

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

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

5 Comments

iusting Over a year ago

I have change it to EXECUTE 'CREATE USER ' || nom || ' WITH PASSWORD ' || 'pass'; but now I get a syntax error psql:comercial/insert_intos.sql:4: ERROR: syntax error at or near "00123456" LINE 1: CREATE USER 00123456B WITH PASSWORD pass

Wolph Over a year ago

If your username starts with a number you'll need to add quotes to it, I'll modify the example

iusting Over a year ago

You seem to get closer and closer to the truth :). Changed it to EXECUTE 'CREATE USER ''' || nom || ''' WITH PASSWORD ' || 'pass'; and the error is now psql:comercial/insert_intos.sql:4: ERROR: syntax error at or near "'00123456B'" LINE 1: CREATE USER '00123456B' WITH PASSWORD pass

Wolph Over a year ago

Oops... wrong quotes :X Should be " instead of ''

iusting Over a year ago

Thank you very much Wolph. It worked with double quotes, the final form: EXECUTE 'CREATE USER ' || '"' || nom || '"' ||' WITH PASSWORD ' ||''''|| 'pass'||'''';

Collectives™ on Stack Overflow

Add user to database in postgresql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related