[postgis-users] Creating a template for postgis-enabled databases?
Kevin Neufeld
kneufeld at refractions.net
Tue Dec 18 13:44:47 PST 2007
I agree, Shoaib, but in fact, I would go one step further.
I would highly recommend as a postgis best practice (in a production
environment anyway) that only the postgres user has usage access to the
public schema. All other users should not have write access to the
public schema at all. Doing so *significantly complicates* upgrading
your database over major versions.
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO public;
GRANT ALL ON SCHEMA public TO postgres;
The postgres user would in turn create users with their own (or shared)
schemas as scratch spaces and grant permissions accordingly, in addition
to granting read access to the spatial_ref_sys table and read and
possibly write access to geometry_columns table.
CREATE SCHEMA devel AUTHORIZATION postgres;
CREATE ROLE writer
NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
GRANT ALL ON SCHEMA devel TO spatial_group;
GRANT SELECT, UPDATE, INSERT, DELETE
ON TABLE public.geometry_columns TO spatial_group;
GRANT SELECT, UPDATE, INSERT, DELETE
ON TABLE public.spatial_ref_sys TO spatial_group;
CREATE ROLE spatial_user LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
GRANT spatial_group TO spatial_user;
Such an environment can be set up in the postgis_template database and
used for all subsequent databases.
Then, when it's time to perform a database upgrade, since no user data
(tables/functions/datatypes) exists in the public schema, all that's
required is to copy a user's schema to a new upgraded postgres/postgis
instance.
Cheers,
Kevin
-------------
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7
Phone: (250) 383-3022
Email: kneufeld at refractions.net
Shoaib Burq wrote:
> You are right Sebastien,
> I should have mentioned the above template is not ideal for production
> as it grants PUBLIC all privileges on geometry_columns & spatial_ref_sys.
>
> For production you will probably want to change the ownership of
> geometry_columns & spatial_ref_sys tables to the user creating the
> database and revoke it from public.
>
> The only way I can think of doing that is to write a plpgsql script or
> a shell script that will make the changes AFTER the database has been
> created using the template_postgis using ALTER commands Chander
> suggested.
>
> it will probably contain something like:
>
> REVOKE ALL ON geometry_columns FROM PUBLIC
> GRANT ALL ON geometry_columns TO <user>
>
> Anyone else had any experience with being able to do this at the time
> the database is created from the template?
>
> Let us know if you do write something ...
> Shoaib
>
> On Dec 18, 2007 11:40 PM, Chander Ganesan <chander at otg-nc.com
> <mailto:chander at otg-nc.com>> wrote:
>
> Sebastien ARBOGAST wrote:
>> I still have one problem with this procedure. The owner of
>> template_postgis is the user with which I created it, in this case
>> postgres. And when I create a new database based on this template,
>> both geometry_columns and spatial_ref_sys have their owner set to
>> postgres instead of the owner of the new database.
>>
> When you create a database, the database itself is owned by the
> user that issued the create database command....if that user was
> 'postgres', then he/she would own the database. If you look at
> the syntax of the create database command, they you'll see how you
> can set the owner of a DB when you create the DB. However, I
> suspect that isn't your problem.
>
> Instead, I think that you have a schema or other objects in the
> 'template_postgis' database that is owned by the user 'postgres'.
> That schema, along with any other permissions *inside* the
> database are copied "as is" when creating a new database
> (regardless of the database owner). As such, you should change
> the ownership of the schema so that it's owned by the appropriate
> user (the same would go for any tables, indexes, etc. inside the
> database that you want owned by someone else).
>
> I recommend you check out the online help for 'ALTER SCHEMA',
> 'ALTER TABLE', 'ALTER SEQUENCE', and the other related commands to
> see how to change the ownership of the objects you want to change.
>
> Hope that helps.
>
> Chander
>
>> Is it normal? Is there a way to change that?
>>
>> 2007/12/16, Sebastien ARBOGAST <sebastien.arbogast at gmail.com> <mailto:sebastien.arbogast at gmail.com>:
>>
>>> Yes! That worked great. Thanks to both of you.
>>>
>>> 2007/12/16, LuVar <varga.lubomir at orangemail.sk> <mailto:varga.lubomir at orangemail.sk>:
>>>
>>>> Jop. I get also the same. Just try to disconnect in pgAdmin and
>>>> reconnect. Than click ONLY on that database, in which you want to do
>>>> somethink. Dont open "template_postgis" table. You must have an red
>>>> cross iccon on "template_postgis" database.
>>>>
>>>> Try, and good luck.
>>>>
>>>> Sunday, December 16, 2007, 3:25:11 PM, you wrote:
>>>>
>>>>
>>>>> I just did that and I can't create a database based on template_postgis.
>>>>>
>>>>> When I try to do it via pgAdmin, I get a message saying that "Source
>>>>> database "template_postgis" is being accessed by other users
>>>>>
>>>>> When I try to run the command in terminal, I get the following message:
>>>>>
>>>>> createdb: could not connect to database template1: could not translate
>>>>> host name "tagspot_local" to address: nodename nor servname provided,
>>>>> or not known
>>>>>
>>>>
>>>>> 2007/12/16, Shoaib Burq <shoaib at nomad-labs.com> <mailto:shoaib at nomad-labs.com>:
>>>>>
>>>>>> Sebastian, please see:
>>>>>> http://geospatial.nomad-labs.com/2007/12/16/template-postgis-database/
>>>>>> shoaib
>>>>>>
>>>>>>
>>>>>> On Dec 16, 2007 10:15 AM, Sebastien ARBOGAST <sebastien.arbogast at gmail.com> <mailto:sebastien.arbogast at gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I've just installed postgresql 8.2.5 and postgis 1.3.2 on my mac and
>>>>>>> everything works great so far. Now I'm just looking for a way to
>>>>>>> simplify the creation of postgis-enabled databases in pgAdmin.
>>>>>>> I saw that in the Windows distribution, there is a template to do so,
>>>>>>> but there is no such template in my installation. Knowing that it's
>>>>>>> the first time that I use postgresql and postgis, is there a way to
>>>>>>> create such a template?
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Sébastien Arbogast
>>>>>>>
>>>>>>> http://www.sebastien-arbogast.com
>>>>>>> _______________________________________________
>>>>>>> postgis-users mailing list
>>>>>>> postgis-users at postgis.refractions.net <mailto:postgis-users at postgis.refractions.net>
>>>>>>>
>>>>>>>
>>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>>
>>>>>>
>>>> --
>>>> Best regards,
>>>> LuVar mailto:varga.lubomir at orangemail.sk
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net <mailto:postgis-users at postgis.refractions.net>
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>>>
>>> --
>>> Sébastien Arbogast
>>>
>>> http://www.sebastien-arbogast.com
>>>
>>>
>>
>
>
> --
> Chander Ganesan
> The Open Technology Group
> One Copley Parkway, Suite 210
> Morrisville, NC 27560
> Phone: 877-258-8987/919-463-0999
> http://www.otg-nc.com
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> <mailto: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
>
More information about the postgis-users
mailing list