New Privilege model purposal

Here it is:

Proposal for a new
PostgreSQL Privilege System

July 2000, Jan Wieck

Introduction

The existing permission checking system, implemented in
the PostgreSQL database, has alot of missing
capabilities. Users complained about it in the past. With
some new features (referential integrity for example),
this system isn't flexible enough any more and need to be
expanded or replaced soon.

This document is a draft for implementing a completely
new, object/functionality based permission concept. It
defines a fine grained, expandable, general purpose
permission checking functionality. It should cover DML-
and DDL-statements all at once.

Object Privileges

Object Privileges can be GRANTed or REVOKEed to a
particular user or group. The possible Privileges are:

ALL [PRIVILEGES] Synonym for any of the privileges,
applicaple to the object in question.

ALTER Permission to alter the schema WRT
the object in question.

INSERT Permission to INSERT rows into the
named relation.

UPDATE Permission to UPDATE rows in the
named relation.

DELETE Permission to DELETE rows from the
named relation.

SELECT Permission to SELECT rows from the
named relation or sequence.

EXECUTE Permission to call the named function
or procedure.

LOCK Permission to exclusively lock the
named relation.

REFERENCES Permission to create a foreign key
reference to the named relation.

TRUNCATE Permission to truncate the named
relation.

System Privileges

System Privileges are to grant permission to execute DDL-
statements or for database wide Object permissions (valid
for all objects of a particular kind).

SUPERUSER A special System Privilege,
superseding any other rights. What
the holder of this right want's to
do, he does. It is the same as now,
usesuper in pg_shadow.

----------

CREATE SESSION Permission to login. Checked after
general hba.conf access had been
granted. Not having this right will
cause the new backend to immediately
terminate.

ALTER SESSION Permission to change session specific
attributes like character set
localization.

----------

CREATE TABLE Permission to create new table in a
database.

ALTER ANY TABLE Permission to alter any table of the
database. Includes rights to create
or drop rules, triggers, references
etc.

DROP ANY TABLE Permission to drop any table of the
database.

INSERT ANY TABLE Permission to INSERT rows into any
table of the database.

UPDATE ANY TABLE Permission to UPDATE rows in any
table of the database.

DELETE ANY TABLE Permission to DELETE rows from any
table of the database.

SELECT ANY TABLE Permission to SELECT rows from any
relation of the database.

LOCK ANY TABLE Permission to explicitly LOCK any
relation of the database.

REFERENCE ANY TABLE Permission to use any table of the
database in referential integrity
constraints.
----------

CREATE SEQUENCE Permission to create a new sequence.

ALTER ANY SEQUENCE Permission to readjust all sequences.

DROP ANY SEQUENCE Permission to drop any sequence in
the database.

----------

CREATE VIEW Permission to create views in the
database.

ALTER ANY VIEW Permission to alter any view of the
database.

DROP ANY VIEW Permission to drop any view of the
database.

----------

CREATE FUNCTION Permission to create new functions in
the database.

ALTER ANY FUNCTION Permission to alter any function of
the database.

DROP ANY FUNCTION Permission to drop any function of
the database.

----------

CREATE TYPE Permission to create a new type.

ALTER ANY TYPE Permission to alter any type of the
database.

DROP ANY TYPE Permission to drop any type of the
database.

----------

CREATE OPERATOR Permission to create a new operator.

ALTER ANY OPERATOR Permission to alter any operator of
the database.

DROP ANY OPERATOR Permission to drop any operator of
the database.

----------

CREATE AGGREGATE Permission to create a new aggregate.

ALTER ANY AGGREGATE Permission to alter any aggregate of
the database.

DROP ANY AGGREGATE Permission to drop any aggregate of
the database.

----------

CREATE OBJECT Permission to create a new table,
sequence, type, operator or
aggregate.

ALTER ANY OBJECT Permission to alter any table,
sequence, type, operator or aggregate
of the database.

DROP ANY OBJECT Permission to drop any table,
sequence, type, operator or aggregate
of the database.

----------

TRUNCATE ANY Permission to truncate any relation
of the database.

Implementation

New privilege check funciton

A new function

bool pg_check_priv(
int32 privtype,
Oid privobject,
int32 privobjowner);

will be called in the appropriate places. The
privtype is a #define constant from a new
"utils/privileges.h" header file. Privobject is the
OID of the object (relation, function, aggregate
etc.) to check for. Privobjowner is the owner of the
object (relowner, proowner, aggowner etc).

The function will know about privilege relationships.
So only one call like

pg_check_priv(PRIV_ALTER_TABLE,
Relation->rd_id,
Relation->rd_rel->relowner);

pg_check_priv(PRIV_EXEC_FUNCTION,
finfo->fn_oid,
finfo->fn_owner);

would be sufficient to check whether the actual
caller is permitted to do that.

System catalog changes

Pg_proc is extended by two new bool fields. Prosetuid
and procheckperm. These two and the proowner are
held in the fmgr_info struct.

If a function is called through the fmgr (any user
defined function is), the function manager honours
these flags. Prosetuid will cause the function
manager to switch to another effective user id, used
during pg_check_perms() for the time of the function
invocation. Procheckperm causes the function manager
to check whether the actual (effective) user is
allowed to execute the function (by calling
pg_check_perms()).

Pg_shadow is extended with an array, holding all the
groups the user belongs to. So after looking up the
user, all group relationships are known.

Two new system catalogs, pg_userprivilege and
pg_groupprivilege are created to hold the actual
privileges. They are members of the system cache for
fast lookup.

Pg_class will loose it's relacl attribute.

All the (security relevant) information in pg_shadow,
pg_group, pg_userprivilege and pg_groupprivilege is
only modified during GRANT, REVOKE or
CREATE/DROP/ALTER statements. So it's IMHO not an
issue to performance questions.

Related details

The system will manage a stack, remembering nested
states of the effective user id. Calls through the
function manager can switch for- and backward to
another one, so prosetuid functions will inherit the
effective permissions of the function (trigger)
owner. The stack is reinitialized at transaction
aborts.

For special purposes, there will be another function
pg_check_realperms() checking against the real user
id allways (don't know what it'll be good for, but in
case...).

Comments?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #