Securing User Data with Postgres

login.sql

-- See Magnus Hagander: Secure Your Web Webapp passwords in PostgreSQL
-- YouTube: http://youtu.be/MuLMIQBB4Ew

-- Below the SECURITY DEFINER directive makes the query run as the user which
-- created the stored procedure which is the only user which has access to the
-- user table. The app which uses this database would connect with a user which
-- does not have access to this table, preventing queries from pulling all of
-- the data in the table.

CREATE OR REPLACE FUNCTION login(_userid text,
       _pwd text, OUT _email text)
 RETURNS text
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $$ BEGIN
  SELECT email INTO _email FROM users
    WHERE users.userid=lower(_userid)
    AND pwdhash = crypt(_pwd, users.pwdhash);
END;
$$

REVOKE ALL ON users FROM public;