PostgREST
Fazendo JOINs encadeados
A partir da tabela person_emails, vai para persons e depois natural_persons
/person_emails?select=*,persons(*,natural_persons(*))
Fazendo autenticação inteiramente no PostgreSQL
Rode este script
-- Enabling pgcrypto extensionCREATE EXTENSION IF NOT EXISTS pgcrypto; -- Installing JWT functions CREATE OR REPLACE FUNCTION public.url_encode(data BYTEA) RETURNS TEXT LANGUAGE sql AS $$ SELECT translate(encode(data, 'base64'), E'+/=\n', '-_'); $$; CREATE OR REPLACE FUNCTION public.url_decode(data TEXT) RETURNS BYTEA LANGUAGE sql AS $$ WITH t AS (SELECT translate(data, '-_', '+/') AS trans), rem AS (SELECT length(t.trans) % 4 AS remainder FROM t) -- compute padding size SELECT decode( t.trans || CASE WHEN rem.remainder > 0 THEN repeat('=', (4 - rem.remainder)) ELSE '' END, 'base64') FROM t, rem; $$; CREATE OR REPLACE FUNCTION public.algorithm_sign(signables TEXT, secret TEXT, algorithm TEXT) RETURNS TEXT LANGUAGE sql AS $$ WITH alg AS ( SELECT CASE WHEN algorithm = 'HS256' THEN 'sha256' WHEN algorithm = 'HS384' THEN 'sha384' WHEN algorithm = 'HS512' THEN 'sha512' ELSE '' END AS id) -- hmac throws error SELECT public.url_encode(public.hmac(signables, secret, alg.id)) FROM alg; $$; CREATE OR REPLACE FUNCTION sign(payload JSON, secret TEXT, algorithm TEXT DEFAULT 'HS256') RETURNS TEXT LANGUAGE sql AS $$ WITH header AS ( SELECT public.url_encode(convert_to('{"alg":"' || algorithm || '","typ":"JWT"}', 'utf8')) AS data ), payload AS ( SELECT public.url_encode(convert_to(payload::text, 'utf8')) AS data ), signables AS ( SELECT header.data || '.' || payload.data AS data FROM header, payload ) SELECT signables.data || '.' || public.algorithm_sign(signables.data, secret, algorithm) FROM signables; $$; CREATE OR REPLACE FUNCTION verify(token text, secret text, algorithm text DEFAULT 'HS256') RETURNS table(header json, payload json, valid boolean) LANGUAGE sql AS $$ SELECT convert_from(public.url_decode(r[1]), 'utf8')::json AS header, convert_from(public.url_decode(r[2]), 'utf8')::json AS payload, r[3] = public.algorithm_sign(r[1] || '.' || r[2], secret, algorithm) AS valid FROM regexp_split_to_array(token, '\.') r; $$; -- Creating basic_auth schema and users table CREATE SCHEMA IF NOT EXISTS basic_auth; CREATE TABLE IF NOT EXISTS basic_auth.users ( "login" TEXT PRIMARY KEY, "pass" TEXT NOT NULL CHECK (length("pass") < 512), "role" NAME NOT NULL CHECK (length("role") < 512) ); -- Creating a trigger to check if the user has a matching role in the pg_catalog.pg_roles table CREATE OR REPLACE FUNCTION basic_auth.check_role_exists() RETURNS TRIGGER 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 database role: ' || new.role; RETURN NULL; END IF; RETURN new; end $$ language plpgsql; -- Creating constraint trigger DROP TRIGGER IF EXISTS ensure_user_role_exists ON basic_auth.users; CREATE CONSTRAINT TRIGGER ensure_user_role_exists AFTER INSERT OR UPDATE ON basic_auth.users FOR EACH ROW EXECUTE PROCEDURE basic_auth.check_role_exists(); -- Creating trigger to encrypt passwords CREATE OR REPLACE FUNCTION basic_auth.encrypt_pass() RETURNS TRIGGER 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 $$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS encrypt_pass ON basic_auth.users; CREATE TRIGGER encrypt_pass BEFORE INSERT OR UPDATE ON basic_auth.users FOR EACH ROW EXECUTE PROCEDURE basic_auth.encrypt_pass(); CREATE TYPE basic_auth.jwt_token AS ( token text ); -- Creating function to return the role from a given login and password CREATE OR REPLACE FUNCTION basic_auth.user_role("login" TEXT, "pass" TEXT) RETURNS NAME LANGUAGE plpgsql AS $$ BEGIN RETURN ( SELECT "role" FROM basic_auth.users WHERE users."login" = user_role."login" AND users."pass" = crypt(user_role."pass", users."pass") ); END; $$; -- Setting JWT secret /* CHANGE DATABASE HERE!!!! */ --ALTER DATABASE postgres SET "app.settings.jwt_secret" TO 'batata'; /* CHANGE THE SCHEMA HERE!!!! */ -- login should be on your exposed schema -- JWT Token generation CREATE OR REPLACE FUNCTION login("login" TEXT, "pass" TEXT) RETURNS basic_auth.jwt_token AS $$ DECLARE _role NAME; result basic_auth.jwt_token; BEGIN -- check login and password SELECT basic_auth.user_role("login", "pass") INTO _role; IF _role IS NULL THEN raise invalid_password USING MESSAGE = 'invalid login or password'; END IF; SELECT sign( row_to_json(r), current_setting('app.settings.jwt_secret') ) AS token FROM ( SELECT _role as role, login."login" as "login", extract(EPOCH FROM now() + '1 day'::INTERVAL)::INTEGER AS exp ) r INTO result; RETURN result; END; $$ LANGUAGE plpgsql; -- the names "anon" and "authenticator" are configurable and not -- sacred, we simply choose them for clarity CREATE ROLE anon; CREATE ROLE authenticator noinherit LOGIN PASSWORD 'YOUR_SECRET_PASSWORD'; GRANT anon TO authenticator; GRANT usage ON SCHEMA public, basic_auth TO anon; GRANT SELECT ON TABLE pg_authid, basic_auth.users TO anon; GRANT EXECUTE ON FUNCTION LOGIN(TEXT,TEXT) TO anon;
Criando um arquivo de configuração
Crie o arquivo de configuração para o PostgREST (postgrest.conf)
db-uri = "postgres://authenticator:YOUR_SECRET_PASSWORD@localhost:5432/postgres"db-schema = "public" db-anon-role = "anon" jwt-secret = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" app.settings.jwt-secret = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"