[GENERAL] Problem with roles and permissions

 

Hi --

 

I've recently started using PostgreSQL after working with MySQL for quite a while. I've been trying to set up roles so I can manage permissions per-user, but it's not working the way I expected and I'm wondering if anyone can steer me straight.

 

What I've done so far is to set up a "group role" and explicitly granted it every possible type of permission on the database in question, and also on the individual relations in that database. Then I created a "login role" and made it part of the group role I created. I expected that to let me connect to the dbms using that login role and database, and that I'd then be able to select/insert/update/delete in the relations in that database. I can connect just fine to that database with that login role, as I expected, but I then can't execute any commands. A sample error message from a failed INSERT is "ERROR:  permission denied for relation sessions". A look in pgAdmin, however, tells me that I've explicitly granted the group role all permissions on both the relation and the database it's in.

 

It seems there must be some other step that I didn't know to do. In MySQL, for example, after changing permissions, one needs to "flush privileges" to get everything to take effect. Is there something analogous to this in PostgreSQL? If so, can someone tell me how to do it via pgAdmin III?

 

For context, I'm using PostgreSQL 8.3 and pgAdmin III on Windows XP.

 

Thanks for your help!

--  Chandra Barnett <chandra.barnett@cognition.com> 310-641-7200 x207 Software Engineer, Cognition, Inc. <http://www.cognition.com>