"Sam Howard" <sam.howard@gmxtechnologies.com> writes:
> Trying to implement user level access and security, and am finding
> unexpected behavior with respect to group roles.
I believe the problem is here:
> CREATE ROLE appuser LOGIN
> ENCRYPTED PASSWORD 'mdblahblahblah'
> NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
^^^^^^^^^
> GRANT db_group TO appuser;
The behavior you're expecting requires appuser to be marked INHERIT.
As the CREATE ROLE man page explains:
A role with the INHERIT attribute can automatically use whatever
database privileges have been granted to all roles it is
directly or indirectly a member of. Without INHERIT, membership
in another role only grants the ability to SET ROLE to that
other role; the privileges of the other role are only available
after having done so. If not specified, INHERIT is the default.
regards, tom lane
PS: Sorry for belated response, but I and most of the other developers
have been off at a conference ...
