Internal __pgsqlite_* tables leak into client sqlite_master queries

[robertjbass]

Internal __pgsqlite_* bookkeeping tables leak into client sqlite_master queries

Version: 0.0.22 (reproduced on 0.0.19 as well)

Summary

pgsqlite stores its own bookkeeping in tables named __pgsqlite_*
(__pgsqlite_schema, __pgsqlite_migrations, __pgsqlite_enum_types,
__pgsqlite_string_constraints, __pgsqlite_numeric_constraints,
__pgsqlite_datetime_cache, etc.). The catalog queries pgsqlite synthesizes
itself already hide these, e.g. src/catalog/pg_class.rs:

db.query("SELECT name FROM sqlite_master WHERE type='table' \
          AND name NOT LIKE 'sqlite_%' AND name NOT LIKE '__pgsqlite_%'")

But a sqlite_master (or its sqlite_schema alias) query written directly by
a client is passed through verbatim, so any tool that lists the native SQLite
catalog sees pgsqlite's internal tables mixed in with the user's own.

Reproduce

pgsqlite --database /tmp/t.sqlite --port 5599 &
psql "host=127.0.0.1 port=5599 user=postgres dbname=main gssencmode=disable" \
  -c "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);"

psql "host=127.0.0.1 port=5599 user=postgres dbname=main gssencmode=disable" \
  -tA -c "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"

Observed output includes pgsqlite's internal tables:

__pgsqlite_array_types
__pgsqlite_datetime_cache
__pgsqlite_enum_types
__pgsqlite_migrations
__pgsqlite_schema
__pgsqlite_string_constraints
...
customers

Expected

A client sqlite_master / sqlite_schema query should not expose pgsqlite's
internal __pgsqlite_* bookkeeping tables, consistent with the filtering that
pgsqlite's own synthesized catalog queries already apply.

Proposed fix

Intercept client sqlite_master / sqlite_schema SELECTs and AND
name NOT LIKE '__pgsqlite_%' into the WHERE clause before execution, failing
open (running the query unchanged) whenever it cannot be parsed as a single
SELECT over a real sqlite_master relation. A PR implementing this is attached.

Related observation (out of scope for the attached PR)

Separately from the __pgsqlite_* tables, pgsqlite materializes its
pg_catalog / information_schema emulation as real SQLite tables named
pg_class, pg_type, pg_attribute, information_schema_tables,
information_schema_columns, ... (plus supporting idx_* /
sqlite_autoindex_* entries). These also appear in a client sqlite_master
listing and are the same relations that surface through
information_schema.tables. Filtering those is a broader policy decision (a
user could in principle name a table pg_*), so the attached PR deliberately
scopes itself to the unambiguous __pgsqlite_* prefix only. Flagging it here
for visibility.

[robertjbass]

@erans I'm the founder of Layerbase and I use pgsqlite for our serverless SQLite3 implementation. I have a very hacky solution in place that I will use if this is not accepted although I'd really love to be able to continue using pgsqlite instead of a fork or a minimal replication written in Go that I have on deck.

No worries if this doesn't fit the vision for what you're looking for, I'd just really appreciate a response so I know if I should abandon this angle. I've had several users get confused by the internal tables and think their data had been corrupted. Thank you for the consideration.

[erans]

Interesting. I guess the best way would be to add a flag that if turned on, will hide these tables - that might fit to your usage