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

[Donovan Cameron]

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/5f977a4b/attachment.html>