Hashing and Verifying passwords with postgresql extension pgcrypto

password_auth.sql

```sql

-- Create the extension if it doesn't exist
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create table
CREATE TABLE IF NOT EXISTS users(
    id serial not null primary key,
    username text not null unique,
    password text not null,
    salt text not null
);

-- Create the trigger function
CREATE OR REPLACE FUNCTION hash_password() RETURNS TRIGGER AS $$
BEGIN
  NEW.salt := gen_salt('bf', 8);
  NEW.password := crypt(NEW.password, NEW.salt);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- Create the trigger
CREATE TRIGGER hash_password_trigger
BEFORE INSERT OR UPDATE OF password ON users
FOR EACH ROW
EXECUTE FUNCTION hash_password();


CREATE OR REPLACE FUNCTION verify_password(uname text, pswd text)
RETURNS BOOLEAN AS $$
DECLARE
  hashed_password text;
  salt text;
BEGIN
  SELECT users.password, users.salt INTO hashed_password, salt FROM users WHERE users.username = uname;
  RETURN hashed_password IS NOT NULL AND crypt(pswd, salt) = hashed_password;
END;
$$ LANGUAGE plpgsql;


-- Create a function to generate a JWT token
CREATE OR REPLACE FUNCTION generate_jwt(payload jsonb, secret text)
RETURNS text AS $$
BEGIN
  RETURN hmac(payload::text, secret, 'sha256');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION verify_jwt(token text, secret text)
RETURNS boolean AS $$
DECLARE
  payload jsonb;
  calculated_token text;
BEGIN
  payload := (SELECT jsonb_array_elements(jsonb_array_elements(regexp_split_to_array(token, '\.'))->0));
  calculated_token := hmac(payload::text, secret, 'sha256');
  RETURN calculated_token = token;
END;
$$ LANGUAGE plpgsql;

```