julius.g (3) [Avatar] Offline
#1
It would've saved me much time if it was explicitly stated what role (postgres superuser) was required when installing the topology extension and how to get the non-super-user role to use the extension.

I don't know if anyone else ran into errors (especially related to permissions) when trying to create and use the topology extension in chapter 13, but I'll write down how I got mine to work and save some time for someone else. Since I'm new to postgres and postgis, what I'm doing might not be the right approach, but it's how I could use the topology extension without being the postgres super user.

The one sentence (page 312 in PDF) that related roles/permissions to this extension is:
In some cases, the role you’re logged in as may have its own custom search_path setting that will override the database’s search_path. Before you continue, verify that topology is part of your search_path by running this SQL statement:...


What I found out and set up was the following (via pgAdmin4):
1. login/connect as the postgres superuser. Then install the topology extension using the command in the book (page 312 in PDF)

2. As the postgres non-super-user, check that topology is my search_path (page 312 in PDF), which it was. But I couldn't use it - I got "permission denied"
2.a. In pgAdmin4, the output of SELECT postgis_full_version() also has a tab "Messages." In it, there's this
NOTICE: Topology support cannot be inspected. Is current user granted USAGE on schema "topology" ?

3. After googling and checking stackoverflow (https://gis.stackexchange.com/questions/152236/postgis-topology-requires-admin), I ended up logging in as the postgres superuser and granting permissions on the topology schema to the non-super-user.
3.a. in pgAdmin4, expand 'postgis_in_action' database -> schemas -> topology.
3.b. Right click "properties" -> select tab "security" -> add the non-super-user, in this case 'pia_user' -> grant all -> Then save[/tt].

4. Verify that non-super-user 'pia_user' can execute select * from topology without encountering errors related to permissions.

P.S. Here's the output of SELECT postgis_full_version() from the non-super-user pia_user:
"POSTGIS="2.3.7 r16523" PGSQL="96" GEOS="3.6.2-CAPI-1.10.2 4d2925d" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.4, released 2018/03/19" LIBXML="2.7.8" LIBJSON="0.12" RASTER"

Best regards,
Julius
regina.leo (265) [Avatar] Offline
#2
Thanks for the feedback. We debated a bit about how much we should get into that as its a bit of a detour and most users starting out are testing on their local deskop server usually with one account. We should have at least mentioned the issue of permissions and maybe put in the appendix permission issues and things like connecting externally.

Permissions stuff and pg_hba conf iis something we cover more extensively in our other book - PostgreSQL: Up and Running.
julius.g (3) [Avatar] Offline
#3
Thank you for the book recommendation - once I get through this one, and need more know-how on PostgresSQL, I'll go for it. My interest so far has been on maps, GIS, and I'll probably hoard your other books on these topics smilie

You're right - explaining permissions in detail would've been a detour.

A short paragraph - maybe one or two sentences - would still help. Maybe stating the one account that you'd expect most of the readers would use; that it's a postgres superuser; and that using a non-superuser might involve additional configuration steps to set the permissions correctly. Then if I (or another reader) decide to use a non-superuser and have to look up these extra steps, it will feel as though we're still being guided by the book/authors.

Best regards,
Julius