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. HYPERLINK "http://www.cognition.com/"<http://www.cognition.com>
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.526 / Virus Database: 270.4.3/1528 - Release Date: 7/1/2008 7:26 AM
Chandra Barnett wrote:
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.
Probably your group role needs to have the INHERIT property set.
Alternatively, you could "SET ROLE group" after connecting.
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?
Fortunately not.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
Probably your group role needs to have the INHERIT property set.
I think it's the other way around --- the login role needs the INHERIT
property. Anyway, one or the other should do it.
regards, tom lane
That did it! Thanks, all.
(Out of curiosity, what's the reason for not making this the default? It seems to me that if you're making a role a child of another role, it's /because/ you want to inherit permissions. OTOH maybe defaulting to more security is smart enough to be worth the confusion...)
-cb.
I think it's the other way around --- the login role needs the INHERIT property. Anyway, one or the other should do it.
regards, tom lane
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.526 / Virus Database: 270.4.3/1528 - Release Date: 7/1/2008 7:26 AM
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.526 / Virus Database: 270.4.3/1528 - Release Date: 7/1/2008 7:26 AM
"Chandra Barnett" <chandra.barnett@cognition.com> writes:
That did it! Thanks, all.
(Out of curiosity, what's the reason for not making this the default?
Uh ... it *is* the default.
regards, tom lane
Bah. Must be pgAdmin, then. In its role creation dialogue, INHERIT is definitely off by default. Guess that's not you guys, though. Sorry to have impugned your judgement... =)
-cb.
"Chandra Barnett" <chandra.barnett@cognition.com> writes:
That did it! Thanks, all.
(Out of curiosity, what's the reason for not making this the default?
Uh ... it *is* the default.
regards, tom lane
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.526 / Virus Database: 270.4.4/1530 - Release Date: 7/2/2008 8:05 AM
On Wed, Jul 2, 2008 at 6:09 PM, Chandra Barnett
<chandra.barnett@cognition.com> wrote:
Bah. Must be pgAdmin, then. In its role creation dialogue, INHERIT is definitely off by default. Guess that's not you guys, though. Sorry to have impugned your judgement... =)
Fixed in SVN. Sorry for the confusion.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com