205 lines
16 KiB
PHP
205 lines
16 KiB
PHP
<?php
|
|
|
|
use Illuminate\Contracts\Console\Kernel;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
// Logical recovery into a newly initialized, private QA cluster. Never repair shared catalogs in place.
|
|
require dirname(__DIR__).'/vendor/autoload.php';
|
|
require __DIR__.'/portal-qa-environment.php';
|
|
$target = portalQaEnvironment();
|
|
$app = require dirname(__DIR__).'/bootstrap/app.php';
|
|
$app->make(Kernel::class)->bootstrap();
|
|
set_exception_handler(static function (Throwable $error): void {
|
|
$diagnostic = json_encode(['at' => gmdate('c'), 'mode' => $GLOBALS['argv'][1] ?? 'capture', 'class' => get_class($error), 'message' => $error->getMessage()], JSON_PRETTY_PRINT);
|
|
$private = dirname(__DIR__).'/storage/app/private/investmoz-v2/portal-audit/';
|
|
file_put_contents($private.'cluster-recovery-error-'.gmdate('Ymd_His').'-'.bin2hex(random_bytes(4)).'-private.json', $diagnostic);
|
|
file_put_contents($private.'cluster-recovery-error-private.json', $diagnostic);
|
|
fwrite(STDERR, 'QA recovery stopped; details private, active environment untouched.'.PHP_EOL);
|
|
exit(1);
|
|
});
|
|
function recoveryQuote(string $name): string
|
|
{
|
|
return '"'.str_replace('"', '""', $name).'"';
|
|
}
|
|
function recoverySchema(PDO $pdo): array
|
|
{
|
|
$filter = "n.nspname NOT LIKE 'pg_%' AND n.nspname <> 'information_schema'";
|
|
$queries = [
|
|
'namespaces' => "SELECT n.nspname,pg_get_userbyid(n.nspowner) AS owner,n.nspacl::text AS acl FROM pg_namespace n WHERE $filter ORDER BY 1",
|
|
'relations' => "SELECT n.nspname,c.relname,c.relkind,pg_get_userbyid(c.relowner) AS owner,c.relacl::text AS acl,c.relrowsecurity,c.relforcerowsecurity,c.reloptions FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE $filter AND c.relkind IN ('r','p','v','m','S','f') ORDER BY 1,2",
|
|
'columns' => "SELECT n.nspname,c.relname,a.attnum,a.attname,format_type(a.atttypid,a.atttypmod) AS type,a.attnotnull,a.attidentity,a.attgenerated,pg_get_expr(d.adbin,d.adrelid) AS expression FROM pg_attribute a JOIN pg_class c ON c.oid=a.attrelid JOIN pg_namespace n ON n.oid=c.relnamespace LEFT JOIN pg_attrdef d ON d.adrelid=a.attrelid AND d.adnum=a.attnum WHERE $filter AND a.attnum>0 AND NOT a.attisdropped AND c.relkind IN ('r','p','v','m','f') ORDER BY 1,2,3",
|
|
'constraints' => "SELECT n.nspname,c.relname,k.conname,pg_get_constraintdef(k.oid,true) AS definition,k.convalidated FROM pg_constraint k JOIN pg_namespace n ON n.oid=k.connamespace LEFT JOIN pg_class c ON c.oid=k.conrelid WHERE $filter ORDER BY 1,2,3",
|
|
'indexes' => "SELECT n.nspname,c.relname,pg_get_indexdef(c.oid) AS definition FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE $filter AND c.relkind IN ('i','I') ORDER BY 1,2",
|
|
'functions' => "SELECT n.nspname,p.proname,pg_get_function_identity_arguments(p.oid) AS arguments,pg_get_functiondef(p.oid) AS definition,pg_get_userbyid(p.proowner) AS owner,p.proacl::text AS acl FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace WHERE $filter AND p.prokind IN ('f','p') ORDER BY 1,2,3",
|
|
'triggers' => "SELECT n.nspname,c.relname,t.tgname,pg_get_triggerdef(t.oid,true) AS definition,t.tgenabled FROM pg_trigger t JOIN pg_class c ON c.oid=t.tgrelid JOIN pg_namespace n ON n.oid=c.relnamespace WHERE $filter AND NOT t.tgisinternal ORDER BY 1,2,3",
|
|
'views' => "SELECT n.nspname,c.relname,pg_get_viewdef(c.oid,true) AS definition FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE $filter AND c.relkind IN ('v','m') ORDER BY 1,2",
|
|
'enums' => "SELECT n.nspname,t.typname,e.enumsortorder,e.enumlabel FROM pg_enum e JOIN pg_type t ON t.oid=e.enumtypid JOIN pg_namespace n ON n.oid=t.typnamespace WHERE $filter ORDER BY 1,2,3",
|
|
'sequences' => "SELECT n.nspname,c.relname,format_type(s.seqtypid,NULL) AS type,s.seqstart,s.seqincrement,s.seqmax,s.seqmin,s.seqcache,s.seqcycle FROM pg_sequence s JOIN pg_class c ON c.oid=s.seqrelid JOIN pg_namespace n ON n.oid=c.relnamespace WHERE $filter ORDER BY 1,2",
|
|
'extensions' => 'SELECT extname,extversion FROM pg_extension ORDER BY 1',
|
|
];
|
|
|
|
return array_map(fn ($sql) => $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC), $queries);
|
|
}
|
|
function recoveryCompare(PDO $pdo, string $directory, array $manifest): array
|
|
{
|
|
$pdo->exec('BEGIN READ ONLY');
|
|
try {
|
|
$schema = recoverySchema($pdo);
|
|
$canonical = static function (array $value): array {
|
|
// Dropped column slots are physical storage history, not column ordering/contracts.
|
|
foreach ($value['columns'] as &$column) {
|
|
unset($column['attnum']);
|
|
}
|
|
unset($column);
|
|
foreach ($value['constraints'] as &$constraint) {
|
|
if (in_array($constraint['conname'], ['portal_request_status', 'portal_role_name'], true)
|
|
&& in_array($constraint['relname'], ['portal_profile_requests', 'portal_role_assignments'], true)) {
|
|
// pg_restore reparses the equivalent varchar-array-to-text coercion.
|
|
$constraint['definition'] = str_replace(['::character varying::text', '::character varying', ']::text[]'], ['::text', '::text', ']'], $constraint['definition']);
|
|
}
|
|
}
|
|
unset($constraint);
|
|
|
|
return $value;
|
|
};
|
|
if ($canonical($schema) !== $canonical($manifest['schema'])) {
|
|
file_put_contents($directory.'/restored-schema-private.json', json_encode($schema, JSON_PRETTY_PRINT));
|
|
throw new RuntimeException('Schema/ownership/constraints differ; refuse cutover.');
|
|
}
|
|
$total = 0;
|
|
foreach ($manifest['tables'] as $name => $table) {
|
|
$expected = file_get_contents($directory.'/'.$table['file']);
|
|
throw_unless(hash('sha256', $expected) === $table['sha256'], RuntimeException::class, 'Preservation bytes changed.');
|
|
$actual = implode("\n", $pdo->query('SELECT row_to_json(t)::text FROM '.$name.' t ORDER BY row_to_json(t)::text')->fetchAll(PDO::FETCH_COLUMN));
|
|
if ($table['rows'] > 0) {
|
|
$actual .= "\n";
|
|
}
|
|
throw_unless($actual === $expected, RuntimeException::class, 'Recovered table bytes differ: '.$name);
|
|
$total += $table['rows'];
|
|
}
|
|
foreach ($manifest['sequences'] as $name => $expected) {
|
|
throw_unless($pdo->query('SELECT last_value,is_called FROM '.$name)->fetch(PDO::FETCH_ASSOC) === $expected,
|
|
RuntimeException::class, 'Sequence differs.');
|
|
}
|
|
|
|
return ['tables' => count($manifest['tables']), 'rows' => $total, 'sequences' => count($manifest['sequences']), 'schema_equal' => true, 'all_rows_equal' => true];
|
|
} finally {
|
|
$pdo->exec('ROLLBACK');
|
|
}
|
|
}
|
|
$mode = $argv[1] ?? 'capture';
|
|
$base = storage_path('app/private/investmoz-v2/portal-qa-recovery');
|
|
if ($mode === 'capture') {
|
|
portalQaAssertTarget($target);
|
|
$source = DB::connection()->getPdo();
|
|
$directory = $base.'/'.gmdate('Ymd_His');
|
|
mkdir($directory, 0700, true);
|
|
$source->exec('BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY');
|
|
$identity = $source->query('SELECT current_database() AS database,inet_server_port() AS port,current_setting(\'data_directory\') AS data_directory')->fetch(PDO::FETCH_ASSOC);
|
|
$manifest = ['captured_at' => gmdate('c'), 'target' => $identity, 'schema' => recoverySchema($source), 'tables' => [], 'sequences' => [],
|
|
'transaction_read_only' => true, 'active_access' => false, 'key_rotated' => false];
|
|
foreach ($source->query("SELECT schemaname,tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema') ORDER BY 1,2")->fetchAll(PDO::FETCH_ASSOC) as $table) {
|
|
$name = recoveryQuote($table['schemaname']).'.'.recoveryQuote($table['tablename']);
|
|
$file = sprintf('%04d.ndjson', count($manifest['tables']) + 1);
|
|
$bytes = implode("\n", $source->query('SELECT row_to_json(t)::text FROM '.$name.' t ORDER BY row_to_json(t)::text')->fetchAll(PDO::FETCH_COLUMN));
|
|
$count = $bytes === '' ? 0 : substr_count($bytes, "\n") + 1;
|
|
if ($count > 0) {
|
|
$bytes .= "\n";
|
|
}
|
|
file_put_contents($directory.'/'.$file, $bytes);
|
|
$manifest['tables'][$name] = ['schema' => $table['schemaname'], 'name' => $table['tablename'], 'file' => $file, 'rows' => $count, 'sha256' => hash('sha256', $bytes)];
|
|
}
|
|
foreach ($manifest['schema']['sequences'] as $sequence) {
|
|
$name = recoveryQuote($sequence['nspname']).'.'.recoveryQuote($sequence['relname']);
|
|
$manifest['sequences'][$name] = $source->query('SELECT last_value,is_called FROM '.$name)->fetch(PDO::FETCH_ASSOC);
|
|
}
|
|
$roles = $source->query('SELECT * FROM pg_authid ORDER BY rolname')->fetchAll(PDO::FETCH_ASSOC);
|
|
$members = $source->query('SELECT r.rolname AS role,m.rolname AS member,a.admin_option,a.inherit_option,a.set_option FROM pg_auth_members a JOIN pg_authid r ON r.oid=a.roleid JOIN pg_authid m ON m.oid=a.member ORDER BY 1,2')->fetchAll(PDO::FETCH_ASSOC);
|
|
file_put_contents($directory.'/roles-private.json', json_encode(['roles' => $roles, 'members' => $members], JSON_PRETTY_PRINT | JSON_THROW_ON_ERROR));
|
|
$manifest['database_options'] = $source->query('SELECT datcollate,datctype,pg_encoding_to_char(encoding) AS encoding,datlocprovider FROM pg_database WHERE datname=current_database()')->fetch(PDO::FETCH_ASSOC);
|
|
$source->exec('ROLLBACK');
|
|
file_put_contents($directory.'/manifest.json', json_encode($manifest, JSON_PRETTY_PRINT | JSON_THROW_ON_ERROR));
|
|
file_put_contents($base.'/current-capture-private.json', json_encode(['directory' => $directory], JSON_THROW_ON_ERROR));
|
|
echo json_encode(['directory' => $directory, 'tables' => count($manifest['tables']), 'rows' => array_sum(array_column($manifest['tables'], 'rows')), 'schema_captured' => true, 'source_writes' => 0]).PHP_EOL;
|
|
exit;
|
|
}
|
|
$directory = json_decode(file_get_contents($base.'/current-capture-private.json'), true, 512, JSON_THROW_ON_ERROR)['directory'];
|
|
throw_unless(str_starts_with(strtolower(realpath($directory)), strtolower(realpath($base).DIRECTORY_SEPARATOR)), RuntimeException::class, 'Recovery path refused.');
|
|
$manifest = json_decode(file_get_contents($directory.'/manifest.json'), true, 512, JSON_THROW_ON_ERROR);
|
|
if ($mode === 'roles') {
|
|
$maintenance = new PDO('pgsql:host=127.0.0.1;port=55442;dbname=postgres', 'postgres', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
|
|
$newIdentity = $maintenance->query('SELECT inet_server_port() AS port,current_setting(\'data_directory\') AS data_directory')->fetch(PDO::FETCH_ASSOC);
|
|
throw_unless((int) $newIdentity['port'] === 55442 && str_contains(str_replace('\\', '/', $newIdentity['data_directory']), '/.config/investmoz/qa-postgres-recovery/'), RuntimeException::class, 'New QA cluster required for roles.');
|
|
$saved = json_decode(file_get_contents($directory.'/roles-private.json'), true, 512, JSON_THROW_ON_ERROR);
|
|
$known = $maintenance->query('SELECT rolname FROM pg_authid')->fetchAll(PDO::FETCH_COLUMN);
|
|
foreach ($saved['roles'] as $role) {
|
|
if (str_starts_with($role['rolname'], 'pg_')) {
|
|
continue;
|
|
}
|
|
$name = recoveryQuote($role['rolname']);
|
|
if (! in_array($role['rolname'], $known, true)) {
|
|
$maintenance->exec('CREATE ROLE '.$name);
|
|
}
|
|
$options = [];
|
|
foreach (['rolsuper' => 'SUPERUSER', 'rolinherit' => 'INHERIT', 'rolcreaterole' => 'CREATEROLE', 'rolcreatedb' => 'CREATEDB', 'rolcanlogin' => 'LOGIN', 'rolreplication' => 'REPLICATION', 'rolbypassrls' => 'BYPASSRLS'] as $key => $option) {
|
|
$options[] = ($role[$key] ? '' : 'NO').$option;
|
|
}
|
|
$options[] = 'CONNECTION LIMIT '.(int) $role['rolconnlimit'];
|
|
$options[] = 'PASSWORD '.($role['rolpassword'] === null ? 'NULL' : $maintenance->quote($role['rolpassword']));
|
|
$options[] = 'VALID UNTIL '.$maintenance->quote($role['rolvaliduntil'] ?? 'infinity');
|
|
$maintenance->exec('ALTER ROLE '.$name.' '.implode(' ', $options));
|
|
}
|
|
foreach ($saved['members'] as $member) {
|
|
$maintenance->exec('GRANT '.recoveryQuote($member['role']).' TO '.recoveryQuote($member['member']).' WITH ADMIN '.($member['admin_option'] ? 'TRUE' : 'FALSE')
|
|
.', INHERIT '.($member['inherit_option'] ? 'TRUE' : 'FALSE').', SET '.($member['set_option'] ? 'TRUE' : 'FALSE'));
|
|
}
|
|
echo json_encode(['roles_preserved' => count($saved['roles']), 'memberships_preserved' => count($saved['members']), 'port' => 55442, 'active_access' => false]).PHP_EOL;
|
|
exit;
|
|
}
|
|
$port = $mode === 'verify-live' ? 55441 : 55442;
|
|
$pdo = new PDO('pgsql:host=127.0.0.1;port='.$port.';dbname='.$target['database'], 'postgres', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
|
|
$identity = $pdo->query('SELECT current_database() AS database,inet_server_port() AS port,current_setting(\'data_directory\') AS data_directory')->fetch(PDO::FETCH_ASSOC);
|
|
throw_unless((int) $identity['port'] === $port && $identity['database'] === $target['database']
|
|
&& str_contains(str_replace('\\', '/', $identity['data_directory']), '/.config/investmoz/qa-postgres-recovery/'), RuntimeException::class, 'New isolated cluster required.');
|
|
if ($mode === 'load') {
|
|
throw_unless((int) $pdo->query('SELECT count(*) FROM public.users')->fetchColumn() === 0, RuntimeException::class, 'Fresh empty destination required.');
|
|
$pdo->beginTransaction();
|
|
// Restore existing rows without firing application triggers or creating duplicate audit events.
|
|
// Foreign keys are recreated/validated below; row bytes and trigger definitions are compared.
|
|
$pdo->exec("SET LOCAL session_replication_role = 'replica'");
|
|
$foreignKeys = $pdo->query("SELECT n.nspname,c.relname,k.conname,pg_get_constraintdef(k.oid) AS definition FROM pg_constraint k JOIN pg_class c ON c.oid=k.conrelid JOIN pg_namespace n ON n.oid=c.relnamespace WHERE k.contype='f' AND n.nspname NOT LIKE 'pg_%' ORDER BY 1,2,3")->fetchAll(PDO::FETCH_ASSOC);
|
|
foreach ($foreignKeys as $fk) {
|
|
$pdo->exec('ALTER TABLE '.recoveryQuote($fk['nspname']).'.'.recoveryQuote($fk['relname']).' DROP CONSTRAINT '.recoveryQuote($fk['conname']));
|
|
}
|
|
// Extension creation seeds spatial_ref_sys; replace those seed rows with the exact captured rows.
|
|
$pdo->exec('TRUNCATE TABLE '.implode(',', array_keys($manifest['tables'])));
|
|
foreach ($manifest['tables'] as $name => $table) {
|
|
$columns = array_values(array_filter($manifest['schema']['columns'], fn ($col) => $col['nspname'] === $table['schema'] && $col['relname'] === $table['name'] && $col['attgenerated'] === ''));
|
|
$list = implode(',', array_map(fn ($col) => recoveryQuote($col['attname']), $columns));
|
|
$query = $pdo->prepare('INSERT INTO '.$name.' ('.$list.') OVERRIDING SYSTEM VALUE SELECT '.$list.' FROM json_populate_record(NULL::'.$name.',CAST(? AS json))');
|
|
$bytes = file_get_contents($directory.'/'.$table['file']);
|
|
throw_unless(hash('sha256', $bytes) === $table['sha256'], RuntimeException::class, 'Recovery bytes changed.');
|
|
foreach (explode("\n", rtrim($bytes, "\n")) as $line) {
|
|
if ($line !== '') {
|
|
$query->execute([$line]);
|
|
}
|
|
}
|
|
}
|
|
foreach ($manifest['sequences'] as $name => $sequence) {
|
|
$query = $pdo->prepare('SELECT setval(CAST(? AS regclass),CAST(? AS bigint),CAST(? AS boolean))');
|
|
$query->execute([$name, $sequence['last_value'], $sequence['is_called'] ? 'true' : 'false']);
|
|
}
|
|
$pdo->exec("SET LOCAL session_replication_role = 'origin'");
|
|
foreach ($foreignKeys as $fk) {
|
|
$pdo->exec('ALTER TABLE '.recoveryQuote($fk['nspname']).'.'.recoveryQuote($fk['relname']).' ADD CONSTRAINT '.recoveryQuote($fk['conname']).' '.$fk['definition']);
|
|
}
|
|
$pdo->commit();
|
|
echo json_encode(['loaded_tables' => count($manifest['tables']), 'port' => $port, 'active_access' => false]).PHP_EOL;
|
|
} elseif (in_array($mode, ['verify', 'verify-live'], true)) {
|
|
$proof = recoveryCompare($pdo, $directory, $manifest) + ['verified_at' => gmdate('c'), 'target' => $identity, 'active_writes' => 0, 'key_rotated' => false];
|
|
file_put_contents($directory.'/'.$mode.'-proof.json', json_encode($proof, JSON_PRETTY_PRINT | JSON_THROW_ON_ERROR));
|
|
echo json_encode($proof).PHP_EOL;
|
|
} else {
|
|
throw new RuntimeException('Unknown recovery operation.');
|
|
}
|