PostgreSQL and schemas: Difference between revisions
From Wildsong
Jump to navigationJump to search
Brian Wilson (talk | contribs) mNo edit summary |
Brian Wilson (talk | contribs) mNo edit summary |
||
Line 33: | Line 33: | ||
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; | |||
REVOKE ALL ON ALL TABLES IN SCHEMA ci_hoodriver_or FROM PUBLIC; | SET SEARCH_PATH TO ci_hoodriver_or; | ||
GRANT SELECT ON ALL TABLES IN SCHEMA ci_hoodriver_or TO gis_reader; |
Revision as of 04:22, 15 June 2013
I want to keep my spatial data organized
st_oregon State of Oregon oregonstate_edu Oregon State University co_benton_or Benton County co_hoodriver_or ci_hoodriver_or
but I can't put it into separate databases and do queries that involve more than one database.
So I have to load it all into one database but somehow group it. I think I can do this using schemas.
I can create logical groups based on my above naming convention and also I can assign users and roles to schemas.
createdb -U postgres gisdata echo 'CREATE EXTENSION postgis' | psql -U postgres gisdata echo "CREATE ROLE gis_reader; GRANT gis_reader TO webadmin;" | psql -U postgres gisdata
for i in st_oregon oregonstate_edu co_benton_or co_hoodriver_or ci_hoodriver_or; do echo "CREATE SCHEMA $i;" | psql -U postgres gisdata 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' user='%s' password='%s'" % (dbname, username, password)
I need to allow SELECT on each table to the gis_reader role.
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; SET SEARCH_PATH TO ci_hoodriver_or;