SQL User Management using postgres’ users and passwords



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.


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


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

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
    AS $$
  hash_length integer;
  block_count integer;
  output bytea;
  the_last bytea;
  xorsum bytea;
  i_as_int32 bytea;
  i integer;
  j integer;
  k integer;
  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;
    RAISE EXCEPTION 'Unknown algorithm "%"', algorithm;
  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;
    IF output IS NULL THEN
      output := xorsum;
      output := output || xorsum;
    END IF;
  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 how 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
  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.


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.

-- 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, OUT token text)
    LANGUAGE plpgsql security definer
    AS $$
  _role name;
  -- check email and password
  SELECT basic_auth.check_user_pass(username, password) INTO _role;
    RAISE invalid_password USING message = 'invalid user or password';
  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 token;

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


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.

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.


Let us create a sample user:


Test at the SQL level


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

This should return a single scalar field like:

(1 row)

Test at the REST level

An API request to call this function would look like:

curl "http://localhost:3000/rpc/login" \
  -X POST -H "Content-Type: application/json" \
  -d '{ "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:

curl "http://localhost:3000/foobar"

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.

curl "http://localhost:3000/foobar" \
  -H "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.