Shubham sahu

Thanks for going this far with it, and for writing the limit into SECURITY.md instead of leaving it unsaid. On the impersonation hole, here's the shape I'd try. I haven't run it against your tests, so treat it as a sketch. `auth.uid()` answers whatever the session declares, and you can't redefine it because Supabase owns `auth`. Your policies and guards don't have to call it directly, though. Put one function in front of it and let that function decide whether the declaration counts, based on `session_user`, which a session can't set: ```sql create or replace function ekwo.uid() returns uuid language sql stable security definer set search_path = '' as $$ select case -- PostgREST: the claims come from a token it verified when session_user = 'authenticator' then auth.uid() -- the owner (CLI, migrations): it can already disable every trigger, -- so letting it declare a member widens nothing when pg_has_role(session_user, (select c.relowner from pg_catalog.pg_class c where c.oid = 'public.companies'::regclass), 'MEMBER') then auth.uid() -- any other login: only the identity the owner granted it, else null else (select li.user_id from ekwo.login_identity li where li.login = session_user) end $$; ``` `ekwo.login_identity (login name primary key, user_id uuid)` is writable by the owner only. Why I think this avoids a special case per tool: - The CLI keeps doing exactly what it does now (set the claims, `set local role authenticated`). `session_user` stays the login it connected as, and on the self-hosted path that's the owner. - The self-hosted MCP server already acts as one fixed user (`actAsUserId`). If its `dbUrl` is a separate login, that's one `login_identity` row naming the same user, and whatever it declares on top can't move it anywhere else. - Your reporting/BI login gets no row, so `ekwo.uid()` is null and every policy fails closed. With a row it's that one member. That's what SECURITY.md currently tells people to approximate with grants. Costs and caveats: - It's a sweep. I count about 127 `auth.uid()` calls in the migrations, so it would be one migration that recreates the policies and guards. Write it as `(select ekwo.uid())` so it runs once per statement, and add a test that fails on any bare `auth.uid()` in a policy or guard so it can't creep back. `auth.jwt()` and `auth.role()` need the same treatment wherever a guard reads them. - Don't hard-code `'authenticator'` if self-hosted PostgREST might log in under another name. Keep the list in an owner-only table, not a setting, for the same reason as your point 2. - On Supabase, other services may also set claims and switch to `authenticated` under their own logins (I believe Storage does, for its object policies). If any of your policies get evaluated through one of them, that login belongs on the trusted list. Check which `session_user` actually shows up before you flip it on. If a non-owner tool ever has to act for arbitrary members, the other route is a signed claim: the tool presents a token HMAC'd with a secret that lives in an owner-only table, and a definer function verifies it. HMAC is just two `sha256()` calls over padded keys, so it works on PGlite without pgcrypto too. It's more moving parts, though, and the `session_user` gate covers the cases you described.

Update from me (I built NO SUS), 29 Sep 2026. A few things in the post above are out of date, and I can't edit the post body from here, so correcting them in this comment: - **The source isn't public anymore.** NO SUS is closed source now, so the "Source (MIT...)" link at the bottom 404s and there's no repo to point at. The nosus.foo link still works if you want to try a burn note. - **The pairing code is now on by default for every single note or file**, not an option. That means the "default path" above isn't key-free on the server anymore. The direct link still carries the key in the URL fragment, but while the two-digit code is valid (20 minutes by default) the key is stored server-side too, and it's swept within about 10 minutes after the code is used or expires. Multi-file shares are still link-only with no code, so for those the server only ever holds ciphertext. Burn links also use app.nosus.foo now. - Minor: Go approval on the phone is the phone's screen lock, not specifically fingerprint or face. Both questions still stand, and the concurrent-redeem one matters more now that every single share goes through the code path.

Read through the migrations. Keeping every rule in functions and triggers so `service_role` can't walk around it is a good call, and the fresh-session test from 20260918140000 catches a NULL bug most schemas never find. Some notes from the audit-trail side, since that's where an auditor will push: **1. The purge exception is a flag, not the function.** `audit_log_is_append_only()` lets any DELETE through when `ekwo.audit_purge = 'on'`, and the date check plus the `audit_log_purged` record only exist inside `purge_audit_log()`. So the owner on a direct connection can `set local ekwo.audit_purge = 'on'` and delete any rows, with no cutoff and no trace (or just `alter table audit_log disable trigger ...`). That's normal Postgres, but the comment "table owner included" promises more than a trigger can deliver. "Clients can't rewrite history" is the accurate claim. **2. `ekwo.*` settings can be set by any session.** Custom placeholder GUCs have no privileges, so any role can `SET ekwo.installing = 'on'` / `ekwo.year_end_entry = 'on'`. PostgREST can't reach that, so the Supabase path is fine. In the "any Postgres, direct connection" mode, though, a second login role (a reporting user, a BI tool, a restricted bookkeeper role) has `auth.uid()` null and no `ekwo.api_key`, so one SET makes `is_installer()` true for it. Two ways to close it: - gate the installer on membership too: `pg_has_role(session_user, 'ekwo_installer', 'member')` - `ekwo.year_end_entry` is harder, since `close_fiscal_year()` / `opening_balance()` are invoker functions and the guard can't tell who set the flag. One option: make them `security definer` and have `entries_guard_kind()` also require `current_user` to be the owning role. Inside a definer function `current_user` is the owner, and triggers fired from there see that; a direct caller sees their own role. The trade-off is that they then run past RLS, so their own capability checks become the only gate. **3. If you want tamper-evidence and not only tamper-resistance, hash-chain `audit_log`.** - Add `prev_hash` / `row_hash bytea`. In `audit_record()`, take a per-company lock (`pg_advisory_xact_lock(hashtextextended(coalesce(company_id::text, ''), 0))`, or `for update` on a one-row-per-company head table), read the last hash and store `sha256(prev_hash || canonical_bytes)`. Without the lock, two concurrent transactions read the same head and fork the chain. - Build `canonical_bytes` from an explicit column list, not the whole row, so adding a column later doesn't change old hashes. Leave `id` out: your archive import redraws identity values, so a chain over `id` would break on every restore. You've already solved the jsonb trailing-zero problem for `values_sha256`, and the same canonical form works here. - Add `verify_audit_chain(company_id)`, and put the head hash somewhere the operator can't rewrite: the fiscal-year close output, the archive manifest plus a copy kept off the box. Anyone who edits the rows can recompute a chain; what they can't do is match a head that already left the database. - `sha256(bytea)` has been in core since PG 11, so it runs on PGlite without pgcrypto.

One thing the answers above don't cover, and it decides whether RLS does anything at all in your setup: which role your backend queries as. If your backend talks to Supabase with the service_role key, or connects to Postgres directly as `postgres`, RLS is bypassed completely. In that setup "RLS as a safety net" is switched on but never evaluated, so a missed check in the backend is still a leak. For RLS to back up your backend, the backend has to query as the user: - With supabase-js on the server: create a client per request with the anon key and the user's access token as the `Authorization: Bearer <jwt>` header (`global.headers` in `createClient`). Queries then run as `authenticated`, and `auth.uid()` is that user. - With a direct Postgres connection: do it per transaction, e.g. `begin; set local role authenticated; select set_config('request.jwt.claims', '<claims json>', true); ...; commit;`. Use `set local`/`is_local = true` so the role and claims can't stick to a pooled connection and apply to the next request. Keep service_role for the few jobs that really need to cross users (webhooks, cron cleanup, admin tools), and keep those code paths small, because they're the part RLS can't catch. Two smaller points: - Even if every request goes through your backend, the Data API is still reachable at your project URL, and the anon key isn't a secret. A table in an exposed schema with RLS off is readable by anyone who has that key. Either enable RLS (no policies = deny for anon/authenticated) or remove the schema from the exposed schemas in the API settings. - RLS isn't limited to one table. Policies can use subqueries or call a `security definer` helper (e.g. `is_member(org_id)`) for cross-table rules. For performance, write `(select auth.uid())` instead of `auth.uid()` so it's evaluated once per statement rather than per row, and index the columns your policies filter on. I ran into the first point building a file-sharing app on Supabase. It's easy to assume the policies are protecting you when the connection never goes through them.

Nice write-up, and shipping naive.sql next to the fix is a good way to show the bug. I read through 02_functions.sql, and I couldn't find a hole in the locking. The thing I'd poke at is abuse rather than races. 1. Holds can be kept alive forever. `stock_extend` is granted to anon and resets `expires_at` to `clock_timestamp() + ttl` with no ceiling. A script that reserves the whole stock once and calls extend every 14 minutes keeps every unit off the shelf indefinitely, and never pays. Calling `stock_reserve` again on the same cart does the same thing, because it deletes and re-inserts that cart's holds with a fresh expiry inside the lock. So a lifetime cap can't live on the hold row. It needs to live on the cart: a small carts table with `first_reserved_at`, and reserve/extend refusing to push `expires_at` past, say, `first_reserved_at + 2 * ttl`. 2. Cart ids are minted by the client, so the number of carts is unbounded. Even with a lifetime cap, one client can open N carts, and each one takes whatever is left. A per-cart quantity cap doesn't help against that. On Supabase, anonymous sign-ins are a decent fix. If the storefront calls `signInAnonymously()`, every shopper has a real `auth.uid()`, and the functions can key the cart on that instead of accepting `p_cart_id` from the caller. That also removes the bearer-token cart id you call out in the README (nothing to guess or leak), and it gives you something to cap, e.g. max units held per uid across all skus. It doesn't stop someone creating lots of anonymous users, but that goes through Auth's rate limits and optional CAPTCHA instead of being free. Neither of these is a correctness bug in what you built, but for limited drops they're usually what goes wrong right after the oversell is fixed. I hit the same shape of problem with single-use links in a file-sharing app I'm building, so this was a useful read.