35 lines
2.3 KiB
JavaScript
35 lines
2.3 KiB
JavaScript
import { PrismaClient } from '@prisma/client';
|
||
|
||
const password = process.env.APP_DATABASE_PASSWORD;
|
||
if (!password || password.length < 24 || password.length > 256) {
|
||
throw new Error('Set APP_DATABASE_PASSWORD to a dedicated password of 24–256 characters.');
|
||
}
|
||
const prisma = new PrismaClient();
|
||
async function main() {
|
||
const existing = await prisma.$queryRaw`SELECT 1 FROM pg_roles WHERE rolname = 'kita_runtime'`;
|
||
if (!existing.length) await prisma.$executeRawUnsafe('CREATE ROLE kita_runtime LOGIN');
|
||
const owned = await prisma.$queryRaw`SELECT 1 FROM pg_class c JOIN pg_roles r ON c.relowner = r.oid WHERE r.rolname = 'kita_runtime'
|
||
UNION ALL SELECT 1 FROM pg_namespace n JOIN pg_roles r ON n.nspowner = r.oid WHERE r.rolname = 'kita_runtime'`;
|
||
if (owned.length) throw new Error('Runtime role must not own tables or schemas.');
|
||
const memberships = await prisma.$queryRaw`SELECT quote_ident(r.rolname) AS role FROM pg_auth_members m JOIN pg_roles r ON m.roleid = r.oid
|
||
JOIN pg_roles member ON m.member = member.oid WHERE member.rolname = 'kita_runtime'`;
|
||
for (const membership of memberships) await prisma.$executeRawUnsafe(`REVOKE ${membership.role} FROM kita_runtime`);
|
||
// PostgreSQL quotes the secret. Only this server-produced SQL is executed.
|
||
const [quoted] = await prisma.$queryRaw`SELECT format('ALTER ROLE kita_runtime PASSWORD %L LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOBYPASSRLS NOREPLICATION', ${password}) AS statement`;
|
||
await prisma.$executeRawUnsafe(quoted.statement);
|
||
for (const statement of [
|
||
'REVOKE CREATE ON SCHEMA public FROM PUBLIC',
|
||
'GRANT USAGE ON SCHEMA public TO kita_runtime',
|
||
'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO kita_runtime',
|
||
'GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO kita_runtime',
|
||
'REVOKE ALL ON TABLE _prisma_migrations FROM kita_runtime',
|
||
'REVOKE UPDATE, DELETE, TRUNCATE ON TABLE audit_logs FROM kita_runtime',
|
||
]) await prisma.$executeRawUnsafe(statement);
|
||
console.log('Runtime database grants configured. Re-run after future migrations.');
|
||
}
|
||
main().finally(() => prisma.$disconnect()).catch(error => {
|
||
// Avoid putting a statement containing credentials into deployment logs.
|
||
console.error('Runtime role provisioning failed:', error?.code ?? error?.name ?? 'unknown');
|
||
process.exitCode = 1;
|
||
});
|