[introspection] Postico + SplitGraph seems to misbehave (macOS PostGres client)

[onpaws]

Hi, first time SplitGraph user here. Curious to learn more, thanks for your efforts!

Postico is a macOS-native client (not 100% sure but I believe it's written in Cocoa/ObjC) that some Mac users might prefer.

I noticed when I tried to connect Postico to SplitGraph something about schemas seems to get confused, so per the
section on this topic at https://www.splitgraph.com/connect/post-auth-welcome

I thought I'd open this PR.

Here's the config

initial introspection seems to work:

but then clicking into a table unfortunately is broken:

It'd be nice for future SplitGraph users if it were possible to have their OOBE "just work"

If I can provide more logs/info, let me know. Cheers

[mildbyte]

Hey, thanks for raising the issue and investigating!

I looked at the logs: the issue is our query rewriting that patches out some PostgreSQL views like information_schema / pg_catalog to show a list of datasets instead of tables. Here's the query that fails:

SELECT pg_class.oid, obj_description(pg_class.oid, 'pg_class') AS comment, COALESCE(spcname, (select spcname from pg_tablespace, pg_database where pg_tablespace.oid=dattablespace and datname='ddn')) as tablespace, pg_get_viewdef(pg_class.oid,true) AS viewdef FROM pg_class LEFT JOIN pg_tablespace ON pg_tablespace.oid=reltablespace WHERE pg_class.oid = '"cdc-gov/provisional-covid19-death-counts-by-sex-age-and-9bhg-hcku"."provisional_covid19_death_counts_by_sex_age_and"'::regclass::oid AND relkind='r';
SELECT attnum, attname, atttypid, attnotnull, format_type(atttypid,atttypmod), pg_get_expr(adbin, attrelid, true) AS adsrc, col_description(attrelid, attnum), typcategory, has_column_privilege(attrelid,attnum,'SELECT')
FROM pg_attribute
LEFT JOIN pg_type ON atttypid=pg_type.oid
LEFT JOIN pg_attrdef ON adrelid=attrelid AND adnum=attnum
WHERE attrelid = '"cdc-gov/provisional-covid19-death-counts-by-sex-age-and-9bhg-hcku"."provisional_covid19_death_counts_by_sex_age_and"'::regclass AND NOT attisdropped
ORDER BY attnum;
SELECT pg_class.oid, pg_class.relname, indisunique, indisprimary, indisexclusion, indkey, pg_get_indexdef(indexrelid, 0, true) AS definition, ARRAY(select pg_get_indexdef(indexrelid, attnum, true) FROM pg_attribute WHERE attrelid = indexrelid ORDER BY attnum) AS expressions, obj_description(pg_class.oid, 'pg_class') AS comment, indoption, ARRAY(SELECT pg_collation.collname FROM unnest(indcollation) AS t(colid) LEFT JOIN pg_collation ON pg_collation.oid = colid) AS collations, ARRAY(SELECT pg_opclass.opcname FROM generate_series(0, indnatts-1) AS t(i) LEFT JOIN pg_opclass ON pg_opclass.oid = indclass[i]) AS opclasses, pg_get_expr(indpred,indrelid, true), amname FROM pg_index LEFT JOIN pg_class ON pg_class.oid = indexrelid LEFT JOIN pg_am ON pg_class.relam = pg_am.oid WHERE indrelid = '"cdc-gov/provisional-covid19-death-counts-by-sex-age-and-9bhg-hcku"."provisional_covid19_death_counts_by_sex_age_and"'::regclass ORDER BY pg_class.oid; 
SELECT pg_constraint.oid, conname, contype, conkey, nspname AS fschema, relname AS ftable, confkey AS fkeys, pg_get_expr(conbin, conrelid, true), pg_get_constraintdef(pg_constraint.oid, true), confupdtype, confdeltype, obj_description(pg_constraint.oid, 'pg_constraint'), condeferrable, condeferred, connoinherit 
FROM pg_constraint
LEFT JOIN pg_class ON pg_class.oid = confrelid
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE conrelid = '"cdc-gov/provisional-covid19-death-counts-by-sex-age-and-9bhg-hcku"."provisional_covid19_death_counts_by_sex_age_and"'::regclass;
SELECT pg_type.oid, ARRAY(SELECT enumlabel FROM pg_enum WHERE enumtypid=pg_type.oid ORDER BY enumsortorder)
FROM pg_type
WHERE pg_type.oid IN (SELECT atttypid FROM pg_attribute WHERE attrelid = '"cdc-gov/provisional-covid19-death-counts-by-sex-age-and-9bhg-hcku"."provisional_covid19_death_counts_by_sex_age_and"'::regclass) AND typtype='e'

The key part here is this cast:

'"cdc-gov/provisional-covid19-death-counts-by-sex-age-and-9bhg-hcku"."provisional_covid19_death_counts_by_sex_age_and"'::regclass::oid

This is something our rewriting doesn't pick up on: Postico thinks "cdc-gov/provisional-covid19-death-counts-by-sex-age-and-9bhg-hcku"."provisional_covid19_death_counts_by_sex_age_and" is a real table (as it should, since we fooled it into thinking that) and tries to cast it to get its OID. FWIW, normal queries from Postico that reference these fake tables should still work, but I think the solution here is finding these casts in the syntax tree and rewriting them as well.

[onpaws]

Thanks for the prompt reply! I think I follow what you're saying, well roughly anyway :)
TIL two things:

1. intentionally public facing Postgres servers do exist
2. on the fly Postgres query rewriting is a Thing™
   Item 2 in particular sounds intriguing. I'm curious, if one wanted to learn more might there be any resources you'd recommend beyond the official docs? (Ostensibly it's done to protect the server?)

Until today I had assumed port 5432 was intended to be kept private to 'trusted users' only, because "that's just how it's done" (it's how I've built all of my webapps so far at least).
Now I find myself curious to learn more about how one might properly run enduser-facing/multi-tenant Postgres instances like this. I suppose there all kinds of things to properly quota-ify, in addition to query rewriting. Color me intrigued...