Example of how you can do authentication on Postgres.

auth_activate.sql

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;

auth_check.sql

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;

auth_create.sproc.sql

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;

auth_table.sql

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)
);