5

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"
3

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;"
1

You need to also grant privileges for the table's sequence too:

GRANT USAGE, SELECT ON SEQUENCE topology_id_seq TO public;

Or if you want to provide liberal privileges for all sequences:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO public;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.