import * as pgwire from '@powersync/service-jpgwire'; import { readFile } from 'node:fs/promises'; import * as lib_postgres from '@powersync/lib-service-postgres'; import { ErrorCode, logger, ServiceAssertionError, ServiceError } from '@powersync/lib-services-framework'; import { PatternResult, qualifiedName, storage } from '@powersync/service-core'; import * as sync_rules from '@powersync/service-sync-rules'; import * as service_types from '@powersync/service-types'; import { ReplicationIdentity } from './PgRelation.js'; import { rquery } from './rquery.js'; export interface ReplicaIdentityResult { replicationColumns: storage.ColumnDescriptor[]; replicationIdentity: ReplicationIdentity; } export async function getPrimaryKeyColumns( db: pgwire.PgClient, relationId: number, mode: 'primary' | 'replident' ): Promise { const indexFlag = mode == 'primary' ? `i.indisprimary` : `i.indisreplident`; const attrRows = await lib_postgres.retriedQuery(db, { statement: `SELECT a.attname as name, a.atttypid as typeid, t.typname as type, a.attnum as attnum FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY (i.indkey) JOIN pg_type t ON a.atttypid = t.oid WHERE i.indrelid = $1::oid AND ${indexFlag} AND a.attnum > 0 ORDER BY a.attnum`, params: [{ value: relationId, type: 'int4' }] }); return attrRows.rows.map((row) => { return { name: row.decodeWithoutCustomTypes(0) as string, typeId: Number(row.decodeWithoutCustomTypes(1)) // source type can be a bigint, so always cast it } satisfies storage.ColumnDescriptor; }); } export async function getAllColumns(db: pgwire.PgClient, relationId: number): Promise { const attrRows = await lib_postgres.retriedQuery(db, { statement: `SELECT a.attname as name, a.atttypid as typeid, t.typname as type, a.attnum as attnum FROM pg_attribute a JOIN pg_type t ON a.atttypid = t.oid WHERE a.attrelid = $1::oid AND attnum > 0 ORDER BY a.attnum`, params: [{ type: 'varchar', value: relationId }] }); return attrRows.rows.map((row) => { return { name: row.decodeWithoutCustomTypes(0) as string, typeId: row.decodeWithoutCustomTypes(1) as number } satisfies storage.ColumnDescriptor; }); } export async function getReplicationIdentityColumns( db: pgwire.PgClient, relationId: number ): Promise { const rows = await lib_postgres.retriedQuery(db, { statement: `SELECT CASE relreplident WHEN 'd' THEN 'default' WHEN 'n' THEN 'nothing' WHEN 'f' THEN 'full' WHEN 'i' THEN 'index' END AS replica_identity FROM pg_class WHERE oid = $1::oid LIMIT 1`, params: [{ type: 'int8', value: relationId }] }); const idType: string = rows.rows[0]?.decodeWithoutCustomTypes(0); if (idType == 'nothing' || idType == null) { return { replicationIdentity: 'nothing', replicationColumns: [] }; } else if (idType == 'full') { return { replicationIdentity: 'full', replicationColumns: await getAllColumns(db, relationId) }; } else if (idType == 'default') { return { replicationIdentity: 'default', replicationColumns: await getPrimaryKeyColumns(db, relationId, 'primary') }; } else if (idType == 'index') { return { replicationIdentity: 'index', replicationColumns: await getPrimaryKeyColumns(db, relationId, 'replident') }; } else { return { replicationIdentity: 'nothing', replicationColumns: [] }; } } export async function checkSourceConfiguration(db: pgwire.PgClient, publicationName: string): Promise { // Check basic config await lib_postgres.retriedQuery(db, await getCheckSourceConfigurationSql()); // Check that publication exists const rs = await lib_postgres.retriedQuery(db, { statement: `SELECT * FROM pg_publication WHERE pubname = $1`, params: [{ type: 'varchar', value: publicationName }] }); const row = pgwire.pgwireRows(rs)[0]; if (row == null) { throw new ServiceError( ErrorCode.PSYNC_S1141, `Publication '${publicationName}' does not exist. Run: \`CREATE PUBLICATION ${publicationName} FOR ALL TABLES\`, or read the documentation for details.` ); } if (row.pubinsert == false || row.pubupdate == false || row.pubdelete == false || row.pubtruncate == false) { throw new ServiceError( ErrorCode.PSYNC_S1142, `Publication '${publicationName}' does not publish all changes. Create a publication using \`WITH (publish = "insert, update, delete, truncate")\` (the default).` ); } if (row.pubviaroot) { throw new ServiceError( ErrorCode.PSYNC_S1143, `'${publicationName}' uses publish_via_partition_root, which is not supported.` ); } } export async function checkTableRls( db: pgwire.PgClient, relationId: number ): Promise<{ canRead: boolean; message?: string }> { const rs = await lib_postgres.retriedQuery(db, { statement: ` WITH user_info AS ( SELECT current_user as username, r.rolsuper, r.rolbypassrls FROM pg_roles r WHERE r.rolname = current_user ) SELECT c.relname as tablename, c.relrowsecurity as rls_enabled, u.username as username, u.rolsuper as is_superuser, u.rolbypassrls as bypasses_rls FROM pg_class c CROSS JOIN user_info u WHERE c.oid = $1::oid; `, params: [{ type: 'int4', value: relationId }] }); const rows = pgwire.pgwireRows<{ rls_enabled: boolean; tablename: string; username: string; is_superuser: boolean; bypasses_rls: boolean; }>(rs); if (rows.length == 0) { // Not expected, since we already got the oid throw new ServiceAssertionError(`Table with OID ${relationId} does not exist.`); } const row = rows[0]; if (row.is_superuser || row.bypasses_rls) { // Bypasses RLS automatically. return { canRead: true }; } if (row.rls_enabled) { // Don't skip, since we _may_ still be able to get results. return { canRead: false, message: `[${ErrorCode.PSYNC_S1145}] Row Level Security is enabled on table "${row.tablename}". To make sure that ${row.username} can read the table, run: 'ALTER ROLE ${row.username} BYPASSRLS'.` }; } return { canRead: true }; } export interface GetDebugTablesInfoOptions { db: pgwire.PgClient; publicationName: string; connectionTag: string; tablePatterns: sync_rules.TablePattern[]; syncRules: sync_rules.SyncConfig; } interface BatchedDebugTableRow { pattern_ord: number; schema_name: string; name: string; relation_id: number | null; replication_identity: ReplicationIdentity | null; replication_id_json: string; select_error: string | null; in_publication: boolean | null; rls_enabled: boolean | null; username: string | null; is_superuser: boolean | null; bypasses_rls: boolean | null; } let getDebugTablesInfoBatchedSqlPromise: Promise | undefined; let checkSourceConfigurationSqlPromise: Promise | undefined; function getDebugTablesInfoBatchedSql(): Promise { return (getDebugTablesInfoBatchedSqlPromise ??= readFile( new URL('../../sql/debug-tables-info-batched.plpgsql', import.meta.url), 'utf8' )); } function getCheckSourceConfigurationSql(): Promise { return (checkSourceConfigurationSqlPromise ??= readFile( new URL('../../sql/check-source-configuration.plpgsql', import.meta.url), 'utf8' )); } /** * Note: This must use a dedicated connection, not a connection pool. */ export async function getDebugTablesInfo(options: GetDebugTablesInfoOptions): Promise { const { db, publicationName, connectionTag, tablePatterns, syncRules } = options; const patternPayload = JSON.stringify( tablePatterns.map((tablePattern, pattern_ord) => ({ pattern_ord, schema_name: tablePattern.schema, table_pattern: tablePattern.tablePattern, is_wildcard: tablePattern.isWildcard, table_prefix: tablePattern.isWildcard ? tablePattern.tablePrefix : '', input_name: tablePattern.isWildcard ? tablePattern.tablePattern : tablePattern.name })) ); let rows: BatchedDebugTableRow[]; await rquery(db, 'BEGIN'); try { // Anonymous DO blocks cannot take bind parameters directly, so pass the // pattern payload through transaction-local settings on the pinned connection. const fetched = await rquery( db, { statement: `SELECT set_config('powersync.debug.table_patterns', $1, true)`, params: [{ type: 'varchar', value: patternPayload }] }, { statement: `SELECT set_config('powersync.debug.publication_name', $1, true)`, params: [{ type: 'varchar', value: publicationName }] }, { statement: await getDebugTablesInfoBatchedSql() }, { statement: `FETCH ALL FROM powersync_debug_tables_cursor` } ); rows = pgwire.pgwireRows(fetched); await rquery(db, 'COMMIT'); } catch (e) { try { await rquery(db, 'ROLLBACK'); } catch { // Ignore rollback errors after a failed transaction. } throw e; } const result: PatternResult[] = tablePatterns.map((tablePattern) => ({ schema: tablePattern.schema, pattern: tablePattern.tablePattern, wildcard: tablePattern.isWildcard, ...(tablePattern.isWildcard ? { tables: [] } : {}) })); for (const row of rows) { const tablePattern = tablePatterns[row.pattern_ord]; const idColumns = JSON.parse(row.replication_id_json) as string[]; const ref: sync_rules.SourceTableRef = { connectionTag, schema: tablePattern.schema, name: row.name }; const syncData = syncRules.tableSyncsData(ref); const syncParameters = syncRules.tableSyncsParameters(ref); let errors: service_types.ReplicationError[]; if (row.relation_id == null) { errors = [{ level: 'warning', message: `Table ${qualifiedName(ref)} not found.` }]; } else { const idColumnsError = idColumns.length == 0 && row.replication_identity != 'nothing' ? { level: 'fatal' as const, message: `No replication id found for ${qualifiedName(ref)}. Replica identity: ${row.replication_identity}.${row.replication_identity == 'default' ? ' Configure a primary key on the table.' : ''}` } : null; const selectError = row.select_error == null ? null : { level: 'fatal' as const, message: row.select_error }; const replicateError = row.in_publication === false ? { level: 'fatal' as const, message: `Table ${qualifiedName(ref)} is not part of publication '${publicationName}'. Run: \`ALTER PUBLICATION ${publicationName} ADD TABLE ${qualifiedName(ref)}\`.` } : null; const rlsError = row.rls_enabled && !row.is_superuser && !row.bypasses_rls && row.username != null ? { level: 'warning' as const, message: `[${ErrorCode.PSYNC_S1145}] Row Level Security is enabled on table "${row.name}". To make sure that ${row.username} can read the table, run: 'ALTER ROLE ${row.username} BYPASSRLS'.` } : null; errors = [idColumnsError, selectError, replicateError, rlsError].filter( (error) => error != null ) as service_types.ReplicationError[]; } const tableInfo: service_types.TableInfo = { schema: tablePattern.schema, name: row.name, pattern: tablePattern.isWildcard ? tablePattern.tablePattern : undefined, replication_id: idColumns, data_queries: syncData, parameter_queries: syncParameters, errors }; if (tablePattern.isWildcard) { result[row.pattern_ord].tables!.push(tableInfo); } else { result[row.pattern_ord].table = tableInfo; } } return result; } export async function cleanUpReplicationSlot(slotName: string, db: pgwire.PgClient): Promise { logger.info(`Cleaning up Postgres replication slot: ${slotName}...`); await rquery(db, { statement: 'SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots WHERE slot_name = $1', params: [{ type: 'varchar', value: slotName }] }); }