Deep Dive into PostgREST - Time Off Manager (Part 3)

This is the third and final instalment of "Deep Dive into PostgREST". In the first part, we explored basic CRUD functionalities. In the second part, we moved forward with abstraction and used the acquired knowledge to create a simple request/approval workflow.

In Part 3, we will explore authentication and authorisation options to finish something that might resemble a real-world application.

Authentication with pgcrypto

There's no authorisation without knowing user identity. So let's start there. Our users table from the first part had an email to establish the identity, but no way to verify it. We will address this by adding a password column. Of course, nobody in their right mind would store passwords in plain text.

To securely store users' passwords, we are going to utilise PostgreSQL pgcrypto extension (documentation). This built-in extension provides a suite of cryptographic functions for hashing, encryption, and more. In our case, we will leverage crypt with the gen_salt function to generate password hash using bcrypt algorithm.

Let's start with loading the extension and adding password column:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
ALTER TABLE users ADD COLUMN password_hash TEXT NOT NULL DEFAULT gen_salt('bf');

The new column password_hash will accommodate the variable-length bcrypt hash, while default function gen_salt('bf') creates a unique bcrypt salt for every user.

Now that our table structure is set, let's see how we can securely set and verify passwords using pgcrypto.

When a user sets or changes their password, we'll hash it using crypt before storing it in the password_hash column.Here's how:

UPDATE users SET password_hash = crypt('new_password', gen_salt('bf')) WHERE email = 'user@example.com';

and during login, we will verify the hash of the password the user enters with the stored password_hash:

SELECT user_id FROM users WHERE email = 'user@example.com' AND password_hash = crypt('entered_password', password_hash);

JWT Authentication

With the ability to securely verify users' identity, let's move to the next step and build stateless authentication. The de-facto standard is JSON Web Tokens (JWTs). Represented by digitally signed information, they contain claims about user identity. The digital signature ensures the token's integrity and verifies that it hasn't been tampered with. You can find more in official Introduction to JSON Web Tokens.

While PostgreSQL doesn't have built-in JWT support, we will have to either rely on pgjwt extension (GitHub repo)

CREATE EXTENSION IF NOT EXISTS pgjwt;

or you can re-create the logic by including PL/pgSQL that comes with it (please, make sure you replace/remove the @extschema@ to match the schema you are using). In this article we will use schema jwt.

To make the token signature, we need to re-configure PostgREST to decode JWT tokens and configure the secret inside the database (to be able to use it in our code). For security reasons, the key must be at least 32 characters long. You can either use your own method to generate (for example, your password manager) or add it using the following CLI commands

export LC_CTYPE=C
echo "jwt-secret = \"$(LC_ALL=C tr -dc 'A-Za-z0-9' </dev/urandom | head -c32)\"" >> postgrest.conf

And use the generated secret for the database level configuration parameter, using

ALTER DATABASE time_off_manager SET "pgrst.jwt_secret" to "your-jwt-secret-generated-above1";

Please, make sure the database name is updated accordingly to set it correctly. And, I cannot stress it enough, make sure the secret is really 32 characters.

The web user role

In previous parts of this guide, we have worked with two user roles. The first, in PostgREST terminology, called authenticator, is the role used in the db-uri parameter. It's the role used to access the database and its job is to impersonate other users based on the authentication (or lack thereof) of the HTTP requests. In a real production application, this role should be configured to have limited access.

The second role, called anonymous, is used in db-anon-role parameter. This is the role impersonated for all unauthenticated HTTP requests.

The third role, or roles, representing authenticated web users. In our JWT tokens, we will use one specifically designed for PostgREST.

{
	"role": "time_off_user"
}

When JWT is successfully validated, with a role claim, PostgREST will switch to the database role with the provided name for the duration of the HTTP request. While PostgREST is quite flexible, we will limit the impersonated role for authenticated requests to a single hard-coded role. For our application, it's going to be called time_off_user.

Let's generate some JWTs

The first step is to implement logic to create a JWT token asserting all claims we need for our application - in the case of Time Off Manager, we will rely on user_id and role. As discussed in the previous section, we will use the hard-coded role time_off_user. Let's create the role and grant our authenticator the permissions.

CREATE ROLE time_off_user NOLOGIN;
GRANT time_off_user TO time_off_manager;

Now create a function, users can call directly to verify the identity and generate the JWT token.

CREATE OR REPLACE FUNCTION api.login(email text, password text)
  RETURNS text
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $function$
DECLARE
  user_record public.users;
BEGIN
  SELECT * INTO user_record
  FROM public.users
  WHERE users.email = login.email;

  IF user_record.password_hash = crypt(password, user_record.password_hash) THEN
	RETURN create_jwt(user_record.user_id, 'user_id');
  ELSE
	RAISE EXCEPTION 'Invalid email or password';
  END IF;
END;
$function$;

Missing there is helper create_jwt

CREATE OR REPLACE FUNCTION public.create_jwt(user_id integer, role text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
  payload JSON;
BEGIN
  payload := json_build_object(
    'user_id', user_id,
    'role', 'time_off_user',
    'exp', extract(epoch from now()) + 3600 -- 1-hour expiration
  );
  RETURN jwt.sign(payload, current_setting('pgrst.jwt_secret'));  -- Use configuration value
END;
$function$;

Restart the PostgREST instance to apply the changes, and you can try to generate a token using cURL (assuming you have changed the password as demonstrated in the first section ):

curl -X POST "http://localhost:3000/rpc/login" \
	-d '{"email":"manager2@example.com", "password": "new_password"}' \
	-H "Content-Type: application/json"

If you set everything as expected you will get a base64 encoded token. To verify/debug it, you can try to use JWT.io.

Prepare the permissions

The next step is where things get really interesting. First, we need to clean up excessive permissions. Some obvious, some less so.

First let's start with the revoking all the permissions for time_off_anonymous we provided in previous part.

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA api FROM time_off_anonymous;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA api FROM time_off_anonymous;

and same we need to adjust the default privileges

ALTER DEFAULT PRIVILEGES IN SCHEMA api REVOKE SELECT ON TABLES FROM time_off_anonymous;
ALTER DEFAULT PRIVILEGES IN SCHEMA api REVOKE EXECUTE ON FUNCTIONS FROM time_off_anonymous;

Which is the obvious part. The most likely surprising part is the need to remove EXECUTE permissions for PUBLIC. In PostgreSQL, granting usage on a schema also grants the ability to execute functions to PUBLIC. Unless you revoke these privileges, all users will be able to execute the functions.

For our purposes we want to REVOKE the access for PUBLIC and only grant EXECUTE on function api.login()

GRANT USAGE ON SCHEMA api TO time_off_user;

-- REVOKE EXECUTE
ALTER DEFAULT PRIVILEGES IN SCHEMA api REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA api FROM PUBLIC;

-- GRANT EXECUTE 
GRANT EXECUTE ON FUNCTION api.login to time_off_anonymous;

And finally, we need to establish the necessary permissions for time_off_user, the role which will be used for authenticated requests.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO time_off_user;
GRANT SELECT ON ALL TABLES IN SCHEMA api TO time_off_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA api TO time_off_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO time_off_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT ON TABLES TO time_off_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT EXECUTE ON FUNCTIONS TO time_off_user;

We will now use this to get an overview of only relevant vacation balances for the authenticated user.

Authorization with PostgREST

With authentication working, let's start with the implementation of fine-grained authorization in PostgREST. As a first step, the goal is to ensure that users only see the vacation balances they are supposed to. I.e. either their own (for the regular employees) or their own and the people the user manages (for the managers).

For this we will need to access JWT token claims, specifically user_id. To avoid relatively complex notation, let's setup a helper

CREATE OR REPLACE FUNCTION public.current_user_id()
 RETURNS integer
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
    user_id INTEGER;
BEGIN
    user_id := current_setting('request.jwt.claims', true)::json->>'user_id';

    IF user_id IS NULL THEN
        RAISE EXCEPTION 'User ID not found in JWT claims';
    END IF;

    RETURN user_id::integer;
END;
$function$;

PostgREST seamlessly integrates with PostgreSQL's Row Level Security (RLS) to filter data based on user permissions. RLS policies are rules applied at the row level to determine if a user can access a particular row in a table.

ALTER TABLE public.time_off_transactions ENABLE ROW LEVEL SECURITY;

CREATE POLICY select_own_balance ON public.time_off_transactions 
FOR SELECT USING (public.current_user_id() = user_id);

CREATE POLICY select_supervised_balance ON public.time_off_transactions
FOR SELECT USING (EXISTS (SELECT 1 FROM api.users WHERE users.user_id = time_off_transactions.user_id AND users.manager_user_id = public.current_user_id()));

If you are (and I do hope so) using PostgreSQL 15 and higher, you need to switch the view from the default security definer to invoker.

ALTER VIEW api.vacation_balances SET (security_invoker = true);

This way the view, and the underlying tables will be evaluated using the permissions of the user querying the view, not the view owner. This is the behaviour we want. For versions beyond PostgreSQL 15 and more complex use cases, you might need to implement function-based security instead.

But without further delay, let's test "the magic" and (assuming you have authenticated as a manager) you might try to retrieve the vacation balances using cURL

curl "http://localhost:3000/vacation_balances" \
	-H "Authorization: Bearer ${JWT_TOKEN}"

to get a result similar to

[{"year":2024,"user_id":8,"total_amount":25},
 {"year":2024,"user_id":9,"total_amount":25},
 {"year":2024,"user_id":10,"total_amount":25},
 {"year":2024,"user_id":11,"total_amount":25},
 {"year":2024,"user_id":12,"total_amount":25},
 {"year":2024,"user_id":13,"total_amount":25}]

Guess, it's important to mention the Row Security Policies which are used are much more complex, and the whole topic would deserve another article. For now, I do recommend you to ~~consult the documentation~~, to get more understanding. With the multi-role access you can implement with PostgREST, it might be interesting for you to focus on BYPASSRLS which might be applied to certain role(s).

As mentioned before, Row Level Security is just one way to solve this. The traditional approach would be to use functions to hide the separation logic.

Wrapping up the Time Off Manager

Before we wrap up our tutorial, let's finish the core functionality of the Time Off Manager - the approval workflow. Given the basic concepts covered above, this is going to be a good exercise to use all of it.

Similar how we updated time_off_transactions, we will apply Row Level Security to time_off_requests.

ALTER TABLE public.time_off_requests ENABLE ROW LEVEL SECURITY; 

CREATE POLICY select_own_requests ON public.time_off_requests 
FOR SELECT USING (public.current_user_id() = user_id); 

CREATE POLICY select_subordinate_requests ON public.time_off_requests
FOR SELECT USING (EXISTS (SELECT 1 FROM public.users WHERE users.user_id = time_off_requests.user_id AND users.manager_user_id = public.current_user_id()));

Don't forget to switch the view to security_invoker model.

ALTER VIEW api.pending_requests SET (security_invoker = true);

And we wouldn't be done with requests creation, if we wouldn't update function api.request_time_off to take the advantage of the newly propagated user_id from authentication.

CREATE OR REPLACE FUNCTION api.request_time_off(leave_type text, period daterange)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
    v_leave_type_id INT;
    v_request_id INT;
BEGIN
    -- original validation logic 

    -- Ensure the user is requesting for themselves
    IF public.current_user_id() != request_time_off.user_id THEN
        RAISE EXCEPTION 'User can only request time off for themselves';
    END IF;

    -- the rest of the function
END;
$function$;

The similar update then applies to the api.update_request function to ensure only managers can approve/reject time off requests.

CREATE OR REPLACE FUNCTION api.update_request(request_id integer, new_status text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
    v_requested_user_id INT;
    v_request_manager_id INT;
BEGIN
    -- the original code up to the retrival of the requests
 
    IF public.current_user_id() != v_request_manager_id THEN
        RAISE EXCEPTION 'Only the manager can approve or reject this request';
    END IF;

    -- update the request status
END;
$function$;

And that's about it! Obviously, we can't pretend Time Off Manager is anywhere complete, and the real-life application would require much more than what we have covered. But it should provide a good training platform to allow you to write a real API-based backends using just PostgREST.

Correction 2024-06-11: during the final edits the function public.current_user_id somehow got missing from the Markdown [FIXED].

Authentication with pgcrypto#

JWT Authentication#

The web user role#

Let's generate some JWTs#

Prepare the permissions#

Authorization with PostgREST#

Wrapping up the Time Off Manager#