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 class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">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 style><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 class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
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 class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><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>