Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0093_public_org_resolve.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0093_public_org_resolve.sql |
| Original SHA-256 | c4f4d52144e45f11172398a8f7d24dc6eeda9f0700b437984b277830edb193f5 |
| Git blob SHA | e3eedacfd48625b8913435086cf7efd8e66d4dcd |
| Reading-copy SHA-256 | c4f4d52144e45f11172398a8f7d24dc6eeda9f0700b437984b277830edb193f5 |
Recovered source transcript
Download complete source text ↓33 lines · 1.8 KB
-- +goose Up
-- THE PRE TENANT RESOLVE. Turning a public slug into an organization id before any tenant context exists.
--
-- The careers surface in migration 0091 needed one thing before it could do anything else: name the tenant.
-- It did that with a plain query against organizations from the application pool, which returns ZERO ROWS,
-- correctly and by design. organizations carries FORCE ROW LEVEL SECURITY, the application role is
-- NOBYPASSRLS, and a public request has no app.current_org_id to satisfy the policy with. The whole tenancy
-- contract is that no context yields no rows, and that contract does not make an exception for a feature
-- that finds it inconvenient.
--
-- So the resolve crosses the line the SAME way every other pre tenant lookup in this schema does: a SECURITY
-- DEFINER function, narrow enough to be safe.
--
-- IT RETURNS AN IDENTIFIER AND NOTHING ELSE. Not a name, not a status, not a count, not a list. A caller
-- supplies a slug they already knew and learns only whether it corresponds to an active organization, which
-- is the same thing they learn from whether the careers page renders. It cannot be used to enumerate
-- customers, because it answers only about the exact string it was handed.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION public_org_id_by_slug(p_slug text) RETURNS uuid AS $$
SELECT id FROM organizations
WHERE lower(slug) = lower(btrim(p_slug))
AND status = 'active'
LIMIT 1;
$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, pg_temp;
-- +goose StatementEnd
REVOKE ALL ON FUNCTION public_org_id_by_slug(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public_org_id_by_slug(text) TO missionmatch_app;
SELECT mm_mirror_staging_role_privileges();
-- +goose Down
DROP FUNCTION IF EXISTS public_org_id_by_slug(text);