Azure Security Hardening — A Course for the the certification client Migration
**Why this exists:** Vercel and Supabase give you a secure baseline automatically. Azure gives you the *tools* to build an equal or better baseline — but nothing happens until you configure it. This course walks through exactly what's missing right now on `the certification client-prod-uae`, and how to close each gap, in the order that matters.
Four lessons. Each one ends with something you actually do on your own account, not just read about.
---
Lesson 1 — Why "public access: yes" isn't automatically bad, but isn't automatically safe either
The concept
When you created the Postgres server, you set:
- Public access: **Yes**
- Allow public access from any Azure service: **Yes**
This means your database has a real IP address reachable from the internet. The only things standing between "reachable" and "breached" right now are:
1. A username + password (`the client's platform` + whatever you set)
2. Whatever firewall rules you've added
That's actually **the same security model Supabase uses by default** — Supabase's Postgres is also internet-reachable, protected by credentials and RLS. So public access itself isn't the gap. The gap is that on Azure, **you** are the one who has to configure the firewall correctly — Supabase pre-configures sane defaults for you.
What "firewall rules" actually control
Azure Postgres firewall rules are an **allowlist of IP addresses**. Nothing outside that list can even attempt to connect — not "attempt and get rejected at login," but rejected at the network level, before authentication is even tried. This is a stronger control than password auth alone, because it means a leaked password isn't enough on its own — the connection also has to come from an approved IP.
Right now, because you checked "allow public access from any Azure service," **any resource anywhere in Azure — not just yours — can attempt to connect** (though they'd still need valid credentials). This was the right call *temporarily*, because your Container Apps don't exist yet and need a way in. But it's broader than it needs to be long-term.
The fix, later (not now — you don't have Container Apps yet)
Once your Container Apps exist and you know their outbound IPs or you're using VNet integration, you tighten this to:
- Remove "allow any Azure service"
- Add firewall rules scoped to just your Container Apps' specific subnet/IP range
- Optionally move to **Private access (VNet integration)** entirely — this removes the public IP altogether, so the database is only reachable from inside your own virtual network. This is the gold-standard version of "in-region and locked down."
Do this now
Go to `the certification client-prod-uae` → your Postgres server → **Networking** (left sidebar). You'll see the firewall rule list. It's currently just your own IP (from "Add current client IP" if you clicked that) plus the "any Azure service" toggle. Just look at it — don't change anything yet. You're confirming you can find this screen again when Lesson 4 tells you to come back.
---
Lesson 2 — RLS: why the new database is emptier of protection than the one you're replacing
The concept
Your existing Supabase project isn't just a database — it's a database **plus** a security layer sitting on top of every table, called Row Level Security (RLS). Here's the plain-English version of how it works:
Imagine the `observations` table (teacher performance appraisals) sitting on a shelf. Without RLS, anyone with the connection string can read every row. With RLS, Postgres itself checks a rule *before returning any row*: "does this specific logged-in user have permission to see this specific row?" If not, that row simply doesn't come back — not hidden in the UI, actually filtered by the database engine itself.
Your tracker's security depends entirely on this. A teacher can only see their own observations. A principal can see observations for their school. This isn't enforced by your app's code checking `if (user.role === 'principal')` — it's enforced by Postgres itself, which is much harder to accidentally bypass.
Why the new Azure Postgres server doesn't have any of this yet
RLS policies aren't something that exists "in Postgres" generically — they're specific rules you write, tied to specific tables, tied to a specific way of knowing who's logged in (`auth.uid()`). That function — `auth.uid()` — isn't a native Postgres thing. It's **Supabase's own invention**, part of their Auth system (GoTrue).
Plain Azure Postgres has never heard of `auth.uid()`. If you copied your tables over today and pointed the app at Azure, every RLS policy that says `USING (auth.uid() = user_id)` would either **error out** or — worse — **silently evaluate to false or true for everyone**, because the function it depends on doesn't exist. That's the "silent authorization hole" the diagnostic flagged. It's not hypothetical; it's what happens by default if you skip this step.
The fix: Path A, which you already chose
This is why the plan calls for **self-hosting GoTrue + PostgREST + Storage** as their own Container Apps, pointed at the Azure Postgres server. GoTrue recreates the `auth.uid()` function and the whole authentication flow. Once that's running, your *existing* `.sql` policy files — the ones already written for the tracker — can be applied to Azure Postgres essentially unchanged, because the thing they depend on (`auth.uid()`) now exists again.
The alternative (Path B — rewriting everything in app code) was rejected specifically because it's much easier to get subtly wrong, and a subtle RLS bug doesn't throw an error — it just quietly shows someone data they shouldn't see.
Do this now
No action yet — this lesson is conceptual, because GoTrue isn't built yet. But open one of your tracker's policy files (e.g. `11_artefact_policies.sql`) and just look at one `CREATE POLICY` line. Find where it says `auth.uid()`. That's the exact piece of magic this lesson is about — once you can point to it, you understand the dependency.
---
Lesson 3 — Key Vault: why passwords typed into a portal form aren't the end of the story
The concept
You just typed a real password (`the client's platform`'s password) into an Azure portal form. Right now, that password exists in a few places:
- In your head / password manager (good)
- Inside Azure's own encrypted config for that server (fine, that's expected)
- Potentially in your terminal history, a `.env` file, or a Claude Code conversation, if you ever type it again to configure a connection (this is the risk)
**Key Vault's job is to be the *one* place secrets live**, so that instead of pasting a real password into fifty different config files across five Container Apps, each app instead holds a *reference* to Key Vault — a pointer, not the secret itself — and fetches the real value securely at runtime.
This matters for a very concrete reason: **every place a secret is typed or stored is a place it can leak.** A `.env` file committed to git by accident, a password visible in a terminal screenshot, a value pasted into the wrong chat — Key Vault doesn't eliminate all of these risks, but it shrinks the number of places the real secret ever has to exist.
How it fits your five-app estate
Once your Container Apps exist, each one needs to know how to reach the Postgres server — that means each one needs the connection string, which contains the password. Instead of setting that as a plain environment variable on each Container App (visible to anyone with portal access to that app), you:
1. Store the actual password once, in Key Vault
2. Grant each Container App permission to *read* that one secret (via a Managed Identity — Azure's version of "this app is allowed to ask Key Vault for things, without a human password")
3. The Container App fetches the secret at startup, never storing it in its own visible config
This is the direct Azure equivalent of how you already keep the Anthropic API key server-side-only, never client-side, in your Vercel apps — same principle, different platform.
Do this now
Go to your search bar → type **"Key Vault"** → click **+ Create**. Fill in:
- Resource group: `the certification client-prod-uae`
- Key vault name: something like `the certification client-secrets-uae`
- Region: **UAE North**
Click through to Create — this is cheap (near-free at your usage level) and it's fine to have it sitting empty for now. You're just getting the vault to exist so that when Container Apps come online, there's already a home for their secrets. Don't add the Postgres password into it yet — we'll do that together when we build the first Container App.
---
Lesson 4 — Putting it together: the actual order of operations
You don't do all of this at once. Here's the real sequence, tied to what you've already built and what's still ahead:
| Step | What | Status |
|---|---|---|
| 1 | Postgres server created, public access open for setup convenience | ✅ Done |
| 2 | Key Vault created, empty | → Do in Lesson 3 |
| 3 | GoTrue + PostgREST + Storage stood up as Container Apps | Not yet — next major build phase |
| 4 | Existing RLS policy files applied against Azure Postgres, now that `auth.uid()` exists | Depends on step 3 |
| 5 | Site Container Apps (`the certification client-site`, `the certification client-tracker`, `pea-site`, `the school-site`) built, each pulling secrets from Key Vault via Managed Identity | Depends on step 3 |
| 6 | **Only once all four site apps are live and stable**: tighten Postgres firewall — remove "any Azure service," scope to actual Container App ranges or move to VNet-only | Final hardening step |
The honest summary: **today, this database is a scaffold, not a finished, secured system.** It becomes as secure as (and eventually more secure than) Vercel/Supabase specifically by walking through steps 2–6 in order. Skipping ahead — for instance, building the site apps before RLS is re-applied — is exactly how a silent authorization hole gets shipped.
---
Quick-reference glossary
- **RLS (Row Level Security)** — Postgres feature that filters which rows a query can see, based on who's asking. Your tracker's actual security boundary.
- **`auth.uid()`** — a Supabase-specific function your RLS policies depend on. Doesn't exist on plain Azure Postgres until GoTrue is running.
- **GoTrue / PostgREST / Storage API** — the three open-source pieces that make up "Supabase" under the hood. Self-hosting these on Azure recreates the same auth/API/storage behavior your app already expects.
- **Managed Identity** — a way for an Azure resource (like a Container App) to prove who it is to other Azure resources (like Key Vault), without a human-managed password.
- **Firewall rule (Postgres)** — an IP allowlist; connections from outside it are rejected before login is even attempted.
- **Private access / VNet integration** — removes the database's public IP entirely, making it reachable only from inside your own virtual network. The strongest version of network isolation, appropriate once your app architecture is stable.
---
What to actually do this week
1. ✅ Postgres server — done
2. Create the empty Key Vault (Lesson 3, ~5 minutes)
3. Nothing else yet — the next real step is standing up the GoTrue/PostgREST/Storage Container App, which is a build task, not a portal-clicking task. That's the next thing to bring back to this chat when you're ready.