SQL User Management using postgres’ users and passwords
- author:
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 requiredPostgreSQL’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 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;
CREATE EXTENSION 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 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
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.
-- 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 $$
DECLARE
_role name;
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 token;
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.
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:
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.