PostgreSQL and schemas

This is really about PostGIS and schemas but it applies more generally...

I can't put spatial data into separate databases and then do queries that involve more than one database. So I have to load it all into one database but group it. I do this using schemas.

I want it to sort out sensibly when I list it. Everything in California should start with 'ca'.

ca_state          State of California
ca_co_sonoma      County of Sonoma
ca_ci_sonoma      City of Sonoma

I can create logical groups based on my above naming convention and also I can assign users and roles to schemas.

createdb -U postgres gis_data
echo 'CREATE EXTENSION postgis' | psql -U postgres gis_data
echo "CREATE ROLE gis_reader; GRANT gis_reader TO webadmin;" | psql -U postgres gis_data

From the command line I could create a bunch of schemas like this:

for i in ca_state ca_co_marin ca_ci_sanrafael; do
  psql -c "CREATE SCHEMA $i;" -U gis_owner --no-password gis_data
done

To load data this is easy enough. I have to change my connection string from

connstr  = "PG:dbname='%s' user='%s' password='%s'" % (dbname, username, password)

to

connstr  = "PG:dbname='%s' active_schema='%s' user='%s' password='%s'" % (dbname, schema, username, password)

I need to allow SELECT on each table to the gis_reader role.

\c gis_data
REVOKE ALL ON ALL TABLES IN SCHEMA ci_hoodriver_or FROM PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA ci_hoodriver_or TO gis_reader;
GRANT USAGE ON SCHEMA ci_hoodriver_or TO gis_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA co_hoodriver_or TO gis_reader;
GRANT USAGE ON SCHEMA co_hoodriver_or TO gis_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO gis_reader;
GRANT USAGE ON SCHEMA public TO gis_reader;
SET SEARCH_PATH TO ci_hoodriver_or, co_hoodriver_or, st_oregon, public;
ALTER USER gis_reader SET SEARCH_PATH TO ci_hoodriver_or, co_hoodriver_or, st_oregon, public;

\d+