Re: [GENERAL] Installing PL/pgSQL by default - Mailing list pgsql-hackers

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
> > Before we go too far with this, I'd like to know how we will handle the
> > problems outlined here:
> > <http://archives.postgresql.org/pgsql-hackers/2008-02/msg00916.php>
>
> Hm, I think that's only a problem if we define it to be a problem,
> and I'm not sure it's necessary to do so.  Currently, access to PL
> languages is controlled by superusers.  You are suggesting that if
> plpgsql is installed by default, then access to it should be controlled
> by non-superuser DB owners instead.  Why do we have to move the
> goalposts in that direction?  It's not like we expect that DB owners
> should control access to other built-in facilities, like int8 or
> pg_stat_activity for example.  The argument against having plpgsql
> always available is essentially one of security risks, and I would
> expect that most installations think that security risks are to be
> managed by superusers.

I installed PL/pgSQL by default via initdb with the attached patch.  The
only problem is that pg_dump still dumps out the language creation:

    CREATE PROCEDURAL LANGUAGE plpgsql;
    ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

What is odd is that I used the same process that initdb uses to create
other objects.  Does anyone know why this is happening?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/installation.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/installation.sgml,v
retrieving revision 1.331
diff -c -c -r1.331 installation.sgml
*** doc/src/sgml/installation.sgml    8 Dec 2009 20:08:30 -0000    1.331
--- doc/src/sgml/installation.sgml    9 Dec 2009 02:02:33 -0000
***************
*** 2262,2275 ****
       is <command>createlang</command> failing with unusual errors.
       For example, running as the owner of the PostgreSQL installation:
  <screen>
! -bash-3.00$ createlang plpgsql template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plpgsql.so": A memory
addressis not in the address space for the process. 
  </screen>
      Running as a non-owner in the group posessing the PostgreSQL
      installation:
  <screen>
! -bash-3.00$ createlang plpgsql template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plpgsql.so": Bad
address
  </screen>
       Another example is out of memory errors in the PostgreSQL server
       logs, with every memory allocation near or greater than 256 MB
--- 2262,2275 ----
       is <command>createlang</command> failing with unusual errors.
       For example, running as the owner of the PostgreSQL installation:
  <screen>
! -bash-3.00$ createlang plperl template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plperl.so": A memory
addressis not in the address space for the process. 
  </screen>
      Running as a non-owner in the group posessing the PostgreSQL
      installation:
  <screen>
! -bash-3.00$ createlang plperl template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plperl.so": Bad
address
  </screen>
       Another example is out of memory errors in the PostgreSQL server
       logs, with every memory allocation near or greater than 256 MB
Index: src/bin/initdb/initdb.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.177
diff -c -c -r1.177 initdb.c
*** src/bin/initdb/initdb.c    14 Nov 2009 15:39:36 -0000    1.177
--- src/bin/initdb/initdb.c    9 Dec 2009 02:02:36 -0000
***************
*** 176,181 ****
--- 176,182 ----
  static void setup_privileges(void);
  static void set_info_version(void);
  static void setup_schema(void);
+ static void load_plpgsql(void);
  static void vacuum_db(void);
  static void make_template0(void);
  static void make_postgres(void);
***************
*** 1893,1898 ****
--- 1894,1924 ----
  }

  /*
+  * load PL/pgsql server-side language
+  */
+ static void
+ load_plpgsql(void)
+ {
+     PG_CMD_DECL;
+
+     fputs(_("loading PL/pgSQL server-side language ... "), stdout);
+     fflush(stdout);
+
+     snprintf(cmd, sizeof(cmd),
+              "\"%s\" %s template1 >%s",
+              backend_exec, backend_options,
+              DEVNULL);
+
+     PG_CMD_OPEN;
+
+     PG_CMD_PUTS("CREATE LANGUAGE plpgsql;\n");
+
+     PG_CMD_CLOSE;
+
+     check_ok();
+ }
+
+ /*
   * clean everything up in template1
   */
  static void
***************
*** 3125,3130 ****
--- 3151,3158 ----

      setup_schema();

+     load_plpgsql();
+
      vacuum_db();

      make_template0();
Index: src/test/regress/GNUmakefile
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/GNUmakefile,v
retrieving revision 1.79
diff -c -c -r1.79 GNUmakefile
*** src/test/regress/GNUmakefile    26 Oct 2009 21:11:22 -0000    1.79
--- src/test/regress/GNUmakefile    9 Dec 2009 02:02:37 -0000
***************
*** 138,144 ****
  ## Run tests
  ##

! pg_regress_call = ./pg_regress --inputdir=$(srcdir) --dlpath=. --multibyte=$(MULTIBYTE) --load-language=plpgsql
$(NOLOCALE)

  check: all
      $(pg_regress_call) --temp-install=./tmp_check --top-builddir=$(top_builddir)
--schedule=$(srcdir)/parallel_schedule$(MAXCONNOPT) $(TEMP_CONF) 
--- 138,144 ----
  ## Run tests
  ##

! pg_regress_call = ./pg_regress --inputdir=$(srcdir) --dlpath=. --multibyte=$(MULTIBYTE) $(NOLOCALE)

  check: all
      $(pg_regress_call) --temp-install=./tmp_check --top-builddir=$(top_builddir)
--schedule=$(srcdir)/parallel_schedule$(MAXCONNOPT) $(TEMP_CONF)