sql-storage-adapter v0.8.3

Added

• PostgresExporterOptions.unsetEnvironment names libpq variables left out of pg_dump's environment. The exporter of a PostgreSQL adapter leaves out PGSSLNEGOTIATION.

Security

• PostgresExporter refuses, before pg_dump starts, a connection string whose password it cannot move to PGPASSWORD: a URI password that is not valid percent-encoding (%zz, a % at its end, bytes that are not UTF-8) or that decodes to a NUL, and a connection string that holds a NUL. Versions through 0.8.2 passed such a URI whole, its password in --dbname, where the process list shows it. The refusal never holds the connection string.
• PostgresExporter moves the password of keyword=value settings (host=db.example password='a b') to PGPASSWORD too, read as libpq reads them: a value runs to whitespace or sits in single quotes, a backslash takes the character after it, and the later of two passwords wins. An empty password goes to PGPASSWORD empty, as libpq reads an empty setting, so the environment's PGPASSWORD is never sent in its place. Settings libpq refuses (an unterminated quote, a setting with no =) are refused before pg_dump starts. Versions through 0.8.2 passed them whole on the command line.
• The exporter of a PostgreSQL adapter leaves sslpassword, oauth_client_secret and the SCRAM keys of a pool's connection string off pg_dump's command line, where libpq takes them alone: pg reads none of them.
• The exporter of a PostgreSQL adapter has pg_dump check the server as the pool's pg does. pg's TLS is Node's, which checks the server's certificate and its host name, and versions through 0.8.2 wrote sslmode=require for it, with which libpq checks neither without a root certificate file: an export connected to a server whose certificate the pool refuses. pg_dump gets sslmode=verify-full, or verify-ca where pg checks no host name (uselibpqcompat=true with verify-ca, or require beside a root certificate), against the root certificate file the connection string names, else the file PGSSLROOTCERT names, else the system's certificate authorities (sslrootcert=system, which libpq reads from PostgreSQL 16 on; with an older libpq the export fails, saying what to do). uselibpqcompat=true with sslmode=verify-ca and no root certificate is refused before pg_dump starts, as pg refuses it.

Changed

• The release workflow publishes on every push to master that changes a file the package ships: the newest published version with its patch raised by one, or package.json's version when that is higher, written into the run's checkout alone (scripts/release-version.mjs, RELEASING.md). Through 0.8.2 it published only a package.json version npm did not hold, so the fixes merged after 0.8.2 went unpublished.
• new PostgresExporter(connectionString) takes a function that gives the connection string, or a promise of it, when an export runs.
• createDatabase() on Node treats only a postgres:// or postgresql:// URL or connection string as naming a PostgreSQL server, besides a pool, host, database or user. A URL of another scheme (file:./dev.db, which DATABASE_URL holds in a project that runs SQLite through Prisma, or sqlite:...) opens the local default again, as through 0.6.13, and with strict fails with an error that says the option names no server. From 0.6.14 through 0.8.2 such a URL chose ['postgres'] with no fallback and failed at start. To open PostgreSQL from a socket: URL or a socket path, pass type: 'postgres' or use connectDatabase().
• connectDatabase() opens PostgreSQL alone, in every runtime, from any connection string pg reads, and refuses a configuration that names no server (no pool, an empty or unset connection string, and no host, database or user, as connectDatabase(process.env.DATABASE_URL) gives with the variable unset): it throws an error that names the missing setting before it opens anything. Versions through 0.8.2 opened the SQLite file db_data/app.sqlite3 under the working directory on Node when given no server, though the function is documented as connecting to a remote database, and in a browser opened IndexedDB whatever they were given. createSyncManager() calls it only with a URL, as before.
• One Capacitor adapter at a time holds a database: open() fails while another adapter of the page has the same database open (notes and notes.db are one database to the plugin), and close() closes the plugin's connection too.

Fixed

• createStorageFeatures(adapter).exporter on PostgreSQL reads the connection of the adapter's pool as pg reads it, and hands pg_dump a libpq URI of it, so pg_dump reaches the server, the database and the user the pool reaches:
  • A connection string pg and libpq read apart: postgresql://db.example?application_name=x@y names the host db.example to pg and y to libpq, and pg_dump connected to y. An @ in a URL's password, a host left out, ssl=1, sslmode=no-verify and pg's own parameters are written as libpq reads them.
  • A pool made without a host, a port, a user or a database: PGHOST, PGPORT, PGUSER and PGDATABASE, then localhost, 5432, the operating system's user and the user's name for the database, as pg fills them. Versions through 0.8.2 wrote localhost and 5432 for a pool that named no host or port, and refused one that named no database. The adapter's own settings keep the localhost and 5432 of the pool it makes.
  • A pool whose password is a function: the function is asked when the export runs, as the pool asks it for each connection: through the Client the pool makes its connections with, which pg-pool keeps as the pool's Client, whose own routine calls it as a method of a client made of the pool's settings, with no arguments before pg 8.19.0 and from 8.19.0 with the client's ConnectionParameters (node-postgres #3602). So a pool made with another copy of pg than the one this library loads, or with a Client of its own, is asked as its own pg asks; a pool whose Client has no such routine (pg's native one) is asked as the pg this library loads asks. The client is never connected and is made without the settings' stream. Its answer goes to PGPASSWORD. A function that answers no password gives pg_dump an empty PGPASSWORD and a passfile that does not exist: pg then connects with no password and reads neither PGPASSWORD nor a password file, so neither the environment's password nor ~/.pgpass, which libpq reads for an empty password, is sent. A function that fails, or answers anything but a string or undefined, refuses the export. Versions through 0.8.2 left the function's password out, so pg_dump logged in with PGPASSWORD, or not at all.
  • A pool whose ssl holds ca, cert, key, pfx or another TLS option is refused, naming the options and what to do instead: pg_dump reads TLS material from files alone. Versions through 0.8.2 wrote sslmode=require in its place, so pg_dump connected without the client certificate, or checked the server against no certificate authority.
  • TLS follows pg's. A pool that checks no certificate (ssl: { rejectUnauthorized: false }, pg's no-verify) gets sslmode=require over any PGSSLMODE: a PGSSLMODE of verify-ca or verify-full refused the certificate pg accepts. A pool that chose no TLS (ssl: false, ssl=0, sslmode=disable) gets sslmode=disable, where libpq's prefer or allow would use TLS. Where no TLS is pg's default, libpq's default stays, and an empty PGSSLMODE or one libpq does not know, which pg reads as no TLS and libpq refuses, gives way to sslmode=disable. A certificate file in the connection string turns TLS on over ssl=0, and an sslmode libpq does not know means TLS, as pg reads them.
  • pg takes the database from the URL's path alone and drops a database parameter, and reads an empty host parameter as none; the URI holds that reading (pg-connection-string, index.js). libpq's own variables, which pg does not read (PGHOSTADDR, PGSERVICE, PGREQUIRESSL, PGSSLROOTCERT), configure pg_dump as they configure psql.
  • The port is read as pg reads it, with parseInt after the setting, PGPORT and 5432: a port of 5432x is 5432, which libpq refused as no number, and a port of 0 gives way to PGPORT, where versions through 0.8.2 wrote 0. A port pg reads no number in is refused before pg_dump starts, since the pool makes no connection with it.
  • Of a connection string's parameters the URI holds the ones pg reads and libpq takes: options, application_name, fallback_application_name, client_encoding, replication and the certificate files.
  • sslnegotiation reaches pg_dump as the pool's own pg reads it. pg reads it from 8.22.0 (the connection string's over the settings', an empty one as none, then PGSSLNEGOTIATION) and with direct starts TLS at once; pg 8.13.1 to 8.21.x read none and start TLS with an SSLRequest. The exporter reads it on a client of the pool's Client, never connected, so a pool whose pg reads none gives pg_dump none, and TLS for direct only where that pg's parse of the connection string turns TLS on for it (pg-connection-string 2.14.0 and later). pg_dump runs without PGSSLNEGOTIATION, which libpq 17 and later read: the URI holds what the pool's pg reads. A value pg refuses (one but direct or postgres, or direct without TLS) refuses the export, and where pg_dump's libpq is older than 17, which does not know sslnegotiation, the error says to run a pg_dump of PostgreSQL 17 or later. Versions through 0.8.2 handed pg_dump the string's sslnegotiation and turned TLS on for direct whatever the pool's pg read, so an export failed against a server the pool reached: one that offers no TLS, or one older than PostgreSQL 17, which takes no direct TLS. libpq's others, which pg does not read (passfile, target_session_attrs, require_auth, channel_binding, gssencmode, connect_timeout, sslcrl and the rest), are left out: given them, pg_dump read another password file than the pool, or refused a server, an authentication or a session the pool takes.
• resolveStorageAdapter() and createDatabase() open a PostgreSQL candidate in the segment of its own options or the top-level one alone. Tried after an IndexedDB or Capacitor candidate whose options named a segment, it took that segment: on a pool of the caller's the adapter threw, and on a connection string the server got a schema only the browser's options asked for. A local candidate still opens in it.
• IndexedDB adapter: a PRAGMA assignment through all() or get() with a comment between its words (PRAGMA user_version /* note */ = 7), with a quoted name and no space before it (PRAGMA"user_version"=9), or with its schema or name in single quotes (PRAGMA 'user_version' = 10), which SQLite takes for a name there, is saved; the statement is read as SQLite reads it. Versions through 0.8.2 found no assignment in it, so it was stored neither with durability: 'commit' nor by flush() or close().
• createSqlKeyValueStore() writes a value's text from the copy the runtime's structuredClone returns, so each property is read once, as IndexedDB's put reads it, and the value is copied once. Versions through 0.8.2 read each property a second time for the text: a getter's second answer was stored, and a value whose getter answered a function the second time was refused.
• PostgresExporter runs pg_dump with --no-password. Without a password, to a server that asks for one, pg_dump asked for it on the terminal, or with no terminal read its standard input, which the export leaves open, and the export never ended.
• Capacitor adapter: open() goes through the plugin's SQLiteConnection. Versions through 0.8.2 called createConnection(name, ...) on the native plugin, whose methods take one options object, and the plugin refused the call ("CreateConnection: Must provide a database name"). close() closes the plugin's connection, so the database opens again, where a second open() failed with "Connection ... already exists". A database whose connection the plugin holds for something else (another part of the app opened it, or a page before a reload did) is refused with an error that names the database: the adapter closes no connection it did not open, since closing it would end a transaction its holder has open. Two open() calls at once open once, and two close() calls at once close once, each resolving once the plugin's connection is closed: the second resolved at once, and a caller that awaited it alone was refused when it opened the database again while the first close was still running.
• Capacitor adapter: enableWal turns WAL on. PRAGMA journal_mode = WAL went through execute(), which the plugin runs inside a transaction of its own, where SQLite refuses to change the journal mode, and on Android through SQLCipher's execSQL, which fails on a statement that answers a row. It goes through the plugin's query() now, and the adapter warns when SQLite answers another mode.
• Capacitor adapter: a PRAGMA in run(), or at the start or the end of an exec() script, runs through query(), outside the plugin's own transaction. Inside it SQLite ignored PRAGMA foreign_keys, so a script that turned foreign keys off, rebuilt a parent table and turned them on again deleted the child rows through ON DELETE CASCADE.
• Capacitor adapter: foreignKeys is set while open() runs, and a failure there closes the plugin's connection and fails open(), leaving the adapter closed. Versions through 0.8.2 left the adapter open without the setting, and the next open() returned at once.
• sql.js adapter with a file: a write through all() or get() (INSERT ... RETURNING, PRAGMA user_version = 3) is saved as a run() is, by the classifier the IndexedDB adapter saves by (src/shared/queryWrites.ts, one module for both); versions through 0.8.2 waited for a later write. The statements a script ran before the one that failed are saved, and so are the rows a statement changed before it failed (INSERT OR FAIL, UPDATE OR FAIL), which SQLite keeps.
• sql.js adapter with a file: the adapter asks SQLite whether a transaction is open before it saves, instead of counting BEGIN, COMMIT and the like in run() and exec(). A save exports the database, which closes the connection and rolls an open transaction back, and the count missed transactions: after exec('BEGIN; INSERT ...') the save rolled the script's writes back and its COMMIT failed with "no transaction is active"; after a script that ended a transaction run('BEGIN') began (INSERT ...; COMMIT), or after close() with a transaction left open, no write was saved until the next close(); and after ROLLBACK TO a savepoint, the save ended the transaction the savepoint keeps open. close() rolls a transaction left open back before it saves, and two close() calls at once close once, where the second threw a TypeError.
• sql.js and IndexedDB adapters: a save keeps the connection's settings. sql.js's export(), which every save, exportDatabase() and importDatabase() run, closes the connection and opens it again, which resets every PRAGMA. The adapters read the settings before the export and set them again after, whether open() set them or a statement did, through one module (src/shared/sqlJsConnectionSettings.ts). On sql.js, versions through 0.8.2 reset a PRAGMA a statement set (PRAGMA foreign_keys = ON) at the next save, and foreignKeys from open() overrode a later PRAGMA foreign_keys = OFF. On IndexedDB, secure_delete = FAST came back as ON: it reads back as 2, which versions through 0.8.2 set again as a number, and SQLite reads a number there as a boolean; and the journal mode was not kept. TEMP tables, attached databases and functions registered on the sql.js database itself do not survive a save, an export or an import; the adapters warn once when a save drops TEMP objects.
• sql.js and IndexedDB adapters: PRAGMA default_cache_size = N through all() or get() is saved. The deprecated pragma writes the suggested cache size into the database header (offset 48) and changes no row, so versions through 0.8.2 found no write in it on IndexedDB: it was stored neither with durability: 'commit' nor by flush() or close().
• IndexedDB adapter: importDatabase() checks the image with SQLite's quick_check, which reads every page, after the SQLite header, before it replaces anything. Versions through 0.8.2 read the schema alone, so an image with a damaged page past the schema replaced the database and was saved over the stored one. An import waits for a save already running before its own, so that older image is never the last stored.
• The documentation said that Capacitor leaves foreign keys off by default. The plugin turns them on when it opens a database, on Android, iOS and Electron, so Capacitor enforces them unless foreignKeys: false is given.
• examples/remote-postgres.ts connected with createDatabase() and no options, which opens the local default, not DATABASE_URL; it calls connectDatabase(). The capability matrix in ARCHITECTURE.md lists what each adapter's capabilities holds, and its Capacitor configuration takes the options the adapter has.