GRANT select on topology metadata tables to public

[strk]

We do this already for geography_columns, geometry_columns and spatial_ref_sys so I don't see why we shouldn't do for topology.topology and topology.layers as well.

NOTE: lack of this was the source of this QGIS regression: ​https://github.com/qgis/QGIS/issues/32726

[strk]

Related ticket (raster): #4576

[robe]

Oh wait I remember why we did it on just those. Not sure the reason is that important though.

For geography_columns and geometry_columns — they are both views, so what you can see in them is already dictated by visibility of the table to the user.

That said if a user can't see X table, that X table doesn't show in geography/geometry columns views either.

Topology is a real table, so there is some concern there.

spatial_ref_sys wasn't of much concern because it's largely static table that anybody can see by looking at postgis source code anyway.

[robe]

+0.5 here — While there is some risk in that people can see topologies they don't have rights to, I feel like not much data is listed anyway so making it publically readable outweighs the risk.

[strk]

I filed #4682 for turning topology.topology and topology.layer into views

[pramsey]

Seems like the view approach invalidates this.

[strk]

The view approach doesn't have a clear solution at the moment (due to topology identifiers)

[Lars Aksel Opsahl]

When compiling a new cluster from scratch public still seems to miss access to the topology schema.

So I have to run 'GRANT usage ON SCHEMA topology TO PUBLIC'

Could this part of the install script ?

[strk]

Should PUBLIC be also given permission to increment topology_id_seq sequence ? It would be *needed* for anyone who's intended to have permission to create topologies.

[strk]

merge request for review: ​https://gitlab.com/postgis/postgis/-/merge_requests/54

Only grants SELECT on the metadata tables, but it isn't of much use on itself. We could add USAGE as mentioned by Lars, but it would still be a read-only usage unless we also allow incrementing sequences (safe thing to do for PUBLIC?).

[Lars Aksel Opsahl]

The most important is select because it's a lot of used cases where we only need select.

But it is strange that a user can create a table with geometry column and and not topology column, seen from a user perspective so I think you should allow public access to increment sequence.

By the way why are these tables located in the topology schema and not in public as for instance public.geometry_columns is ?

[strk]

I'm thinking as an alternative we could make the CreateTopology function a SECURITY DEFINER and have the code check if the user has permission to create schemas (as creating a topology at the moment means creating a schema…).

Everything is located under topology schema for cleanness (I'd love to also see "postgis" under a "postgis" schema, but PostGIS predates the time when PostgreSQL added support for schemas…)

[Sandro Santilli <strk@…>]

In 3ff851a/git:

GRANT select on topology.topology and topology.layer to PUBLIC

Closes #4575

[strk]

USAGE on schema was left out, I filed #5124 to include that as well