PostgreSQL user registration example with UDFs and custom data types

postgres-user-registration.sql

DROP SCHEMA IF EXISTS membership CASCADE;

CREATE SCHEMA membership;

SET SEARCH_PATH TO membership;

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA membership;

CREATE SEQUENCE id_sequence; -- AS bigint

CREATE OR REPLACE FUNCTION generate_id(OUT new_id bigint) AS
$$
DECLARE
  current_epoch    bigint := 1666445527000;
  sequence_id      bigint;
  now_milliseconds bigint;
  shard_id         int    := 1;
BEGIN
  SELECT nextval('id_sequence') % 1024 INTO sequence_id;
  SELECT floor(extract(EPOCH FROM now()) * 1000) INTO now_milliseconds;
  new_id := (now_milliseconds - current_epoch) << 23;
  new_id := new_id | (shard_id << 10);
  new_id := new_id | sequence_id;
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION generate_random_string(IN string_length int DEFAULT 36)
  RETURNS text
AS
$$
SELECT substring(replace(gen_random_uuid()::text, '-', ''), 0, string_length);
$$
  LANGUAGE sql;

-- Пользовательские типы

CREATE TYPE user_summary AS
(
  id                     bigint,
  email                  varchar(200),
  status                 varchar(50),
  can_login              boolean,
  is_admin               boolean,
  display_name           varchar(255),
  user_key               varchar(36),
  email_validation_token varchar(36),
  experience             interval,
  profile                jsonb,
  audit_logs             jsonb,
  notes                  jsonb
);

CREATE TYPE audit_log_type AS enum (
  'Registration',
  'Authentication',
  'Activity',
  'System'
  );

-- Таблицы

DROP TABLE IF EXISTS user_login_status CASCADE;
DROP TABLE IF EXISTS user_roles CASCADE;
DROP TABLE IF EXISTS roles CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS user_logins CASCADE;
DROP TABLE IF EXISTS audit_logs CASCADE;

CREATE TABLE user_login_statuses
(
  id          serial PRIMARY KEY,
  name        varchar(50)  NOT NULL UNIQUE,
  description varchar(255) NOT NULL,
  can_login   boolean      NOT NULL DEFAULT FALSE
);
INSERT INTO user_login_statuses(name, description, can_login)
VALUES ('Active', 'User can login', TRUE),
       ('Suspended', 'User cannot login for a given reason', FALSE),
       ('Not approved', 'User needs to be approved through email validation', FALSE),
       ('Banned', 'User has been banned', FALSE),
       ('Locked', 'User is locked out due to failed login attempts', FALSE);

CREATE TABLE roles
(
  id   serial PRIMARY KEY,
  name varchar(50) NOT NULL UNIQUE
);
INSERT INTO roles(name)
VALUES ('Member'),
       ('Administrator');

CREATE TABLE user_roles
(
  user_id bigint NOT NULL,
  role_id int    NOT NULL,

  CONSTRAINT user_roles_pk PRIMARY KEY (user_id, role_id)
);

CREATE TABLE users
(
  id                bigint       NOT NULL        DEFAULT generate_id() PRIMARY KEY,
  user_key          char(36)     NOT NULL UNIQUE DEFAULT generate_random_string(36),
  validation_token  varchar(36),
  email             varchar(200) NOT NULL UNIQUE,
  first_name        varchar(50),
  last_name         varchar(50),
  hashed_password   varchar(100),
  search_index      tsvector,
  created_at        timestamptz  NOT NULL        DEFAULT now(),
  login_status_id   int          NOT NULL,
  last_logged_in_at timestamptz,
  logins_count      int          NOT NULL        DEFAULT 0,
  profile           jsonb        NOT NULL        DEFAULT '{}',

  CONSTRAINT users_user_login_statuses_fk FOREIGN KEY (login_status_id) REFERENCES user_login_statuses (id)
);

CREATE TABLE user_logins
(
  id             bigint PRIMARY KEY    DEFAULT generate_id(),
  user_id        bigint       NOT NULL,
  provider_name  varchar(50)  NOT NULL DEFAULT 'local',
  provider_key   varchar(255),
  provider_token varchar(255) NOT NULL,

  CONSTRAINT user_logins_users_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE audit_logs
(
  id         bigserial PRIMARY KEY,
  subject    audit_log_type NOT NULL,
  user_id    bigint         NOT NULL,
  entry      text           NOT NULL,
  data       jsonb,
  created_at timestamptz    NOT NULL DEFAULT now(),

  CONSTRAINT user_audit_logs_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

-- Функции

CREATE OR REPLACE FUNCTION get_display_name(u users) RETURNS varchar(255) AS
$$
DECLARE
  display_name varchar(255);
BEGIN
  IF u.first_name IS NOT NULL AND u.last_name IS NOT NULL THEN
    display_name := concat_ws(' ', u.first_name, u.last_name);
  ELSE
    IF u.last_name IS NOT NULL THEN
      display_name := u.last_name;
    ELSE
      display_name := u.email;
    END IF;
  END IF;

  RETURN display_name;
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_user_by_email(user_email varchar(200))
  RETURNS user_summary
AS
$$
DECLARE
  experience      interval;
  found_user      users;
  status_name     varchar(50);
  can_user_login  boolean;
  is_admin        boolean;
  user_audit_logs jsonb;
BEGIN
  SET SEARCH_PATH TO membership;

  IF exists(SELECT 1 FROM users WHERE email = user_email) THEN
    SELECT * FROM users WHERE email = user_email INTO found_user;

    experience := age(now(), found_user.created_at);

    SELECT INTO status_name, can_user_login s.name, s.can_login
    FROM user_login_statuses s
    WHERE s.id = found_user.login_status_id;

    SELECT exists(SELECT 1
                  FROM user_roles ur
                  INNER JOIN roles r ON ur.user_id = found_user.id AND r.id = ur.role_id
                  WHERE r.name = 'Administrator')
    INTO is_admin;

    SELECT jsonb_agg(logs)
    INTO user_audit_logs
    FROM (SELECT *
          FROM audit_logs l
          WHERE l.user_id = found_user.id) logs;

    RETURN (found_user.id,
            found_user.email,
            status_name,
            can_user_login,
            is_admin,
            get_display_name(found_user),
            found_user.user_key,
            found_user.validation_token,
            experience,
            found_user.profile,
            user_audit_logs,
            jsonb_build_object() /* notes */)::user_summary;
  ELSE
    RAISE 'User with email % is not found', user_email;
  END IF;
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_user_id_by_email_and_password(user_email varchar, user_password varchar)
  RETURNS bigint AS
$$
SELECT user_id
FROM user_logins
WHERE provider_name = 'local'
  AND provider_key = user_email
  AND provider_token = crypt(user_password, provider_token);
$$
  LANGUAGE sql;

CREATE OR REPLACE FUNCTION register_user(user_email varchar, raw_password varchar)
  RETURNS table
          (
            new_id           bigint,
            validation_token varchar(36),
            success          bool,
            message          varchar(255)
          )
AS
$$
DECLARE
  user_login_status_id int;
  user_role_id         int;
BEGIN
  SET SEARCH_PATH TO membership;

  IF NOT exists(SELECT 1 FROM users WHERE email = user_email) THEN
    -- Получаем начальный ID статуса логина
    SELECT id FROM user_login_statuses WHERE name = 'Not approved' INTO user_login_status_id;

    -- Создаем пользователя
    INSERT INTO users(email, login_status_id, validation_token)
    VALUES (user_email, user_login_status_id, generate_random_string(36))
    RETURNING id INTO new_id;

    -- Создаем привязку пользователя к роли
    SELECT id FROM roles WHERE name = 'Member' INTO user_role_id;
    INSERT INTO user_roles(user_id, role_id) VALUES (new_id, user_role_id);

    -- Создаем логин через форму
    INSERT INTO user_logins(user_id, provider_key, provider_token)
    VALUES (new_id, user_email, crypt(raw_password, gen_salt('bf', 10)));

    -- Создаем логин через токен
    INSERT INTO user_logins(user_id, provider_name, provider_key, provider_token)
    VALUES (new_id, 'token', 'token', generate_random_string(36));

    -- Создаем запись в аудит логе
    INSERT INTO audit_logs(user_id, subject, entry)
    VALUES (new_id, 'Registration', 'User registered with email ' || user_email);

    success := TRUE;
    message := 'Welcome!';
  ELSE
    success := FALSE;
    message := 'User with email ' || user_email || ' is already exists';
  END IF;

  RETURN QUERY
    SELECT new_id, validation_token, success, message;
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION authenticate_user(login_provider_key varchar, login_provider_token varchar,
                                             login_provider_name varchar DEFAULT 'local')
  RETURNS table
          (
            auth_user_id bigint,
            email        varchar(200),
            display_name varchar(200),
            success      boolean,
            message      text
          )
AS
$$
DECLARE
  found_user     users;
  return_message text;
  is_success     boolean;
  found_id       bigint;
BEGIN
  SET SEARCH_PATH TO membership;

  IF login_provider_name = 'local' THEN
    SELECT get_user_id_by_email_and_password(login_provider_key, login_provider_token) INTO found_id;
  ELSE
    SELECT user_id
    FROM user_logins
    WHERE provider_name = login_provider_name
      AND provider_key = login_provider_key
      AND provider_token = login_provider_token
    INTO found_id;
  END IF;

  IF found_id IS NOT NULL THEN
    SELECT * FROM users u WHERE u.id = found_id INTO found_user;

    INSERT INTO audit_logs(user_id, subject, entry)
    VALUES (found_id, 'Authentication', 'Logged in user using ' || login_provider_name || ' provider');

    UPDATE users u
    SET last_logged_in_at = now(),
        logins_count      = logins_count + 1
    WHERE u.id = found_id;

    display_name := get_display_name(found_user);
    is_success := TRUE;
    return_message := 'Welcome!';
  ELSE
    is_success := FALSE;
    return_message := 'Invalid authentication credentials';
  END IF;

  RETURN QUERY
    SELECT found_id, found_user.email, display_name, is_success, return_message;
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION authenticate_user(user_email varchar, user_password varchar)
  RETURNS table
          (
            auth_user_id bigint,
            email        varchar(200),
            display_name varchar(200),
            success      boolean,
            message      text
          )
AS
$$
BEGIN
  SET SEARCH_PATH TO membership;

  RETURN QUERY
    SELECT * FROM authenticate_user(user_email, user_password, 'local');
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION authenticate_user_by_token(user_token varchar)
  RETURNS table
          (
            auth_user_id bigint,
            email        varchar(200),
            display_name varchar(200),
            success      boolean,
            message      text
          )
AS
$$
BEGIN
  RETURN QUERY
    SELECT * FROM authenticate_user('token', user_token, 'token');
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION change_user_status(
  user_email varchar,
  new_status_id int,
  reason varchar(200)
) RETURNS user_summary AS
$$
DECLARE
  found_id          bigint;
  current_status_id int;
  new_status_name   varchar(50);
  user_record       user_summary;
BEGIN
  SET SEARCH_PATH TO membership;

  SELECT id, login_status_id FROM users u WHERE u.email = user_email INTO found_id, current_status_id;

  IF current_status_id = new_status_id THEN
    RAISE NOTICE 'User with id % status wasn''t changed because current status is already up to date', found_id;

    RETURN get_user_by_email(user_email);
  END IF;

  IF found_id IS NOT NULL THEN
    UPDATE users SET login_status_id = new_status_id WHERE id = found_id;
    SELECT name FROM user_login_statuses WHERE id = new_status_id INTO new_status_name;
    INSERT INTO audit_logs(user_id, subject, entry)
    VALUES (found_id, 'System', 'User with id ' || found_id || ' status was changed to ' || new_status_name || ' because of ' || reason);
    user_record := get_user_by_email(user_email);
  END IF;

  RETURN user_record;
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION activate_user(user_email varchar, reason varchar) RETURNS user_summary AS
$$
DECLARE
  new_status_id int;
BEGIN
  SET SEARCH_PATH TO membership;

  SELECT id FROM user_login_statuses WHERE name = 'Active' INTO new_status_id;
  RETURN change_user_status(user_email, new_status_id, reason);
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION suspend_user(user_email varchar, reason varchar) RETURNS user_summary AS
$$
DECLARE
  new_status_id int;
BEGIN
  SET SEARCH_PATH TO membership;

  SELECT id FROM user_login_statuses WHERE name = 'Suspended' INTO new_status_id;
  RETURN change_user_status(user_email, new_status_id, reason);
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION ban_user(user_email varchar, reason varchar) RETURNS user_summary AS
$$
DECLARE
  new_status_id int;
BEGIN
  SET SEARCH_PATH TO membership;

  SELECT id FROM user_login_statuses WHERE name = 'Banned' INTO new_status_id;
  RETURN change_user_status(user_email, new_status_id, reason);
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION lock_user(user_email varchar, reason varchar) RETURNS user_summary AS
$$
DECLARE
  new_status_id int;
BEGIN
  SET SEARCH_PATH TO membership;

  SELECT id FROM user_login_statuses WHERE name = 'Locked' INTO new_status_id;
  RETURN change_user_status(user_email, new_status_id, reason);
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION change_user_password(user_email varchar, old_user_password varchar, new_ser_password varchar)
  RETURNS user_summary AS
$$
DECLARE
  found_id bigint;
BEGIN
  SELECT get_user_id_by_email_and_password(user_email, old_user_password) INTO found_id;

  IF found_id IS NOT NULL THEN
    UPDATE user_logins
    SET provider_token = crypt(new_ser_password, gen_salt('bf', 10))
    WHERE user_id = found_id
      AND provider_name = 'local';

    INSERT INTO audit_logs(user_id, subject, entry)
    VALUES (found_id, 'Authentication', 'Password changed');
  END IF;

  RETURN get_user_by_email(user_email);
END;
$$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION update_user(user_email varchar, user_first_name varchar, user_last_name varchar, user_profile jsonb)
  RETURNS user_summary AS
$$
DECLARE
  found_id bigint;
BEGIN
  SELECT id FROM users WHERE email = user_email INTO found_id;

  IF found_id IS NOT NULL THEN
    UPDATE users
    SET first_name = user_first_name,
        last_name  = user_last_name,
        profile    = user_profile
    WHERE id = found_id;
  END IF;

  RETURN get_user_by_email(user_email);
END;
$$
  LANGUAGE plpgsql;

-- Использование

DELETE
FROM users
WHERE email = '[email protected]';

SELECT *
FROM register_user('[email protected]', 'simple-password');

SELECT *
FROM get_user_by_email('[email protected]');

SELECT *
FROM activate_user('[email protected]', 'user is active now');

SELECT *
FROM suspend_user('[email protected]', 'user is suspended again');

SELECT *
FROM authenticate_user('[email protected]', 'simple-password', 'local');

SELECT *
FROM update_user('[email protected]', 'Rob', 'Conery', '{
  "favourite_language": "Elixir"
}'::jsonb);

SELECT *
FROM authenticate_user_by_token('b86b7e00b379486eb2e6d47ad03e1134');