PostgreSQL and schemas
From Wildsong
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+