SQL User Management using postgres’ users and passwords

author:

fjf2002

This is an alternative to chapter SQL User Management, solely using the PostgreSQL built-in table pg_catalog.pg_authid for user management. This means

• no dedicated user table (aside from pg_authid) is required

• PostgreSQL’s users and passwords (i. e. the stuff in pg_authid) are also used at the PostgREST level.

Note

Only PostgreSQL users with SCRAM-SHA-256 password hashes (the default since PostgreSQL v14) are supported.

Warning

This is experimental. We can’t give you any guarantees, especially concerning security. Use at your own risk.

Working with pg_authid and SCRAM-SHA-256 hashes

As in SQL User Management, we create a basic_auth schema:

-- We put things inside the basic_auth schema to hide
-- them from public view. Certain public procs/views will
-- refer to helpers and tables inside.
CREATE SCHEMA IF NOT EXISTS basic_auth;

As in SQL User Management, we create the pgcrypto and pgjwt extensions. Here we prefer to put the extensions in its own schemas:

CREATE SCHEMA ext_pgcrypto;
ALTER SCHEMA ext_pgcrypto OWNER TO postgres;
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA ext_pgcrypto;

Concerning the pgjwt extension, please cf. to Client Auth.

CREATE SCHEMA ext_pgjwt;
ALTER SCHEMA ext_pgjwt OWNER TO postgres;
CREATE EXTENSION IF NOT EXISTS pgjwt WITH SCHEMA ext_pgjwt;

In order to be able to work with postgres’ SCRAM-SHA-256 password hashes, we also need the PBKDF2 key derivation function. Luckily there is a PL/pgSQL implementation on stackoverflow:

CREATE FUNCTION basic_auth.pbkdf2(salt bytea, pw text, count integer, desired_length integer, algorithm text) RETURNS bytea
    LANGUAGE plpgsql IMMUTABLE
    AS $$
DECLARE
  hash_length integer;
  block_count integer;
  output bytea;
  the_last bytea;
  xorsum bytea;
  i_as_int32 bytea;
  i integer;
  j integer;
  k integer;
BEGIN
  algorithm := lower(algorithm);
  CASE algorithm
  WHEN 'md5' then
    hash_length := 16;
  WHEN 'sha1' then
    hash_length = 20;
  WHEN 'sha256' then
    hash_length = 32;
  WHEN 'sha512' then
    hash_length = 64;
  ELSE
    RAISE EXCEPTION 'Unknown algorithm "%"', algorithm;
  END CASE;
  --
  block_count := ceil(desired_length::real / hash_length::real);
  --
  FOR i in 1 .. block_count LOOP
    i_as_int32 := E'\\000\\000\\000'::bytea || chr(i)::bytea;
    i_as_int32 := substring(i_as_int32, length(i_as_int32) - 3);
    --
    the_last := salt::bytea || i_as_int32;
    --
    xorsum := ext_pgcrypto.HMAC(the_last, pw::bytea, algorithm);
    the_last := xorsum;
    --
    FOR j IN 2 .. count LOOP
      the_last := ext_pgcrypto.HMAC(the_last, pw::bytea, algorithm);

      -- xor the two
      FOR k IN 1 .. length(xorsum) LOOP
        xorsum := set_byte(xorsum, k - 1, get_byte(xorsum, k - 1) # get_byte(the_last, k - 1));
      END LOOP;
    END LOOP;
    --
    IF output IS NULL THEN
      output := xorsum;
    ELSE
      output := output || xorsum;
    END IF;
  END LOOP;
  --
  RETURN substring(output FROM 1 FOR desired_length);
END $$;

ALTER FUNCTION basic_auth.pbkdf2(salt bytea, pw text, count integer, desired_length integer, algorithm text) OWNER TO postgres;

Analogous to SQL User Management creates the function basic_auth.user_role, we create a helper function to check the user’s password, here with another name and signature (since we want the username, not an email address). But contrary to SQL User Management, this function does not use a dedicated users table with passwords, but instead utilizes the built-in table pg_catalog.pg_authid:

CREATE FUNCTION basic_auth.check_user_pass(username text, password text) RETURNS name
    LANGUAGE sql
    AS
$$
  SELECT rolname AS username
  FROM pg_authid
  -- regexp-split scram hash:
  CROSS JOIN LATERAL regexp_match(rolpassword, '^SCRAM-SHA-256\$(.*):(.*)\$(.*):(.*)$') AS rm
  -- identify regexp groups with sane names:
  CROSS JOIN LATERAL (SELECT rm[1]::integer AS iteration_count, decode(rm[2], 'base64') as salt, decode(rm[3], 'base64') AS stored_key, decode(rm[4], 'base64') AS server_key, 32 AS digest_length) AS stored_password_part
  -- calculate pbkdf2-digest:
  CROSS JOIN LATERAL (SELECT basic_auth.pbkdf2(salt, check_user_pass.password, iteration_count, digest_length, 'sha256')) AS digest_key(digest_key)
  -- based on that, calculate hashed passwort part:
  CROSS JOIN LATERAL (SELECT ext_pgcrypto.digest(ext_pgcrypto.hmac('Client Key', digest_key, 'sha256'), 'sha256') AS stored_key, ext_pgcrypto.hmac('Server Key', digest_key, 'sha256') AS server_key) AS check_password_part
  WHERE rolpassword IS NOT NULL
    AND pg_authid.rolname = check_user_pass.username
    -- verify password:
    AND check_password_part.stored_key = stored_password_part.stored_key
    AND check_password_part.server_key = stored_password_part.server_key;
$$;

ALTER FUNCTION basic_auth.check_user_pass(username text, password text) OWNER TO postgres;

Public User Interface

Analogous to SQL User Management, we create a login function which takes a username and password and returns a JWT if the credentials match a user in the internal table. Here we use the username instead of the email address to identify a user.

Logins

As described in Client Auth, we’ll create a JWT token inside our login function. Note that you’ll need to adjust the secret key which is hard-coded in this example to a secure (at least thirty-two character) secret of your choosing.

CREATE TYPE basic_auth.jwt_token AS (
  token text
);

-- if you are not using psql, you need to replace :DBNAME with the current database's name.
ALTER DATABASE :DBNAME SET "app.jwt_secret" to 'reallyreallyreallyreallyverysafe';


CREATE FUNCTION public.login(username text, password text) RETURNS basic_auth.jwt_token
    LANGUAGE plpgsql security definer
    AS $$
DECLARE
  _role name;
  result basic_auth.jwt_token;
BEGIN
  -- check email and password
  SELECT basic_auth.check_user_pass(username, password) INTO _role;
  IF _role IS NULL THEN
    RAISE invalid_password USING message = 'invalid user or password';
  END IF;
  --
  SELECT ext_pgjwt.sign(
      row_to_json(r), current_setting('app.jwt_secret')
    ) AS token
    FROM (
      SELECT login.username as role,
        extract(epoch FROM now())::integer + 60*60 AS exp
    ) r
    INTO result;
  RETURN result;
END;
$$;

ALTER FUNCTION public.login(username text, password text) OWNER TO postgres;

Permissions

Analogous to SQL User Management: Your database roles need access to the schema, tables, views and functions in order to service HTTP requests. Recall from the Overview of Role System that PostgREST uses special roles to process requests, namely the authenticator and anonymous roles. Below is an example of permissions that allow anonymous users to attempt to log in.

-- the names "anon" and "authenticator" are configurable and not
-- sacred, we simply choose them for clarity
CREATE ROLE anon NOINHERIT;
CREATE role authenticator NOINHERIT LOGIN PASSWORD 'secret';
GRANT anon TO authenticator;

GRANT EXECUTE ON FUNCTION public.login(username text, password text) TO anon;

Since the above login function is defined as security definer, the anonymous user anon doesn’t need permission to access the table pg_catalog.pg_authid . grant execute on function is included for clarity but it might not be needed, see Functions for more details.

Choose a secure password for role authenticator. Do not forget to configure PostgREST to use the authenticator user to connect, and to use the anon user as anonymous user.

Testing

Let us create a sample user:

CREATE ROLE foo PASSWORD 'bar';

Test at the SQL level

Execute:

SELECT * FROM public.login('foo', 'bar');

This should return a single scalar field like:

                                                            token
-----------------------------------------------------------------------------------------------------------------------------
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9vIiwiZXhwIjoxNjY4MTg4ODQ3fQ.idBBHuDiQuN_S7JJ2v3pBOr9QypCliYQtCgwYOzAqEk
(1 row)

Test at the REST level

An API request to call this function would look like:

HTTPCurl

POST /rpc/login HTTP/1.1

{ "username": "foo", "password": "bar" }

The response would look like the snippet below. Try decoding the token at jwt.io. (It was encoded with a secret of reallyreallyreallyreallyverysafe as specified in the SQL code above. You’ll want to change this secret in your app!)

{
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoic2VwcCIsImV4cCI6MTY2ODE4ODQzN30.WSytcouNMQe44ZzOQit2AQsqTKFD5mIvT3z2uHwdoYY"
}

A more sophisticated test at the REST level

Let’s add a table, intended for the foo user:

CREATE TABLE public.foobar(foo int, bar text, baz float);
ALTER TABLE public.foobar owner TO postgres;

Now try to get the table’s contents with:

HTTPCurl

GET /foobar HTTP/1.1

This should fail — of course, we haven’t specified the user, thus PostgREST falls back to the anon user and denies access. Add an Authorization header. Please use the token value from the login function call above instead of the one provided below.

HTTPCurl

GET /foobar HTTP/1.1
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9vIiwiZXhwIjoxNjY4MTkyMjAyfQ.zzdHCBjfkqDQLQ8D7CHO3cIALF6KBCsfPTWgwhCiHCY

This will fail again — we get Permission denied to set role. We forgot to allow the authenticator role to switch into this user by executing:

GRANT foo TO authenticator;

Re-execute the last REST request. We fail again — we also forgot to grant permissions for foo on the table. Execute:

GRANT SELECT ON TABLE public.foobar TO foo;

Now the REST request should succeed. An empty JSON array [] is returned.