A Simplified Schema Example for PostgREST

schema.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS
users (
  email    TEXT PRIMARY KEY CHECK ( email ~* '^.+@.+\..+$' ),
  pass     TEXT NOT NULL CHECK (length(pass) < 256),
  role     NAME NOT NULL CHECK (length(role) < 256)
);

CREATE TABLE IF NOT EXISTS
artists (
  name TEXT PRIMARY KEY CHECK (length(name) < 256),
  mb_id UUID UNIQUE,
  date_formed DATE 
);

CREATE TABLE IF NOT EXISTS
sort_types (
  name TEXT PRIMARY KEY CHECK (length(name) < 256),
  description TEXT NOT NULL CHECK (length(description) < 512)
);

CREATE TABLE IF NOT EXISTS
sorts (
  artist_name TEXT NOT NULL REFERENCES artists(name),
  sort_type_name TEXT NOT NULL REFERENCES sort_types(name),
  ordinal INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS
rating_types (
  name TEXT PRIMARY KEY CHECK (length(name) < 256),
  description TEXT NOT NULL CHECK (length(description) < 512)
);

CREATE TABLE IF NOT EXISTS
ratings (
  artist_name TEXT NOT NULL REFERENCES artists(name),
  email TEXT NOT NULL REFERENCES users(email),
  rating_type_name TEXT NOT NULL REFERENCES rating_types(name),
  rating INTEGER NOT NULL,
  at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

ALTER TABLE ratings ADD CONSTRAINT ratings_pkey
PRIMARY KEY(artist_name, email, rating_type_name);

CREATE OR REPLACE FUNCTION
check_role_exists() RETURNS TRIGGER
  LANGUAGE plpgsql
  AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles AS r WHERE r.rolname = new.role) THEN
    RAISE foreign_key_violation USING message =
      'unknown role: ' || new.role;
    RETURN null;
  END IF;
  RETURN NEW;
END
$$;

DROP TRIGGER IF EXISTS ensure_user_role_exists ON users;
CREATE CONSTRAINT TRIGGER ensure_user_role_exists
  AFTER INSERT OR UPDATE ON users
  FOR EACH ROW
  EXECUTE PROCEDURE check_role_exists();

CREATE OR REPLACE FUNCTION
encrypt_pass() RETURNS TRIGGER
  LANGUAGE plpgsql
  AS $$
BEGIN
  IF tg_op = 'INSERT' OR new.pass <> old.pass THEN
    new.pass = crypt(new.pass, gen_salt('bf'));
  END IF;
  RETURN new;
END
$$;

DROP TRIGGER IF EXISTS encrypt_pass ON users;
CREATE TRIGGER encrypt_pass
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW
  EXECUTE PROCEDURE encrypt_pass();

CREATE OR REPLACE FUNCTION
user_role(email text, pass text) RETURNS name
  LANGUAGE plpgsql
  AS $$
BEGIN
  RETURN (
  SELECT role FROM users
   WHERE users.email = user_role.email
     AND users.pass = crypt(user_role.pass, users.pass)
  );
END;
$$;

CREATE OR REPLACE FUNCTION
signup(email text, pass text) RETURNS VOID
AS $$
  INSERT INTO users (email, pass, role) VALUES
    (signup.email, signup.pass, 'music_lover');
$$ LANGUAGE sql;

DROP TYPE IF EXISTS jwt_claims CASCADE;
CREATE TYPE jwt_claims AS (role TEXT, email TEXT);

CREATE OR REPLACE FUNCTION
login(email TEXT, pass TEXT) RETURNS jwt_claims
  LANGUAGE plpgsql
  AS $$
DECLARE
  _role NAME;
  result JWT_CLAIMS; 
BEGIN
  SELECT user_role(email, pass) INTO _role;
  IF _role IS NULL THEN 
    RAISE invalid_password USING message = 'invalid user or password';
  END IF;
  SELECT _role AS role, login.email AS email INTO result;
  RETURN result;
END;
$$;

CREATE ROLE app_role NOINHERIT LOGIN PASSWORD 'change_this';
CREATE ROLE music_lover;
CREATE ROLE anon;

GRANT anon, music_lover TO app_role;

GRANT SELECT ON TABLE artists TO music_lover;
GRANT SELECT ON TABLE sort_types TO music_lover;
GRANT SELECT ON TABLE sorts TO music_lover;
GRANT SELECT ON TABLE rating_types TO music_lover;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE ratings TO music_lover;

GRANT SELECT, INSERT ON TABLE users TO anon;
GRANT EXECUTE ON FUNCTION
  login(text,text),
  signup(text, text)
  TO anon;