[postgis-users] Do I have to be a superuser to use postgis extensions?

[Donovan Cameron]

I created a new role for a user in the database - 'saultdon':

*postgres:~> psql postgres -c 'CREATE ROLE saultdon LOGIN CREATEDB;'*

Then I created a new database for that user and a schema for future tables:

postgres:~> createdb -T template_postgis newdb -O saultdon

>From postgres user, newdb has postgis extensions - postgis,postgis_topology
installed and reports 'SELECT postgis_full_version();' correctly:
*
postgis_full_version
          *
*
-----------------------------------------------------------------------------------------------------------------------------------------------------
*
* POSTGIS="2.0.0 r9605" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.8.0, 6 March
2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" TOPOLOGY RASTER*
*(1 row)*

When I to check the postgis version with the user saultdon, it says
postgis_topology not installed. I have to be superuser to use it.

saultdon:~> psql newdb -c 'SELECT postgis_full_version();'
*
postgis_full_version
     *
*
--------------------------------------------------------------------------------------------------------------------------------------------
*
* POSTGIS="2.0.0 r9605" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.8.0, 6 March
2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" RASTER*
*(1 row)*
*NOTICE:  Function postgis_topology_scripts_installed() not found. Is
topology support enabled and topology.sql installed?*
*
*
saultdon:~> psql newdb -c '\dx' #it says it's installed and superuser
postgres can access it fine
*                                         List of installed extensions*
*       Name       | Version |   Schema   |
Description                             *
*
------------------+---------+------------+---------------------------------------------------------------------
*
* plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language*
* postgis          | 2.0.0   | public     | PostGIS geometry, geography,
and raster spatial types and functions*
* postgis_topology | 2.0.0   | topology   | PostGIS topology spatial types
and functions*
*(3 rows)*
*
*
I can use the user saultdon to create a new database from scratch but not
to insert the postgis/postgis_topology extensions:

saultdon:~> createdb test
saultdon:~> psql test -c 'CREATE EXTENSION postgis;'
ERROR:  permission denied to create extension "postgis"
HINT:  Must be superuser to create this extension.


I'm fairly new to postgis/postgresql as a spatial data storage format. Do I
have to alter the saultdon role somehow to allow it to access the postgis
extensions. They seemed to be accessible exclusively from the superuser
postgres.

I am using openSUSE 12.1 (gnome) x64 and postgis 2.0/postgresl 9.3.1.

Thanks for any help or suggestions. I'm sure this is an elementary error
and I have scoured the postgresql and postgis manuals on what to do about
having to create a superuser just to use postgis without any luck.



Donovan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120505/13443ef8/attachment.html>