Thread: BUG #4537: role

The following bug has been logged online:

Bug reference:      4537
Logged by:          Marco Peixoto
Email address:      mpeixoto@metrosp.com.br
PostgreSQL version: 8.2.11
Operating system:   debian 4.1
Description:        role
Details:

CREATE SCHEMA agd
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA agd TO postgres;
GRANT USAGE ON SCHEMA agd TO agd_select; (group)

CREATE ROLE r227602 LOGIN
  ENCRYPTED PASSWORD 'md5a4d27e81e0dc6b9828cf2dc84565fb59'
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
GRANT agd_select TO r227602;

CREATE TABLE agd.clanalis
(
  nr_analista numeric(10) NOT NULL DEFAULT
nextval('agd.clanalis_nr_analista_seq'::regclass), -- Analista
  funcionario integer NOT NULL, -- Funcionário
  CONSTRAINT pk_clanalis PRIMARY KEY (nr_analista),
  CONSTRAINT fk_clanalis_funcionario FOREIGN KEY (funcionario)
      REFERENCES ahb.funcionario (funcionario) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE agd.clanalis OWNER TO postgres;
GRANT ALL ON TABLE agd.clanalis TO postgres;
GRANT SELECT ON TABLE agd.clanalis TO agd_select;


[postgres@sd5metro1 dados]$ psql -p 5433 tscorp01 -U r227602 -W
Password for user r227602:
Welcome to psql 8.2.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

tscorp01=> select * from agd.claloclg;
ERROR:  permission denied for relation clanalis

"Marco Peixoto" <mpeixoto@metrosp.com.br> writes:
> CREATE ROLE r227602 LOGIN
>   ENCRYPTED PASSWORD 'md5a4d27e81e0dc6b9828cf2dc84565fb59'
>   NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

> GRANT agd_select TO r227602;

You've got the role set to NOINHERIT.  This means that it can *become*
agd_select (via SET ROLE) and then use agd_select's privileges, but it
doesn't have use of those privileges without doing SET ROLE.

BTW, when I try this example I get a complaint about schema agd,
not relation clanalis --- so there must be a few more grants you
didn't show us in your system.  I'd still guess the NOINHERIT is
the root of your problem though.

            regards, tom lane