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.

This is not a "group by" question. I am struggling with permissions in postgres because I apparently don't understand how group roles work. Searching for this on SO has proven difficult because I get buried in questions about group by, which isn't the problem.

I have been using postgres to manage a series of research projects. These databases are not connected to any web front-ends. I import all of the data from CSV. For the past few months, I have been the only user. This has worked really well. I am the db owner and nobody else needed to care. Recently, I built a complex database containing data from several sources. Because of the number of tables involved, each data source has its own schema and I have created a series of convenience views to make it easier to work with these various data sets. These schemas should be read only to all users, except for myself.

This project requires several other people to have access to this database. Each user has a username / password and an individual schema, where they can work without cluttering up the public schema. I want to be able to control access to the schemas such that the schemas holding the original data are "select only" to the other analysts. However, I want the all other schemas in the database to more or less function like the public schema in terms of access. These schemas exist for logical structure, not to control access. I want everyone to be able to read/write/create/drop/etc. in these other schemas that don't hold the original data.

Unfortunately, postgres roles are rather tricky or I've been rather slow to understand. Setting this up has been tricky as new tables are created by different users. I keep having to re-run the grant access commands manually as we add new tables.

The alter default privileges seems to imply that I can do this using group roles, but when I have tried to do so, other users were unable to access new tables. This seems to imply that I can set up group roles that will allow me to manage access, but I haven't succeed thus far:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

I'm just looking for some help to understand how to best utilize group roles to control access to schemas/tables/views in Postgres. I don't want to have to manually grant access every time I add a new table, or worse, manually grant access every time I add a new user. I would prefer to be able to add a new user to a larger group, that controls the access.


Based on comments given, I have tried to use the following syntax. In this example, there are two users, achoens (me) and mrubin (a co-worker). I can log in as both users. As achoens, I run the following:

create schema foo;
alter default privileges in schema foo grant select on all tables to public;
select 1 into foo.test;

I log off as achoens and log in as mrubin. I then run

select * from foo.test;

Which returns a permissions error. In this instance, Postgres 9.1 is running on a windows server but I tried it on my home server running Ubuntu and got the same results. I like postgres but this makes it much harder to use in a collaborative environment without just making everyone into an administrator, which I would prefer to not do.

I also edited the above syntax to reference against a user group called analyst, which both achoens and mrubin are members of. No difference. User mrubin was unable to read the silly little one row table.

share|improve this question

migrated from stackoverflow.com May 16 '13 at 21:30

This question came from our site for professional and enthusiast programmers.

    
Don't worry, your question isn't getting shot down, it is just getting moved over to the DBA site. –  mu is too short May 16 '13 at 17:00
    
After playing around with it, it most definitely looks like there's a bug or three in Postgres... –  Denis de Bernardy May 16 '13 at 18:32
2  
Your Postgres version? –  Igor Romanchenko May 16 '13 at 20:25
1  
@Denis Details? Please show a sequence of commands that you see as faulty and the relevant PostgreSQL version. Any such concerns are extremely serious. –  Craig Ringer May 17 '13 at 6:12
    
I am really wondering if what I need to do is wrap my head around how to do this via a group role. –  Choens May 17 '13 at 13:32

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
I have tried something similar to this, without success (thus far). I created a new group role called 'analyst'. Using alter default privileges, I gave members of analyst read access to the schema. I then made user mrubin a member of the analyst schema. USer mrubin was still unable to read the table. I like your suggestion, but I'm still trying to figure out how to do so successfully. –  Choens May 17 '13 at 16:41
    
@Choens From the edited question it looks like you didn't grant the user USAGE of the schema its self, so they can't see the tables. –  Craig Ringer May 18 '13 at 1:23
    
@Riner You are right. I didn't. I added that and everything did work. That has been my problem. –  Choens May 20 '13 at 14:16
    
I accepted this answer, but I posted my final syntax below, just to make it easier for someone else to use. –  Choens May 20 '13 at 14:20

This appears to work - for public. I'm assuming (but have not yet tested) that I can do something similar with another group role. As me (the local admin), I run the following.

create schema foo;
grant usage on schema foo to public;
alter default privileges in schema foo grant select on all tables to public;
select 1 into foo.test;

Once this has finished, I log in as another user, and I am able to:

select * from foo.test;

Which is what I want to be able to do. Now I just need to adjust this for a couple of different roles with slightly different permissions and everything should work just peachy.

share|improve this answer
    
Thanks for showing exactly what you did, upvoted. It's often better to create specific roles rather than using public but it sounds like that's what you did in your real db, just simplified it out for the example here. –  Craig Ringer May 20 '13 at 23:38

Have you tried something like:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA individual_schema1 TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA individual_schema2 TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA individual_schema3 TO PUBLIC;
...
GRANT SELECT ON ALL TABLES IN SCHEMA core_schema TO PUBLIC;

This will give R/O access to the core schema to all users (current and future) and R/W on all other schemas.

Alternatively you can grant all this rights to a group (basic_group) and then do

GRANT basic_group to newly_created_user;

Also you may need to setup the default privileges on schemas, as described in this question Grant all on a specific schema in the db to a group role in PostgreSQL

share|improve this answer
    
This grants write perms to the tables, which isn't what OP wants. Fwiw in addition, I was thinking the same when writing my own answer, but testing revealed a PG bug (in 9.2 anyway). Running alter default privileges in schema foo grant select on tables to public; is, per docs, supposed to grant select on all tables in schema foo to public; automatically on new tables (i.e. what OP wants), but testing revealed that you still need to run that query manually (i.e. what OP is currently doing) for some reason. –  Denis de Bernardy May 16 '13 at 20:51
    
I have tried to get this to work and it hasn't worked. I'm going to post some syntax in my first post. –  Choens May 17 '13 at 13:11

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.