Thread: default database creation with initdb
As per discussion on -hackers the attached patch creates the 'default'
database at initdb time as a default target for initial connections to
keep template1 free from connections and available as template source.
I consider this DB a system object, so it's created before
make_template0 sets the last_system_oid (wondering why template0 isn't
considered a system db too)
Regards,
Andreas
Index: src/bin/initdb/initdb.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.83
diff -u -r1.83 initdb.c
--- src/bin/initdb/initdb.c 30 Apr 2005 08:08:51 -0000 1.83
+++ src/bin/initdb/initdb.c 18 Jun 2005 08:37:16 -0000
@@ -177,6 +177,7 @@
static void set_info_version(void);
static void setup_schema(void);
static void vacuum_db(void);
+static void make_default(void);
static void make_template0(void);
static void trapsig(int signum);
static void check_ok(void);
@@ -1828,6 +1829,38 @@
}
/*
+ * copy template1 to pg_system
+ */
+static void
+make_default(void)
+{
+ PG_CMD_DECL;
+ char **line;
+ static char *pg_system_setup[] = {
+ "CREATE DATABASE \"default\";\n",
+ "REVOKE CREATE,TEMPORARY ON DATABASE \"default\" FROM public;\n",
+ NULL
+ };
+
+ fputs(_("copying template1 to default ... "), stdout);
+ fflush(stdout);
+
+ snprintf(cmd, sizeof(cmd),
+ "\"%s\" %s template1 >%s",
+ backend_exec, backend_options,
+ DEVNULL);
+
+ PG_CMD_OPEN;
+
+ for (line = pg_system_setup; *line; line++)
+ PG_CMD_PUTS(*line);
+
+ PG_CMD_CLOSE;
+
+ check_ok();
+}
+
+/*
* copy template1 to template0
*/
static void
@@ -2606,6 +2639,8 @@
vacuum_db();
+ make_default();
+
make_template0();
if (authwarning != NULL)
Umm. Tiny item, but your comment still refers to the database as pg_system ;-) //Magnus > -----Original Message----- > From: pgsql-patches-owner@postgresql.org > [mailto:pgsql-patches-owner@postgresql.org] On Behalf Of Andreas Pflug > Sent: Saturday, June 18, 2005 10:42 AM > To: PostgreSQL-patches > Subject: [PATCHES] default database creation with initdb > > As per discussion on -hackers the attached patch creates the > 'default' > database at initdb time as a default target for initial > connections to keep template1 free from connections and > available as template source. > > I consider this DB a system object, so it's created before > make_template0 sets the last_system_oid (wondering why > template0 isn't considered a system db too) > > Regards, > Andreas >
Magnus Hagander wrote:
> Umm. Tiny item, but your comment still refers to the database as
> pg_system ;-)
:-)
Regards,
Andreas
Index: src/bin/initdb/initdb.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.83
diff -u -r1.83 initdb.c
--- src/bin/initdb/initdb.c 30 Apr 2005 08:08:51 -0000 1.83
+++ src/bin/initdb/initdb.c 18 Jun 2005 08:54:07 -0000
@@ -177,6 +177,7 @@
static void set_info_version(void);
static void setup_schema(void);
static void vacuum_db(void);
+static void make_default(void);
static void make_template0(void);
static void trapsig(int signum);
static void check_ok(void);
@@ -1828,6 +1829,38 @@
}
/*
+ * copy template1 to default
+ */
+static void
+make_default(void)
+{
+ PG_CMD_DECL;
+ char **line;
+ static char *default_setup[] = {
+ "CREATE DATABASE \"default\";\n",
+ "REVOKE CREATE,TEMPORARY ON DATABASE \"default\" FROM public;\n",
+ NULL
+ };
+
+ fputs(_("copying template1 to default ... "), stdout);
+ fflush(stdout);
+
+ snprintf(cmd, sizeof(cmd),
+ "\"%s\" %s template1 >%s",
+ backend_exec, backend_options,
+ DEVNULL);
+
+ PG_CMD_OPEN;
+
+ for (line = default_setup; *line; line++)
+ PG_CMD_PUTS(*line);
+
+ PG_CMD_CLOSE;
+
+ check_ok();
+}
+
+/*
* copy template1 to template0
*/
static void
@@ -2606,6 +2639,8 @@
vacuum_db();
+ make_default();
+
make_template0();
if (authwarning != NULL)
On Saturday 18 June 2005 04:55, Andreas Pflug wrote:
> Magnus Hagander wrote:
> > Umm. Tiny item, but your comment still refers to the database as
> > pg_system ;-)
> >
What is the purpose of this database? A generalized, shared resource for tool
makers and add-on packages to store information in PostgreSQL, or a working
database that is usable (and to be used) out of the box for new users? I
really don't think we want the latter... I can see users connecting via psql
and then playing around with different add/create type statements. It is all
too common a question from newbies... "does postgresql have a default
database to get started with?" They'll see this database and begin creating
schema and using this as thier main database, and I think we ought to avoid
that. If people don't like pg_system, pg_addons seem like a much safer name
to go with imho.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> + "CREATE DATABASE \"default\";\n",
> + "REVOKE CREATE,TEMPORARY ON DATABASE \"default\" FROM public;\n",
Uh, why the rights revocation? That makes the thing essentially useless
... except to superusers, who will not be affected anyway. I don't
think this is a sane default.
In any case, fixing initdb for this is trivial. I count something north
of 160 other references to template1 in the current sources, scattered
across 60+ files. Every one of those has to be looked at and possibly
changed.
regards, tom lane
Robert Treat wrote: >On Saturday 18 June 2005 04:55, Andreas Pflug wrote: > > >>Magnus Hagander wrote: >> >> >>>Umm. Tiny item, but your comment still refers to the database as >>>pg_system ;-) >>> >>> >>> > >What is the purpose of this database? A generalized, shared resource for tool >makers and add-on packages to store information in PostgreSQL, or a working >database that is usable (and to be used) out of the box for new users? I >really don't think we want the latter... I can see users connecting via psql >and then playing around with different add/create type statements. It is all >too common a question from newbies... "does postgresql have a default >database to get started with?" > A sample DB would be nice... > They'll see this database and begin creating >schema and using this as thier main database, and I think we ought to avoid >that. If people don't like pg_system, pg_addons seem like a much safer name >to go with imho. > > To avoid people creating stuff in it, the patch revokes CREATE from public (won't help against admins), which in turn Tom thinks is "not a sane default". Regards, Andreas
On Sat, Jun 18, 2005 at 09:27:49 -0400, Robert Treat <xzilla@users.sourceforge.net> wrote: > On Saturday 18 June 2005 04:55, Andreas Pflug wrote: > > Magnus Hagander wrote: > > > Umm. Tiny item, but your comment still refers to the database as > > > pg_system ;-) > > > > > What is the purpose of this database? A generalized, shared resource for tool > makers and add-on packages to store information in PostgreSQL, or a working > database that is usable (and to be used) out of the box for new users? I > really don't think we want the latter... I can see users connecting via psql > and then playing around with different add/create type statements. It is all > too common a question from newbies... "does postgresql have a default > database to get started with?" They'll see this database and begin creating > schema and using this as thier main database, and I think we ought to avoid > that. If people don't like pg_system, pg_addons seem like a much safer name > to go with imho. I believe the intention is that things that need to connect to some database to do their work (e.g. psql -l, createuser) will connect to that database. createdb will still connect to template1, but now will be less likely to have a conflict with another user being connected to template1 at the same time. I didn't check the patch to see if the behavior of the psql -l and createuser were changed or if just the initdb behavior was changed. I don't think it will be a big deal if people put stuff in that database.