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

[Paragon Corporation]

It might have to do with search_paths.  the CREATE EXTENSION
postgis_topology 
adds topology to the search path for you, but this is lost if you create
from template.
 
 


  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Donovan
Cameron
Sent: Sunday, May 06, 2012 3:37 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Do I have to be a superuser to use
postgisextensions?


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/20120507/d069def8/attachment.html>