Forem: Rodrigo Mansueli

Secure Your Supabase Auth with email_guard

Rodrigo Mansueli — Fri, 31 Oct 2025 11:16:27 +0000

Building a secure authentication system is not just about strong passwords. It starts with checking the quality of user signups in your Supabase.com project. Disposable email addresses often lead to spam accounts, abuse, and fake users that fill up your database. Also, Gmail's flexible rules (like dots and +tags) let people make many accounts from one email.

Meet email_guard, a Trusted Language Extension (TLE) for PostgreSQL. It works well with Supabase Auth hooks to:

Block disposable email domains with a big, auto-updated list.
Normalize Gmail addresses to stop duplicate signups (by removing dots and +tags).
Offer easy installation that fits in any schema.

When you use this with Supabase's own security tools, like leaked password protection, you build strong protection against bad signups and abuse.

Why You Need email_guard for Supabase Authentication Security

The Problem: Disposable Emails and Gmail Tricks

Disposable email services (like mailinator.com or guerrillamail.com) let anyone make short-term emails. These are good for tests, but often used to:

Make spam or bot accounts.
Skip limits on trials or rates.
Avoid bans by making new accounts fast.

Gmail addressing quirks are tricky too. These all go to the same inbox:

john.doe@gmail.com
j.o.h.n.d.o.e@gmail.com
johndoe+promo@gmail.com
johndoe+whatever@gmail.com

Without fixing this, a user can make many accounts by adding dots or +tags. This breaks rules for one account per person and can cheat referral programs or trials.

The Solution: Smart Email Validation at Signup in Supabase

The email_guard TLE gives you:

A blocklist for disposable domains with over 20,000 known ones (updated weekly).
Gmail normalization that removes dots and +tags, and sets the domain to gmail.com.
A helper for Supabase Auth hooks that checks before creating a user.

All this runs in PostgreSQL, so it is fast, safe, and clear. For more on Supabase auth, see the Supabase Auth docs.

Understanding Trusted Language Extensions (TLE) in Supabase

Before installation, let's quickly explain what a Trusted Language Extension is and why it helps.

What is a TLE?

A Trusted Language Extension (TLE) is a PostgreSQL add-on written in a safe language (like PL/pgSQL or PL/Python). You can install it without special admin rights. This is key for hosted setups like Supabase, where you lack full access.

TLEs come from database.dev, the package manager for PostgreSQL. This makes them:

Easy to install with the Supabase CLI.
Version-controlled for safe updates.
Flexible to place in any schema.
Safe for production.

Learn more in Supabase's Trusted Language Extensions for Postgres blog post or the extensions docs.

Why Use TLEs for Security in Supabase?

With security logic in a TLE:

It runs in the database, near your data.
It stays the same for all apps and calls.
You can check it with version history.
It cannot be skipped by app code.

This is great for auth checks, data rules, and policies.

Step 1: Installing the TLE Infrastructure on Supabase

Before adding email_guard, set up the pg_tle extension (already on Supabase) and the dbdev tool for easy install.

A. Set Up dbdev and Supabase CLI (If Needed)

If not done yet:

Install dbdev CLI: Follow the dbdev getting started guide.
Install Supabase CLI: Check the Supabase CLI docs.
Link your project: Run supabase link to connect to your Supabase database.

Supabase has pg_tle ready, so create it:

CREATE EXTENSION IF NOT EXISTS pg_tle;

B. Generate the Migration with dbdev

Use dbdev to get the latest email_guard (version 0.3.1 or newer) in your migrations:

dbdev add \
  -o ./supabase/migrations/ \
  -v 0.3.1 \
  -s extensions \
  package \
  -n mansueli@email_guard

What this does:

Makes a new migration file in ./supabase/migrations/.
Puts the extension in the extensions schema (good for Supabase).
Uses version 0.3.1 (change for new versions).

Adjust the -o path if your folder is different.

C. Apply the Migration

Push to your Supabase database:

supabase db push

Done! The extension is installed with the full blocklist.

For more on managing extensions, see Supabase database extensions docs.

Step 2: Understanding What You Just Installed

The email_guard extension adds objects in your schema (like extensions):

Table: disposable_email_domains

-- Holds over 20,000 disposable email domains
CREATE TABLE extensions.disposable_email_domains (
  domain text PRIMARY KEY,
  CONSTRAINT disposable_email_domains_domain_lowercase
    CHECK (domain = lower(domain))
);

It fills with domains like:

mailinator.com
guerrillamail.com
10minutemail.com
And many more.

Function: normalize_email(text)

-- Example: normalize_email('J.o.h.n.Doe+promo@gmail.com')
-- Returns: 'johndoe@gmail.com'
SELECT extensions.normalize_email('J.o.h.n.Doe+promo@gmail.com');

What it does:

Makes lowercase.
For Gmail/Googlemail:
- Removes dots from the start.
- Cuts after + (and the +).
- Sets domain to gmail.com.
For others: Just lowercase.

Function: is_disposable_email_domain(text)

-- Check if a domain is disposable
SELECT extensions.is_disposable_email_domain('mailinator.com'); -- true
SELECT extensions.is_disposable_email_domain('gmail.com');      -- false

Smart check:

Looks at parent domains (e.g., sub.mailinator.com matches).
Fast with index.

Function: is_disposable_email(text)

-- Easy check for full emails
SELECT extensions.is_disposable_email('user@guerrillamail.com'); -- true

Hook Helper: hook_prevent_disposable_and_enforce_gmail_uniqueness(jsonb)

This is the key part! For Supabase Auth hooks, it:

Checks disposable domains → Sends 403 error if yes.
Normalizes Gmail → Checks if the same normalized email exists.
Sends 409 error if duplicate.
Allows phone signups or non-email.

Step 3: Wire Up the Supabase Auth Hook for Email Validation

Now, connect it to signups.

Navigate to Auth Hooks in Dashboard

Go to your Supabase Dashboard.
Pick your project.
Go to Authentication → Hooks.
Turn on Before User Created.

Configure the Hook

Pick:

Hook Type: Postgres Function.
Schema: extensions (or your choice).
Function: hook_prevent_disposable_and_enforce_gmail_uniqueness.

Done! The hook is on. See Supabase Auth Hooks docs for details.

What Happens During Signup

When signing up, the hook checks first:

// Try with disposable email
supabase.auth.signUp({
  email: 'test@mailinator.com',
  password: 'secure_password_123'
})
// ❌ Error: { message: "Disposable email addresses are not allowed", status: 403 }

// Try with duplicate Gmail
// If johndoe@gmail.com exists
supabase.auth.signUp({
  email: 'j.o.h.n.d.o.e+test@gmail.com',
  password: 'secure_password_123'
})
// ❌ Error: { message: "A user with this normalized email already exists", status: 409 }

// Valid email
supabase.auth.signUp({
  email: 'alice@example.com',
  password: 'secure_password_123'
})
// ✅ User created

Step 4: Testing Your email_guard Setup on Supabase

Check if it works.

Test 1: Check Disposable Email Detection

-- True
SELECT extensions.is_disposable_email('user@mailinator.com');

// False
SELECT extensions.is_disposable_email('user@gmail.com');

Test 2: Test Gmail Normalization

-- All return 'johndoe@gmail.com'
SELECT extensions.normalize_email('John.Doe@gmail.com');
SELECT extensions.normalize_email('j.o.h.n.d.o.e@googlemail.com');
SELECT extensions.normalize_email('johndoe+promo@gmail.com');

Test 3: Simulate the Hook

-- Disposable reject
SELECT extensions.hook_prevent_disposable_and_enforce_gmail_uniqueness(
  '{"user": {"email": "test@guerrillamail.com"}}'::jsonb
);
-- Error: "Disposable email addresses are not allowed"

// Gmail duplicate (after adding test user)
SELECT extensions.hook_prevent_disposable_and_enforce_gmail_uniqueness(
  '{"user": {"email": "j.o.h.n.d.o.e+test@gmail.com"}}'::jsonb
);
-- Error: "A user with this normalized email already exists"

Step 5: Combining with Supabase's Built-in Protections

email_guard is stronger with Supabase features.

Leaked Password Protection

Supabase checks against HaveIBeenPwned. Turn it on in Dashboard → Authentication → Password Protection.

// Leaked password
supabase.auth.signUp({
  email: 'alice@example.com',
  password: 'password123'  // Leaked
})
// ❌ Error: { message: "Password has been leaked", status: 422 }

Keeping the Blocklist Current in email_guard

email_guard updates itself.

How Updates Work

A GitHub workflow:

Runs every week (Mondays).
Gets new list from disposable-email-domains repo.
Makes upgrade script if changed.
Updates version (e.g., 0.3.1 to 0.3.2).
Saves changes auto.

Upgrading to the Latest Version

For new version:

# Make upgrade migration
dbdev add \
  -o ./supabase/migrations/ \
  -v 0.3.2 \  # New
  -s extensions \
  package \
  -n mansueli@email_guard

# Apply
supabase db push

It adds new domains and keeps data. Watch releases on GitHub repo.

Advanced Usage & Customization for Supabase email_guard

Custom Domain Blocking

Block extra domains:

-- Add one
INSERT INTO extensions.disposable_email_domains (domain)
VALUES ('suspicious-domain.com')
ON CONFLICT DO NOTHING;

-- Remove one
DELETE FROM extensions.disposable_email_domains
WHERE domain = 'some-domain.com';

Checking Existing Users

Audit users:

-- Find disposable
SELECT id, email
FROM auth.users
WHERE extensions.is_disposable_email(email);

-- Find Gmail duplicates
WITH normalized AS (
  SELECT 
    id,
    email,
    extensions.normalize_email(email) AS normalized_email
  FROM auth.users
  WHERE email ILIKE '%@gmail.com'
     OR email ILIKE '%@googlemail.com'
)
SELECT 
  normalized_email,
  array_agg(email) AS duplicate_emails,
  count(*) AS duplicate_count
FROM normalized
GROUP BY normalized_email
HAVING count(*) > 1;

Custom Hook Logic

Make your own hook:

CREATE OR REPLACE FUNCTION public.my_custom_signup_hook(event jsonb)
RETURNS jsonb
LANGUAGE plpgsql
AS $$
DECLARE
  user_email text;
BEGIN
  user_email := event->'user'->>'email';

  IF extensions.is_disposable_email(user_email) THEN
    RAISE EXCEPTION 'Nice try! No disposable emails here.'
      USING HINT = 'Please use a permanent email address',
            ERRCODE = 'P0001';
  END IF;

  -- Add custom rules

  RETURN event;
END;
$$;

Performance Considerations for email_guard in Supabase

Benchmarking

Functions are fast:

-- Domain check: ~0.1ms
SELECT extensions.is_disposable_email_domain('mailinator.com');

-- Normalize: ~0.05ms
SELECT extensions.normalize_email('j.o.h.n.d.o.e+test@gmail.com');

-- Full hook: ~1-2ms

Index Optimization

Extension adds index on auth.users(email). For big databases:

-- Partial index for Gmail
CREATE INDEX IF NOT EXISTS users_gmail_normalized_idx 
ON auth.users (extensions.normalize_email(email))
WHERE email ILIKE '%@gmail.com' OR email ILIKE '%@googlemail.com';

Troubleshooting email_guard on Supabase

Hook Not Triggering

Check setup:

SELECT * FROM supabase_functions.hooks
WHERE hook_name = 'before_user_created';

Check rights:

GRANT EXECUTE ON FUNCTION extensions.hook_prevent_disposable_and_enforce_gmail_uniqueness(jsonb)
TO supabase_auth_admin;

False Positives

If wrong block:

DELETE FROM extensions.disposable_email_domains
WHERE domain = 'legitimate-domain.com';

Report to blocklist repo.

Migration Conflicts

Use different schema:

dbdev add \
  -o ./supabase/migrations/ \
  -v 0.3.1 \
  -s email_guard \
  package \
  -n mansueli@email_guard

Update hook to email_guard.hook_prevent_disposable_and_enforce_gmail_uniqueness.

Security Best Practices for Supabase Authentication

Defense in Depth

Add layers:

Verify emails: Make users confirm.
CAPTCHA: Use hCaptcha on forms.
Rate limits: Stop many tries per IP.
Review accounts: Flag odd patterns.

Monitoring

Track blocks:

CREATE TABLE IF NOT EXISTS blocked_signups (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  email text NOT NULL,
  reason text NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- Log in hook (add yourself)

Privacy Considerations

Avoid logging full emails.
Hash data for stats.
Follow GDPR/CCPA for stored info.

Conclusion: Building a Secure Foundation with email_guard on Supabase

Authentication is the door to your app. Secure it with layers. The email_guard TLE gives a simple way to block disposable emails and stop Gmail duplicates in Supabase.

With Supabase tools like leaked password checks, email verification, and rate limits, you get a strong system that:

✅ Blocks bad signups auto.
✅ Stops abuse without work.
✅ Grows with your app.
✅ Updates weekly.

It runs in the database, so it is clear, checked, and hard to skip.

Next Steps

Install the extension as shown.
Turn on the auth hook in dashboard.
Test with disposable and Gmail tests.
Watch logs for blocks.
Update when new versions come.

For more, see:

Check my previous posts: Building User Authentication with Username and Password Using Supabase and Streamlining PostgreSQL Function Management with Supabase.

Building Secure API Key Management with Supabase, KSUID & PostgreSQL

Rodrigo Mansueli — Thu, 21 Aug 2025 15:39:25 +0000

Managing API keys seems easy at first, but it involves many choices. You need keys that are secure, fast to check, easy to use, and simple to sort. This guide shows how to build secure API key management with Supabase.com. We use PostgreSQL and KSUID to meet these needs.

In this post, you will learn a simple pattern with Supabase, PostgreSQL, and KSUID (via the pg_idkit extension). By the end, you get SQL code to add to your database. You also get workflows to create, check, and list API keys. This setup is optimized for Supabase users, with tips on the Supabase CLI and JavaScript client. It helps with secure API key management in Supabase.

For more on secrets in Supabase, see previous posts on Supabase Vault. If you work with PostgreSQL extensions, check out Trusted Language Extensions for Postgres which is used on this blog post.

Overview: A Simple Way to Manage API Keys

We make API keys that look like this:

[KSUID][SECRET]

The KSUID is a short ID that sorts by time. We use it as the row ID.
The SECRET is a random string shown once to the user.
In the database, we store:
- The KSUID (as id),
- A salted hash of the secret,
- A random salt,
- The last 4 characters for the dashboard,
- The client_id (UUID) to link to a Supabase user (see Supabase Auth docs),
- And a name for the key label.

To check a key: Split it into KSUID and secret. Find the row by KSUID (fast with index). Compute the hash and compare. This is quick: one lookup and one hash.

This method keeps your Supabase API key management secure and efficient.

Step 1: Install pg_idkit Extension with dbdev and Supabase CLI

The pg_idkit extension gives KSUID functions like gen_random_ksuid_microsecond(). We install it as a Trusted Language Extension (TLE) using dbdev CLI to generate a migration. Then, use Supabase CLI to apply it. This fits well with Supabase workflows for installing PostgreSQL extensions in Supabase.

A. Set Up dbdev and Supabase CLI

If you do not have dbdev CLI, install it. Follow the dbdev installation docs. For Supabase CLI, see the Supabase CLI docs. Link your local project to your Supabase database.

Note: On Supabase, the pg_tle extension is already installed. This meets the prerequisite.

B. Generate Migration File with dbdev

Run this command to create a migration file for pg_idkit version 0.0.4 in the extensions schema:

dbdev add -o "./supabase/migrations/" -v 0.0.4 -s extensions package -n kiwicopple@pg_idkit

This makes a SQL file in your migrations folder.
Adjust the path if your migrations folder is different.

For more details, see the dbdev install a package docs.

C. Apply the Migration with Supabase CLI

Push the migration to your Supabase database:

supabase db push

This applies the extension. Now you can use functions like:

SELECT gen_random_ksuid_microsecond();

For more on extensions, see Supabase Database Extensions docs.

Step 2: Set Up the Table Schema

Use this SQL in your Supabase SQL editor or a migration file. It creates the api_keys table with KSUID as the primary key.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE api_keys (
  id TEXT PRIMARY KEY DEFAULT gen_random_ksuid_microsecond(), -- KSUID as ID (27 chars)
  client_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,                    -- user label
  secret_hash BYTEA NOT NULL,            -- digest(secret || salt)
  salt BYTEA NOT NULL DEFAULT gen_random_bytes(16), -- auto salt
  last4 TEXT NOT NULL,                   -- last 4 chars of secret for UI
  created_at TIMESTAMPTZ DEFAULT now()
);
-- Index for fast per-user listing, ordered by KSUID (time-sortable)
CREATE INDEX api_keys_client_id_idx ON api_keys (client_id, id DESC);

Notes:

KSUIDs sort by time and are 27 characters long.
We link to auth.users for Supabase auth.
Use the Supabase CLI to push this as a migration: supabase migration new create_api_keys_table, add the SQL, then supabase db push.

Step 3: Create an API Key Server-Side

This function creates a key. It takes client_id and name, generates everything, and returns the full key once.

Add this in a migration or SQL editor:

CREATE OR REPLACE FUNCTION create_api_key(client_id UUID, key_name TEXT)
RETURNS TEXT AS $$
DECLARE
  ksuid_prefix TEXT := gen_random_ksuid_microsecond();
  secret TEXT := encode(gen_random_bytes(16), 'base32'); -- random secret
  salt_val BYTEA := gen_random_bytes(16);
  full_key TEXT := ksuid_prefix || secret;
BEGIN
  INSERT INTO api_keys (id, client_id, name, secret_hash, salt, last4)
  VALUES (
    ksuid_prefix,
    client_id,
    key_name,
    digest(secret || salt_val, 'sha256'),
    salt_val,
    right(secret, 4)
  );
  RETURN full_key; -- show this to the user once
END;
$$ LANGUAGE plpgsql;

The database makes the secret. Show it once in your app.

Step 4: Verify API Keys with Supabase Edge Functions

To check keys, use a Supabase Edge Function. This is fast and secure. Create one with the Supabase CLI:

supabase functions new verify-api-key

Edit functions/verify-api-key/index.ts with Supabase JS:

import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

const supabase = createClient(Deno.env.get('SUPABASE_URL')!, Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!);

Deno.serve(async (req) => {
  const { full_key } = await req.json();
  const { data, error } = await supabase.rpc('verify_api_key', { full_key });

  if (error || !data) {
    return new Response(JSON.stringify({ error: 'Invalid key' }), { status: 401 });
  }

  return new Response(JSON.stringify({ client_id: data }), { status: 200 });
});

Deploy it: supabase functions deploy verify-api-key.

The verify_api_key function (add to DB):

CREATE OR REPLACE FUNCTION verify_api_key(full_key TEXT)
RETURNS UUID AS $$
DECLARE
  ksuid_prefix TEXT := left(full_key, 27);
  secret TEXT := substr(full_key, 28);
  stored_hash BYTEA;
  stored_salt BYTEA;
  key_owner UUID;
BEGIN
  SELECT secret_hash, salt, client_id
  INTO stored_hash, stored_salt, key_owner
  FROM api_keys
  WHERE id = ksuid_prefix;

  IF NOT FOUND THEN
    RETURN NULL;
  END IF;

  IF digest(secret || stored_salt, 'sha256') = stored_hash THEN
    RETURN key_owner; -- valid: return owner id
  ELSE
    RETURN NULL;       -- invalid
  END IF;
END;
$$ LANGUAGE plpgsql;

This is efficient: one index lookup and one hash. See Supabase Functions docs for more.

Step 5: List Keys for Your Dashboard

This function lists keys, sorted by time.

CREATE OR REPLACE FUNCTION list_api_keys(
  client_id UUID,
  limit_count INT DEFAULT 50,
  offset_count INT DEFAULT 0
)
RETURNS TABLE(id TEXT, name TEXT, last4 TEXT, created_at TIMESTAMPTZ) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name, last4, created_at
  FROM api_keys
  WHERE client_id = list_api_keys.client_id
  ORDER BY id DESC
  LIMIT limit_count OFFSET offset_count;
END;
$$ LANGUAGE plpgsql;

Call it from your app with Supabase JS:

const { data, error } = await supabase.rpc('list_api_keys', { client_id: user.id });

How Your App Uses These in Supabase

Create a Key

App calls: const { data } = await supabase.rpc('create_api_key', { client_id: user.id, key_name: 'My Key' });
Show data once to the user.

Verify a Key

Send full_key to your Edge Function.
If it returns client_id, allow access.

List Keys

Use the list function in your dashboard.

Additional Notes on Security and Choices

You cannot get back secrets. This is safer.
Add revocation: Include revoked_at in the table and check in verify_api_key.
For rate limits, log usage after verify succeeds.

This setup makes secure API key management in Supabase simple and fast.

Ready to try? Start with the Supabase CLI and build your own.

Building a Slack Bot for AI-Powered Conversations with Supabase

Rodrigo Mansueli — Tue, 10 Oct 2023 13:38:09 +0000

In today's tech-driven world, integrating AI into your communication tools can enhance productivity and user experiences. Slack, a widely used team collaboration platform, allows developers to create custom bots that automate tasks and provide intelligent responses. In this blog post, we'll guide you through the process of building a Slack bot that harnesses the power of AI, specifically Hugging Face and OpenAI, using the Supabase platform.

Prerequisites:

Before we dive into the code, you'll need a few things:

Supabase Account: Ensure you have an account on Supabase.
Slack Workspace: You'll require access to a Slack workspace where you can create and install the bot.
API Keys: Obtain API keys for Hugging Face and OpenAI.

Creating the Slack Bot Manifest:

To begin, you need to define the bot's characteristics in a manifest file (manifest.yaml). This file specifies the bot's display name, features, OAuth configuration, and settings. Here's an example manifest for your Slack AI bot:

display_information:
  name: SlackAI
features:
  bot_user:
    display_name: SlackAI
    always_online: false
oauth_config:
  scopes:
    bot:
      - app_mentions:read
      - chat:write
      - im:write
      - channels:history
      - commands
settings:
  org_deploy_enabled: false
  socket_mode_enabled: false
  token_rotation_enabled: false

We'll interact with the Slack bot using custom webhooks. For detailed instructions, refer to our previous post on Deploying and Debugging Custom Webhooks on Supabase & PostgreSQL.

Setting Secrets on Supabase CLI:

You'll need to securely store sensitive information, such as API keys, on Supabase using the CLI. Here's how to do it:

# Set OpenAI API Key
supabase --project-ref nacho_slacker secrets \
set OPEN_AI=sk-FB9ZJJbluyN4CH0luSQkwG0t6ZZG3wSQ6SFyBj3k8JZDRKSt

# Set Hugging Face API Key
supabase --project-ref nacho_slacker secrets \
set HUGGINGFACE_TOKEN=hf_rSji7SZZuZwN8ZW53tN4CH0TKRtFCN6Cai

# Set Bot OAuth Token
supabase --project-ref nacho_slacker secrets \
set SLACK_TOKEN=xoxb-3882900064320-6000640064577-QDr8Nk637kw6nachoR7bDu9

Edge Functions: The Soul of Your Slack Bot

In this section, we'll explore two critical Edge Functions, namely slack_ai_mentions.ts and consume_job.ts. These functions play vital roles in your Slack bot's functionality.

slack_ai_mentions.ts: This Edge Function sets up an HTTP server using Deno to listen for Slack events. When the bot receives an app mention, it processes the text adding it into a Queue System in Postgres, which will process the requests and send them back to the user.

import { serve } from 'https://deno.land/std@0.197.0/http/server.ts';
import { WebClient } from 'https://deno.land/x/slack_web_api@6.7.2/mod.js';
import { SupabaseClient } from 'https://esm.sh/@supabase/supabase-js@2';

const slack_bot_token = Deno.env.get("SLACK_TOKEN") ?? "";
const bot_client = new WebClient(slack_bot_token);
const supabase_url = Deno.env.get("SUPABASE_URL") ?? "";
const service_role = Deno.env.get("SUPABASE_SERVICE_ROLE_KEY");
const supabase = new SupabaseClient(supabase_url, service_role);

console.log(`Slack URL verification function up and running!`);

serve(async (req) => {
  try {
    const req_body = await req.json();
    console.log(JSON.stringify(req_body, null, 2));
    const { token, challenge, type, event } = req_body;

    if (type == 'url_verification') {
      return new Response(JSON.stringify({ challenge }), {
        headers: { 'Content-Type': 'application/json' },
        status: 200,
      });
    } else if (event.type == 'app_mention') {
      const { user, text, channel, ts } = event;
      const url_path = text.toLowerCase()
            .includes('code') ? '/code' : '/general';
      const { error } = await supabase.from('job_queue').insert({
        http_verb: 'POST',
        payload: { user, text, channel, ts },
        url_path: url_path
      });

      if (error) {
        console.error(error);
        return new Response(JSON.stringify({ error: error.message }), {
          headers: { 'Content-Type': 'application/json' },
          status: 400,
        });
      }
      await post(channel, 
                 ts, 
                 `Taking a look and will get back to you shortly!`);
      return new Response('', { status: 200 });
    }
  } catch (error) {
    return new Response(JSON.stringify({ error: error.message }), {
      headers: { 'Content-Type': 'application/json' },
      status: 400,
    });
  }
});

async function post(channel: string, thread_ts: string, message: string): 
  Promise<void> {
  try {
    const result = await bot_client.chat.postMessage({
      channel: channel,
      thread_ts: thread_ts,
      text: message,
    });
    console.info(result);
  } catch (e) {
    console.error(`Error posting message: ${e}`);
  }
}

consume_job.ts: This Edge Function manages tasks related to AI model interactions. It handles requests from the slack_ai_mentions.ts file and communicates with either Hugging Face or OpenAI to generate text responses. The blog post provides detailed instructions on installing and configuring this Edge Function.

import { serve } from "https://deno.land/std@0.197.0/http/server.ts";
import { WebClient } from "https://deno.land/x/slack_web_api@6.7.2/mod.js";

const slack_bot_token = Deno.env.get("SLACK_TOKEN") ?? "";
const bot_client = new WebClient(slack_bot_token);
const hf_token = Deno.env.get("HUGGINGFACE_TOKEN") ?? "";
const openai_api_key = Deno.env.get("OPEN_AI") ?? "";

console.log("Function that will handle the tasks!");

serve(async (req) => {
  const payload = await req.json();
  const url = new URL(req.url);
  const method = req.method;

  // Extract the last part of the path as the command
  const command = url.pathname.split("/").pop();
  try {
    let generated_text = '';
    if (command == "general") {
      generated_text = await getGeneratedTextFromHuggingFace(payload);
    } else {
      generated_text = await getGeneratedTextFromChatGPT(payload);
    }
    await post(payload.channel, 
               payload.ts, 
               `Thanks for asking: ${generated_text}`);
    return new Response('ok',
      { headers: { "Content-Type": "application/json" }, 
        status: 200
      },
    );
  } catch (error) {
    console.error(error);
    return new Response(
      JSON.stringify({ error: error.message }),
      {
        headers: { "Content-Type": "application/json" },
        status: 500,
      },
    );
  }
});

async function post(
  channel: string,
  thread_ts: string,
  message: string,
): Promise<void> {
  try {
    const result = await bot_client.chat.postMessage({
      channel: channel,
      thread_ts: thread_ts,
      text: message,
    });
    console.info(result);
  } catch (e) {
    console.error(`Error posting message: ${e}`);
  }
}

async function getGeneratedTextFromHuggingFace(payload) {
  let huggingface_url = "";
  let body_content = "";
  huggingface_url = "https://api-inference.huggingface.co/models/mistralai/Mistral-7B-Instruct-v0.1";
  body_content = `[INST] ${payload.text} [/INST]`;
  body_content = JSON.stringify({ "inputs": body_content }, null, 2);

  const huggingface_response = await fetch(huggingface_url, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      "Authorization": `Bearer ${hf_token}`,
    },
    body: body_content,
  });
  const huggingface_data = await huggingface_response.json();
  if (huggingface_data.error) {
    console.error(huggingface_data.error);
    throw new Error(huggingface_data.error.message);
  }
  const generated_text = huggingface_data[0]
        .generated_text.split("[/INST]");
  return generated_text.length > 1 ? 
         generated_text[1] : generated_text[0];
}

async function getGeneratedTextFromChatGPT(payload) {
  const headers = {
    "Content-Type": "application/json",
    "Authorization": `Bearer ${openai_api_key}`,
  };

  const openai_url = "https://api.openai.com/v1/chat/completions";

  let body_content = {
    "model": "gpt-3.5-turbo", // Replace with the name of the chat model you want to use
    "messages": [
      {
        "role": "system",
        "content": `You are a detail-oriented, helpful, 
                    and eager to please assistant.`
      },
      {
        "role": "user",
        "content": payload.text
      }
    ]
  };
  const body_content_text = JSON.stringify(body_content, null, 2);
  const openai_response = await fetch(openai_url, {
    method: "POST",
    headers: headers,
    body: body_content_text,
  });
  const openai_data = await openai_response.json();
  if (openai_data.error) {
    console.error(openai_data.error);
    throw new Error(openai_data.error.message);
  }
  return openai_data.choices[0].message.content.trim();
}

These Edge Functions are essential components of the Slack bot, allowing it to seamlessly integrate with external AI services and provide intelligent responses in real-time.

Deploying Edge Functions:

Next, you'll deploy these two Edge Functions using Supabase CLI:

supabase functions deploy consume_job \
--project-ref nacho_slacker --no-verify-jwt

supabase functions deploy slack_ai_mentions \
--project-ref nacho_slacker --no-verify-jwt

Storing Secrets Securely in Vault

To ensure the security of sensitive information, it's crucial to utilize Vault for secret management. Here's a step-by-step guide to adding service_role and consumer_function to Vault:

Add service_role by using the service_role key found in the Supabase dashboard.
Incorporate consumer_function into Vault by utilizing the URL of the Edge Function consume_job from the Supabase dashboard.

Configuring Slack Apps

For your bot to seamlessly interact with Slack, you'll need to configure Slack Apps:

Navigate to the Slack Apps page.
Under "Event Subscriptions," add the URL of the slack_ai_mentions function and click to verify the URL.
The Edge function will respond, confirming that everything is set up correctly.
Add app-mention in the events the bot will subscribe to.

Installing DBDEV and Supa_Queue

To enhance your Supabase project's capabilities, consider installing the supabase-dbdev and supa_queue extensions. Here's how you can do it:

Installing `supabase-dbdev`

-- Install supabase-dbdev
create extension if not exists http with schema extensions;
create extension if not exists pg_tle;
select pgtle.uninstall_extension_if_exists('supabase-dbdev');
drop extension if exists "supabase-dbdev";
select
    pgtle.install_extension(
        'supabase-dbdev',
        resp.contents ->> 'version',
        'PostgreSQL package manager',
        resp.contents ->> 'sql'
    )
from http(
    (
        'GET',
        'https://api.database.dev/rest/v1/'
        || 'package_versions?select=sql,version'
        || '&package_name=eq.supabase-dbdev'
        || '&order=version.desc'
        || '&limit=1',
        array[
            ('apiKey', 'YOUR_DATABASE_DEV_API_KEY_HERE')::http_header
        ],
        null,
        null
    )
) x,
lateral (
    select
        ((row_to_json(x) -> 'content') #>> '{}')::json -> 0
) resp(contents);
create extension "supabase-dbdev";
select dbdev.install('supabase-dbdev');
drop extension if exists "supabase-dbdev";
create extension "supabase-dbdev";

Installing the `supa_queue` Extension

To further empower your Supabase project, consider installing the supa_queue extension. This extension offers a robust queue system for efficiently managing tasks and processes. Here's how you can install it:

-- Install supa_queue
select dbdev.install('mansueli-supa_queue');
create extension "mansueli-supa_queue" version '1.0.3';

Note: The supa_queue extension is a Trusted Language Extension available at https://database.dev/mansueli/supa_queue. It was created based on a previous post we published about building a simple & robust queue system for PostgreSQL. This extension enables you to implement a powerful queue system in your Supabase project, which can prove invaluable for managing asynchronous tasks and background processing.

Installing and Interacting with Your Slack Bot

Now that your Slack bot is ready to roll, it's time to integrate it into your Slack workspace and start interacting with it in two different contexts: one for general use with the Hugging Face model and another for code-related inquiries using the ChatGPT model.

Installing Your Bot to Slack

Add Your Bot to Slack: Navigate to your Slack workspace and access the "Apps" section. Search for your bot's name (e.g., "SlackAI") and add it to your workspace.
Authorize Bot Permissions: Ensure you authorize the necessary permissions for your bot, including sending messages, interacting with users, and reading messages.
Place Your Bot in a Channel: Choose a Slack channel where you'd like your bot to operate. Invite your bot to join the channel by mentioning it (e.g., @SlackAI).

General Use with Hugging Face Model

In this context, you can utilize the Hugging Face model for general conversations and inquiries. Mention your bot in the channel and ask a question or initiate a conversation. For example:

@SlackAI How's the weather today?

Your bot, powered by the Hugging Face model, will respond with intelligent insights or answers.

Code-Related Inquiries with ChatGPT Model

When you have code-related questions or need assistance with technical matters, you can invoke the ChatGPT model. Mention your bot again in the channel and frame your inquiry:

@SlackAI Can you help me with this Python code snippet?

Your bot will be ready to assist with coding-related queries, making your team's technical discussions more efficient.

Conclusion

In this comprehensive exploration of creating a powerful Slack bot with AI capabilities using Supabase, you've gained the knowledge and tools to elevate your team's communication and productivity. By seamlessly integrating AI models and efficient queue management into your Slack bot, you've unlocked a world of automation and intelligent responses.

As you continue to refine and expand your bot's functionalities, consider these top-performing articles for further insights and optimizations:

Using Custom Claims & Testing RLS with Supabase: Dive deep into custom claims and role-level security with Supabase to enhance your application's data security.
Supabase User Self-Deletion: Empower Users with Edge Functions: Learn how to implement user self-deletion features using Supabase Edge Functions, empowering users with control over their accounts.
Creating Customized i18n-Ready Authentication Emails using Supabase Edge Functions, PostgreSQL, and Resend: Dive into internationalization and create personalized authentication emails with Supabase Edge Functions and PostgreSQL.
Testing Supabase Edge Functions with Deno Test: Explore best practices for testing your Supabase Edge Functions using Deno, ensuring robust functionality.

These articles complement your journey to master Supabase and Slack bot development, offering valuable insights and strategies for enhancing your applications. Experimentation and customization are key to tailoring your bot to meet your project's specific needs.

References

For additional guidance and resources, consider exploring these valuable references:

Supabase Official Documentation: Access the official documentation for Supabase to explore the platform's capabilities and features.
PostgreSQL Official Documentation: Dive into the official documentation of PostgreSQL, the powerful database system at the core of Supabase.
Deno Documentation: Explore Deno documentation for insights into this secure runtime for JavaScript and TypeScript.

Building a Slack bot infused with AI capabilities offers an exciting opportunity to automate tasks and deliver intelligent responses, ultimately streamlining your team's interactions and fostering a more engaging work environment. Enjoy the journey of experimenting with various AI models and expanding your bot's functionalities!

🚀 Learn more about Supabase

Easy Deployment and Rollback of PostgreSQL Functions with Supabase

Rodrigo Mansueli — Tue, 03 Oct 2023 16:55:00 +0000

In the realm of database management, version control, and deployment are crucial. Efficiently deploying and managing database functions is vital for maintaining the integrity of your data-driven applications. While database migrations, as detailed in Supabase's migration guide, are ideal for long-term projects, there are scenarios, such as prototyping and rapid development, where you need more flexibility.

In this blog post, we'll explore an approach tailored for quick prototyping and agile development—how to easily deploy and rollback PostgreSQL functions using Supabase. Supabase, a powerful open-source alternative to traditional database management systems, simplifies the process of deploying and managing functions in these scenarios.

If you're working on more complex workflows or long-term projects, we highly recommend referring to Supabase's migration guide for optimal version control and deployment practices.

PostgreSQL and Supabase in Modern Web Applications

PostgreSQL, a robust open-source relational database management system (RDBMS), has gained popularity in web development due to its reliability, extensibility, and support for complex data types.

Supabase, an open-source platform, offers various tools and services for modern web applications. It leverages PostgreSQL as its core database engine and provides a user-friendly interface for managing data, authentication, and more.

We'll explore how Supabase complements PostgreSQL by simplifying function deployment and rollback. You can refer to the PostgreSQL Documentation to learn more about PostgreSQL.

Tracking Function History in PostgreSQL

When managing a PostgreSQL database, it's essential to track changes made to functions over time. This historical record allows you to review, audit, and revert to previous versions if needed.

To facilitate this, we'll create an archive.function_history table that stores crucial information about each function, including its name, arguments, return type, source code, and language settings.

Here's the SQL code for creating this table:

CREATE SCHEMA archive;

CREATE TABLE archive.function_history (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY,
  schema_name text,
  function_name text,
  args text,
  return_type text,
  source_code text,
  lang_settings text,
  updated_at timestampz DEFAULT now(),
  version NUMERIC DEFAULT 1,
  CONSTRAINT function_history_pkey PRIMARY KEY (id)
);

--Handling version numbers automatically:
CREATE OR REPLACE FUNCTION calculate_version()
RETURNS TRIGGER AS $$
BEGIN
  -- Calculate the version number for new rows
    SELECT COALESCE(MAX(version), 0) + 1
    INTO NEW.version
    FROM archive.function_history
    WHERE schema_name = NEW.schema_name
      AND function_name = NEW.function_name
      AND return_type = NEW.return_type
      AND args = NEW.args;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_function_history
BEFORE INSERT ON archive.function_history
FOR EACH ROW
EXECUTE FUNCTION calculate_version();

Saving Function History

The `archive.save_function_history` Function

To automate recording function changes, we'll create a PostgreSQL function called archive.save_function_history. This function takes parameters such as the function name, arguments, return type, source code, schema name, and language settings.

Here's the SQL code for creating the archive.save_function_history function:

CREATE OR REPLACE 
FUNCTION archive.save_function_history(
  function_name text,
  args text,
  return_type text,
  source_code text,
  schema_name text default 'public',
  lang_settings text default 'plpgsql'
) RETURNS void 
SET search_path = public, archive
SECURITY DEFINER
AS
$$
BEGIN
  INSERT INTO archive.function_history (
        schema_name, 
        function_name, 
        args, 
        return_type, 
        source_code, 
        lang_settings)
  VALUES (schema_name, function_name, args, return_type, source_code, lang_settings);
END;
$$
LANGUAGE plpgsql;
-- Protecting the function:
REVOKE EXECUTE ON FUNCTION 
archive.save_function_history FROM public;

REVOKE EXECUTE ON FUNCTION 
archive.save_function_history FROM anon, authenticated;

This function allows us to easily store a snapshot of a function each time it's modified.

Deploying Functions from Source

The `create_function_from_source` Function

Managing functions often involves deploying them from source code. PostgreSQL requires specific syntax for function creation, and Supabase simplifies this with the create_function_from_source function.

CREATE OR REPLACE FUNCTION 
create_function_from_source(
  function_text text,
  schema_name text default 'public'
) RETURNS text 
SECURITY DEFINER
AS $$
DECLARE
  function_name text;
  argument_types text;
  return_type text;
  function_source text;
  lang_settings text;
BEGIN
  -- Execute the function text to create the function
  EXECUTE function_text;

  -- Extract function name from function text
  SELECT (regexp_matches(function_text, 'create (or replace )?function (public\.)?(\w+)', 'i'))[3]
  INTO function_name;

  -- Get function details from the system catalog
  SELECT pg_get_function_result(p.oid), 
                pg_get_function_arguments(p.oid), p.prosrc, l.lanname
  INTO return_type, argument_types, function_source, lang_settings
  FROM pg_proc p
  JOIN pg_namespace n ON n.oid = p.pronamespace
  JOIN pg_language l ON l.oid = p.prolang
  WHERE n.nspname = schema_name AND p.proname = function_name;

  -- Save function history
  PERFORM archive.save_function_history(function_name, argument_types, return_type, function_text, schema_name, lang_settings);

  RETURN 'Function created successfully.';
EXCEPTION
  WHEN others THEN
    RAISE EXCEPTION 'Error creating function: %', sqlerrm;
END;
$$ LANGUAGE plpgsql;
-- Protecting the function:
REVOKE EXECUTE ON FUNCTION 
create_function_from_source FROM public;

REVOKE EXECUTE ON FUNCTION 
create_function_from_source FROM anon, authenticated;

This function takes the function's SQL source code and schema name as parameters, creating the function within the database. It's a powerful tool for dynamic function creation.

Here's an example of deploying a function using create_function_from_source:

SELECT create_function_from_source(
$$
-- Note that you can just paste the function below:
CREATE OR REPLACE FUNCTION public.convert_to_uuid(input_value text)
 RETURNS uuid
AS $function$
DECLARE
  hash_hex text;
BEGIN
  -- Return null if input_value is null or an empty string
  IF input_value IS NULL OR NULLIF(input_value, '') IS NULL THEN
    RETURN NULL;
  END IF;
  hash_hex := substring(encode(digest(input_value::bytea, 'sha512'), 'hex'), 1, 36);
  RETURN (left(hash_hex, 8) || '-' || right(hash_hex, 4) || '-4' || right(hash_hex, 3) || '-a' || right(hash_hex, 3) || '-' || right(hash_hex, 12))::uuid;
END;
$function$
LANGUAGE plpgsql
IMMUTABLE
SECURITY DEFINER;
-- End of the function above
$$
);

Rolling Back Functions

Rolling back functions is as crucial as deploying them. Mistakes happen, and being able to revert to a previous version can save valuable time and prevent data corruption.

The rollback_function function comes to the rescue. It retrieves the most recent function version from the archive.function_history table and executes it. If no previous version exists, it gracefully handles the situation.

Here's the SQL code for creating and using the rollback_function:

CREATE OR REPLACE FUNCTION rollback_function(
  func_name text,
  schema_n text default 'public'
) RETURNS text 
SECURITY DEFINER
AS $$
DECLARE
  function_text text;
BEGIN
  -- Get the most recent function version from the function_history table
  SELECT source_code
  INTO function_text
  FROM archive.function_history
  WHERE function_name = func_name AND schema_name = schema_n
  ORDER BY updated_at DESC
  LIMIT 1;

  -- If no previous version is found, raise an error
  IF function_text IS NULL THEN
    RAISE EXCEPTION 'No previous version of function % found.', func_name;
  END IF;

  -- Add 'or replace' to the function text if it's not already there (case-insensitive search and replace)
  IF NOT function_text ~* 'or replace' THEN
    function_text := regexp_replace(function_text, 'create function', 'create or replace function', 'i');
  END IF;

  -- Drop current version:
  EXECUTE format('DROP FUNCTION IF EXISTS %I.%I', schema_n, func_name);
  -- Execute the function text to create the function
  EXECUTE function_text;

  RETURN 'Function rolled back successfully.';
EXCEPTION
  WHEN others THEN
    RAISE EXCEPTION 'Error rolling back function: %', sqlerrm;
END;
$$ LANGUAGE plpgsql;

-- Protecting the function:
REVOKE EXECUTE ON FUNCTION rollback_function FROM public;
REVOKE EXECUTE ON FUNCTION rollback_function FROM anon, authenticated;

-- Example of rolling back a function
SELECT rollback_function('convert_to_uuid');

Setting up existing functions as the first version

If you are starting with an existing database but want to start versioning from now. You can use this function below to archive all in the public schema.

CREATE OR REPLACE FUNCTION archive.setup_function_history(schema_name text default 'public')
RETURNS VOID AS
$$
DECLARE
  function_record record;
BEGIN
  -- Loop through existing functions in the specified schema
  FOR function_record IN (
    SELECT
      n.nspname AS schema_name,
      p.proname AS function_name,
      pg_catalog.pg_get_function_arguments(p.oid) AS args,
      pg_catalog.pg_get_function_result(p.oid) AS return_type,
      pg_catalog.pg_get_functiondef(p.oid) AS source_code,
      l.lanname AS lang_settings
    FROM pg_catalog.pg_proc p
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
    LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
    WHERE n.nspname = schema_name
  )
  LOOP
    -- Insert information about the function into the history table
    PERFORM archive.save_function_history(
      function_record.function_name,
      function_record.args,
      function_record.return_type,
      function_record.source_code,
      function_record.schema_name,
      function_record.lang_settings
    );
  END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT archive.setup_function_history();

Conclusion

In conclusion, efficient management of PostgreSQL functions is crucial for web application development. Supabase, with its integration with PostgreSQL and the tools we've explored, offers a streamlined approach to function deployment and rollback.

Key takeaways from this blog post include the importance of function history tracking, the creation of the archive.function_history table, the archive.save_function_history function for recording changes, and the convenience of create_function_from_text and

rollback_function for deployment and rollback.

If you found this article valuable, you might also be interested in exploring related topics:

Rate Limiting Supabase Requests with PostgreSQL and pg_headerkit: Learn how to implement rate limiting for Supabase requests.
Comprehensive Guide: Deploying and Debugging Custom Webhooks on Supabase & PostgreSQL: Explore techniques for deploying and debugging custom webhooks.

We encourage you to explore Supabase and PostgreSQL further to unlock the full potential of efficient database management.

Additional Resources

For further information and exploration, here are some additional resources:

Feel free to delve deeper into these resources to enhance your understanding of these powerful tools for database management.

🚀 Learn more about Supabase

Configuring Office365 as the SMTP Provider in Supabase Auth: A Comprehensive Guide

Rodrigo Mansueli — Tue, 26 Sep 2023 14:13:49 +0000

Email communication is a crucial aspect of many web applications, including those built on the Supabase platform. Whether it's for account verification, password resets, or general communication within the application, reliable email delivery is essential. In this guide, we will address a common issue: "Sending emails from Supabase Auth via Office365 is not working as expected." We will guide you through the process of configuring Office365 for SMTP authentication to ensure seamless email communication within your Supabase applications.

Understanding the Issue

SMTP (Simple Mail Transfer Protocol) serves as the backbone for sending emails from web applications. When integrating GoTrue as an authentication service in Supabase, the platform relies on SMTP to send important email notifications to users. However, some users have reported issues with Office365 not allowing these emails to go through.

GoTrue is designed to streamline user authentication and management within Supabase applications. To ensure its features work seamlessly, the underlying email delivery system must be properly configured.

Troubleshooting

If you've encountered the problem of emails not being sent when using Office365 as the SMTP server, you may have noticed the following symptoms:

Failed Email Delivery: Emails not reaching their intended recipients.
Authentication Issues: Error messages indicating authentication problems.
Spam or Non-Delivery: Emails being marked as spam or not being delivered at all.

Why Office365 May Block SMTP Authentication

Office365, like many email services, has security measures in place to prevent unauthorized use of its SMTP servers. By default, it may block SMTP authentication for specific mailboxes to protect against misuse. Understanding these security measures is crucial when configuring Office365 for SMTP in GoTrue.

Solution - Configuring Office365 for SMTP Authentication

Now that we've grasped the issue's context and symptoms, let's delve into the solution: configuring Office365 for SMTP authentication.

Step-by-Step Guide

Accessing Supabase.com

To begin, visit Supabase.com to learn more about Supabase and its features.

Accessing the Office365 Admin Center

Next, you'll need to access the Office365 Admin Center. Follow these steps:

Log in to your Office365 account as an administrator.
Navigate to the Admin Center.

Navigating to Mailbox Settings

Once you're in the Admin Center, proceed to find mailbox settings:

Locate and click on the "Users" or "Active users" option.
Select the user whose mailbox settings you want to configure.

Enabling SMTP Authentication

SMTP Authentication must be enabled for the selected mailbox. Here's how:

Scroll down to the "Email apps" section.
Click "Manage email apps."
Find "SMTP AUTH (SMTP Authentication)" and ensure it's enabled.

Alternative Method: PowerShell

In some cases, you may prefer using PowerShell for this configuration, especially if you need to configure multiple mailboxes. Here's the PowerShell command to enable SMTP authentication. If you never used PowerShell to connect with your Office365 Exchange services, run the following commands:

Set-ExecutionPolicy RemoteSigned
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session

We can set this mailbox to allow SMTP Authentication now:

Set-CASMailbox -Identity <MailboxIdentity> -SmtpClientAuthenticationDisabled $false

In the command, replace <MailboxIdentity> with the actual mailbox you want to configure (e.g., support@contoso.com).

Using PowerShell provides flexibility and scalability, especially in larger organizations.

Testing the Configuration

With Office365 configured for SMTP authentication, it's essential to test the setup to ensure everything is working as expected. Testing helps verify that email notifications from GoTrue will be reliably delivered to your users.

To perform this test, you can use the supabase.auth.admin.inviteUserByEmail method provided by Supabase. This method allows you to send an invitation email to a specified email address. Here's how you can use it in JavaScript:

const { data, error } = await supabase
  .auth.admin.inviteUserByEmail('email@example.com');

You can find detailed information about this method in the Supabase documentation.

By using this method, you can invite a dummy user to your app. If the email is sent successfully, it indicates that your SMTP configuration with Office365 is working correctly. However, if you encounter any errors or issues during this test, you may need to revisit your SMTP configuration settings and consult the troubleshooting tips in the next section to resolve the problem.

Testing is a crucial step in ensuring the reliability of your email communication within Supabase applications. Make sure to perform this test as part of your configuration process and periodically to verify ongoing functionality.

Conclusion

In conclusion, configuring Office365 for SMTP authentication is a critical step in ensuring the smooth operation of email notifications within your Supabase applications. We have explored the background context of SMTP, and the integration of GoTrue in Supabase, and addressed the issue of emails not being delivered successfully. By following the step-by-step guide and considering alternative methods like PowerShell, you can overcome these challenges and provide reliable email communication to your users.

Remember that proper configuration and regular testing are key to maintaining a robust email system. We encourage you to follow best practices for email configuration and security to ensure the integrity of your email communications.

Additional Resources

For further reading and reference, here are some additional resources:

Please note that configurations and settings may change over time, so it's a good practice to refer to the official documentation for the most up-to-date information.

🚀 Learn more about Supabase

Building User Authentication with Username and Password using Supabase

Rodrigo Mansueli — Tue, 19 Sep 2023 14:01:28 +0000

User authentication is essential for web application security and user management. Supabase is an open-source platform that makes it easy to build secure authentication systems. In this blog post, we will show you how to enhance user login options by enabling users to log in with their username and password or their email and password. You can check this example also on the GitHub repo.

Prerequisites

Before we embark on this tutorial, it's crucial to have a foundational understanding of web development and Node.js. Familiarity with Next.js, a popular React framework, will also be advantageous. Additionally, you need Node.js and npm (Node Package Manager) installed on your system.

Setting up the Project

To kickstart our journey, let's create a new Next.js project integrated with Supabase. Execute the following command to establish the initial project structure:

npx create-next-app -e with-supabase username-login

This command will generate a Next.js project preconfigured with Supabase support with default authentication and routes.

Configuring Supabase

Before we start coding, let's set up Supabase for our project. You need a Supabase project to get the credentials you need. Make sure you have your Supabase URL and API keys ready. It's important to keep these credentials safe, such as by using environment variables. You can find more information on configuring Supabase in the Supabase documentation.

Tweaking the Sign-In Route

Now, let's closely examine the code responsible for user sign-in. Navigate to the username-login/app/auth/sign-in/route.ts file in your project. We will replace the default code for user authentication as follows:

Replace:

const { error } = await supabase.auth.signInWithPassword({
  email,
  password,
})

With:

const { data } = await supabase.functions.invoke('sign-in', {
  body: {
    email,
    password,
  }
});

const { error } = await supabase.auth.setSession({
  access_token: data.access_token,
  refresh_token: data.refresh_token
});

This code leverages Supabase Edge Functions to handle user sign-in, enabling users to choose between username and password or email for login.

Edge Function for Sign-In

Within the authentication workflow, this edge function plays a pivotal role in managing sign-in requests. This function offers users greater flexibility by enabling them to use their usernames as an alternative login option.

To set up the Edge Function for Sign-In, create a Deno script as follows:

import { serve } from 'https://deno.land/std@0.192.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'
import { corsHeaders } from '../_shared/cors.ts'

console.log(`Function "sign-in" is up and running!`)

const options =  {
  auth: {
    flowType: 'implicit',
    autoRefreshToken: false,
    persistSession: false,
    detectSessionInUrl: false
  }
};

serve(async (req: Request) => {
  // This is needed if you're planning to invoke your function from a browser.
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders })
  }
  try {
    const supabaseAdmin = createClient(
      Deno.env.get('SUPABASE_URL') ?? '',
      Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? '',
      options
    )
    const { email, password } = await req.json()
    // Create a Supabase client with the Auth context of the logged-in user.
    const supabaseClient = createClient(
      // Supabase API URL - env var exported by default.
      Deno.env.get('SUPABASE_URL') ?? '',
      // Supabase API ANON KEY - env var exported by default.
      Deno.env.get('SUPABASE_ANON_KEY') ?? '',
      options
    )
    const { data: profileData, error: userError } = await supabaseAdmin
      .from('profiles')
      .select('email')
      .or(`username.eq.${email},email.eq.${email}`)
      .limit(1)
      .maybeSingle();
    console.log("profileData:" + JSON.stringify(profileData, null, 2))
    if (userError) throw userError

    const { data: { session }, error } = await supabaseClient.auth.signInWithPassword({
      email: profileData.email,
      password: password,
    })
    if (error) throw error
    console.log("data:" + JSON.stringify(session, null, 2))
    return new Response(JSON.stringify(session), {
      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
      status: 200,
    });
  } catch (error) {
    return new Response(JSON.stringify({ error: error }), {
      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
      status: 400,
    })
  }
})

This Deno script handles sign-in requests and allows users to choose between usernames and email for login. It leverages Supabase Edge Functions, enhancing the flexibility of your authentication system.

To learn more about testing Supabase Edge Functions, check our dedicated blog post on Testing Supabase Edge Functions with Deno Test.

Here's an example structure for the public.profiles table that you can use in your project:

CREATE TABLE public.profiles (
  id uuid NOT NULL,
  updated_at timestamp with time zone NULL,
  username text NULL,
  full_name text NULL,
  avatar_url text NULL,
  website text NULL,
  email text NULL,
  CONSTRAINT profiles_pkey PRIMARY KEY (id),
  CONSTRAINT profiles_username_key UNIQUE (username),
  CONSTRAINT profiles_id_fkey FOREIGN KEY (id) REFERENCES auth.users (id) ON DELETE CASCADE,
  CONSTRAINT username_length CHECK (char_length(username) >= 3)
);
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

You can read more about creating such a table in Managing User Data.

Conclusion

In this extensive exploration of user authentication with Supabase, we've equipped you with the knowledge and tools to build secure and flexible login systems for your web applications. By enabling users to choose between username and password or email and password login methods, you've enhanced the user experience.

As you continue to develop and fine-tune your authentication systems, consider the following articles for further insights and optimizations:

Comprehensive Guide: Deploying and Debugging Custom Webhooks on Supabase & PostgreSQL: Learn how to automate webhooks for seamless integration into your applications.
Creating Customized i18n-Ready Authentication Emails using Supabase Edge Functions, PostgreSQL, and Resend: Dive into the world of internationalization and create personalized authentication emails.
How to Boost Supabase Reliability: A Guide to Using Postgres Foreign Data Wrappers: Enhance the reliability of your Supabase applications with foreign data wrappers.

These articles will complement your journey to master Supabase authentication and take your web development skills to the next level. Experimentation and customization are key to tailoring authentication systems to your specific project needs.

References

For additional guidance and resources, consider exploring these valuable references:

Supabase Official Documentation: Explore Supabase's official documentation for comprehensive insights into this robust backend platform.
PostgreSQL Official Documentation: Dive into the official documentation of PostgreSQL, the powerful database system at the core of Supabase.
Next.js Documentation: Access Next.js documentation for further information on this popular React framework.
Deno Documentation: Explore Deno documentation for insights into this secure runtime for JavaScript and TypeScript.

With these references and your newfound knowledge, you're well-prepared to create exceptional web applications powered by Supabase. Continue your journey in web development, and don't hesitate to explore additional features and customization options offered by Supabase and PostgreSQL.

🚀 Learn more about Supabase

Rate Limiting Supabase Requests with PostgreSQL and pg_headerkit

Rodrigo Mansueli — Tue, 05 Sep 2023 12:04:13 +0000

Introduction

Rate limiting is a critical aspect of web applications that ensures fair usage of resources and prevents abuse. In this blog post, we'll explore how to implement rate limiting for Supabase requests using PostgreSQL and the pg_headerkit extension. This article is part of a series on optimizing Supabase performance, and it builds upon our previous guide on Boosting Supabase Reliability.

Supabase, a powerful backend platform built on top of PostgreSQL, relies on PostgreSQL as its underlying database. By leveraging PostgreSQL and pg_headerkit, we can efficiently control the rate at which requests are made to Supabase, ensuring optimal performance and resource allocation.

Prerequisites

Before we dive into the implementation, make sure you have the following prerequisites:

Supabase Project: Ensure you have an existing Supabase project with the necessary API endpoints set up.
PostgreSQL Database: Use PostgreSQL as your backend database for Supabase. If you haven't set up PostgreSQL with Supabase yet, follow the official documentation to get started.
pg_headerkit: You will need to install the pg_headerkit library. Find installation instructions and more information on this library at https://database.dev/burggraf/pg_headerkit.

Setting up the Environment

Before we dive into rate limiting, let's ensure we have the necessary prerequisites in place:

Supabase Account: Make sure you have a Supabase account set up.
Database.dev: Install dbdev using https://database.dev/installer.

Next, let's install and set up pg_headerkit:

SELECT dbdev.install('burggraf-pg_headerkit');
CREATE EXTENSION "burggraf-pg_headerkit" VERSION '1.0.0';

Creating the Rate Limiting Infrastructure

In this section, we'll dive into the process of creating the essential infrastructure for rate limiting within your Supabase-powered application. Rate limiting is a crucial mechanism that allows you to control the number of requests made to your Supabase endpoints, ensuring fair usage of resources and maintaining system stability.

The `request_log` Table

We begin with the creation of the request_log table. This table serves as a main component for tracking and monitoring incoming requests. Here's how we set it up:

CREATE UNLOGGED TABLE request_log (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY,
  ip inet NOT NULL,
  timestamp timestamptz DEFAULT NOW()
);

The request_log table has three essential columns:

id: A unique identifier for each log entry, automatically generated.
ip: This column captures the client's IP address, helping us identify the source of each request.
timestamp: It records the exact time each request was made, ensuring accurate tracking.

The `register_request` Function

With the request_log table in place, we proceed to create the register_request function. This function plays a pivotal role in the rate-limiting process by logging every incoming request and associating it with the client's IP address. Here's how it's defined:

CREATE OR REPLACE FUNCTION register_request(ip_in TEXT) 
RETURNS VOID 
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO request_log (ip) 
  VALUES (inet(ip_in));
END;
$$;

The register_request function takes the client's IP address as input and inserts a corresponding entry into the request_log table. This action ensures that we have a comprehensive record of all incoming requests, which is essential for rate limiting and analytics.

With the infrastructure for tracking requests established, we're now ready to move forward with the rate-limiting implementation. In the following sections, we'll explore how to set rate limits and enforce them effectively.

Cleaning Old Requests

To maintain the efficiency of our system, it's crucial to regularly clean up old request logs. The clean_old_requests function takes care of this task:

CREATE OR REPLACE FUNCTION clean_old_requests() 
RETURNS VOID 
LANGUAGE plpgsql AS $$
BEGIN
  -- Delete request logs older than 12 hours
  DELETE FROM request_log 
  WHERE timestamp < NOW() - INTERVAL '12 hours';
END;
$$;

This function ensures that our database remains clutter-free and retains only the most relevant request data.

Implementing Rate Limiting

Now, let's delve into implementing rate limiting within our Supabase-powered application. Rate limiting is essential to prevent abuse and ensure fair resource allocation. We achieve this through the exceeded_rate_limit and check_rate_limit functions.

The `exceeded_rate_limit` Function

The exceeded_rate_limit function is responsible for checking if a client has exceeded the rate limit, which in this example is set at 5 requests per minute. Here's how it's defined:

CREATE OR REPLACE FUNCTION exceeded_rate_limit(ip_in TEXT) 
RETURNS BOOLEAN 
LANGUAGE plpgsql AS $$
DECLARE
  request_count INTEGER;
BEGIN
  SELECT count(*) INTO request_count 
  FROM request_log 
  WHERE ip = inet(ip_in) AND timestamp > NOW() - INTERVAL '1 minute';

  RETURN request_count >= 5; -- limit of 5 requests per minute
END;
$$;

This function counts the number of requests made by a client within the last minute and returns true if the limit is exceeded.

The `check_rate_limit` Function

The check_rate_limit function is pivotal for enforcing rate limits. It effectively manages rate limiting by logging the current request using the register_request function and verifying if the rate limit has been surpassed. If the limit is exceeded, it raises an exception:

CREATE OR REPLACE FUNCTION check_rate_limit() 
RETURNS VOID 
LANGUAGE plpgsql 
SET search_path = public, hdr, extensions
SECURITY DEFINER
AS $$
DECLARE 
  current_ip TEXT := hdr.ip();
  request_method TEXT := current_setting('request.method', TRUE);
BEGIN
  -- Only log non-GET requests because they are run
  -- in read-only transactions
  IF request_method IS NULL OR request_method <> 'GET' THEN
    PERFORM register_request(current_ip);
  END IF;

  -- Check if the rate limit has been exceeded 
  -- and raise an exception if necessary
  IF exceeded_rate_limit(current_ip) THEN
    RAISE EXCEPTION 'Rate limit exceeded';
  END IF;
END;
$$;

This function is a crucial component of your rate-limiting strategy, ensuring that each incoming request is correctly monitored and preventing clients from exceeding their allocated rate limits. It's important to note that this function primarily focuses on rate limiting for insert operations. While rate limiting for GET requests is possible, it may introduce performance concerns, such as making network requests that insert rate-limiting data.

Configuring pg_headerkit with PostgREST

To seamlessly integrate rate limiting with your Supabase-powered application, configure the pgrst.db_pre_request option to utilize the check_rate_limit function as a pre-request action within PostgREST:

ALTER ROLE authenticator 
SET pgrst.db_pre_request = 'check_rate_limit';
NOTIFY pgrst, 'reload config';

This configuration ensures that every request made to Supabase undergoes rate limit validation before execution, guaranteeing a fair and controlled usage of resources. Now, we can test the rate limit by sending a few post requests to a table:

Scheduled Cleanup

Maintaining the performance of your database requires periodic cleanup of old request logs. Schedule the clean_old_requests function to run automatically every midnight:

SELECT cron.schedule(
  'clean_old_requests',
  '0 0 * * *', -- Run every midnight
  $$ SELECT clean_old_requests(); $$
);

This automated cleanup process is crucial for keeping your database in an optimal state, free from unnecessary clutter, and ensuring efficient resource management.

Conclusion

In this comprehensive blog post, we've delved into the intricacies of implementing rate limiting for your Supabase-powered applications. Leveraging the power of PostgreSQL and the versatile pg_headerkit extension, we've provided you with a step-by-step guide to ensure fair resource allocation and safeguard your application against abuse.

Rate limiting is a fundamental tool in your arsenal to maintain top-notch performance and deliver a consistently excellent user experience. Armed with the knowledge gained from this article, you're now well-prepared to seamlessly integrate rate limiting into your Supabase application.

Don't stop here; take the concepts discussed in this post and adapt them to your specific use cases. Experiment, explore, and fine-tune your rate-limiting strategy to perfectly align with your application's unique requirements.

If you found this article valuable, you might also be interested in exploring related topics:

Boosting Supabase Reliability: A Guide to Using Postgres Foreign Data Wrappers: Learn how to enhance the reliability of your Supabase applications.
Exploring Data Relationships with Supabase and PostgreSQL: Dive deeper into understanding data relationships in Supabase.
Safeguarding Data Integrity with pg-safeupdate in PostgreSQL and Supabase: Explore techniques to maintain data integrity in your database.

For further information and guidance, consider these valuable references. For any questions, feedback, or assistance, please don't hesitate to reach out to me. We're here to help you on your journey to mastering rate limiting in Supabase.

References

For further information and guidance, consider these valuable references:

Supabase Official Documentation: Explore Supabase's official documentation for in-depth insights into this powerful backend platform.
PostgreSQL Official Documentation: Dive into the official documentation of PostgreSQL, the robust database system at the core of Supabase.
pg_headerkit Extension: For more details on pg_headerkit, visit the official GitHub repository.
PostgREST Official Documentation: Discover PostgREST's documentation for additional information on this helpful tool.

🚀 Learn more about Supabase

Comprehensive Guide: Deploying and Debugging Custom Webhooks on Supabase & PostgreSQL

Rodrigo Mansueli — Tue, 29 Aug 2023 16:18:33 +0000

In today's dynamic landscape of cutting-edge application development, the role of automation has grown paramount, revolutionizing user experiences and operational efficiency. A domain where automation truly thrives is the seamless integration of webhooks into your application's architecture. Webhooks introduce the power to orchestrate actions triggered by specific events within your database. This automated responsiveness gains exceptional potency when harmonized with a versatile backend service like Supabase and the robust capabilities of PostgreSQL. This comprehensive article embarks on a journey to unveil the art of creating and debugging PostgreSQL webhooks using the Supabase platform, highlighting the potent synergy that can reshape and optimize your development workflow.

The Nexus of Empowerment: An Alliance of Supabase and PostgreSQL

In the dynamic realm of modern application development, the selection of a backend service extends beyond technical considerations; it's a strategic choice with far-reaching implications for productivity and efficiency. The formidable partnership between Supabase and PostgreSQL presents a compelling proposition. This article delves into the realm of webhooks—automated triggers designed to spring into action in response to specific database events. Our journey involves an in-depth exploration of the intricacies involved in designing and debugging Postgres webhooks, harnessing the capabilities of Supabase to establish a fluid and efficient workflow.

Expanding Horizons: The Webhook Wrapper Function

At the heart of our webhook implementation, a pivotal role is played by the request_wrapper function. This versatile function operates as an intermediary, adroitly handling HTTP methods such as GET, POST, and DELETE. It forges smooth communication channels with external services and APIs, driven by dynamic input parameters encompassing method, url, params, body, and headers. Crafted using the expressive plpgsql language and fortified by the security definer concept for meticulous execution control, this function embodies the quintessence of streamlined automation.

Taking Charge of Your Automation: The Supabase Advantage

While Supabase generously offers webhooks as part of its toolkit, it is worth contemplating the merits of deploying them independently. By taking the reins of deployment, you gain the freedom to expand the timeout window beyond a mere second, granting you greater control over the nature of the requests being executed. This strategic move allows you to tailor the automation to your specific needs, resulting in a more responsive and versatile system.

-- 
-- Webhook wrapper
--
CREATE OR REPLACE FUNCTION request_wrapper(
    method TEXT,
    url TEXT,
    params JSONB DEFAULT '{}'::JSONB,
    body JSONB DEFAULT '{}'::JSONB,
    headers JSONB DEFAULT '{}'::JSONB
)
RETURNS BIGINT
SECURITY DEFINER
SET search_path = public, extensions, net
LANGUAGE plpgsql
AS $$
DECLARE
    request_id BIGINT;
    timeout INT;
BEGIN
    timeout := 3000;

    IF method = 'DELETE' THEN
        SELECT net.http_delete(
            url:=url,
            params:=params,
            headers:=headers,
            timeout_milliseconds:=timeout
        ) INTO request_id;
    ELSIF method = 'POST' THEN
        SELECT net.http_post(
            url:=url,
            body:=body,
            params:=params,
            headers:=headers,
            timeout_milliseconds:=timeout
        ) INTO request_id;
    ELSIF method = 'GET' THEN
        SELECT net.http_get(
            url:=url,
            params:=params,
            headers:=headers,
            timeout_milliseconds:=timeout
        ) INTO request_id;
    ELSE
        RAISE EXCEPTION 'Method must be DELETE, POST, or GET';
    END IF;
    RETURN request_id;
END;
$$;

Creating a Trigger Function for Automated Insert Events

Imagine a scenario where a new row enters the picture in the orders table. This particular moment sets in motion an automatic process triggered by a special function we've named after_order_insert(). Within this function's code lies a sophisticated logic that effortlessly triggers a call to another function called request_wrapper. This dynamic function orchestrates a POST request that's directed to a specific webhook URL you define. The magic happens as the values from the newly added row come together to form a JSON package—an essential element that shapes the upcoming webhook request.

CREATE OR REPLACE FUNCTION after_order_insert()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM request_wrapper('POST', 'your_webhook_url_here', 
                            '{"order_id": ' || NEW.order_id || 
                            ', "customer_id": ' || NEW.customer_id || 
                            ', "total_amount": ' || NEW.total_amount || '}');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Leading the Way with Triggers: Powering Automation for New Inserts

In the dynamic realm of PostgreSQL databases, the birth of triggers stands as a foundational pillar of database event automation. Our carefully crafted trigger, given the name orders_insert_webhook, steps onto the stage. This conductor is designed to harmonize with the rhythm of each fresh row that finds its place in the revered orders table. This invisible maestro then coordinates the execution of the after_order_insert() function as soon as a new data insertion event occurs. The result is a symphony of seamless coordination, allowing webhooks to come to life in response to the emergence of new data.

CREATE TRIGGER orders_insert_webhook
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION after_order_insert();

Exploring Webhook Debugging: A Closer Look

The journey through webhook integration entails not only implementation but also a vital debugging phase. This phase ensures the smooth functioning of your automated processes. At the core of this endeavor lies the net._http_response table—a valuable tool for debugging. This table captures the responses generated by HTTP requests executed through the request_wrapper function. It offers insights into request outcomes, including success, status codes, headers, content, timeouts, error messages, and creation timestamps.

Navigating the Debugging Process: Inside `net._http_response`

In your PostgreSQL database architecture, the net._http_response table takes on a crucial role. Its design encapsulates essential debugging information, aiding you in refining your automated workflows. This table serves as a repository of records detailing each HTTP request's journey. These insights are invaluable for diagnosing and resolving any issues in your automation processes. From tracking response status and content type to examining headers, content, and possible timeouts, this table becomes your compass in the journey to create robust and efficient automation.

Real-World Insight: Practical Scenarios

To understand the practicality of the net._http_response table, let's consider an example where you've set up a webhook to notify customers upon order fulfillment. As you navigate this process, the net._http_response table becomes your ally.

Use Case 1: Checking Request Status

To verify request status, execute the following query:

SELECT id, status_code, timed_out
FROM net._http_response;

This query offers a quick overview of request details, displaying IDs, status codes, and any timeouts.

Use Case 2: Exploring Request Details

For a comprehensive view of a specific request, use:

SELECT *
FROM net._http_response
WHERE id = your_request_id_here;

Replace your_request_id_here with the actual ID of the request. This query provides a detailed examination, including headers, content, and error messages.

In the process of webhook debugging, the net._http_response table shines as a guiding light. Its insights empower you to craft and maintain automation processes that endure.

-- Example: Checking request status
SELECT id, status_code, timed_out
FROM net._http_response;

-- Example: Exploring request details
SELECT *
FROM net._http_response
WHERE id = your_request_id_here;

Wrapping Up: Your Path to Smarter Automation

As we come to the end of this exploration into the world of automation and efficiency in app development, it's time to reflect on the journey we've taken. Throughout this article, we've teamed up Supabase and PostgreSQL with webhooks—a powerful trio that empowers you to create smarter, more responsive applications.

If you're hungry for more insights and want to delve deeper into enhancing your Supabase-powered apps, don't forget to check out some of our previous articles:

Wondering how to make your Supabase setup even more reliable? Dive into our guide on Boosting Supabase Reliability with Postgres Foreign Data Wrappers.
Exploring data relationships in Supabase and PostgreSQL? Learn the ropes with our article on Exploring Data Relationships with Supabase and PostgreSQL.
Need to empower your users with secure password verification? Our insights on Secure Password Verification and Update with Supabase and PostgreSQL have got you covered.

With these resources at your fingertips, you're well-equipped to take your app development journey to new heights. Remember, the world of coding is full of possibilities, and webhooks are just the beginning. Keep innovating, keep learning, and keep coding!

Got any thoughts or questions? We'd love to hear from you. Drop a comment below and let's keep the conversation going!

🚀 Learn more about Supabase

Creating Customized i18n-Ready Authentication Emails using Supabase Edge Functions, PostgreSQL, and Resend

Rodrigo Mansueli — Tue, 22 Aug 2023 14:26:17 +0000

Welcome to another exciting tutorial on building robust applications! Today, we're delving into the world of Supabase, an open-source backend solution that equips developers with a powerful toolkit for creating scalable applications. In this guide, we'll explore how to harness the potential of Supabase Edge Functions, PostgreSQL, and the Resend email service to craft a personalized authentication email system.

Introduction

As the demand for efficient backend solutions continues to grow, Supabase has emerged as a game-changer. Offering features like real-time subscriptions, authentication, and database management, Supabase simplifies complex backend tasks. However, one unique challenge lies in customizing email templates for user engagement. In our previous blog post on Exploring Data Relationships with Supabase and PostgreSQL, we delved into the intricacies of data relationships. Building upon that foundation, we now tackle the challenge of creating a personalized authentication email system that also seamlessly supports i18n localization.

The Significance of i18n Localization in Emails

In today's global landscape, catering to diverse audiences is imperative for enhancing user engagement. Internationalization (i18n) ensures that emails resonate with users across various cultures and languages. By dynamically replacing content based on the user's preferred language, we not only enhance the user experience but also foster a sense of inclusivity.

Prerequisites

Before we dive into the technical intricacies, it's essential to be well-acquainted with Supabase, PostgreSQL, and have a grasp of the basics of Deno if necessary. Setting up your development environment, installing essential tools, and configuring dependencies based on your operating system will establish a solid foundation for a successful implementation.

Establishing a Solid Database Foundation

Our journey begins with the establishment of a robust database foundation. This involves the creation of an email_templates table within an internal schema. This table serves as a pivotal repository, storing vital information such as subject lines, content, languages, and template types.

CREATE SCHEMA internal;

CREATE TABLE internal.email_templates (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY,
  subject TEXT NULL,
  content TEXT NULL,
  email_language TEXT NULL,
  email_type TEXT NULL,
  CONSTRAINT email_templates_pkey PRIMARY KEY (id)
);

By crafting this strong foundation, we lay the groundwork for a dynamic and versatile email system. This system will be powered by the forthcoming get_email_template function, which we'll explore in the following sections.

Crafting the Dynamic `get_email_template` Function

The core of our email system resides within the get_email_template function. This dynamic function retrieves email templates, utilizing inputs such as the template type, link, and language. Importantly, the function seamlessly integrates with the email_templates table, providing personalized email content.

CREATE OR REPLACE FUNCTION get_email_template(
  template_type TEXT,
  link TEXT,
  language TEXT DEFAULT 'en'
)
RETURNS JSON
SECURITY DEFINER
SET search_path = public, internal AS
$BODY$
DECLARE
  email_subject TEXT;
  email_content TEXT;
  email_json JSON;
BEGIN
  SELECT subject, REPLACE(content, '{{LINK}}', link) INTO email_subject, email_content
  FROM internal.email_templates
  WHERE email_type = template_type AND email_language = language;
  email_json := json_build_object('subject', email_subject, 'content', email_content);
  RETURN email_json;
END;
$BODY$
LANGUAGE plpgsql;
-- Protect this function to be only available to service_role key:
REVOKE EXECUTE ON FUNCTION get_email_template FROM anon, authenticated;

Customizing Email Templates for Common Authentication Scenarios

To enhance user experience and engagement, we'll be customizing email templates for several common authentication scenarios: password recovery, signup confirmation, invitation, and magic link.

These templates will be available in three languages: Portuguese, English, and Danish. You can seamlessly integrate these templates into the email_templates table within the internal schema. Below, you'll find the templates for each scenario:

--
-- Data for Name: email_templates; Type: TABLE DATA; Schema: internal; Owner: postgres
--

INSERT INTO "internal"."email_templates" ("id", "subject", "content", "email_language", "email_type") VALUES
    (1, 'Din Magisk Link', '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<meta http-equiv="Content-Type" content="text/html charset=UTF-8" />
<html lang="da">
<head></head>
<body style="background-color:#ffffff;font-family:-apple-system,BlinkMacSystemFont,&quot;Segoe UI&quot;,Roboto,Oxygen-Sans,Ubuntu,Cantarell,&quot;Helvetica Neue&quot;,sans-serif">
    <table align="center" role="presentation" cellSpacing="0" cellPadding="0" border="0" width="100%" style="max-width:37.5em;margin:0 auto;padding:20px 25px 48px;background-image:url(&quot;/assets/background-image.png&quot;);background-position:bottom;background-repeat:no-repeat, no-repeat">
        <tr style="width:100%">
            <td>
                <h1 style="font-size:28px;font-weight:bold;margin-top:48px">🪄 Din magiske link</h1>
                <table style="margin:24px 0" align="center" border="0" cellPadding="0" cellSpacing="0" role="presentation" width="100%">
                    <tbody>
                        <tr>
                            <td>
                                <p style="font-size:16px;line-height:26px;margin:16px 0"><a target="_blank" style="color:#FF6363;text-decoration:none" href="{{LINK}}">👉 Klik her for at logge ind 👈</a></p>
                                <p style="font-size:16px;line-height:26px;margin:16px 0">Hvis du ikke har anmodet om dette, bedes du ignorere denne e-mail.</p>
                            </td>
                        </tr>
                    </tbody>
                </table>
                <p style="font-size:16px;line-height:26px;margin:16px 0">Bedste hilsner,<br />- Contoso Team</p>
                <hr style="width:100%;border:none;border-top:1px solid #eaeaea;border-color:#dddddd;margin-top:48px" />
                <p style="font-size:12px;line-height:24px;margin:16px 0;color:#8898aa;margin-left:4px">Contoso Technologies Inc.</p>
            </td>
        </tr>
    </table>
</body>
</html>
', 'da', 'magiclink');

-- Check the full SQL file and all templates in the GitHub repo
-- https://github.com/mansueli/Supabase-Edge-AuthMailer

By offering tailored templates for these scenarios in multiple languages, you ensure that your email communications resonate with a broader audience. This personalization fosters stronger user engagement and interaction.

Developing the Deno Edge Function (index.ts)

In the upcoming section, we'll delve into the development process of the Deno Edge Function responsible for managing authentication email requests. Below, we'll provide an overview of the pivotal steps involved in this process:

Initializing Imports and Constants: We'll commence by importing essential modules and setting up constants that will facilitate the seamless development process.
Creating a Secure Supabase Client: Learn how to establish a secure connection to Supabase using admin credentials, ensuring authorized access to the required resources.
Handling Incoming HTTP Requests: Discover the utilization of the serve function to effectively handle incoming HTTP requests, enhancing the overall responsiveness of your system.
Extracting Vital Parameters: Understand the process of extracting crucial parameters from incoming requests, such as email, authentication type, language, password, and redirection URL.
Generating Secure Authentication Links: Explore the steps involved in generating secure authentication links using the Supabase admin API, facilitating secure user interactions.
Customizing Redirection Links: Learn how to customize redirection links to match specific requirements and elevate the user experience.
Invoking get_email_template Function: Dive into the usage of the Supabase rpc method to invoke the get_email_template function, enabling seamless retrieval of email content.
Integration of Resend API: Understand the seamless integration of the Resend API, allowing the delivery of personalized and informative emails to users.

For the complete code of the Deno Edge Function, refer to the provided index.ts file.

// Importing required libraries
import { serve } from 'https://deno.land/std@0.192.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

// Defining CORS headers
export const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
}

// Log to indicate the function is up and running
console.log(`Function "auth-mailer" up and running!`)

// Creating a Supabase client using environment variables
const supabaseAdmin = createClient(
  Deno.env.get('SUPABASE_URL') ?? '',
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
)

// Define a server that handles different types of requests
serve(async (req: Request) => {
  // Handling preflight CORS requests
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders })
  }

  try {
    // Destructuring request JSON and setting default values
    let { email, type, language = 'en', password = '', redirect_to = '' } = await req.json();
    console.log(JSON.stringify({ email, type, language, password }, null, 2));

    // Generate a link with admin API call
    let linkPayload: any = {
      type,
      email,
    }

    // If type is 'signup', add password to the payload
    if (type == 'signup') {
      linkPayload = {
        ...linkPayload,
        password,
      }
      console.log("linkPayload", linkPayload);
    }

    // Generate the link
    const { data: linkResponse, error: linkError } = await supabaseAdmin.auth.admin.generateLink(linkPayload)
    console.log("linkResponse", linkResponse);

    // Throw error if any occurs during link generation
    if (linkError) {
      throw linkError;
    }

    // Getting the actual link and manipulating the redirect link
    let actual_link = linkResponse.properties.action_link;
    if (redirect_to != '') {
      actual_link = actual_link.split('redirect_to=')[0];
      actual_link = actual_link + '&redirect_to=' + redirect_to;
    }

    // Log the template data
    console.log(JSON.stringify({ "template_type":type, "link": linkResponse, "language":language }, null, 2));

    // Get the email template
    const { data: templateData, error: templateError } = await supabaseAdmin.rpc('get_email_template', { "template_type":type, "link": actual_link, "language":language });

    // Throw error if any occurs during template fetching
    if (templateError) {
      throw templateError;
    }

    // Send the email using resend
    const RESEND_API_KEY = Deno.env.get('RESEND_API_KEY')
    const resendRes = await fetch('https://api.resend.com/emails', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        Authorization: `Bearer ${RESEND_API_KEY}`,
      },
      body: JSON.stringify({
        from: 'rodrigo@mansueli.com',
        to: email,
        subject: templateData.subject,
        html: templateData.content,
      }),
    });

    // Handle the response from the resend request
    const resendData = await resendRes.json();
    return new Response(JSON.stringify(resendData), {
      status: resendRes.status,
      headers: {
        'Content-Type': 'application/json',
      },
    })
  } catch (error) {
    // Handle any other errors
    return new Response(JSON.stringify({ error: error.message }), {
      headers: { ...corsHeaders, 'Content-Type': 'application/json' },
      status: 400,
    })
  }
})

Addressing Supabase Email Template Behavior

It's essential to acknowledge a particular behavior of Supabase when handling email templates. By default, Supabase removes HTML tags from email templates when using the platform's default templates. The approach we present here allows you to exercise control over the visual presentation of your email content. To effectively include HTML elements in your email templates, you can adhere to standard HTML practices.

Conclusion and Future Enhancements

Congratulations on successfully constructing a personalized authentication email system using Supabase Edge Functions, PostgreSQL, and the Resend service. By harnessing the capabilities of Supabase, you've streamlined the process of delivering tailored authentication emails to your users.

While this tutorial covers the foundational aspects, there's always room for growth. Consider expanding the range of email template customization or integrating additional third-party services to elevate user engagement. As you continue exploring Supabase's capabilities, share your insights and enhancements with the open-source community.

You can find the complete code used in this article on GitHub.

Further Learning Resources

For additional learning, we recommend exploring the following resources:

🚀 Learn more about Supabase

How to Boost Supabase Reliability: A Guide to Using Postgres Foreign Data Wrappers

Rodrigo Mansueli — Tue, 15 Aug 2023 15:00:39 +0000

Welcome to this comprehensive guide on enhancing the reliability and isolation of your Supabase project using the power of Foreign Data Wrappers (FDW). In this blog post, we will delve into the strategic implementation of FDWs to connect your project with an independent queue system. By adopting this approach, your Supabase project gains an additional layer of resilience, ensuring that even if your primary system encounters issues, the queue continues to operate seamlessly. This exploration promises to equip you with the knowledge to fortify your application's robustness. In a previous post, we explored how to create a pseudo FDW to MySQL using an Edge Function as a bridge, now we'll explore using proper Postgres FDW.

Prerequisites for Implementing FDWs

To embark on this journey, it's essential to have the following prerequisites in place.

Main Supabase Project: You should have your primary Supabase project set up and operational. This serves as the foundation upon which we'll build the enhanced system.
Foreign Supabase Project: Similarly, your separate foreign Supabase project needs to be ready. This distinct database will be integrated using the Foreign Data Wrappers approach.

Before proceeding, ensure these prerequisites are aligned to make the most of this guide.

Exploring the Process: The process we're about to undertake involves creating a bridge between your main Supabase project and the foreign Supabase project through the magic of Foreign Data Wrappers. This bridge facilitates communication and data sharing between the projects, offering enhanced reliability and isolation.

Create a User in the Foreign Database:

In this crucial step, we lay the foundation for secure communication. By creating a dedicated user 'foreign_user' and granting it specific privileges, we establish the necessary credentials to bridge the databases. The use of BYPASSRLS ensures controlled data access.

CREATE USER foreign_user WITH PASSWORD 'password' BYPASSRLS;

GRANT SELECT, INSERT, UPDATE, DELETE 
ON ALL TABLES IN SCHEMA public 
TO foreign_user;

GRANT USAGE, SELECT 
ON ALL SEQUENCES IN SCHEMA public 
TO foreign_user;

Configuration in the Main Database:

This step involves configuring your main Supabase database to communicate with the foreign server. We set up a new schema 'queue' to house the imported foreign data. Additionally, we establish a foreign server named 'foreign_server,' specifying connection details like host, port, and dbname. The user mapping ensures secure authentication, while the IMPORT FOREIGN SCHEMA command integrates the foreign schema into your main database.

CREATE EXTENSION postgres_fdw;
CREATE SCHEMA queue;

ALTER DEFAULT PRIVILEGES IN SCHEMA queue
GRANT ALL ON TABLES TO postgres;

CREATE SERVER foreign_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'db.foreign.supabase.co', port '5432', dbname 'postgres');

CREATE USER MAPPING FOR postgres
    SERVER foreign_server
    OPTIONS (user 'foreign_user', password 'password');

IMPORT FOREIGN SCHEMA public
    FROM SERVER foreign_server
    INTO queue;

ALTER SERVER foreign_server 
    OPTIONS (fetch_size '50000');

Explaining fetch_size Adjustment: The ALTER SERVER command with the fetch_size option plays a vital role in optimizing data retrieval. By setting the fetch size to '50000,' we dictate how many rows of data the server fetches in a single request. Larger fetch sizes can enhance data retrieval efficiency, especially when dealing with substantial datasets.

Enhancing Queue Reliability: This approach not only boosts the reliability of your main Supabase project but also increases the robustness of your queue system. To understand more about creating a queue system using Supabase and PostgreSQL, refer to the article Building a Queue System with Supabase and PostgreSQL.

Conclusion

By meticulously following these steps, you pave the way for a robust, interconnected Supabase ecosystem. The integration of Foreign Data Wrappers fosters reliability, ensuring that the queue system remains resilient even in the face of challenges.

Don't hesitate to explore the Supabase documentation, which offers a wealth of knowledge on database extensions and their applications.

🚀 Learn more about Supabase

Integrating Password + Email Sign-in for magic-link users with Supabase and PostgreSQL

Rodrigo Mansueli — Tue, 01 Aug 2023 13:53:35 +0000

In the realm of web development, the popularity of backend-as-a-service (BaaS) platforms has soared due to their convenience and time-saving abilities. One such platform that has garnered attention is Supabase, an open-source BaaS alternative. Supabase offers developers an array of features, including authentication, database management, and more. In this comprehensive guide, we will explore how to enable password + email sign-in for users who initially started with magic links in Supabase. This integration empowers users to effortlessly transition from magic links to password-based authentication.

Prerequisites

Before diving into this step-by-step tutorial, ensure you have the following prerequisites in place:

Deno: Deno is a secure runtime for JavaScript and TypeScript. Visit the official website for instructions on installing Deno.
Supabase Account: Create a free Supabase account if you haven't done so already. You'll require your Supabase URL and anonymous key for authentication.
PostgreSQL: Ensure you have PostgreSQL installed or set up a PostgreSQL database using Supabase.

To maximize your understanding of this tutorial, it's beneficial to have a basic grasp of Supabase, PostgreSQL, and HTTP requests.

Setting Up the Database

Let's begin by assuming you have one of the web app templates with a public.profile table. We'll specifically add a new column to the "profiles" table to indicate whether existing users are using magic links.

-- Setting the profiles table to indicate that existing users were using magic links
ALTER TABLE public.profiles 
ADD COLUMN auth_options text[] DEFAULT ARRAY['magiclink']::text[] NULL;

Implementing the Edge Function

The edge function plays a pivotal role in this integration as it verifies the user's authentication method (magic link or password) and updates the necessary information accordingly. Below is the Deno code for the edge function:

import { serve } from "https://deno.land/std@0.192.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

// CORS headers for allowing requests from any origin
const corsHeaders = {
  "Access-Control-Allow-Origin": "*",
  "Access-Control-Allow-Headers":
    "authorization, x-client-info, apikey, content-type",
};

serve(async (req) => {
  if (req.method === 'OPTIONS') {
    return new Response('ok', { headers: corsHeaders });
  }

  // Creating a Supabase client with the provided authorization token
  const supabaseClient = createClient(
    Deno.env.get("SUPABASE_URL") ?? "",
    Deno.env.get("SUPABASE_ANON_KEY") ?? "",
    {
      global: { headers: { Authorization: req.headers.get("Authorization")! } },
      auth: {
        autoRefreshToken: false,
        persistSession: false,
        detectSessionInUrl: false
      }
    }
  );

  // Getting the user information from the provided authorization token
  const { data: { user }, error: userError } = await supabaseClient.auth.getUser();
  if (userError) {
    console.error("User error", userError);
    return new Response(JSON.stringify({ error: userError.message }), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 400,
    });
  }

  // Getting the current authentication options for the user from the profiles table
  const { data: auth_options, error: authError } = await supabaseClient.from('profiles').select('auth_options').eq('id', user.id);
  if (authError) {
    console.error("Auth options error", authError);
    return new Response(JSON.stringify({ error: authError.message }), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 400,
    });
  }

  // Checking if the user already has password-based authentication
  if (auth_options[0].auth_options.includes("password")) {
    console.error("Password update not allowed for users with password-based authentication");
    return new Response(JSON.stringify({ error: "Password update not allowed for users with password-based authentication" }), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 400,
    });
  }

  // Creating a separate Supabase client with the service role key for administrative tasks
  const supabaseAdmin = createClient(
    Deno.env.get("SUPABASE_URL") ?? "",
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") ?? "",
    {
      auth: {
        autoRefreshToken: false,
        persistSession: false,
        detectSessionInUrl: false
      }
    }
  );

  // Updating the user's password using the administrative client
  const { newPassword } = await req.json();
  const { error: updateError } = await supabaseAdmin.auth.admin.updateUserById(user.id, { password: newPassword });
  if (updateError) {
    console.error("Update error", updateError);
    return new Response(JSON.stringify({ error: updateError.message }), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 400,
    });
  }

  // Updating the authentication options for the user in the profiles table
  const updatedAuthOptions = [...auth_options[0].auth_options];
  if (!updatedAuthOptions.includes("password")) {
    updatedAuthOptions.push("password");
  }

  const { error: updateAuthOptionsError } = await supabaseClient.from('profiles').update({ auth_options: updatedAuthOptions });
  if (updateAuthOptionsError) {
    console.error("Update auth options error", updateAuthOptionsError);
    return new Response(JSON.stringify({ error: updateAuthOptionsError.message }), {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 400,
    });
  }

  // Returning a success message if everything went smoothly
  return new Response(
    JSON.stringify({ message: "Password updated successfully" }),
    {
      headers: { ...corsHeaders, "Content-Type": "application/json" },
      status: 200,
    },
  );
});

Testing the Integration

To ensure the seamless functioning of our integration, let's conduct some tests encompassing various scenarios:

Test with a user who has been using magic links and now wants to set a password.
Test with a user who already has a password set to ensure password updates are not allowed for such users.

Conclusion

Congratulations on successfully integrating password + email sign-in with Supabase and PostgreSQL! This integration provides users with the flexibility to switch from magic links to password-based authentication effortlessly, enhancing the overall user experience. By harnessing the power of Supabase's open-source backend-as-a-service capabilities and PostgreSQL's robustness, you can build secure and user-friendly applications more efficiently.

Safeguarding Data Integrity with pg-safeupdate in PostgreSQL and Supabase

Rodrigo Mansueli — Tue, 25 Jul 2023 19:40:58 +0000

Maintaining data integrity is of paramount importance when developing web applications that rely on PostgreSQL as the underlying database. Accidental data modifications can lead to severe consequences, compromising the reliability and accuracy of the application. In this blog post, we will explore how PostgreSQL's pg-safeupdate extension, in conjunction with Supabase.com, a versatile backend-as-a-service platform, can be a powerful tool to ensure data safety and integrity.

What is pg-safeupdate?

pg-safeupdate plays a crucial role in PostgreSQL, aiming to mitigate the risks linked with unintended data updates. Its primary goal is to prevent update queries lacking a WHERE clause, which can inadvertently update all rows in a table. Enforcing the presence of a WHERE clause, pg-safeupdate ensures explicit and targeted updates, significantly reducing the risk of accidental data alterations.

Enabling the Extension

Enabling pg-safeupdate can be done at two levels: on a per-connection basis or for all connections to the database. To enable it for a specific connection, you can use the following SQL command:

LOAD 'safeupdate';

For broader applications, you can enable pg-safeupdate for all connections to the database with the following command:

ALTER DATABASE my_app_db SET session_preload_libraries = 'safeupdate';

The choice between the two methods depends on the specific use case and the desired level of data protection.

How pg-safe update Works:

Once pg-safeupdate is enabled, it actively monitors all update queries issued against the database. If an update query is detected without a WHERE clause, indicating an attempt to update all rows in the affected table, pg-safeupdate it intervenes and returns an error message. This prompt reminds developers to include a WHERE clause, ensuring that updates are precise, deliberate, and confined to specific rows.

Usage Example: To better understand the functioning of pg-safeupdate, let's consider a real-world example involving a inventory table. This table contains columns such as product_id, product_name, quantity, and price_per_unit. Without pg-safeupdate, an unintentional update query might look like this:

LOAD 'safeupdate';

UPDATE inventory SET quantity = 100;

This query updates the quantity for all products to 100, which is certainly not what we intended. However, with pg-safeupdate enabled, the query will be intercepted, and an error message will be returned:

ERROR: UPDATE requires a WHERE clause.

To proceed with the update, we must provide a WHERE clause that specifies the product_id of the product we want to modify:

UPDATE inventory SET quantity = 100 WHERE product_id = 'ABC123';

This updated query now precisely modifies the quantity of the product with the product_id of 'ABC123', ensuring data integrity.

Real-world Use Cases: In real-world scenarios, pg-safeupdate becomes a powerful safeguard against accidental data alterations. It prevents catastrophic data corruption in production environments, where unintended updates could lead to substantial financial losses or erode user trust. Additionally, during development and testing, pg-safeupdate ensures that database changes are deliberate, minimizing debugging efforts caused by unintentional data modifications.

Comparisons with Alternative Methods: While several methods exist for ensuring data integrity, pg-safeupdate stands out for its simplicity and effectiveness. Database triggers can achieve similar outcomes, but they might require more complex configurations and can be less transparent. Application-level constraints depend on the application code's correctness, which introduces a higher risk of human error. In contrast, pg-safeupdate offers a direct and efficient approach to enforcing update restrictions.

Conclusion: In the world of web applications, data integrity is non-negotiable. PostgreSQL's pg-safeupdate extension, in collaboration with Supabase, presents a formidable solution to mitigate the risks of accidental data updates. Mandating the use of WHERE clauses in update queries pg-safeupdate ensures that updates are intentional and specific. When combined with Supabase's features, developers can create robust and reliable web applications, safeguarding data from inadvertent modifications.

Additional Tips and Best Practices:

Enable pg-safeupdate during development and testing phases to catch unintended updates early in the development process.
Always include a WHERE clause in update queries, even when the extension is not enabled, as a best practice for data safety.
Consider enabling pg-safeupdate for all connections in production environments to enforce strict update restrictions consistently.

Forem: Rodrigo Mansueli

Secure Your Supabase Auth with email_guard

Why You Need email_guard for Supabase Authentication Security

The Problem: Disposable Emails and Gmail Tricks

The Solution: Smart Email Validation at Signup in Supabase

Understanding Trusted Language Extensions (TLE) in Supabase

What is a TLE?

Why Use TLEs for Security in Supabase?

Step 1: Installing the TLE Infrastructure on Supabase

A. Set Up dbdev and Supabase CLI (If Needed)

B. Generate the Migration with dbdev

C. Apply the Migration

Step 2: Understanding What You Just Installed

Table: disposable_email_domains

Function: normalize_email(text)

Function: is_disposable_email_domain(text)

Function: is_disposable_email(text)

Hook Helper: hook_prevent_disposable_and_enforce_gmail_uniqueness(jsonb)

Step 3: Wire Up the Supabase Auth Hook for Email Validation

Navigate to Auth Hooks in Dashboard

Configure the Hook

What Happens During Signup

Step 4: Testing Your email_guard Setup on Supabase

Test 1: Check Disposable Email Detection

Test 2: Test Gmail Normalization

Test 3: Simulate the Hook

Step 5: Combining with Supabase's Built-in Protections

Leaked Password Protection

Keeping the Blocklist Current in email_guard

How Updates Work

Upgrading to the Latest Version

Advanced Usage & Customization for Supabase email_guard

Custom Domain Blocking

Checking Existing Users

Custom Hook Logic

Performance Considerations for email_guard in Supabase

Benchmarking

Index Optimization

Troubleshooting email_guard on Supabase

Hook Not Triggering

False Positives

Migration Conflicts

Security Best Practices for Supabase Authentication

Defense in Depth

Monitoring

Privacy Considerations

Conclusion: Building a Secure Foundation with email_guard on Supabase

Next Steps

Building Secure API Key Management with Supabase, KSUID & PostgreSQL

Overview: A Simple Way to Manage API Keys

Step 1: Install pg_idkit Extension with dbdev and Supabase CLI

A. Set Up dbdev and Supabase CLI

B. Generate Migration File with dbdev

C. Apply the Migration with Supabase CLI

Step 2: Set Up the Table Schema

Step 3: Create an API Key Server-Side

Step 4: Verify API Keys with Supabase Edge Functions

Step 5: List Keys for Your Dashboard

How Your App Uses These in Supabase

Create a Key

Verify a Key

List Keys

Additional Notes on Security and Choices

Building a Slack Bot for AI-Powered Conversations with Supabase

Edge Functions: The Soul of Your Slack Bot

Storing Secrets Securely in Vault

Configuring Slack Apps

Installing DBDEV and Supa_Queue

Installing supabase-dbdev

Installing the supa_queue Extension

Installing and Interacting with Your Slack Bot

Installing Your Bot to Slack

General Use with Hugging Face Model

Code-Related Inquiries with ChatGPT Model

Conclusion

References

Easy Deployment and Rollback of PostgreSQL Functions with Supabase

PostgreSQL and Supabase in Modern Web Applications

Tracking Function History in PostgreSQL

Saving Function History

Installing `supabase-dbdev`

Installing the `supa_queue` Extension

The `archive.save_function_history` Function

The `create_function_from_source` Function

The `request_log` Table

The `register_request` Function

The `exceeded_rate_limit` Function

The `check_rate_limit` Function

Navigating the Debugging Process: Inside `net._http_response`

Crafting the Dynamic `get_email_template` Function