[postgis-users] postgis for non-superuser user

[Shoaib Burq]

Thanks alex

I tried the following and it seems to work for me. Can you check if i
am doing somthing i shouldn't be :)

cheers
shoaib
--------------
To create the template in psql run:

\c template1
create database template_postgis with template = template1;

-- set the 'datistemplate' record in the 'pg_database' table for
'template_postgis' to TRUE indicating its a template
UPDATE pg_database SET datistemplate = TRUE where datname = 'template_postgis';
\c template_postgis
CREATE LANGUAGE plpgsql ;
\i /usr/share/postgresql/contrib/lwpostgis.sql;
\i /usr/share/postgresql/contrib/spatial_ref_sys.sql;

GRANT ALL ON geometry_columns TO PUBLIC;
GRANT ALL ON spatial_ref_sys TO PUBLIC;

-- vacuum freeze: it will guarantee that all rows in the database are
"frozen" and will not be subject to transaction ID wraparound
problems.
VACUUM FREEZE;
-----------

Now non-superuser's can create postgis db's using template_postgis

createdb -h <host-name> my_gisdb -W -T template_postgis


On 4/21/06, alex bodnaru <alexbodn at 012.net.il> wrote:
> hi,
>
> i wrote it.
>
> please use it.
>
> alex
>
> Shoaib Burq wrote:
> > Thanks Markus,
> >
> >
> >>If you can call "create database", then you actually _are_ superuser, right?
> >
> >
> > Hummm... not as I understand it. a non-superuser can create db's but
> > may not be able to create a procedural language or define the C
> > functions.
> >
> >
> >>And for installing PostGIS into a database, you need to define some 'C'
> >>functions. As 'C' is considered an "unsafe" language by PostgreSQL (you
> >>can bypass the whole security system), you need superuser rights for this.
> >
> >
> > So the upshot: no-superuser no postgis db? :(
> >
> >
> >>The debian installation has a template_postgis database, AFAIR.
> >
> >
> > I wonder if there's anything stopping me from using that on Gentoo?
> >
> > cheers
> > shoaib
> > --
> > http://geospatial.nomad-labs.com
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>