Created
March 16, 2016 16:31
-
-
Save hays-hutton/6ef359149786e92623c5 to your computer and use it in GitHub Desktop.
A Simplified Schema Example for PostgREST
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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