Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Bruce Momjian wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: 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? I think pg_dump pays attention to what schema the objects are in, and that's most likely creating them in PUBLIC. Try adding set search_path = pg_catalog. It's not impossible that we'll have to tweak pg_dump a bit; it's never had to deal with languages that shouldn't be dumped ... I found that pg_dump tests for pg_language.lanispl == true, which is true for all the stored procedure languages. I can easily special case plpgsql, or check for FirstNormalObjectId, though I don't see that used in pg_dump currently. A more difficult issue is whether we should preserve the fact that plpgsql was _removed_ in the pg_dump output, i.e, if someone removes plpgsql from a database, do we issue a DROP LANGUAGE in pg_dump? I don't remember us having to deal with anything like this before. OK, the attached patch installs plpgsql by default from initdb, and supresses the dumping of CREATE LANGUAGE in 8.5 and in 8.3/8.4 if binary upgrade is used (because you know you are upgrading to a release that has plpgsql installed by default). The 8.3/8.4 is necessary so the schema load doesn't generate any errors and cause pg_migrator to exit. Applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
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?
I think pg_dump pays attention to what schema the objects are in,
and that's most likely creating them in PUBLIC. Try adding
set search_path = pg_catalog.
It's not impossible that we'll have to tweak pg_dump a bit; it's
never had to deal with languages that shouldn't be dumped ...
I found that pg_dump tests for pg_language.lanispl == true, which is
true for all the stored procedure languages. I can easily special case
plpgsql, or check for FirstNormalObjectId, though I don't see that used
in pg_dump currently.
A more difficult issue is whether we should preserve the fact that
plpgsql was _removed_ in the pg_dump output, i.e, if someone removes
plpgsql from a database, do we issue a DROP LANGUAGE in pg_dump? I
don't remember us having to deal with anything like this before.
OK, the attached patch installs plpgsql by default from initdb, and
supresses the dumping of CREATE LANGUAGE in 8.5 and in 8.3/8.4 if binary
upgrade is used (because you know you are upgrading to a release that
has plpgsql installed by default). The 8.3/8.4 is necessary so the
schema load doesn't generate any errors and cause pg_migrator to exit.
--
Bruce Momjian br...@momjian.ushttp://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.333
diff -c -c -r1.333 installation.sgml
*** doc/src/sgml/installation.sgml 15 Dec 2009 22:59:53 - 1.333
--- doc/src/sgml/installation.sgml 17 Dec 2009 23:35:36 -
***
*** 2266,2279
is commandcreatelang/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 address is 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
--- 2266,2279
is commandcreatelang/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 address is 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.178
diff -c -c -r1.178 initdb.c
*** src/bin/initdb/initdb.c 11 Dec 2009 03:34:56 - 1.178
--- src/bin/initdb/initdb.c 17 Dec 2009 23:35:36 -
***
*** 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);
***
*** 1894,1899
--- 1895,1925
}
/*
+ * 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
***
*** 3126,3131
--- 3152,3159
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Hi, Le 11 déc. 2009 à 01:43, Bruce Momjian a écrit : Would you be up for writing the extension facility? Uh, well, I need to help with the patch commit process at this point --- if I find I have extra time, I could do it. I will keep this in mind. If you ever find the time to do it, that would be excellent! The extension facility is on the top list of Josh Berkus missing things we'll have to provide soon (or something) and a very annoying missing feature, the last stone of the high praised extensibility of PostgreSQL. http://it.toolbox.com/blogs/database-soup/postgresql-development-priorities-31886 It could also means that pg_migrator would have an easier time handling user data types etc, if extension authors are able to implement the hooks to call to migrate their data from previous to next major version ondisk format. Anyway, thanks for considering it! -- dim PS: of course I've developed some ideas of how I'd like this to work and some use cases too, so bug me on IRC or whatever for details etc :) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: 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? I think pg_dump pays attention to what schema the objects are in, and that's most likely creating them in PUBLIC. Try adding set search_path = pg_catalog. It's not impossible that we'll have to tweak pg_dump a bit; it's never had to deal with languages that shouldn't be dumped ... I found that pg_dump tests for pg_language.lanispl == true, which is true for all the stored procedure languages. I can easily special case plpgsql, or check for FirstNormalObjectId, though I don't see that used in pg_dump currently. A more difficult issue is whether we should preserve the fact that plpgsql was _removed_ in the pg_dump output, i.e, if someone removes plpgsql from a database, do we issue a DROP LANGUAGE in pg_dump? I don't remember us having to deal with anything like this before. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Bruce Momjian br...@momjian.us writes: A more difficult issue is whether we should preserve the fact that plpgsql was _removed_ in the pg_dump output, i.e, if someone removes plpgsql from a database, do we issue a DROP LANGUAGE in pg_dump? I don't remember us having to deal with anything like this before. The public schema is a precedent. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Tom Lane t...@sss.pgh.pa.us writes: It's not impossible that we'll have to tweak pg_dump a bit; it's never had to deal with languages that shouldn't be dumped ... Ah, the best would be to have extensions maybe. Then you could do this in initdb, filling in template0: CREATE EXTENSION plpgsql ...; Then at createdb time, what would become automatic is: INSTALL EXTENSION plpgsql; And that's it. pg_dump would now about extensions and only issues this latter statement in its dump. Bruce, there are some mails in the archive with quite advanced design proposal that has been discussed and not objected to, and I even provided a rough sketch of how I wanted to attack the problem. http://archives.postgresql.org/pgsql-hackers/2009-06/msg01281.php http://archives.postgresql.org/pgsql-hackers/2009-07/msg01425.php http://archives.postgresql.org/pgsql-hackers/2009-07/msg01468.php The major version dependant SQL code is now much less of a problem than before because we have inline DO statements. So you don't need to create a function for this anymore. Real life kept me away from having the time to prepare the code patch, and I don't think that will change a bit in the 8.5 release cycle, whatever my hopes were earlier this year. But having everyone talk about the feature and come to an agreement as to what it should provide and how was the hard part of it, I think, and is now about done. Would you be up for writing the extension facility? Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Dimitri Fontaine wrote: Tom Lane t...@sss.pgh.pa.us writes: It's not impossible that we'll have to tweak pg_dump a bit; it's never had to deal with languages that shouldn't be dumped ... Ah, the best would be to have extensions maybe. Then you could do this in initdb, filling in template0: CREATE EXTENSION plpgsql ...; Then at createdb time, what would become automatic is: INSTALL EXTENSION plpgsql; And that's it. pg_dump would now about extensions and only issues this latter statement in its dump. Bruce, there are some mails in the archive with quite advanced design proposal that has been discussed and not objected to, and I even provided a rough sketch of how I wanted to attack the problem. http://archives.postgresql.org/pgsql-hackers/2009-06/msg01281.php http://archives.postgresql.org/pgsql-hackers/2009-07/msg01425.php http://archives.postgresql.org/pgsql-hackers/2009-07/msg01468.php The major version dependant SQL code is now much less of a problem than before because we have inline DO statements. So you don't need to create a function for this anymore. Real life kept me away from having the time to prepare the code patch, and I don't think that will change a bit in the 8.5 release cycle, whatever my hopes were earlier this year. But having everyone talk about the feature and come to an agreement as to what it should provide and how was the hard part of it, I think, and is now about done. Would you be up for writing the extension facility? Uh, well, I need to help with the patch commit process at this point --- if I find I have extra time, I could do it. I will keep this in mind. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Tom Lane wrote:
Andrew Dunstan and...@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 br...@momjian.ushttp://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 - 1.331
--- doc/src/sgml/installation.sgml 9 Dec 2009 02:02:33 -
***
*** 2262,2275
is commandcreatelang/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 address is 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 commandcreatelang/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 address is 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 - 1.177
--- src/bin/initdb/initdb.c 9 Dec 2009 02:02:36 -
***
*** 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
***
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Bruce Momjian br...@momjian.us writes: 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? I think pg_dump pays attention to what schema the objects are in, and that's most likely creating them in PUBLIC. Try adding set search_path = pg_catalog. It's not impossible that we'll have to tweak pg_dump a bit; it's never had to deal with languages that shouldn't be dumped ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Tom Lane t...@sss.pgh.pa.us writes: Right, just like every other thing that's pre-installed. If a particular installation wishes to let individual DB owners control this, the superuser can drop plpgsql from template1. It's not apparent to me why we need to allow non-superusers to override the project's decisions about what should be installed by default. I guess it boils down to being nice to hosting platforms, where they will want to give as much power as can be given to database owners without having those hosted people be superusers. So should the decision to remove plpgsql be on the hosting platform hands or the hosted database owner? Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Dimitri Fontaine dfonta...@hi-media.com writes: So should the decision to remove plpgsql be on the hosting platform hands or the hosted database owner? Why not? If they really want to prohibit use of a feature the upstream project has decided should be standard, that's their privilege. The argument against seems to be basically this should work exactly like it did before, but if that's the standard then we can never have plpgsql installed by default at all. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Tom Lane t...@sss.pgh.pa.us writes: Why not? If they really want to prohibit use of a feature the upstream project has decided should be standard, that's their privilege. Well, I guess they could also automate their database creation to fix the privileges and assign the ownership of the language to the owner of the database. Then whether or not to have plpgsql there is up to the owner. For non-hosted environments, you always want to tweak some things, like installing plpgsql in the first place. So... The argument against seems to be basically this should work exactly like it did before, but if that's the standard then we can never have plpgsql installed by default at all. Don't get me wrong, I'm all for having plpgsql installed by default. I though we were talking about how to provide that and trying to decide if having to be superuser to drop plpgsql after having created the database is blocking the way forward, knowing than installing the language only requires being database owner. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Tom == Tom Lane t...@sss.pgh.pa.us writes: Andrew Dunstan and...@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 Tom Hm, I think that's only a problem if we define it to be a Tom problem, and I'm not sure it's necessary to do so. Currently, Tom access to PL languages is controlled by superusers. You are Tom suggesting that if plpgsql is installed by default, then access Tom to it should be controlled by non-superuser DB owners instead. Currently, a non-superuser db owner can install plpgsql, and having installed it, can DROP it or grant/revoke access to it: test= create language plpgsql; CREATE LANGUAGE test= revoke usage on language plpgsql from public; REVOKE test= drop language plpgsql; DROP LANGUAGE The complaint is that if plpgsql is installed by default, then it will be owned by postgres rather than by the db owner, who will then not be able to drop it or use grant/revoke on it. (This only became an issue since the ability for non-superuser DB owners to install languages from pltemplate was added.) -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Andrew Gierth and...@tao11.riddles.org.uk writes: Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom Hm, I think that's only a problem if we define it to be a Tom problem, and I'm not sure it's necessary to do so. The complaint is that if plpgsql is installed by default, then it will be owned by postgres rather than by the db owner, who will then not be able to drop it or use grant/revoke on it. Right, just like every other thing that's pre-installed. If a particular installation wishes to let individual DB owners control this, the superuser can drop plpgsql from template1. It's not apparent to me why we need to allow non-superusers to override the project's decisions about what should be installed by default. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default
Andrew Dunstan and...@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. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
