[GIS] PostGIS Topology Requires Admin

postgispostgresqltopology

I am running PostGIS on an Ubuntu server. I am able to add the Topology extension via CREATE EXTENSION, but when I try to use it as anything other than an admin I am denied permission.

I ran GRANT USAGE ON SCHEMA topology TO public, as suggested in this chain. When I run CreateTopology() I get a permissions error like the following:

ERROR: permission denied for sequence topology_id_seq

CONTEXT: PL/pgSQL function topology.createtopology(character varying,integer,double precision,boolean) line 17 at FOR over SELECT rows

I'm guessing this means that I need to monkey with more permissions on the topology schema. However,

  1. That seems an odd design flaw. Shouldn't the Topology extension automatically take care of permissions when it is created, just like the PostGIS extension does for the spatial_ref_sys table?
  2. I'm not sure how far down the permissions rabbit hole I need to go in order to make the extension usable.

Has anyone else encountered this issue?

Here's the output of SELECT postgis_full_version() from the regular user:

"POSTGIS="2.1.5 r13152" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.2, released 2015/02/10" LIBXML="2.9.1" LIBJSON="UNKNOWN" TOPOLOGY RASTER"

Best Answer

The set of SQL commands that were needed for PostGIS 2.2.1:

create extension postgis_topology;
grant usage on schema topology to dbuser;
grant all on all tables in schema topology to dbuser;
grant usage, select on all sequences in schema topology to dbuser;

You need to be the database superuser to execute these queries, for example in Ubuntu:

sudo -u postgres psql dbname -c "create extension postgis_topology;"
sudo -u postgres psql dbname -c "grant usage on schema topology to dbuser;"
sudo -u postgres psql dbname -c "grant all on all tables in schema topology to dbuser;"
sudo -u postgres psql dbname -c "grant usage, select on all sequences in schema topology to dbuser;"
Related Question