Read restriction on geometry_columns is unnecessary

[pramsey]

I see we are being pretty hard-core about preventing leakage from low-level access keys, by, for example disallowing access to pg_* tables that might ordinarily be visible to read users.

However, for geometry_columns this is unnecessary (I think) because the views will only allow users to see tables to which they have read permission. I demonstrated this with a test user on my own database.

CREATE USER test LOGIN;
GRANT SELECT ON foobar TO test;

Then connect as test and run SELECT * FROM geometry_columns. Only the foobar table shows up.

In general though, even this is not quite idea as we really want to allow read users to programatically list every table they have access to, not just every spatial table.

[dgaubert]

Hey Paul!

We forbid the access to pg_* for default_public ™️ users. As you might know, geometry_columns is a view which internally reads from system tables such as pg_namespace, pg_class, etc... That's why you are receiving the error system tables are forbidden. By the way,
if you send your api_key the geometry_column is available.

This check was implemented for historiacal reasons that I don't know (maybe @rochoa can shed some light here). We can implement an exception for the geometry_column view, but I want to understand better what is your use case:

we really want to allow read users to programatically list every table they have access to, not just every spatial table.

Could you describe a bit more your needs about this?

[pramsey]

Yes, geometry_columns is a view that reads the system tables, but it's a view that includes a check on the privs of the caller and only shows to the user tables that they are authorized to read.

https://github.com/postgis/postgis/blob/9abf29439391b54c1a8fb08e675931a11589eb2d/postgis/postgis.sql.in#L5630

The use case if for OGR, which likes to read a list of "available layers" from a data source. Previously it did that via calls to the system tables. This still works for the master API key, but it does not for restricted read keys, which causes failures in the driver. Right now ogr2ogr is basically broken if you want to use it with a non-master api key.

[dgaubert]

Oh! I see. Thanks for the explanation.

Added to engine's backlog.