[Date Prev][Date Next] [Thread Prev][Thread Next] [Date Index] [Thread Index]

Re: RFC: common database policy/infrastracture

First of all, this package would be a God-send for me (see below)
Note that 'wwwconfig-common' already contains most of the needed infrastructure... but it is too php-oriented. Splitting it in purely Apache/PHP-oriented scripts (which would remain as wwwconfig-common) and a new 'dbconfig-common' package would probably be in order.

Please note that i find the 'database-common' name choice a very unfortunate one...it would surely cause confusion for the end user... dbconfig-common has none of these problems.

Oliver Elphick wrote:

On Mon, 2004-10-18 at 08:19, Javier Fernández-Sanguino Peña wrote:
I'm missing some "Best practice" on how to setup the database itself. That is, how to setup the tables (indexes, whatever...) that the application will use from the database and, maybe, even some initial data in some of the tables.

I would suggest something like this:

1. Identify the server, database type (PostgreSQL, MySQL, Firebird,
etc.) and access method (UNIX socket, TCP/IP, TCP/IP with SSL)

2. If your package needs to create a user or database, identify the
database administrator's id and password; note that this may include
doing "su -c postgres" or similar.
Almost ok, except for the fact that most of us prefer to ask the database administrator's password(and usually name) during postint, so that it can be forgotten( db_reset()'d ). This passwords are(and should) usually only necessary to setup the database, while all the database-related operations from the packaged program are done via an unprivileged user which was setup precisely for this purpose.

3. Determine and, if necessary, create the database user which will own
your package's database and other DB objects.  If your chosen server is
remote, or the server package's policy forbids application packages to
change the authentication setup, this may require manual intervention by
a database administrator.  In that case, your package will be left
installed but not yet usable - any attempt to use it should return a
message saying what steps are needed to get it working.

4. For PostgreSQL, the preferred method of supplying a password from a
script is by creating ~HOME/.pgpass (perms=0600) and specifying the
password there as described in the PostgreSQL manual.  If
password-authenticated access to the database is required, the
installation should create this file for the duration of the
installation only; if it already exists with different contents, it
should be moved aside.  The installation script should use trap
statements to ensure that everything is put back as it was at the
termination of the script.

5. If the database does not already exist,

  a. Create the database, assigning it to the ownership of the
     chosen database user.  For PostgreSQL:

        createdb -O <owner> [-E <encoding>] <database_name>

  b. As the owner, run an SQL script (appropriate to the kind of
     database) to create the schema and populate it.  For PostgreSQL:

        psql -d <database_name> -f <script_file> -e [-h <host>]
            [-p <port>] -U <database_owner>


        su - <database_owner> -c "psql -d <database_name>
                                       -f <script_file> -e [-h <host>]
                                      [-p <port>]"

     The latter is preferable if the system user <database_owner>
     exists, because it matches PostgreSQL's default authentication

     At this point, database authentication may forbid the execution of
     the script; this again may need manual intervention by the
     database administrator.

6. If the database does exist,

  a.  As the owner, run any script necessary to update the database
      objects. (The PostgreSQL script command is as above; the same
      caveats apply, though one would expect that password access as
      database_owner would already be set up and would therefore

If the database supports SQL transactions (as PostgreSQL does), SQL
scripts should do everything inside a transaction, so that either all
objects are successfully created and populated or else there is no
change at all to the database.
Ok... assuming this applies to user setup also :-?

One common issue is that the application depends on that in order to work and it's not done automatically. Maybe the user is prompted to do it but he might be unable to do so until the installation is finished. For an example of this problem see #205683 (and #219696, #265735, #265878).

The problem there is that the prompting is being done in the preinst,
which is useless, because the files referred to do not yet exist.  That
is not specifically a database-using problem; it is simply a packaging
error.  That package should hold all the information it needs in its
preinst script, or else not attempt to do things in the preinst.
I think this package we are proposing should contain all the Debconf templates (to avoid duplicates in all packages, as it is now) and *routines* which perform the needed tasks as fail-safely as possible... packages would then only need to be modified to make use of the new infrastructure; that is, use database-common's templates from config(after appropiate substitutions and checking) and use the provided routines from postinst to do the actual work.

It is, however, quite possible for the application installation to fail
because of circumstances beyond the packaging system's ability to
manage.  Therefore, the package installation scripts need to be able to
report what further steps are needed in order for installation to be
...which can be done with a "note" type template where the information would be substituted.... and this package should provide quite a lot of pre-defined exit-actions for this purpose (barring actual path information or other details, which would be substituted in as appropiate)

It might be good to provide a common mechanism to setup the database so
that users are not asked to run an SQL script under /usr/share/XXX (usually
doc/package/examples). Maybe even defining a common location for these
(/usr/share/db-setup/PACKAGE/XXXX.{mysql,pgsql}?). Notice that the SQL
script that needs to be run might difer between RDBMS.

Almost certainly it will.  See above for the commands to run.
I have had no problems with this... i maintain 2 packages with precisely these needs... i simply maintain 3 "flavors" of the needed executables[which are linked to the corresponding client libs] and ship postinst scripts suited for each particular database with the corresponding "flavor"... where one can not afford such luxury (i.e., a PHP application) a template asking for the desired "flavor"(maybe with the detected available servers marked in another color and the options for which no client-libraries have been found greyed-out) would be in order.

( sorry for the "density" of these last paragraphs )


Reply to: