New Privilege model purposal - Mailing list pgsql-hackers

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.
Itshould 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
Privilegesare:
 
       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
isthe 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
backendto 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
effectiveuser id,  used           during  pg_check_perms() for the time of the function           invocation.
Procheckpermcauses 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_groupprivilegeis           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
throughthe           function manager can  switch  for-  and  backward  to           another  one, so prosetuid
functionswill 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
realuser           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@Yahoo.com #