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

[Donovan Cameron]

Been playing further and found that the only way I can create a database
for another user is not from the template_postgis because any user loses
access to the topology schema for some reason but from a scratch database.

This is even when I create the new database from the postgres user with:
*createdb -T template_postgis -O saultdon newdb*
*#user saultdon can not access topology schema*
*psql newdb -c 'GRANT USAGE ON SCHEMA topology TO PUBLIC;'*
*#user saultdon can not access topology schema*
*dropdb newdb*

Below is what works.

As postgres user:
*createdb newdb -O saultdon*
*psql newdb -c 'CREATE EXTENSION postgis;'*
*psql newdb -c 'CREATE EXTENSION postgis_topology;'*
*psql newdb -c 'GRANT USAGE ON SCHEMA topology to PUBLIC;'*

That is the only way I can get a postgis enabled database for a
non-postgres user.

Even though the GRANT USAGE has been used on template_postgis and my user
saultdon can access the template_postgis and use 'SELECT
postgis_full_version();' on it successfully, the user saultdon can create a
new database from template_postgis but will lose access to the topology
schema within the new db...

Maybe this is a bug of some sort but not sure where to point it out.
Or is there something I can suggest to the package maintainers for openSUSE
to set some defaults like:
1.) create a template_postgis database by default
  a.) *GRANT USAGE ON SCHEMA topology TO PUBLIC;* and,
  b.) *update pg_database set datistemplate = true where datname =
'template_postgis';*
*
*

Donovan

On Sun, May 6, 2012 at 11:58 AM, Donovan Cameron <sault.don at gmail.com>wrote:

> I also read from some instructions for something unrelated [1] that this
> function also works, as postgres user:
> *su - postgres*
> *psql*
> *update pg_database set datistemplate = true where datname =
> 'template_postgis';*
> *\q*
> *exit*
>
> That looks like it is setting a configuration parameter in the pg_database
> listing that flags the template_postgis database as a template allowing it
> to be used in the same manner as template1.
>
> So I read further and found it on pg *1675* or* **Table 45-15.
> pg_database Columns *in the 9.1 Postgresql manual.
> This seems alot more appropriate than to use ALTER or GRANT which are
> irrelevant for what I am trying to do.
>
> So I tried it and it now allows non-postgres users to use template_postgis
> as a template in case any other openSUSE users need to know.
>
> But now the weird part... Any new databases I created from
> template_postgis doesn't have topology enabled again! But my first database
> does for some reason. The first database that is working was created from
> the postgres user that simply set the flag '-O saultdon' for the createdb
> command. The second database is created from the user saultdon from the
> template_postgis because I can copy it now.
>
> So I tried again to create the database as postgres:
> *postgres:~> createdb -O saultdon -T template_postgis newdb*
> *
> *
> Again, only postgres user can use topolgy schema and user saultdon cannot!
>
> I don't really understand what's happening because now the saultdon user
> can't use 'CREATE EXTENSION postgis;' on any databases created using just:
> createdb newdb where a template is not used. Gives me the same - must be
> superuser - error.
>
> This is even after I've run the 'GRANT USAGE ON SCHEMA topology TO PUBLIC;'
> And restarted the postgresql server.
>
> Donovan
>
>
> [1] http://code.grical.org/browser/INSTALL.TXT?rev=417%3A8ccbd40c0aba#L169
>
>
> On Sun, May 6, 2012 at 11:30 AM, Donovan Cameron <sault.don at gmail.com>wrote:
>
>> Using the GRANT sql worked. I read more about it in the postgresql
>> manual. Thanks.
>>
>> GRANT USAGE ON SCHEMA topology TO PUBLIC;
>>
>> Then It never fully took effect till I restarted the server,
>> *su -*
>> *rcpostgresql restart*
>> *exit*
>>
>> Done.
>>
>> Now, that part about giving access to PUBLIC on the template_postgis
>> database.
>> This is because without it, a non-superuser cannot create a database of
>> their own when using:
>> *createdb -T template_postgis newdb*
>> *createdb: database creation failed: ERROR:  permission denied to copy
>> database "template_postgis"*
>>
>> I tried using the similar GRANT for databases but it doesn't mention
>> anything about allowing users to copy. Only connect, so I tried that:
>> *GRANT CONNECT ON DATABASE template_postgis TO PUBLIC;*
>>
>> But still, cannot create a database without being postgres user.
>> Not sure if I need to maybe make user of ALTER like so:
>> *ALTER DATABASE template_postgis OWNER TO PUBLIC;*
>> Or try,
>> *ALTER ROLE saultdon CREATEDB IN DATABASE template_postgis;*
>>
>> Does one of those give me copy permissions because CONNECT hasn't.
>>
>> Thanks for the help so far with changing schema privileges.
>>
>>
>>
>>
>> Donovan
>>
>> On Sun, May 6, 2012 at 1:31 AM, Sandro Santilli <strk at keybit.net> wrote:
>>
>>> On Sat, May 05, 2012 at 10:58:24PM -0700, Donovan Cameron wrote:
>>>
>>> > 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.
>>> ...
>>> > *NOTICE:  Function postgis_topology_scripts_installed() not found. Is
>>> > topology support enabled and topology.sql installed?*
>>>
>>> Try
>>> GRANT usage on schema topology to public;
>>>
>>> Or variations of the above. When satisfied consider doing it on the
>>> template database.
>>>
>>> --strk;
>>>
>>>  ,------o-.
>>>  |   __/  |    Delivering high quality PostGIS 2.0 !
>>>  |  / 2.0 |    http://strk.keybit.net - http://vizzuality.com
>>>  `-o------'
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120506/c01ced03/attachment.html>