DB level authentication with PostgreSQL

pgauth.sql

RESET SESSION AUTHORIZATION;
DROP OWNED BY webuser, login, user1;
DROP ROLE IF EXISTS webuser, login, user1;

CREATE ROLE login LOGIN NOINHERIT;
CREATE ROLE webuser NOINHERIT;

DROP SCHEMA IF EXISTS auth CASCADE;
CREATE SCHEMA auth;
SET search_path TO auth;

REVOKE ALL ON schema public FROM public;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO login, webuser;
GRANT USAGE ON SCHEMA auth, public TO login, webuser;

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id serial NOT NULL PRIMARY KEY,
    role name, -- implicitly UNIQUE
    email varchar(75) NOT NULL UNIQUE,
    password varchar(128) NOT NULL,
    CONSTRAINT valid_email CHECK ( email ~* '^.+@.+\..+$' )
);

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-------------------------------------------------------------------------------
---========================= CREATE USER ===================================---
-------------------------------------------------------------------------------

DROP SEQUENCE IF EXISTS role_counter;
CREATE SEQUENCE role_counter;

CREATE OR REPLACE FUNCTION create_user() RETURNS TRIGGER AS $$
DECLARE
    role name;
BEGIN
    role := 'user' || nextval('role_counter');
    EXECUTE FORMAT('CREATE ROLE "%I" INHERIT', role);
    EXECUTE FORMAT('GRANT %I TO login', role);
    EXECUTE FORMAT('GRANT webuser TO %I', role);
    NEW.role = role;
    NEW.password = crypt(NEW.password, gen_salt('bf'));
    RETURN new;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS create_user on users;
CREATE TRIGGER create_user BEFORE INSERT on users
    FOR EACH ROW EXECUTE PROCEDURE create_user();


-------------------------------------------------------------------------------
---========================= LOGIN/LOGOUT ==================================---
-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION public.login(email varchar(75), password varchar(128))
RETURNS BOOLEAN AS $$
DECLARE
    rolname name;
BEGIN
    IF EXISTS(
        SELECT 1 FROM pg_tables WHERE tablename = '_current_role') THEN
        RAISE EXCEPTION 'Nope';
    END IF;

    -- TODO raise error if current_user is different than login;
    SELECT role INTO rolname FROM users u WHERE
        u.email = login.email AND
        u.password = crypt(login.password, u.password);
    IF FOUND THEN
        CREATE TEMPORARY TABLE _current_role (role name NOT NULL PRIMARY KEY);
        GRANT SELECT ON _current_role TO PUBLIC;
        INSERT INTO _current_role VALUES (rolname);
        RETURN true;
    ELSE RETURN false;
    END IF;
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.logout() RETURNS VOID AS $$
BEGIN DROP TABLE IF EXISTS _current_role; END
$$ LANGUAGE plpgsql SECURITY DEFINER;

-------------------------------------------------------------------------------
---========================= CHECK ROLE ====================================---
-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION check_role() RETURNS BOOLEAN AS $$
DECLARE
rolname name;
BEGIN
    SELECT role INTO STRICT rolname FROM _current_role WHERE role = current_user;
    RETURN TRUE;
END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION check_role_trigger() RETURNS TRIGGER AS $$
DECLARE
rolname name;
BEGIN
    SELECT role INTO STRICT rolname FROM _current_role WHERE role = current_user;
    RETURN NULL;
END
$$ LANGUAGE plpgsql;

-------------------------------------------------------------------------------
---========================= PATCH TABLE ===================================---
-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION patch_table(name text) RETURNS VOID AS $$
DECLARE
orig text;
BEGIN
    orig := name || '_orig';

    EXECUTE FORMAT('ALTER TABLE %I RENAME TO %I', name, orig);
    EXECUTE FORMAT('DROP VIEW IF EXISTS %I', name);
    EXECUTE FORMAT(
        'CREATE VIEW %I AS SELECT * FROM %I WHERE check_role()', name, orig);
    EXECUTE FORMAT('DROP TRIGGER IF EXISTS check_role ON %I', orig);
    EXECUTE FORMAT(
        'CREATE TRIGGER check_role '
        'BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON %I '
        'FOR EACH STATEMENT EXECUTE PROCEDURE check_role_trigger()', orig);
    
END
$$ LANGUAGE plpgsql;

-------------------------------------------------------------------------------
---========================= TEST DATA =====================================---
-------------------------------------------------------------------------------

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    "id" serial NOT NULL PRIMARY KEY,
    "test" varchar(75) NOT NULL UNIQUE
);

INSERT INTO users (email, password) VALUES
    ('[email protected]', 'hello');

INSERT INTO test (test) VALUES ('aaa'), ('bbb');
SELECT patch_table('test');

GRANT USAGE ON SEQUENCE test_id_seq TO webuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON test TO webuser;


-- Simulate new connection from user login
SET SESSION AUTHORIZATION login;
-- login user is free to change role to any user, but role will be checked
-- before each query
SET ROLE user1;

SELECT * FROM test; -- Error

SELECT public.login('[email protected]', 'hello');

SELECT * FROM test; -- OK

-- Logout to let somebody else use the connection
S