<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 9.00.8112.16443"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=724522710-07052012><FONT color=#0000ff
size=2 face=Arial>It might have to do with search_paths. the CREATE
EXTENSION postgis_topology </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=724522710-07052012><FONT color=#0000ff
size=2 face=Arial>adds topology to the search path for you, but this is lost if
you create from template.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=724522710-07052012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=724522710-07052012><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<BLOCKQUOTE
style="BORDER-LEFT: #0000ff 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px"
dir=ltr>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Donovan Cameron<BR><B>Sent:</B> Sunday, May 06, 2012 3:37 PM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Do I have to
be a superuser to use postgisextensions?<BR></FONT><BR></DIV>
<DIV></DIV>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.
<DIV><BR></DIV>
<DIV>This is even when I create the new database from the postgres user
with: </DIV>
<DIV><B>createdb -T template_postgis -O saultdon newdb</B></DIV>
<DIV><B><I>#user saultdon can not access topology schema</I></B></DIV>
<DIV><B>psql newdb -c 'GRANT USAGE ON SCHEMA topology TO PUBLIC;'</B></DIV>
<DIV>
<DIV>
<DIV><B><I>#user saultdon can not access topology schema</I></B></DIV></DIV>
<DIV><B>dropdb newdb</B></DIV>
<DIV><BR></DIV>
<DIV>Below is what works.</DIV>
<DIV><BR></DIV>
<DIV>As postgres user:</DIV>
<DIV><B>createdb newdb -O saultdon</B></DIV>
<DIV><B>psql newdb -c 'CREATE EXTENSION postgis;'</B></DIV>
<DIV><B>psql newdb -c 'CREATE EXTENSION postgis_topology;'</B></DIV>
<DIV><B>psql newdb -c 'GRANT USAGE ON SCHEMA topology to PUBLIC;'</B></DIV>
<DIV><BR></DIV>
<DIV>That is the only way I can get a postgis enabled database for a
non-postgres user.</DIV>
<DIV><BR></DIV>
<DIV>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...</DIV>
<DIV><BR></DIV>
<DIV>Maybe this is a bug of some sort but not sure where to point it
out.</DIV>
<DIV>Or is there something I can suggest to the package maintainers for
openSUSE to set some defaults like:</DIV>
<DIV>1.) create a template_postgis database by default</DIV>
<DIV> a.) <B>GRANT USAGE ON SCHEMA topology TO PUBLIC;</B> and,</DIV>
<DIV> b.) <B style="FONT-FAMILY: arial,helvetica,sans-serif">update
pg_database set datistemplate = true where datname =
'template_postgis';</B></DIV>
<DIV><B style="FONT-FAMILY: arial,helvetica,sans-serif"><BR></B></DIV>
<DIV><BR></DIV>
<DIV>Donovan<BR><BR>
<DIV class=gmail_quote>On Sun, May 6, 2012 at 11:58 AM, Donovan Cameron <SPAN
dir=ltr><<A href="mailto:sault.don@gmail.com"
target=_blank>sault.don@gmail.com</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>I also read from some instructions for something unrelated
[1] that this function also works, as postgres user:
<DIV><B>su - postgres</B><BR>
<DIV><B>psql</B></DIV>
<DIV><SPAN><FONT face="arial, helvetica, sans-serif"><B>update pg_database
set datistemplate = true where datname =
'template_postgis';</B></FONT></SPAN></DIV>
<DIV><FONT face="arial, helvetica, sans-serif"><B>\q</B></FONT></DIV>
<DIV><FONT face="arial, helvetica, sans-serif"><B>exit</B></FONT></DIV>
<DIV><FONT face="arial, helvetica, sans-serif"><BR></FONT></DIV>
<DIV><FONT face="arial, helvetica, sans-serif">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.</FONT></DIV>
<DIV><FONT face="arial, helvetica, sans-serif"><BR></FONT></DIV>
<DIV><FONT face="arial, helvetica, sans-serif">So I read further and found
it on pg <I>1675</I> or<I> </I></FONT><I>Table 45-15. pg_database
Columns </I>in the 9.1 Postgresql manual.</DIV>
<DIV>This seems alot more appropriate than to use ALTER or GRANT which are
irrelevant for what I am trying to do.</DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>So I tried again to create the database as postgres:</DIV>
<DIV><B>postgres:~> createdb -O saultdon -T template_postgis
newdb</B></DIV>
<DIV><B><BR></B></DIV>
<DIV>Again, only postgres user can use topolgy schema and user saultdon
cannot!</DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>This is even after I've run the 'GRANT USAGE ON SCHEMA topology TO
PUBLIC;'</DIV>
<DIV>And restarted the postgresql server.</DIV>
<DIV><BR></DIV>
<DIV>Donovan</DIV>
<DIV><FONT face="arial, helvetica, sans-serif"><BR></FONT></DIV>
<DIV><FONT face="arial, helvetica, sans-serif"><BR></FONT></DIV>
<DIV>[1] <A
href="http://code.grical.org/browser/INSTALL.TXT?rev=417%3A8ccbd40c0aba#L169"
target=_blank>http://code.grical.org/browser/INSTALL.TXT?rev=417%3A8ccbd40c0aba#L169</A></DIV>
<DIV>
<DIV class=h5>
<DIV><BR><BR>
<DIV class=gmail_quote>On Sun, May 6, 2012 at 11:30 AM, Donovan Cameron
<SPAN dir=ltr><<A href="mailto:sault.don@gmail.com"
target=_blank>sault.don@gmail.com</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>Using the GRANT sql worked. I read more about it in the
postgresql manual. Thanks.
<DIV><BR></DIV>
<DIV>GRANT USAGE ON SCHEMA topology TO PUBLIC;</DIV>
<DIV><BR></DIV>
<DIV>Then It never fully took effect till I restarted the server,</DIV>
<DIV><B>su -</B></DIV>
<DIV><B>rcpostgresql restart</B></DIV>
<DIV><B>exit</B></DIV>
<DIV><BR></DIV>
<DIV>Done.</DIV>
<DIV><BR></DIV>
<DIV>Now, that part about giving access to PUBLIC on the template_postgis
database.</DIV>
<DIV>This is because without it, a non-superuser cannot create a database
of their own when using:</DIV>
<DIV><B>createdb -T template_postgis newdb</B></DIV>
<DIV><FONT color=#ff0000><B>createdb: database creation failed: ERROR:
permission denied to copy database
"template_postgis"</B></FONT></DIV>
<DIV><BR></DIV>
<DIV>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:</DIV>
<DIV><B>GRANT CONNECT ON DATABASE template_postgis TO PUBLIC;</B></DIV>
<DIV><BR></DIV>
<DIV>But still, cannot create a database without being postgres
user.</DIV>
<DIV>Not sure if I need to maybe make user of ALTER like so:</DIV>
<DIV><B>ALTER DATABASE template_postgis OWNER TO PUBLIC;</B></DIV>
<DIV>Or try,</DIV>
<DIV><B>ALTER ROLE saultdon CREATEDB IN DATABASE
template_postgis;</B></DIV>
<DIV><BR></DIV>
<DIV>Does one of those give me copy permissions because CONNECT
hasn't.</DIV>
<DIV><BR></DIV>
<DIV>Thanks for the help so far with changing
schema privileges.</DIV><SPAN><FONT color=#888888>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>Donovan</DIV></FONT></SPAN>
<DIV>
<DIV>
<DIV><BR></DIV>
<DIV>
<DIV class=gmail_quote>On Sun, May 6, 2012 at 1:31 AM, Sandro Santilli
<SPAN dir=ltr><<A href="mailto:strk@keybit.net"
target=_blank>strk@keybit.net</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>
<DIV>On Sat, May 05, 2012 at 10:58:24PM -0700, Donovan Cameron
wrote:<BR><BR>> When I to check the postgis version with the user
saultdon, it says<BR>> postgis_topology not installed. I have to be
superuser to use it.<BR></DIV>...<BR>> *NOTICE: Function
postgis_topology_scripts_installed() not found. Is<BR>> topology
support enabled and topology.sql installed?*<BR><BR>Try<BR>GRANT usage
on schema topology to public;<BR><BR>Or variations of the above. When
satisfied consider doing it on the<BR>template
database.<BR><BR>--strk;<BR><BR> ,------o-.<BR> | __/
| Delivering high quality PostGIS 2.0 !<BR> |
/ 2.0 | <A href="http://strk.keybit.net"
target=_blank>http://strk.keybit.net</A> - <A
href="http://vizzuality.com"
target=_blank>http://vizzuality.com</A><BR> `-o------'<BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR></DIV></DIV></DIV></BLOCKQUOTE></DIV><BR></DIV></DIV></DIV></DIV></BLOCKQUOTE></DIV><BR></DIV></DIV></BLOCKQUOTE></BODY></HTML>