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.

• András Váczi: There is no read or read/write user in itself. How do you define these? On the other hand, I think you missed to set the default permissions for the containing schema. See ALTER DEFAULT PRIVILEGES.
• Erwin Brandstetter: You should add the SQL statements to create your users in the question. And information about schemas and grants to schemas. This related answer for DEFAULT PRIVILEGES may be of help.
• bshender: I did not grant on the role, but GRANT ALL ON TABLES IN SCHEMA and GRANT ALL ON SEQUENCES IN SCHEMA (as well as connect to db) to the user specifically. Is this the likely cause of the issue? Granting in this way works differently then creating a role and assigning it?

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.

• bshender: I've pulled off this project for now and haven't had a chance to test this answer. But thank you. I'll let you know as soon as I get back to it.
• JustAMartin: Thanks, FOR ROLE rwc nailed it for me. Without it, read-only users did not have permissions on new tables. Postgres can be confusing sometimes, this query is so unintuitive - why should I use FOR ROLE with another role name to grant permissions for other roles? As long, as I'm executing the query as Postgres admin, I should be able to grant any permissions to any roles without mentioning some other, seemingly unrelated role.
• Erwin Brandstetter: @JustAMartin: You may be misunderstanding some fine points of ALTER DEFAULT PRIVILEGES. And there is currently (up to v1.20) a confusing bug in pgAmdin - if you should be working with it: dba.stackexchange.com/a/53936/3684
• JustAMartin: Yes, this only for the particular role they belong to is the most important bit that I was missing, coming from MySQL where it makes no difference which role is the original owner of newly created objects.