I created a new role for a user in the database - 'saultdon':<div><br></div><div><font size="1" face="'courier new', monospace"><b>postgres:~> psql postgres -c 'CREATE ROLE saultdon LOGIN CREATEDB;'</b></font></div>
<div><br></div><div>Then I created a new database for that user and a schema for future tables:</div><div><br></div><div><font face="'courier new', monospace" size="1">postgres:~> createdb -T template_postgis newdb -O saultdon</font></div>
<div><font face="'courier new', monospace" size="1"><br></font></div><div><font face="'courier new', monospace" size="1">From postgres user, newdb has postgis extensions - postgis,postgis_topology installed and reports 'SELECT postgis_full_version();' correctly:</font></div>
<div><div><b><font size="1" face="'courier new', monospace"> postgis_full_version </font></b></div>
<div><b><font size="1" face="'courier new', monospace">-----------------------------------------------------------------------------------------------------------------------------------------------------</font></b></div>
<div><b><font size="1" face="'courier new', monospace"> 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</font></b></div>
<div><b><font size="1" face="'courier new', monospace">(1 row)</font></b></div></div><div><br></div><div>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.</div>
<div><br></div><div><font face="'courier new', monospace" size="1">saultdon:~> psql newdb -c 'SELECT postgis_full_version();'</font></div><div><div><b><font size="1" face="'courier new', monospace"> postgis_full_version </font></b></div>
<div><b><font size="1" face="'courier new', monospace">--------------------------------------------------------------------------------------------------------------------------------------------</font></b></div><div>
<b><font size="1" face="'courier new', monospace"> 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</font></b></div>
<div><b><font size="1" face="'courier new', monospace">(1 row)</font></b></div></div><div><b><font color="#ff0000" size="1" face="'courier new', monospace">NOTICE: Function postgis_topology_scripts_installed() not found. Is topology support enabled and topology.sql installed?</font></b></div>
<div><b><font color="#ff0000" size="1"><br></font></b></div><div><font face="'courier new', monospace" size="1">saultdon:~> psql newdb -c '\dx' #it says it's installed and superuser postgres can access it fine</font></div>
<div><div><b><font size="1" face="'courier new', monospace"> List of installed extensions</font></b></div><div><b><font size="1" face="'courier new', monospace"> Name | Version | Schema | Description </font></b></div>
<div><b><font size="1" face="'courier new', monospace">------------------+---------+------------+---------------------------------------------------------------------</font></b></div><div><b><font size="1" face="'courier new', monospace"> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language</font></b></div>
<div><b><font size="1" face="'courier new', monospace"> postgis | 2.0.0 | public | PostGIS geometry, geography, and raster spatial types and functions</font></b></div><div><b><font size="1" face="'courier new', monospace"> postgis_topology | 2.0.0 | topology | PostGIS topology spatial types and functions</font></b></div>
<div><b><font size="1" face="'courier new', monospace">(3 rows)</font></b></div></div><div><b><font size="1" face="'courier new', monospace"><br></font></b></div><div><font face="arial, helvetica, sans-serif">I can use the user saultdon to create a new database from scratch but not to insert the postgis/postgis_topology extensions:</font></div>
<div><font face="arial, helvetica, sans-serif"><br></font></div><div><div style="font-weight:bold;font-size:x-small;font-family:'courier new',monospace">saultdon:~> createdb test</div><div style="font-weight:bold;font-size:x-small;font-family:'courier new',monospace">
saultdon:~> psql test -c 'CREATE EXTENSION postgis;'</div><div style="font-weight:bold;font-size:x-small;font-family:'courier new',monospace"><font color="#ff0000">ERROR: permission denied to create extension "postgis"</font></div>
<div style="font-weight:bold;font-size:x-small;font-family:'courier new',monospace"><font color="#ff0000">HINT: Must be superuser to create this extension.</font></div><div style="font-weight:bold;font-size:x-small;font-family:'courier new',monospace">
<font color="#ff0000"><br></font></div><div style="font-weight:bold;font-size:x-small;font-family:'courier new',monospace"><font color="#ff0000"><br></font></div><div><font face="arial, helvetica, sans-serif">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.</font></div>
</div><div><font face="arial, helvetica, sans-serif"><br></font></div><div><font face="arial, helvetica, sans-serif">I am using openSUSE 12.1 (gnome) x64 and postgis 2.0/postgresl 9.3.1.</font></div><div><font face="arial, helvetica, sans-serif"><br>
</font></div><div><font face="arial, helvetica, sans-serif">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.</font></div>
<div><font face="arial, helvetica, sans-serif"><br></font></div><div><font face="arial, helvetica, sans-serif"><br></font></div><div><font face="arial, helvetica, sans-serif"><br></font></div><div><font face="arial, helvetica, sans-serif">Donovan</font></div>