Role Inheritance Without Explicit Naming?

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 to a 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 correct privileges 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. Can I 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_name is 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;