Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A Simplified Schema Example for PostgREST
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.