PostgreSQL user permissions

Question

I have a PostgreSQL 9.3 database with the PostGIS extension backing a web application and GeoServer. I have created three users.

A read only user for accessing the data in the reference data schema.

A read/write user for accessing and updating data in the schema that holds the application state.

A read/write/create user for the mapping data being accessed by GeoServer.

The first two work fine. The third one I'm having an issue with. Logged in as the user GeoServer can create new tables just fine. But subsequently cannot read from them. I have to go in specifically and manually apply the permissions. This is untenable for the application. It doesn't make a lot of sense to me that a user could create a table they can't read from. So I figure there is a non-obvious permission setting I missed. My Google searching led me to plenty of instructions for oddities of setting up a read only user, but not for this.

Answer 1

This should work (as superuser):

CREATE ROLE rwc;    -- read/write/create user
CREATE SCHEMA foo;
ALTER  SCHEMA foo OWNER TO rwc;
-- GRANT ALL ON SCHEMA foo TO rwc;  -- alternatively

CREATE ROLE r;      -- read user
GRANT USAGE ON SCHEMA foo TO r;

CREATE ROLE rw;     -- read/write user

GRANT r TO rw;
GRANT rw TO rwc;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT SELECT ON TABLES TO r;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT USAGE ON SEQUENCES TO r;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT INSERT, UPDATE, DELETE ON TABLES TO rw;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT SELECT, UPDATE ON SEQUENCES TO rw;

You probably missed one of these steps.
You may need do adjust existing objects additionally since default privileges are only granted to new objects.