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;
<<attachment: smime.p7s>>
