69 lines
3.6 KiB
SQL
69 lines
3.6 KiB
SQL
|
-- ════════════════════════════════════════════════════════════════════════════
|
||
|
|
-- ReBAC Phase 2: grant-level expiry + dead permission column cleanup
|
||
|
|
-- ════════════════════════════════════════════════════════════════════════════
|
||
|
|
-- This migration:
|
||
|
|
-- 1. Adds expires_at (TIMESTAMPTZ) to access_grants — uniform expiry for
|
||
|
|
-- all subject types (token, user, future external).
|
||
|
|
-- 2. Migrates existing token expiry from storage.shares.expires_at.
|
||
|
|
-- 3. Backfills any Read grants missing for shares created after the
|
||
|
|
-- initial migration (safety net — idempotent via NOT EXISTS).
|
||
|
|
-- 4. Adds two performance indexes (expires_at partial, granted_by).
|
||
|
|
-- 5. Drops the now-dead permission and expiry columns from storage.shares.
|
||
|
|
-- storage.shares becomes token-only metadata: id, token, password_hash,
|
||
|
|
-- access_count, created_at, created_by, item_id, item_type, item_name.
|
||
|
|
--
|
||
|
|
-- Conceptual model: a share token is an authentication principal, not a
|
||
|
|
-- permission type. Access = having a non-expired Read grant in access_grants
|
||
|
|
-- for Subject::Token(share.id). Tokens are always read-only by definition.
|
||
|
|
|
||
|
|
-- ── 1. Add expires_at ────────────────────────────────────────────────────────
|
||
|
|
ALTER TABLE storage.access_grants
|
||
|
|
ADD COLUMN IF NOT EXISTS expires_at TIMESTAMPTZ;
|
||
|
|
|
||
|
|
-- ── 2. Migrate token expiry (shares.expires_at is BIGINT unix seconds) ───────
|
||
|
|
UPDATE storage.access_grants ag
|
||
|
|
SET expires_at = to_timestamp(s.expires_at)
|
||
|
|
FROM storage.shares s
|
||
|
|
WHERE ag.subject_type = 'token'
|
||
|
|
AND ag.subject_id = s.id
|
||
|
|
AND s.expires_at IS NOT NULL;
|
||
|
|
|
||
|
|
-- ── 3. Backfill Read grants for shares that missed the initial migration ──────
|
||
|
|
INSERT INTO storage.access_grants
|
||
|
|
(subject_type, subject_id, resource_type, resource_id, permission, granted_by, granted_at)
|
||
|
|
SELECT
|
||
|
|
'token',
|
||
|
|
s.id,
|
||
|
|
s.item_type,
|
||
|
|
s.item_id::UUID,
|
||
|
|
'read',
|
||
|
|
s.created_by,
|
||
|
|
to_timestamp(s.created_at)
|
||
|
|
FROM storage.shares s
|
||
|
|
WHERE s.permissions_read
|
||
|
|
AND NOT EXISTS (
|
||
|
|
SELECT 1 FROM storage.access_grants ag
|
||
|
|
WHERE ag.subject_type = 'token'
|
||
|
|
AND ag.subject_id = s.id
|
||
|
|
AND ag.permission = 'read'
|
||
|
|
)
|
||
|
|
ON CONFLICT DO NOTHING;
|
||
|
|
|
||
|
|
-- ── 4. Performance indexes ───────────────────────────────────────────────────
|
||
|
|
-- Partial index for expiry checks (only rows that actually expire)
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_grants_expires_at
|
||
|
|
ON storage.access_grants (expires_at) WHERE expires_at IS NOT NULL;
|
||
|
|
|
||
|
|
-- Needed for GET /api/grants/outgoing/resources (currently missing)
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_grants_granted_by
|
||
|
|
ON storage.access_grants (granted_by);
|
||
|
|
|
||
|
|
-- ── 5. Drop dead columns from storage.shares ─────────────────────────────────
|
||
|
|
-- Permissions were never enforced (no public write endpoints, frontend
|
||
|
|
-- hard-codes write=false/reshare=false). Expiry is now in access_grants.
|
||
|
|
ALTER TABLE storage.shares
|
||
|
|
DROP COLUMN IF EXISTS permissions_read,
|
||
|
|
DROP COLUMN IF EXISTS permissions_write,
|
||
|
|
DROP COLUMN IF EXISTS permissions_reshare,
|
||
|
|
DROP COLUMN IF EXISTS expires_at;
|