-- Least-privilege grants for certificate automation credentials. Existing
-- credentials are migrated to reload-only access for all manual certificate ids;
-- rotating them in 0.28.2 lets operators narrow them to explicit ids.
CREATE TABLE IF NOT EXISTS certificate_api_grants (
    key_id TEXT PRIMARY KEY REFERENCES certificate_api_credentials(key_id) ON DELETE CASCADE,
    allow_deploy INTEGER NOT NULL DEFAULT 0 CHECK (allow_deploy IN (0, 1)),
    certificate_ids TEXT NOT NULL DEFAULT '["*"]' CHECK (length(certificate_ids) <= 4096)
);

INSERT OR IGNORE INTO certificate_api_grants(key_id, allow_deploy, certificate_ids)
SELECT key_id, 0, '["*"]' FROM certificate_api_credentials;