How to load postgis objects and function definitions into a separate schema?

Question

I do not want all my table definitions to be included in one public schema, such as PostGIS definitions that are created with the standard PostGIS installation process (http://postgis.refractions.net/docs/ch02.html).

Does anyone have any suggestions on how to save elements in separate schemas when using PostGIS?

Answer 1

As I just answered your previous question, I recommend not using the public scheme for your objects at all . Reserve it for extensions such as PostGis, and use one or more separate schemas for your objects.

An open schema is no different from any other schema in your database. PostgreSQL does not need this at all. This is just a standard scheme in which many extensions put their stuff. So put your stuff somewhere else and set search_path where you need it.

Then you can also create the appropriate users with the relevant search_path presets. A basic installation might look something like this:

 CREATE ROLE sales; ALTER ROLE sales SET search_path=sales, public; -- postgis functions in public? COMMENT ON ROLE sales IS 'Sales app uses this user to connect.'; CREATE ROLE sales_admin; ALTER ROLE sales_admin SET search_path=sales, public; COMMENT ON ROLE sales_admin IS 'Owns objects in schema sales.'; CREATE SCHEMA sales; GRANT ALL ON SCHEMA sales TO sales_admin; GRANT USAGE ON SCHEMA sales TO sales; COMMENT ON SCHEMA sales IS 'All objects for my sales app here.' 

You will also be interested in DEFAULT PRIVILEGES for users or schemes. More on this closely related issue:
Provide everything in a specific schema in db for a group role in PostgreSQL

Answer 2

 --start out with it in public: create extension postgis; --add other extensions that depend on postgis being in public schema like postgis_tiger_geocoder --then move it create schema postgis; grant all on schema postgis to public; alter database [database_name] set search_path = "$user", public, postgis; alter extension postgis set schema postgis;