PostgreSQL – Why Granting Access to Sequences is Necessary

It sounds like what you probably want is to:

• Create a role to own all the common tables and schema, or just use your own if you really will always be the only one with full control of the main tables.

• Create another role you intend to give only read-only access to the shared tables and schemas. GRANT that role rights using GRANT SELECT ON ALL TABLES IN SCHEMA [x] for each shared schema. You may also want to ALTER DEFAULT PRIVILEGES to make sure this role has read rights on any new tables created in these schemas too.

• Now GRANT each user membership of the read-only access role with INHERIT.

• For the private schemas, create a schema the same as the user's username with CREATE SCHEMA [username] AUTHORIZATION [username] or the older style where you create the schema then ALTER SCHEMA ... OWNER TO.

See the postgresql manual for the detailed syntax of all of the above commands. Start with user management, part of the broader database administration topic that includes grant management etc. The PostgreSQL manual is detailed, comprehensive and readable: reading it is strongly recommended.

Update Oct. 30, 2015: This answer is simpler and more accurate than my old answer, which I removed for the sake of brevity. To see the old answer, take a look at the edit history.

1. As production user: pg_dump -Fc my_prod_db > prod_dump.db
2. Drop and create staging database
3. As staging user: pg_restore --no-owner --dbname my_staging_db path/to/prod_dump.db