# 8.4 The Manager's Override (Security Definers)
We have a perfectly secure Cafe. The `secrets` table is protected by a Bouncer (Row-Level Security), ensuring the `cafe_waiter` can never read the `cafe_manager`'s safe code.
But what if the Waiter *needs* to perform an auditing task that requires reading all the secrets? We don't want to make the Waiter a permanent Manager—that's too dangerous. We need to grant them **temporary, delegated authority**.
## The Manager's Permission Slip (Security Definer)
Postgres allows functions to be defined as `SECURITY DEFINER`. This is like a **Permission Slip** handed to the Waiter by the Manager.
Normally, when you run a function (`SECURITY INVOKER`), the Elephant asks, "Who is running this?" With a `SECURITY DEFINER` function, the Elephant asks, "Who *signed* this slip?"
> [!NOTE]
> **Technical Contrast: Invoker vs. Definer**
> Normally, functions are **`SECURITY INVOKER`**. The bouncers check the person who walked in the door. But a **`SECURITY DEFINER`** function skips the individual check and uses the Creator’s ID instead. It’s the difference between using your own library card and using the Principal’s golden key.
> [!CAUTION]
> **Privilege Escalation**: This is a powerful but dangerous tool. By creating a `SECURITY DEFINER` function, you are effectively letting another role "become" you for the duration of that function. You are allowing **Privilege Escalation**—the ability to act above their usual pay grade.
Let's log in as the `postgres` Superuser (the highest level Manager) and create a stamp for the Waiters.
```sql
-- Creating the Magical Stamp
CREATE OR REPLACE FUNCTION read_all_secrets() RETURNS SETOF secrets AS $
SELECT * FROM secrets;
$ LANGUAGE sql SECURITY DEFINER SET search_path = public;
-- Revoke it from the general public, and give it ONLY to the waiters
REVoke ALL ON FUNCTION read_all_secrets() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION read_all_secrets() TO cafe_waiter;
```
## Using the Stamp
Now, let's watch what happens when the lowly Waiter tries to read the `secrets` table directly versus using the Magical Stamp.
```sql
SET ROLE cafe_waiter;
-- Normal Read (Bouncer blocks the view)
SELECT * FROM secrets;
-- Literal Output:
-- id | owner | secret
-- ---+-------------+--------------------------
-- 2 | cafe_waiter | The Chef burned the toast
-- Using the Stamp (Bouncer steps aside)
SELECT * FROM read_all_secrets();
-- Literal Output:
-- id | owner | secret
-- ---+--------------+---------------------------
-- 1 | cafe_manager | The safe code is 555-1234
-- 2 | cafe_waiter | The Chef burned the toast
```
When the `read_all_secrets()` function runs, the Elephant temporarily treats the Waiter as if they were the `postgres` superuser! They bypass RLS entirely. As soon as the function ends, the magic wears off, and they are a lowly Waiter again.
> [!WARNING]
> **The `search_path` Trap**: Never hand out a Permission Slip without explicitly setting the `search_path = public` (or your specific schema).
>
> **A Crime Scene Scenario**: Imagine the Manager hands you a slip that says "Clean the floor in the pantry." If you have a private, hidden room that you *also* call "the pantry," a relative slip might let you clean your private room using the Manager's golden bucket! By setting **`search_path = public`**, you are writing "Clean the floor in **THE MAIN KITCHENS PANTRY**" on the slip, so there is no confusion.
>
> **The Shape of the Idea**: Shared authority requires **Specific Destinations**. Without a fixed path, you are giving a blank check that can be cashed in any room!
## The Bouncer's Audit Checklist
Before you open the doors of the Elephant Cafe to the public, every Security-Conscious Architect should perform a final sweep of their "Security Posture":
1. **Name Tags (Roles)**: Are all staff members using unique roles, or are they sharing passwords like a pack of unruly monkeys?
2. **Room Keys (GRANTs)**: Have you handed out the smallest keys possible? (Principle of Least Privilege).
3. **Standing Orders (Defaults)**: Will new ledgers created tomorrow automatically receive the correct security stamps?
4. **The VIP List (RLS)**: Is Row-Level Security active on every multi-tenant table to ensure customers only see their own rows?
5. **The Wall-Hacks**: Who holds the Superuser sticker, and are they only using it for emergencies?
The Elephant is naturally lazy, but the Bouncer must be naturally untrusting. Always audit your bouncers before the busy lunch rush!
---
| ← Previous | ↑ Table of Contents | Next → |
| :--- | :---: | ---: |
| [[Chapter 8/8.3 - The VIP List (Row-Level Security)\|8.3 The VIP List (Row-Level Security)]] | [[Learn You a Postgres for Great Good\|Home]] | [[Summary and Epilogue\|Summary & Epilogue]] |