Secure One-Time Tokens with Supabase and Postgres

Nonces (Numbers Used Once) or also called one-time tokens are crucial for enhancing security in various web application scenarios.

While Redis is often the go-to solution for storing ephemeral data like nonces, Postgres can be a robust and efficient alternative. It's especially useful when you're starting with Supabase and want to keep your stack simple and consolidated, without adding another service.

Let's explore how to create and manage nonces using Postgres, and why it's a solid choice for many use cases.

What Are Nonces and Why Do We Need Them?

Nonces are unique, temporary tokens used to prevent replay attacks and ensure the freshness of requests. They're like digital Post-it notes that say, "This request is valid just once, and only for a short time."

Use Cases for Nonces

Nonces are essential for various security and operational scenarios, including:

1. OAuth Flow: Prevent CSRF attacks during authentication
2. Password Reset: Ensure one-time use of reset links
3. Email Verification: Validate email addresses securely
4. API Request Signing: Add an extra layer of security to API calls
5. Two-Factor Authentication (2FA): Generate and validate one-time codes
6. CSRF Protection: Mitigate Cross-Site Request Forgery vulnerabilities
7. Preventing Double Submissions: Avoid duplicate form submissions

I came to this implementation while working on a project that required secure nonces for validating oAuth tokens. I wanted to keep the stack simple and avoid adding another service like Redis for nonce management. Postgres, with its ACID compliance and powerful features, turned out to be a perfect fit.

Why Postgres is a Solid Choice for Nonce Management

Here are some reasons why Postgres is a great option for managing nonces:

1. Data Consistency: Postgres's ACID compliance ensures reliable nonce creation and validation
2. Built-in Expiration: Use Postgres's time-based functions for automatic nonce expiration
3. Scalability: Postgres can handle high volumes of nonce operations efficiently
4. Simplicity: With Supabase, you're already using Postgres - no need for an additional service
5. Transactional Operations: Atomic read-and-delete operations prevent race conditions
6. Flexible Storage: Store additional metadata with nonces using JSONB fields
7. Automatic Cleanup: Use Postgres's built-in job scheduling for removing expired nonces

Implementation with Supabase and Postgres

Let's break down the key components of our nonce management system.

First, we need a table to store nonces, which we name nonces. This table will hold the nonce ID, challenge (if any), data (as JSONB), and expiration time.

The Nonces Table

create table if not exists public.nonces (
  id uuid primary key default gen_random_uuid(),
  nonce uuid not null default gen_random_uuid() unique,
  challenge varchar(255),
  data jsonb not null default '{}',
  expires_at timestamp with time zone not null default now() + interval '10 minutes'
);
 
revoke all on public.nonces from public, service_role;
 
grant select, insert, update, delete on public.nonces to service_role;
 
alter table public.nonces enable row level security;

This table structure allows for:

• Unique nonce generation
• Optional challenge association
• Flexible data storage with JSONB (to store user IDs, etc.)
• Automatic expiration after 10 minutes (customizable)

Creating a Nonce

We now create a function to generate a new nonce. We'll associate it with a challenge and store additional data if needed:

create or replace function public.create_nonce(challenge varchar(255), data jsonb)
returns uuid as $$
declare
  nonce_id uuid;
begin
  insert into public.nonces(challenge, data)
  values (challenge, data)
  returning id into nonce_id;
 
  return nonce_id;
end;
$$ language plpgsql;

To create a nonce, call this function with the challenge and data you want to store. It returns the nonce ID, which you'd normally use as a way to identify the nonce coming back from another system.

Reading and Validating a Nonce

When we need to read and validate a nonce, we use the function read_nonce. This function reads the nonce, checks for expiration, and deletes it if it's valid, so it can't be used again:

create or replace function public.read_nonce(nonce_id uuid)
returns setof public.nonces as $$
declare
  query public.nonces;
begin
  select *
  from public.nonces
  where id = nonce_id
  for update
  into query;
 
  if query.expires_at < now() then
    delete from public.nonces where id = nonce_id;
    raise exception 'nonce is expired';
  end if;
 
  if query.id is null then
    raise exception 'nonce not found';
  end if;
 
  delete from public.nonces where id = nonce_id;
 
  return next query;
end
$$ language plpgsql;

This function:

1. Reads the nonce
2. Checks for expiration
3. Deletes the nonce if it's valid (ensuring one-time use)
4. Raises exceptions for expired or non-existent nonces

Automatic Cleanup

To keep the nonces table clean and efficient, we can schedule a Postgres job to delete expired nonces regularly.

Here's how you can do it using the cron extension:

create or replace function kit.delete_expired_nonces() returns void as $$
begin
  delete from public.nonces where expires_at < now();
end;
$$ language plpgsql;
 
select cron.schedule(
  'delete-expired-nonces',
  '0 3 * * *',
  'CALL kit.delete_expired_nonces()'
);

NB: we have a private schema kit where we store our utility functions. You can replace kit with public if you don't have a separate schema.

This setup automatically removes expired nonces daily at 3 AM, keeping your database tidy.

Using Nonces in Your Application

Here's a quick example of how you might use nonces in a password reset flow:

// Generate a nonce for password reset
const { data, error } = await supabase.rpc('create_nonce', {
  data: { user_id: 123 }
});
 
if (error) throw error;
const nonceId = data;
 
// Send password reset email with nonce ID in the link
 
// When user clicks the link, validate the nonce
try {
  const { data, error } =
    await supabase.rpc('read_nonce', {
      nonce_id: nonceId
    });
 
  if (error) {
    throw error;
  }
 
  // Nonce is valid, allow password reset
  // The nonce has been automatically deleted
} catch (error) {
  // Handle invalid or expired nonce
}

NB: This example uses Supabase's rpc method to call the Postgres functions. Since this function requires the service_role, you need to ensure your Supabase client has the necessary permissions.

This prevents direct access to the nonces table which is for server-side use only.

Conclusion

Using Postgres for nonce management with Supabase offers a robust, scalable, and easy-to-implement solution, without the need for additional services.

It leverages your existing database, provides strong consistency guarantees, and integrates seamlessly with your Supabase-powered application.

Whether you're implementing OAuth flows, securing API calls, or managing one-time operations, this Postgres-based nonce system has got you covered.

Ciao! 👋