Role Inheritance Without Explicit Naming? - Mailing list pgsql-general

Hi all,

I have four roles involved:

meetphil - the database owner, should not login
mpwebui - the role the web application logs in as, should have very limited privileges, but should be able to SET ROLE
toa user that has the correct privileges, should login 
mpusers - the main group for regular users, the group on which I'll grant default privileges, should not login
francois - one of the roles that has the right to do stuff, should login

I've gist'd everything here: https://gist.github.com/francois/9318054 (also appended at the end of this email).

In a fresh cluster, I create my users:

$ psql -U meetphil -d meetphil
psql (9.1.5)
Type "help" for help.

meetphil=> \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 colette   |                                                | {mpusers}
 francois  |                                                | {mpusers}
 meetphil  |                                                | {}
 mpusers   | Cannot login                                   | {}
 mpwebui   | No inheritance                                 | {mpusers}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rene      |                                                | {mpusers}

After the users, I create my database and ALTER DEFAULT PRIVILEGES. When the database owner creates objects, the
correctprivileges are granted: 

meetphil=> \ddp
                Default access privileges
  Owner   | Schema |   Type   |     Access privileges
----------+--------+----------+---------------------------
 meetphil |        | function | =X/meetphil              +
          |        |          | meetphil=X/meetphil      +
          |        |          | mpusers=X/meetphil
 meetphil |        | sequence | meetphil=rwU/meetphil    +
          |        |          | mpusers=rwU/meetphil
 meetphil |        | table    | meetphil=arwdDxt/meetphil+
          |        |          | mpusers=arwdxt/meetphil
(3 rows)

Then, I create my schema, including parties, a simple table:

meetphil=> \dp parties
                                Access privileges
 Schema |  Name   | Type  |     Access privileges     | Column access privileges
--------+---------+-------+---------------------------+--------------------------
 public | parties | table | meetphil=arwdDxt/meetphil+|
        |         |       | mpusers=arwdxt/meetphil   |
(1 row)

When I login as francois, I can create a row in the parties table:

$ psql -U francois -d meetphil
psql (9.1.5)
Type "help" for help.

meetphil=> INSERT INTO parties(party_id) VALUES(default) RETURNING party_id;
 party_id
----------
        1
(1 row)

INSERT 0 1

On the other hand, when I login as mpwebui, I cannot SET ROLE TO francois:

$ psql -U mpwebui -d meetphil
psql (9.1.5)
Type "help" for help.

meetphil=> SET ROLE TO francois;
ERROR:  permission denied to set role "francois"

mpwebui also cannot insert into tables, which is the desired state:

meetphil=> INSERT INTO parties(party_id) VALUES(default) RETURNING party_id;
ERROR:  permission denied for relation parties

By changing how I create my regular users, I can login as mpwebui, then set role to francois and insert to the parties
table:

CREATE ROLE francois WITH LOGIN INHERIT IN ROLE mpusers ROLE mpwebui;

This results in the following \du:

                                        List of roles
 Role name |                   Attributes                   |            Member of
-----------+------------------------------------------------+---------------------------------
 colette   |                                                | {mpusers}
 francois  |                                                | {mpusers}
 meetphil  |                                                | {}
 mpusers   | Cannot login                                   | {}
 mpwebui   | No inheritance                                 | {mpusers,francois,rene,colette}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rene      |                                                | {mpusers}

Note how mpwebui is now a member of francois, rene and colette. I expected mpwebui to inherit francois through mpusers.
CanI enable mpwebui to SET ROLE to francois without naming francois explicitely in mpwebui? 

I've found https://wiki.postgresql.org/images/d/d1/Managing_rights_in_postgresql.pdf which talks a bit about
inheritance,but I believe I have the same setup, but I must be wrong. 

I feel I'm pretty close, but the answer eludes me. It must be something basic. Can anyone spot it?

Thanks!
François Beausoleil

-- In a fresh cluster, login as postgres:
-- psql -U postgres -d postgres

-- The owner of all database objects
-- This user can and will change the database schema
CREATE ROLE meetphil WITH NOSUPERUSER NOCREATEDB NOCREATEROLE LOGIN;

-- The group which all regular users will be part of
CREATE ROLE mpusers WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOLOGIN;

-- The user which the web application connects as
-- Has limited rights by itself
CREATE ROLE mpwebui WITH NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN IN ROLE mpusers;

-- The regular people
CREATE ROLE francois WITH LOGIN INHERIT IN ROLE mpusers;
CREATE ROLE rene WITH LOGIN INHERIT IN ROLE mpusers;
CREATE ROLE colette WITH LOGIN INHERIT IN ROLE mpusers;

-- Create the application database itself
CREATE DATABASE meetphil WITH
    owner = meetphil
    template = template0
    encoding = 'UTF-8'
    lc_ctype = 'en_US.UTF-8'
    lc_collate = 'en_US.UTF-8';

-- Grant privileges
GRANT CONNECT, TEMPORARY ON DATABASE meetphil TO mpwebui, mpusers;

\connect meetphil

ALTER DEFAULT PRIVILEGES FOR ROLE meetphil
  GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER
  ON TABLES
  TO mpusers;

ALTER DEFAULT PRIVILEGES FOR ROLE meetphil
  GRANT EXECUTE
  ON FUNCTIONS
  TO mpusers;

ALTER DEFAULT PRIVILEGES FOR ROLE meetphil
  GRANT SELECT, UPDATE, USAGE
  ON SEQUENCES
  TO mpusers;

-- Execute as user meetphil, in database meetphil
-- psql -U meetphil -d meetphil
SET client_min_messages TO warning;

CREATE TABLE parties(
    party_id serial not null primary key
);

CREATE TABLE party_names(
    party_id int not null references parties
  , surname text not null
  , rest_of_name text
  , valid_starting_on date not null default current_date

  , unique(party_id, valid_starting_on, surname, rest_of_name)
  , constraint surname_not_empty check(length(trim(surname)) > 0)
  , constraint surname_is_trimmed check(trim(surname) = surname)
  , constraint rest_of_name_is_trimmed check((rest_of_name is not null and trim(rest_of_name) = rest_of_name) or
rest_of_nameis null ) 
);

-- Execute as mpwebui in the meetphil database
-- psql -U mpwebui -d meetphil
SET ROLE TO francois;

-- Results in:
-- ERROR:  permission denied to set role "francois"

-- I'd like to run this, as user francois
-- INSERT INTO parties(party_id) VALUES (default) RETURNING party_id;