Skip to content

Instantly share code, notes, and snippets.

@blargism
Last active February 7, 2018 23:08
Show Gist options
  • Save blargism/d78bbc25d552c7ed3b98ffaef718b715 to your computer and use it in GitHub Desktop.
Save blargism/d78bbc25d552c7ed3b98ffaef718b715 to your computer and use it in GitHub Desktop.
Example of how you can do authentication on Postgres.
CREATE OR REPLACE FUNCTION auth_activate(
_activation character varying,
_member_id integer
) RETURNS member AS $$
DECLARE
_auth auth;
_member member;
BEGIN
SELECT * INTO _auth
FROM
auth
WHERE
activation = _activation AND
member_id = _member_id;
IF _auth.auth_id IS NULL THEN
RAISE EXCEPTION USING MESSAGE = 'That activation token is not valid.';
RETURN NULL;
END IF;
UPDATE auth SET
member_status = 'active',
activation = NULL
WHERE
member_id = _auth.member_id AND
activation = _activation;
SELECT * INTO _member
FROM
member
WHERE
member_id = _auth.member_id;
RETURN _member;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION auth_check(
_username VARCHAR,
_password TEXT
) RETURNS member AS $$
DECLARE
_member member;
_match auth;
BEGIN
SELECT * INTO _match
FROM
auth
WHERE
username = _username AND
(password = crypt(_password, password));
IF _match IS NULL OR _match.member_id IS NULL THEN
RAISE EXCEPTION USING MESSAGE = 'Username or password are bad';
RETURN NULL;
END IF;
SELECT * INTO _member
FROM
member
WHERE
member_id = _match.member_id;
RETURN _member;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION auth_create(
_username VARCHAR,
_password TEXT,
_member_id INTEGER
) RETURNS INTEGER AS $$
DECLARE
_encrypted_password TEXT;
_activation VARCHAR;
_auth_id INT;
BEGIN
PERFORM check_exists('username', _username);
PERFORM check_exists('password', _password);
_encrypted_password = crypt(_password, gen_salt('bf'));
_activation = random_string(24);
INSERT INTO auth
(member_id, username, password, activation, member_status)
VALUES
(_member_id, _username, _encrypted_password, _activation, 'registering')
RETURNING auth_id INTO _auth_id;
RETURN _activation;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE auth (
auth_id serial NOT NULL,
member_id integer REFERENCES member(member_id),
username character varying NOT NULL,
password text,
activation character varying,
member_status member_status,
CONSTRAINT auth_id_pky PRIMARY KEY (auth_id),
CONSTRAINT auth_username_unq UNIQUE (username)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment