PostgreSQL: conditional statements in functions

Question

I've been trying to create a basic user authentication system within postgreSQL 9.4, but have been coming unstuck. My users table looks like this:

-- Users table
CREATE TABLE users (
  user_id SERIAL NOT NULL PRIMARY KEY,

  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT NOT NULL,
  password TEXT NOT NULL,

  failed_login_attempts INT NOT NULL DEFAULT 0
    CONSTRAINT positive_login_attempts CHECK (failed_login_attempts >= 0), 
  last_failed_login_attempt TIMESTAMP NULL,

  UNIQUE(email),

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP NULL
);

These functions work fine:

-- check to see if a user exists
CREATE OR REPLACE FUNCTION user_exists (auth_email VARCHAR(254))
RETURNS SETOF users AS $$
  SELECT *
  FROM users
  WHERE email = auth_email
$$ LANGUAGE SQL;

-- authenticates a user against the system
CREATE OR REPLACE FUNCTION authenticate_user (auth_email VARCHAR(254), auth_password VARCHAR(72))
RETURNS SETOF users AS $$
  SELECT *
  FROM users
  WHERE email = auth_email
  AND password = crypt(auth_password, password))
$$ LANGUAGE SQL;

But then, when I try and combine these, I fall flat on my face. In semi-pseudocode, what I'd like to do is this:

-- login function
CREATE OR REPLACE FUNCTION user_login (auth_email VARCHAR(254), auth_password VARCHAR(72)) RETURNS SETOF users AS $$
  IF EXISTS (SELECT COUNT(*) FROM user_exists(auth_email))
    IF EXISTS (SELECT COUNT(*) FROM authenticate_user (auth_email, auth_password))
      -- set the failed_login_attempts value to 0
      -- set the last failed login attempt as NULL
      -- return the user details
    ELSE
      -- increment the failed_login_attempts value
      -- set the last failed login attempt as the current time
      -- return nothing
    END IF;
  ELSE
    -- return nothing
  END IF;
$$ LANGUAGE SQL;

Is that possible? Am I going down entirely the wrong lines?

The purpose of the 'failed login attempts' would be to set it to incrementally longer cooling off periods - eg failed attempts:

1 second
2 seconds
4s
8s
16s
...

Nicolai · Accepted Answer · 2015-06-10 15:50:39Z

2

Is it required that functions are written using SQL language? There is a solution with PLPGSQL procedure if you accept PLPGSQL.

CREATE OR REPLACE FUNCTION user_login (auth_email VARCHAR(254), auth_password VARCHAR(72)) 
RETURNS SETOF users AS 
$$
DECLARE 
    found_user users;
BEGIN
    SELECT u.* 
    FROM users u
    WHERE u.email=auth_email
    INTO found_user;

    -- Check password here using your algorithm
    IF found_user.password = auth_password THEN
        RETURN NEXT found_user;
        RETURN;
    END IF;

    UPDATE users SET
          failed_login_attempts = failed_login_attempts + 1
        , last_failed_login_attempt = now()
    WHERE user_id = found_user.user_id;
END;
$$
LANGUAGE plpgsql;

answered Jun 10, 2015 at 15:50

Nicolai

5,8251 gold badge28 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: conditional statements in functions

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