Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
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. –  dezso Jan 10 at 22:16
1  
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. –  Erwin Brandstetter Jan 10 at 22:42
    
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? –  bshender Jan 13 at 15:51

1 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.

share|improve this answer
    
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. –  bshender Jan 27 at 3:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.