How do you load postgis object and function definitions into a separate schema?

Question

I don't want to throw all my table definitions into the same public schema as the PostGIS definitions that get created with the standard PostGIS installation process (http://postgis.refractions.net/docs/ch02.html).

Does anyone have any suggestions for how to keep things organized into separate schemas when using PostGIS?

Answer 1

As I just answered on your related preceding question I recommend not to use the public schema for your objects at all. Reserve it for extensions like PostGis and use one or more separate schemas for your objects.

The public schema is in no way different than any other schema in your database. PostgreSQL does not need it at all. It just happens to be the default schema where many extensions put their stuff. So put your stuff somewhere else and set the search_path where you need it.

Then you can also create corresponding users with a matching preset search_path. The basic setup could 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'll also be interested in DEFAULT PRIVILEGES for users or schemas. More under this closely related question:
Grant all on a specific schema in the db to 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;