On Wed, 24 Jan 2007, Tom Lane wrote:

> In detail, it'd look something like:
>
> * For an untrusted language: must be superuser to either create or use
> the language (no change from current rules).  Ownership of the
> pg_language entry is really irrelevant, as is its ACL.
>
> * For a trusted language:
>
> * if pg_pltemplate.something is ON: either a superuser or the current
> DB's owner can CREATE the language.  In either case the pg_language
> entry will be marked as owned by the DB owner (pg_database.datdba),
> which means that subsequently he (or a superuser) can grant or deny
> USAGE within his DB.
>
> * if pg_pltemplate.something is OFF: must be superuser to CREATE the
> language; subsequently it will be owned by you, so only you or another
> superuser can grant or deny USAGE (same behavior as currently).

I think I have what is described here implemented in this patch, so that
it can be better understood.  Thoughts?


-- 
Nobody said computers were going to be polite.
Index: src/backend/catalog/aclchk.c
===================================================================
RCS file: 
/data/local/jeremyd/postgres/cvsuproot/pgsql/src/backend/catalog/aclchk.c,v
retrieving revision 1.135
diff -c -r1.135 aclchk.c
*** src/backend/catalog/aclchk.c        23 Jan 2007 05:07:17 -0000      1.135
--- src/backend/catalog/aclchk.c        25 Jan 2007 06:35:21 -0000
***************
*** 1003,1013 ****
                /*
                 * Get owner ID and working copy of existing ACL. If there's no 
ACL,
                 * substitute the proper default.
-                *
-                * Note: for now, languages are treated as owned by the 
bootstrap
-                * user. We should add an owner column to pg_language instead.
                 */
!               ownerId = BOOTSTRAP_SUPERUSERID;
                aclDatum = SysCacheGetAttr(LANGNAME, tuple, 
Anum_pg_language_lanacl,
                                                                   &isNull);
                if (isNull)
--- 1003,1010 ----
                /*
                 * Get owner ID and working copy of existing ACL. If there's no 
ACL,
                 * substitute the proper default.
                 */
!               ownerId = pg_language_tuple->lanowner;
                aclDatum = SysCacheGetAttr(LANGNAME, tuple, 
Anum_pg_language_lanacl,
                                                                   &isNull);
                if (isNull)
***************
*** 1770,1777 ****
                                (errcode(ERRCODE_UNDEFINED_OBJECT),
                                 errmsg("language with OID %u does not exist", 
lang_oid)));
  
!       /* XXX pg_language should have an owner column, but doesn't */
!       ownerId = BOOTSTRAP_SUPERUSERID;
  
        aclDatum = SysCacheGetAttr(LANGOID, tuple, Anum_pg_language_lanacl,
                                                           &isNull);
--- 1767,1773 ----
                                (errcode(ERRCODE_UNDEFINED_OBJECT),
                                 errmsg("language with OID %u does not exist", 
lang_oid)));
  
!       ownerId = ((Form_pg_language) GETSTRUCT(tuple))->lanowner;
  
        aclDatum = SysCacheGetAttr(LANGOID, tuple, Anum_pg_language_lanacl,
                                                           &isNull);
Index: src/backend/commands/proclang.c
===================================================================
RCS file: 
/data/local/jeremyd/postgres/cvsuproot/pgsql/src/backend/commands/proclang.c,v
retrieving revision 1.71
diff -c -r1.71 proclang.c
*** src/backend/commands/proclang.c     22 Jan 2007 01:35:20 -0000      1.71
--- src/backend/commands/proclang.c     25 Jan 2007 06:30:12 -0000
***************
*** 17,22 ****
--- 17,24 ----
  #include "access/heapam.h"
  #include "catalog/dependency.h"
  #include "catalog/indexing.h"
+ #include "catalog/pg_authid.h"
+ #include "catalog/pg_database.h"
  #include "catalog/pg_language.h"
  #include "catalog/pg_namespace.h"
  #include "catalog/pg_pltemplate.h"
***************
*** 27,32 ****
--- 29,35 ----
  #include "miscadmin.h"
  #include "parser/gramparse.h"
  #include "parser/parse_func.h"
+ #include "utils/acl.h"
  #include "utils/builtins.h"
  #include "utils/fmgroids.h"
  #include "utils/lsyscache.h"
***************
*** 36,50 ****
  typedef struct
  {
        bool            tmpltrusted;    /* trusted? */
        char       *tmplhandler;        /* name of handler function */
        char       *tmplvalidator;      /* name of validator function, or NULL 
*/
        char       *tmpllibrary;        /* path of shared library */
  } PLTemplate;
  
  static void create_proc_lang(const char *languageName,
!                                Oid handlerOid, Oid valOid, bool trusted);
  static PLTemplate *find_language_template(const char *languageName);
  
  
  /* ---------------------------------------------------------------------
   * CREATE PROCEDURAL LANGUAGE
--- 39,56 ----
  typedef struct
  {
        bool            tmpltrusted;    /* trusted? */
+       bool            tmpldbaallowed; /* db owner allowed to create? */
        char       *tmplhandler;        /* name of handler function */
        char       *tmplvalidator;      /* name of validator function, or NULL 
*/
        char       *tmpllibrary;        /* path of shared library */
  } PLTemplate;
  
  static void create_proc_lang(const char *languageName,
!                                Oid languageOwner, Oid handlerOid, Oid valOid, 
bool trusted);
  static PLTemplate *find_language_template(const char *languageName);
  
+ static Oid find_desired_language_owner (PLTemplate *pltemplate);
+ 
  
  /* ---------------------------------------------------------------------
   * CREATE PROCEDURAL LANGUAGE
***************
*** 61,74 ****
        Oid                     funcargtypes[1];
  
        /*
-        * Check permission
-        */
-       if (!superuser())
-               ereport(ERROR,
-                               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                                errmsg("must be superuser to create procedural 
language")));
- 
-       /*
         * Translate the language name and check that this language doesn't
         * already exist
         */
--- 67,72 ----
***************
*** 97,102 ****
--- 95,123 ----
                                        (errmsg("using pg_pltemplate 
information instead of CREATE LANGUAGE parameters")));
  
                /*
+                * Check permission
+                */
+               if (pltemplate->tmpltrusted && pltemplate->tmpldbaallowed)
+               {
+                               if (!pg_database_ownercheck(MyDatabaseId, 
GetUserId()))
+                                       ereport(ERROR,
+                                                       
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                                        errmsg("must be 
database owner or superuser to create procedural language \"%s\"", 
languageName)));
+               }
+               else if (!superuser())
+               {
+                       if (!pltemplate->tmpltrusted)
+                               ereport(ERROR,
+                                               
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                                errmsg("must be superuser to 
create untrusted procedural language")));
+                       else
+                               ereport(ERROR,
+                                               
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                                errmsg("must be superuser to 
create procedural language \"%s\"", languageName),
+                                                errhint("Column 
pg_pltemplate.tmpldbaallowed has been set to false for this language.")));
+               }
+ 
+               /*
                 * Find or create the handler function, which we force to be in 
the
                 * pg_catalog schema.  If already present, it must have the 
correct
                 * return type.
***************
*** 171,177 ****
                        valOid = InvalidOid;
  
                /* ok, create it */
!               create_proc_lang(languageName, handlerOid, valOid,
                                                 pltemplate->tmpltrusted);
        }
        else
--- 192,198 ----
                        valOid = InvalidOid;
  
                /* ok, create it */
!               create_proc_lang(languageName, 
find_desired_language_owner(pltemplate), handlerOid, valOid,
                                                 pltemplate->tmpltrusted);
        }
        else
***************
*** 189,194 ****
--- 210,223 ----
                                         errhint("The supported languages are 
listed in the pg_pltemplate system catalog.")));
  
                /*
+                * Check permission
+                */
+               if (!superuser())
+                       ereport(ERROR,
+                                       
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                        errmsg("must be superuser to create 
custom procedural language")));
+ 
+               /*
                 * Lookup the PL handler function and check that it is of the 
expected
                 * return type
                 */
***************
*** 227,233 ****
                        valOid = InvalidOid;
  
                /* ok, create it */
!               create_proc_lang(languageName, handlerOid, valOid, 
stmt->pltrusted);
        }
  }
  
--- 256,262 ----
                        valOid = InvalidOid;
  
                /* ok, create it */
!               create_proc_lang(languageName, BOOTSTRAP_SUPERUSERID, 
handlerOid, valOid, stmt->pltrusted);
        }
  }
  
***************
*** 236,242 ****
   */
  static void
  create_proc_lang(const char *languageName,
!                                Oid handlerOid, Oid valOid, bool trusted)
  {
        Relation        rel;
        TupleDesc       tupDesc;
--- 265,271 ----
   */
  static void
  create_proc_lang(const char *languageName,
!                                Oid languageOwner, Oid handlerOid, Oid valOid, 
bool trusted)
  {
        Relation        rel;
        TupleDesc       tupDesc;
***************
*** 258,263 ****
--- 287,293 ----
  
        namestrcpy(&langname, languageName);
        values[Anum_pg_language_lanname - 1] = NameGetDatum(&langname);
+       values[Anum_pg_language_lanowner - 1] = ObjectIdGetDatum(languageOwner);
        values[Anum_pg_language_lanispl - 1] = BoolGetDatum(true);
        values[Anum_pg_language_lanpltrusted - 1] = BoolGetDatum(trusted);
        values[Anum_pg_language_lanplcallfoid - 1] = 
ObjectIdGetDatum(handlerOid);
***************
*** 295,300 ****
--- 325,359 ----
        heap_close(rel, RowExclusiveLock);
  }
  
+ 
+ static Oid find_desired_language_owner (PLTemplate *pltemplate)
+ {
+       if (pltemplate->tmpltrusted && pltemplate->tmpldbaallowed)
+       {
+               /* find datdba for current db */
+               HeapTuple       tuple;
+               Oid                     dba;
+ 
+               tuple = SearchSysCache(DATABASEOID,
+                               ObjectIdGetDatum(MyDatabaseId),
+                               0, 0, 0);
+               if (!HeapTupleIsValid(tuple))
+                       ereport(ERROR,
+                                       (errcode(ERRCODE_UNDEFINED_DATABASE),
+                                        errmsg("database with OID %u does not 
exist", MyDatabaseId)));
+ 
+               dba = ((Form_pg_database) GETSTRUCT(tuple))->datdba;
+ 
+               ReleaseSysCache(tuple);
+               return dba;
+       }
+       else
+       {
+               /* current behaviour */
+               return BOOTSTRAP_SUPERUSERID;
+       }
+ }
+ 
  /*
   * Look to see if we have template information for the given language name.
   */
***************
*** 325,330 ****
--- 384,390 ----
  
                result = (PLTemplate *) palloc0(sizeof(PLTemplate));
                result->tmpltrusted = tmpl->tmpltrusted;
+               result->tmpldbaallowed = tmpl->tmpldbaallowed;
  
                /* Remaining fields are variable-width so we need heap_getattr 
*/
                datum = heap_getattr(tup, Anum_pg_pltemplate_tmplhandler,
***************
*** 382,395 ****
        ObjectAddress object;
  
        /*
-        * Check permission
-        */
-       if (!superuser())
-               ereport(ERROR,
-                               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
-                                errmsg("must be superuser to drop procedural 
language")));
- 
-       /*
         * Translate the language name, check that the language exists
         */
        languageName = case_translate_language_name(stmt->plname);
--- 442,447 ----
***************
*** 411,416 ****
--- 463,476 ----
                return;
        }
  
+       /*
+        * Check permission
+        */
+       if (!has_privs_of_role (GetUserId(), ((Form_pg_language) 
GETSTRUCT(langTup))->lanowner))
+               ereport(ERROR,
+                               (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                                errmsg("must be owner to drop procedural 
language")));
+ 
        object.classId = LanguageRelationId;
        object.objectId = HeapTupleGetOid(langTup);
        object.objectSubId = 0;
Index: src/include/catalog/pg_language.h
===================================================================
RCS file: 
/data/local/jeremyd/postgres/cvsuproot/pgsql/src/include/catalog/pg_language.h,v
retrieving revision 1.29
diff -c -r1.29 pg_language.h
*** src/include/catalog/pg_language.h   5 Jan 2007 22:19:52 -0000       1.29
--- src/include/catalog/pg_language.h   25 Jan 2007 03:51:13 -0000
***************
*** 36,41 ****
--- 36,42 ----
  CATALOG(pg_language,2612)
  {
        NameData        lanname;
+       Oid                     lanowner;               /* language owner */
        bool            lanispl;                /* Is a procedural language */
        bool            lanpltrusted;   /* PL is trusted */
        Oid                     lanplcallfoid;  /* Call handler for PL */
***************
*** 54,79 ****
   *            compiler constants for pg_language
   * ----------------
   */
! #define Natts_pg_language                             6
  #define Anum_pg_language_lanname              1
! #define Anum_pg_language_lanispl              2
! #define Anum_pg_language_lanpltrusted         3
! #define Anum_pg_language_lanplcallfoid                4
! #define Anum_pg_language_lanvalidator         5
! #define Anum_pg_language_lanacl                       6
  
  /* ----------------
   *            initial contents of pg_language
   * ----------------
   */
  
! DATA(insert OID = 12 ( "internal" f f 0 2246 _null_ ));
  DESCR("Built-in functions");
  #define INTERNALlanguageId 12
! DATA(insert OID = 13 ( "c" f f 0 2247 _null_ ));
  DESCR("Dynamically-loaded C functions");
  #define ClanguageId 13
! DATA(insert OID = 14 ( "sql" f t 0 2248 _null_ ));
  DESCR("SQL-language functions");
  #define SQLlanguageId 14
  
--- 55,81 ----
   *            compiler constants for pg_language
   * ----------------
   */
! #define Natts_pg_language                             7
  #define Anum_pg_language_lanname              1
! #define Anum_pg_language_lanowner             2
! #define Anum_pg_language_lanispl              3
! #define Anum_pg_language_lanpltrusted         4
! #define Anum_pg_language_lanplcallfoid                5
! #define Anum_pg_language_lanvalidator         6
! #define Anum_pg_language_lanacl                       7
  
  /* ----------------
   *            initial contents of pg_language
   * ----------------
   */
  
! DATA(insert OID = 12 ( "internal" PGUID f f 0 2246 _null_ ));
  DESCR("Built-in functions");
  #define INTERNALlanguageId 12
! DATA(insert OID = 13 ( "c" PGUID f f 0 2247 _null_ ));
  DESCR("Dynamically-loaded C functions");
  #define ClanguageId 13
! DATA(insert OID = 14 ( "sql" PGUID f t 0 2248 _null_ ));
  DESCR("SQL-language functions");
  #define SQLlanguageId 14
  
Index: src/include/catalog/pg_pltemplate.h
===================================================================
RCS file: 
/data/local/jeremyd/postgres/cvsuproot/pgsql/src/include/catalog/pg_pltemplate.h,v
retrieving revision 1.3
diff -c -r1.3 pg_pltemplate.h
*** src/include/catalog/pg_pltemplate.h 5 Jan 2007 22:19:53 -0000       1.3
--- src/include/catalog/pg_pltemplate.h 25 Jan 2007 01:36:57 -0000
***************
*** 37,42 ****
--- 37,43 ----
  {
        NameData        tmplname;               /* name of PL */
        bool            tmpltrusted;    /* PL is trusted? */
+       bool            tmpldbaallowed; /* PL is installable by db owner? */
        text            tmplhandler;    /* name of call handler function */
        text            tmplvalidator;  /* name of validator function, or NULL 
*/
        text            tmpllibrary;    /* path of shared library */
***************
*** 54,66 ****
   *            compiler constants for pg_pltemplate
   * ----------------
   */
! #define Natts_pg_pltemplate                                   6
  #define Anum_pg_pltemplate_tmplname                   1
  #define Anum_pg_pltemplate_tmpltrusted                2
! #define Anum_pg_pltemplate_tmplhandler                3
! #define Anum_pg_pltemplate_tmplvalidator      4
! #define Anum_pg_pltemplate_tmpllibrary                5
! #define Anum_pg_pltemplate_tmplacl                    6
  
  
  /* ----------------
--- 55,68 ----
   *            compiler constants for pg_pltemplate
   * ----------------
   */
! #define Natts_pg_pltemplate                                   7
  #define Anum_pg_pltemplate_tmplname                   1
  #define Anum_pg_pltemplate_tmpltrusted                2
! #define Anum_pg_pltemplate_tmpldbaallowed     3
! #define Anum_pg_pltemplate_tmplhandler                4
! #define Anum_pg_pltemplate_tmplvalidator      5
! #define Anum_pg_pltemplate_tmpllibrary                6
! #define Anum_pg_pltemplate_tmplacl                    7
  
  
  /* ----------------
***************
*** 68,78 ****
   * ----------------
   */
  
! DATA(insert ( "plpgsql"               t "plpgsql_call_handler" 
"plpgsql_validator" "$libdir/plpgsql" _null_ ));
! DATA(insert ( "pltcl"         t "pltcl_call_handler" _null_ "$libdir/pltcl" 
_null_ ));
! DATA(insert ( "pltclu"                f "pltclu_call_handler" _null_ 
"$libdir/pltcl" _null_ ));
! DATA(insert ( "plperl"                t "plperl_call_handler" 
"plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plperlu"               f "plperl_call_handler" 
"plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plpythonu"     f "plpython_call_handler" _null_ 
"$libdir/plpython" _null_ ));
  
  #endif   /* PG_PLTEMPLATE_H */
--- 70,80 ----
   * ----------------
   */
  
! DATA(insert ( "plpgsql"               t t "plpgsql_call_handler" 
"plpgsql_validator" "$libdir/plpgsql" _null_ ));
! DATA(insert ( "pltcl"         t t "pltcl_call_handler" _null_ "$libdir/pltcl" 
_null_ ));
! DATA(insert ( "pltclu"                f t "pltclu_call_handler" _null_ 
"$libdir/pltcl" _null_ ));
! DATA(insert ( "plperl"                t t "plperl_call_handler" 
"plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plperlu"               f t "plperl_call_handler" 
"plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plpythonu"     f t "plpython_call_handler" _null_ 
"$libdir/plpython" _null_ ));
  
  #endif   /* PG_PLTEMPLATE_H */
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to