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;
