Assigning variables to output parameters in a PostgreSQL trigger

Question

I'm starting a PostgreSQL project, after many years with Sybase and SQL Server, and am new to it's quirks, but so far have been impressed.

I've written a small function that returns OUT parameters. I pass in a clear password and the function returns the encrypted password and a salt. Here it is:

CREATE OR REPLACE FUNCTION iSecGenPwdSalt(pwdin text, out salt text, out userpassword text)
AS 
$BODY$
DECLARE 

    BEGIN
        -- Generate a salt...
        salt = gen_salt('bf', 10);

        -- Now generate the password hash...
        userpassword = crypt(pwdin, salt);
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION iSecGenPwdSalt(text, out text , out text ) OWNER TO postgres;

I'd like to call this function within a trigger and use the values to populate the password and salt columns I have, but I can't fathom the syntax. I've tried multiple variations ona theme but I'm still having problems.

select salt, userpassword from iSecGenPwdSalt('mymagicpassword');

Returns it's results beautifully, so I'd like to call this function and get the results into a pair of variables in a trigger so I can assign the values.

Here's my code:

SELECT usersalt=salt, userpwd=userpassword 
  FROM iSecGenPwdSalt(NEW.systemuserpassword);

The error I get is thus:

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function "isec_trg_systemuser" line 13 at SQL statement

So simply assigning a pair of variables to a resultset derived from OUT parameters in a trigger is causing me grief.

Grzegorz Szpetkowski · Accepted Answer · 2011-08-18 21:17:29Z

1

In your specific situation I think that you could use SELECT INTO statement with multiple columns:

CREATE OR REPLACE FUNCTION userAuth_insert_f() RETURNS TRIGGER AS $$
BEGIN
    SELECT INTO NEW.usersalt, NEW.userpwd
        salt, userpassword FROM iSecGenPwdSalt(NEW.systemuserpassword);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Full example (using pgcrypto contrib module):

DROP TABLE IF EXISTS userAuth; 
CREATE TABLE userAuth (
    id serial,
    systemuserpassword text,
    usersalt text,
    userpwd text
);

CREATE OR REPLACE FUNCTION iSecGenPwdSalt
    (pwdin text, OUT salt text, OUT userpassword text)
AS $$ BEGIN
    salt = gen_salt('bf', 10);
    userpassword = crypt(pwdin, salt);
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS userAuth_insert_t ON userAuth;
CREATE TRIGGER userAuth_insert_t
    BEFORE INSERT ON userAuth
    FOR EACH ROW
    EXECUTE PROCEDURE userAuth_insert_f();

Invoking:

=>INSERT INTO userAuth (systemuserpassword) VALUES ('mymagicpassword');
INSERT 0 1
=>SELECT usersalt, userpwd FROM userauth;
           usersalt            |                           userpwd                
-------------------------------+---------------------------------------------------
 $2a$10$7FqU5a/DrM.CWGlQPlGz6e | $2a$10$7FqU5a/DrM.CWGlQPlGz6eLjuN3UTwh/hlgxTnogS..
(1 row)

answered Aug 18, 2011 at 21:17

Grzegorz Szpetkowski

38.1k6 gold badges94 silver badges140 bronze badges

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

1 Comment

Noodle Over a year ago

I think you're right - I have to change the way I approach the problem. Perhaps I was thinking in the Sybase/SQL Server mentality of selecting into variables. The SELECT INTO NEW.columnname seems like a good workaround and perhaps a better approach than that which I set out to achieve.

Daniel Lyons · Accepted Answer · 2011-08-18 16:35:10Z

This isn't really an answer to the question you're asking, but I had to do a similar thing recently, and I did it with a view and rules instead of triggers. I hope this is helpful despite not really being an answer to the question your asked directly.

Here is my user table and view:

CREATE TABLE users_t (
  username VARCHAR(16) PRIMARY KEY,
  email    VARCHAR UNIQUE,
  password CHAR(60),
  salt     CHAR(29)
);

CREATE VIEW users AS SELECT username, email, '********'::varchar AS password FROM users_t;

The insert rule takes care of generating the salt value:

CREATE RULE users_insert_rule AS ON INSERT TO users DO INSTEAD
INSERT INTO users_t (username, email, password, salt) 
  SELECT new.username, new.email, crypt(new.password, salt.salt), salt.salt 
  FROM (SELECT gen_salt('bf') as salt) salt
RETURNING username, email, '********'::varchar;

For completeness I'm including the update and delete rules, even though they aren't especially relevant:

CREATE RULE users_update_rule AS ON UPDATE TO users DO INSTEAD
  UPDATE users_t 
  SET 
    username = new.username, 
    email = new.email, 
    password = crypt(new.password, salt)
RETURNING username, email, '********'::varchar;

CREATE RULE users_delete_rule AS ON DELETE TO users DO INSTEAD
  DELETE FROM users_t WHERE username = old.username
RETURNING username, email, '********'::varchar;

Then I wrote an authentication function which takes care of using the salt:

CREATE OR REPLACE FUNCTION authenticate(varchar, varchar) RETURNS setof users AS $$
SELECT * FROM users 
WHERE username =
  (SELECT username FROM users_t 
   WHERE username = $1 AND crypt($2, salt) = password)
$$ LANGUAGE sql;

So you can "log in" by querying SELECT * FROM authenticate('username', 'password'); and your users are created by inserting into the users view. You can set the permissions such that you have an unprivileged role that can see the users view but not the underlying users_t table. This way you have an easy to interface with users table with salted passwords without having to deal with the salt in the application.

Thanks very much. I think the solutions presented here from both you and Grzegorz Szpetkowski are eminently usable and both point towards the SELECT INTO NEW.columnname approach, rather than intermediate variables.

Collectives™ on Stack Overflow

Assigning variables to output parameters in a PostgreSQL trigger

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related