Debian Developers - RFC: common database policy/infrastracture

Author

RFC: common database policy/infrastracture

sean finney

2004-10-16, 5:59 pm

hey all,

for those who weren't following the previous thread[1], i've come up with
a rough draft of a "best practices" document for database based
applications. i'd greatly appreciate input, especially from the current
maintainers of database-using or database-server applications. the draft
is available at:

http://people.debian.org/seanius/po...app-policy.html

also, after much discussion in this thread and privately
with joey hess, i think the best approach of the options discussed
would be to provide debconf questions in a single run-time dependency
package via the debconf REGISTER method. it's not the best solution
(because of the risk of the questions being asked at postinst time),
but it should guaranteeably work, it's centralized, easily translatable,
and makes less work for folks who use it with their packages.

this package, "database-common", currently is only a collection of
normalized debconf templates. this is very much a work in progress,
but i think at this point it's worthwhile to make sure there's sufficient
interest as well as possibly get some help

deb http://people.debian.org/~seanius/policy/examples/ ./
deb-src http://people.debian.org/~seanius/policy/examples/ ./

what's lacking:

- the blocks of code for the .*config, .*inst and .*rm files, which
could be provided in a set of wrapper scripts to be sourced by the
individual packages.
- a demonstration package using this
- possibly a debhelper-like package to automatically add these blocks
of code

also,

- further amendments to the "best practices" document based on developer
feedback.
- resulting changes in packaging from said changes
- templates for pgsql and other database setups (currently only some
common templates and the mysql-centric ones)
- translations

so please, let me know what you think!

sean

[1] http://lists.debian.org/debian-deve...0/msg00340.html

--

Oliver Elphick

2004-10-17, 5:50 pm

On Sun, 2004-10-17 at 00:26, sean finney wrote:
> hey all,
>
> for those who weren't following the previous thread[1], i've come up with
> a rough draft of a "best practices" document for database based
> applications. i'd greatly appreciate input, especially from the current
> maintainers of database-using or database-server applications. the draft
> is available at:
>
> http://people.debian.org/seanius/po...app-policy.html

Thanks for this, Here's my first response.

I attach a diff on the templates file, including questions for a
PostgreSQL installation..

Bear in mind that users seeing these questions may not understand their
implications. Some of my additions are intended to help out such
people.

Template: database-common/database-type
Type: select

How will you determine what servers are available? A select list
implies that the config script has gathered the data from somewhere. If
database servers are on some other machine, how will you get them to be
represented on this list?

Maybe there needs to be some kind of advertising service, such as is
used by CUPS, that each database server will use to advertise its
existence and the services it can provide. Or maybe this field should
be free text, so that non-local servers can be specified.

(See below about combining this question with others.)

Choices: ${database_types}
_Description: What database type should ${pkg} use?

The data presented should say what version of the database server is
available (if more than one host is present), since some software will
require a particular release level. (For example, a package I am
developing at the moment will require PostgreSQL 8 as a minimum release
level.) Getting this data from a remote machine implies that there is
an enquiry service that can return it. Clearly, the config script
should be able to exclude servers that cannot offer the requried
facilities. If we are using remote services, we cannot depend on
package dependencies at this point, so this may be a time for error exit
with a recommendation for the installation of some particular server
package.

Template: database-common/mysql/app_user
_Description: What mysql username should ${pkg} use?

The description needs to make clear the distinction between database
users and system users. (They may be the same but they need not be.)

We also need to make it clear that the app-user is the user which will
own the new database and the objects in it and will have the ability to
alter any data in it.

Whatever its priority, this question would not appear if the database
has already been created, since the database owner would already be
determined.

Template: database-common/mysql/app_password
_Description: What mysql password should ${pkg} use?

This would need to be provided even if the package already exists and
has created a database, if the app_user user is needed to make any
changes to an existing database.

Template: database-common/mysql/host
Type: select
Choices: local socket
_Description: What mysql host should ${pkg} use?
Please provide the hostname of the database server for ${pkg}.

Template: database-common/mysql/host_new
Type: string
_Description: What mysql host should ${pkg} use?
Please provide the hostname of the database server for ${pkg}.

Why two of these questions? Does the choice of local or socket mean
localhost or UNIX socket? (Does mysql offer that option?)

I think it would be better if the database discovery program presented
all this information and presented it together as the choices for
database-common/database-type. The question is really, which particular
database, at which release level and on which server, should hold the
data? Presenting all possible choices in one go seems easier to cope
with:

mysql 4.0 local
mysql 3.23 host2.local.net
postgresql 7.4 local
postgresql 8.0 devel.local.net
firebird 1.5 local

There may still be a choice of access method at this point. For
PostgreSQL, you may be able to use UNIX socket, clear TCP/IP or TCP/IP
with SSL, and these may have different authentication requirements
(password, ident, pam, kerberos).

Having once got all information necessary to connect to the selected
database server, the config script needs to attempt a connection to
discover if it will be able to complete the installation. This is by no
means guaranteed, since database access policies may preclude it and
need to be changed by the database administrator. As we have seen, the
server may not be on the local machine, so using root privilege to
override may not be possible.

Even if the server is on the local machine, I am opposed to having any
application package alter the database access policies. This is OK for
the installation of the server package itself, since it could not
otherwise be installed, but we should not assume that the system
administrator is the same as the database administrator; nor should we
allow an application package to make that assumption, since it might
then make changes to the access policies which would inadvertently open
other databases to unauthorised access. (This is already covered by
Debian policy, forbidding the alteration of conffiles, but I think it
should be stated explicitly.) I think it is appropriate for an access
failure at this stage to result in a message detailing what needs to be
done in the database server to permit installation to proceed.

Template: database-common/mysql/purge
Default: true
_Description: Do you want to purge your the mysql database for
${pkg}?

I think the default should be false. Destroying a database is a major
disaster if it is unintentional; we should never assist people to shoot
themselves in the foot.

Template: database-common/mysql/admin_user
_Description: What is the name of your database's administrative
user?

For PostgreSQL, this is "postgres"; the installation scripts have to do

su - postgres

in order to use it.

Template: database-common/mysql/admin_pw
_Description: What is the name^Wpassword of your database's
administrative user?

This and the app_user password questions need to be deregistered as soon
as a successful installation is completed; although the passwords.dat
part of the debconf database has 600 perms, cleartext passwords should
not be left lying around anywhere unless necessary.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================

"Trust in the LORD, and do good..." Psalms 37:3

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Oliver Elphick

2004-10-17, 5:50 pm

On Sun, 2004-10-17 at 21:41, Oliver Elphick wrote:
> I attach a diff on the templates file, including questions for a
> PostgreSQL installation..

Well, I do now.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================

"Trust in the LORD, and do good..." Psalms 37:3

sean finney

2004-10-17, 8:50 pm

hey oliver,

On Sun, Oct 17, 2004 at 09:41:36PM +0100, Oliver Elphick wrote:
> I attach a diff on the templates file, including questions for a
> PostgreSQL installation..

thanks!

> Bear in mind that users seeing these questions may not understand their
> implications. Some of my additions are intended to help out such
> people.

okay. part of the plan is to use different priorities for the
information in the script. for example, database name and user can
be priority low and automatically set.

> Template: database-common/database-type
> Type: select
>
> How will you determine what servers are available? A select list
> implies that the config script has gathered the data from somewhere. If
> database servers are on some other machine, how will you get them to be
> represented on this list?

ideally a common script could take care of this, and substitute the
choices in. the question database-type is for apps that support
multiple database backends (mysql, postgresql, etc). then there's
a question like:

Template: database-common/mysql/host
Type: select

(which should more appropriately be called "location" i guess) this
serves a default list and could also include a "new" option,
which would let someone input the next question:

Template: database-common/mysql/host_new
Type: string

the latter question could then contain a cache of the most recently
used responses from the defaults + the former.

> Maybe there needs to be some kind of advertising service, such as is
> used by CUPS, that each database server will use to advertise its
> existence and the services it can provide. Or maybe this field should
> be free text, so that non-local servers can be specified.

i think a common script could figure this out pretty easily, though
making sure that it runs at the right time (it's possible that it
may be configured before pgsql/mysql are unpacked) will be tricky.

> The data presented should say what version of the database server is
> available (if more than one host is present), since some software will
> require a particular release level. (For example, a package I am
> developing at the moment will require PostgreSQL 8 as a minimum release
> level.) Getting this data from a remote machine implies that there is
> an enquiry service that can return it. Clearly, the config script
> should be able to exclude servers that cannot offer the requried
> facilities. If we are using remote services, we cannot depend on
> package dependencies at this point, so this may be a time for error exit
> with a recommendation for the installation of some particular server
> package.

i think if the admin knows enough to be using a remote sql host, they're
probably okay to figure that out, but it would be nice. basically, we'd
need from each database-package maintainer a series of scripts to find
this information.

> Template: database-common/mysql/app_user
> _Description: What mysql username should ${pkg} use?
>
> The description needs to make clear the distinction between database
> users and system users. (They may be the same but they need not be.)

good point, i'll make a clarification on that.

> We also need to make it clear that the app-user is the user which will
> own the new database and the objects in it and will have the ability to
> alter any data in it.

definitely. i'll do the same for the admin ones too.

> Template: database-common/mysql/app_password
> _Description: What mysql password should ${pkg} use?
>
> This would need to be provided even if the package already exists and
> has created a database, if the app_user user is needed to make any
> changes to an existing database.

i think that we'd need to either ask the user if it's been flushed
from debconf, or a way to automatically grok it from whatever config
file it's stored in. or, use the admin user/pass for performing these
operations (though this could lead to more uncontained damage in a broken
script)

> Template: database-common/mysql/host
> Type: select
> Choices: local socket
> _Description: What mysql host should ${pkg} use?
> Please provide the hostname of the database server for ${pkg}.
>
> Template: database-common/mysql/host_new
> Type: string
> _Description: What mysql host should ${pkg} use?
> Please provide the hostname of the database server for ${pkg}.
>
> Why two of these questions? Does the choice of local or socket mean
> localhost or UNIX socket? (Does mysql offer that option?)

i think i explained this a bit earlier. this would effectively be a
list of cached URI's and a question to get a new one from the user.
not very clear outside of context, i guess.

> I think it would be better if the database discovery program presented
> all this information and presented it together as the choices for
> database-common/database-type. The question is really, which particular
> database, at which release level and on which server, should hold the
> data? Presenting all possible choices in one go seems easier to cope
> with:
>
> mysql 4.0 local
> mysql 3.23 host2.local.net
> PostgreSQL 7.4 local
> PostgreSQL 8.0 devel.local.net
> firebird 1.5 local

i think it would make the most sense this way, but it would
be too much for debconf to input this all at once. however,
what if debconf stored a list of completed server resources
used by other packages (like you provided above), and prompted
the user with that *first*, and then otherwise input the
questions with the seperate questions?

> There may still be a choice of access method at this point. For
> PostgreSQL, you may be able to use UNIX socket, clear TCP/IP or TCP/IP
> with SSL, and these may have different authentication requirements
> (password, ident, pam, kerberos).

so then we'd need more questions, and need to take this into mind
with whatever interface was provided.

> Having once got all information necessary to connect to the selected
> database server, the config script needs to attempt a connection to
> discover if it will be able to complete the installation. This is by no
> means guaranteed, since database access policies may preclude it and
> need to be changed by the database administrator. As we have seen, the
> server may not be on the local machine, so using root privilege to
> override may not be possible.

i think most of this could be handled under the hood, if each database
resource provider gives methods for getting this information. they
could be simple shell scripts that print information and return 0 for
success or some set of non-zero information-providing error values.

> Even if the server is on the local machine, I am opposed to having any
> application package alter the database access policies. This is OK for

what exactly do you mean by altering access policies? granting
privileges to a new user?

> Template: database-common/mysql/purge
> Default: true
> _Description: Do you want to purge your the mysql database for
> ${pkg}?
>
> I think the default should be false. Destroying a database is a major
> disaster if it is unintentional; we should never assist people to shoot
> themselves in the foot.

my thought was to have it default to false, but have it be a high
priority question, but don't strongly believe in this stance, so
i'll change that.

> Template: database-common/mysql/admin_user
> _Description: What is the name of your database's administrative
> user?
>
> For PostgreSQL, this is "postgres"; the installation scripts have to do
>
> su - postgres
>
> in order to use it.

so then we don't need to ask that question for pgsql

>
> Template: database-common/mysql/admin_pw
> _Description: What is the name^Wpassword of your database's
> administrative user?
>
> This and the app_user password questions need to be deregistered as soon
> as a successful installation is completed; although the passwords.dat
> part of the debconf database has 600 perms, cleartext passwords should
> not be left lying around anywhere unless necessary.

for the admin password, i agree. for the app_user password, i think
most apps are storing this password in a cleartext file for the
application to use (php web apps, for example). that's my opinion,
anyways.

thanks for all the input!

sean

--

Javier Fernández-Sanguino Peña

2004-10-18, 2:52 am

On Sat, Oct 16, 2004 at 07:26:10PM -0400, sean finney wrote:
> applications. i'd greatly appreciate input, especially from the current
> maintainers of database-using or database-server applications. the draft
> is available at:
>
> http://people.debian.org/seanius/po...app-policy.html

[That should be http://people.debian.org/~seanius/p...pp-policy.html,
BTW]

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.

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).

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.

Just my 2c

Javier

Oliver Elphick

2004-10-18, 2:52 am

On Mon, 2004-10-18 at 03:23, sean finney wrote:
....
>
> what exactly do you mean by altering access policies? granting
> privileges to a new user?

As the PostgreSQL package is delivered, it will only accept connections
where the database user name is the same as the system user name. So,
when I am logged in as 'olly', I can only connect to PostgreSQL as the
database user 'olly'. This means that web-based datbase applications
cannot work, because the connection is done by the system user
'www-data', but the user wants to run it as the database user 'olly';
that connection will be rejected.

In order to get a connection under those circumstances, the
authentication set-up for the database in question needs to be changed
to 'md5' (MD5-encrypted passwords). This is done by altering
/etc/postgresql/pg_hba.conf.

....
> for the admin password, i agree. for the app_user password, i think
> most apps are storing this password in a cleartext file for the
> application to use (php web apps, for example). that's my opinion,
> anyways.

That may differ per application. I would argue that it is very bad
security in all circumstances.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================

"Delight thyself also in the LORD; and he shall give
thee the desires of thine heart." Psalms 37:4

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Oliver Elphick

2004-10-18, 7:51 am

On Mon, 2004-10-18 at 08:19, Javier Fern=E1ndez-Sanguino Pe=F1a wrote:
>=20
> I'm missing some "Best practice" on how to setup the database itself. Tha=
t=20
> is, how to setup the tables (indexes, whatever...) that the application=20
> will use from the database and, maybe, even some initial data in some of=20
> 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.

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=3D0600) 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>

or

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
setup.

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
succeed.)

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.

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

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.

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
completed.

> 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 (usual=
ly
> 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.=20

Almost certainly it will. See above for the commands to run.

--=20
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D
"Delight thyself also in the LORD; and he shall give=20
thee the desires of thine heart." Psalms 37:4

sean finney

2004-10-19, 2:48 am

On Mon, Oct 18, 2004 at 09:19:28AM +0200, Javier Fernández-Sanguino Peña wrote:
> [That should be http://people.debian.org/~seanius/p...pp-policy.html,
> BTW]

oops!

> 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 hadn't addressed that yet because it's very specific to the database
type, and i was starting from a more general perspective. my approach
is to first decide on what the appropriate package behavior is, and then
sort out the technical details on how to get a package to behave
such.

> 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).

that's pretty funny, but exactly the kind of stuff we're trying to
avoid.

> 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.

in addition to the sql, the process of adding users or accessing the
database will differ too.

On Mon, Oct 18, 2004 at 11:30:59AM +0100, Oliver Elphick wrote:
> 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.

good idea.

> 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
> completed.

again, good idea.

On Mon, Oct 18, 2004 at 09:08:35AM +0100, Oliver Elphick wrote:
>
> That may differ per application. I would argue that it is very bad
> security in all circumstances.

well, i suppose we could always ask the admin if they want to store
the passwords...

sean

--

martin f krafft

2004-10-19, 5:53 pm

1. I would set the default of the "leave data after purge" to true
and give it priority high.

2. I am not sure what you mean by "maintainer script". prerm and
preinst should not ask questions. they are there to enact values
stored in the debconf cache. config, on the other hand, should
not make changes but only direct the user through the questions.

Thanks for diving into this topic! We definitely need a policy
here...

--
Please do not CC me when replying to lists; I read them!

.''`. martin f. krafft <madduck@debian.org>
: :' : proud Debian developer, admin, and user
`. `'`
`- Debian - when you have better things to do than fixing a system

Invalid/expired PGP subkeys? Use subkeys.pgp.net as keyserver!

sean finney

2004-10-19, 5:53 pm

On Tue, Oct 19, 2004 at 05:19:47PM +0200, martin f krafft wrote:
> 1. I would set the default of the "leave data after purge" to true
> and give it priority high.

i guess i had forgotten to update the page on this one (someone
else had requested this too). it should be updated now.

> 2. I am not sure what you mean by "maintainer script". prerm and
> preinst should not ask questions. they are there to enact values
> stored in the debconf cache. config, on the other hand, should
> not make changes but only direct the user through the questions.

i'm not quite sure what i meant either. i think it was a blurring
of two different things (when debconf should prompt and when the
info should be acted upon) and some late night typing to blame for
it. i've removed it entirely, since the information is now better
described in another part of the document, about which i'll be soon
sending an update.

sean

--

sean finney

2004-10-19, 5:53 pm

another update for those interested:

http://people.debian.org/~seanius/p...app-policy.html

deb http://people.debian.org/~seanius/policy/examples/ ./
deb-src http://people.debian.org/~seanius/policy/examples/ ./

i've incorporated many of the changes discussed in the related
d-d threads into the document, as well as into the database-common
skeleton package.

i've also added a section to the online document for a proposed
installation/removal process, which outlines in much more detail
the process of configuring/installing/upgrading/removing
databases. input is very much appreciated for this, as it will
be the foundation for the rest of the code.

> what's lacking:
> - the blocks of code for the .*config, .*inst and .*rm files, which
> could be provided in a set of wrapper scripts to be sourced by the
> individual packages.
> - possibly a debhelper-like package to automatically add these blocks
> of code

still needs to be done, though i think it's better if we can settle
on what exactly we want it to do before we go and do it.

> - a demonstration package using this

i'm about half way done with a proof of concept source package
that will generate a generic, mysql, and PostgreSQL version of
an example database application to test this stuff out.

> - further amendments to the "best practices" document based on developer
> feedback.

still accepting input!

> - templates for pgsql and other database setups (currently only some
> common templates and the mysql-centric ones)

thanks to oliver for the postgre stuff.

> - translations

probably better to wait until the questions solidify themselves
before we ask for help with this.

sean

--

Javier Fernández-Sanguino Peña

2004-10-20, 5:51 pm

> > - further amendments to the "best practices" document based on developer
>
> still accepting input!

You probably want to change this:

- leave data after purge? -> only ask during purge.
- back up database before upgrade? -> only ask during upgrades. user should
be notified where to find backups and possibly how to restore.
and other ' only ask during install'

All these questions should be made on configuration, but, obviously, only
once (when the package is first installed or on upgrade if the debconf
question was not previously there). Obviously, the _actions_, will be taken
on purge|postinst|preinst, but the question itself is made in advance the
first time the package is configured. You might want to clarify the table
and change 'only ask' to 'only done' (or acted upon).

Similarly, you want to change the pseucode in 'overview of
installation/removal process' since all the INPUT should be done in
configuration. For an example take a look at how the PostgreSQL does this
(purge on removal and backup on upgrade) for _all_ databases managed by it.

Regards

Javier

sean finney

2004-10-21, 5:57 pm

hi javier,

On Wed, Oct 20, 2004 at 05:05:18PM +0200, Javier Fernández-Sanguino Peña wrote:
> - leave data after purge? -> only ask during purge.
> - back up database before upgrade? -> only ask during upgrades. user should
> be notified where to find backups and possibly how to restore.
> and other ' only ask during install'
>
> All these questions should be made on configuration, but, obviously, only
> once (when the package is first installed or on upgrade if the debconf
> question was not previously there). Obviously, the _actions_, will be taken
> on purge|postinst|preinst, but the question itself is made in advance the
> first time the package is configured. You might want to clarify the table
> and change 'only ask' to 'only done' (or acted upon).

actually, this was intentional. it seemed to me that the general
consensus was that the purge question should be asked just before
they'd be acted upon. in the case of the purge question, the
you're not guaranteed that the answer to the question will
still be there at purge time depending on when it was configured
and what's happened to the debconf cache since. with the upgrade
question, it'd be just fine to ask in the pre-configuration, and
if you look in the pseudo code overview, that's exactly what i
proposed.

> Similarly, you want to change the pseucode in 'overview of
> installation/removal process' since all the INPUT should be done in
> configuration. For an example take a look at how the PostgreSQL does this
> (purge on removal and backup on upgrade) for _all_ databases managed by it.

i'll take a look at this, thanks.

sean

--

José Luis Tallón

2004-10-25, 7:49 am

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 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>
>
> or
>
> 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
> setup.
>
> 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
> succeed.)
>
>
>
>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 :-?

>
>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.
>
>
Hmmm.....
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
>completed.
>
>
....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)

>
>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 )

Best,
J.L.

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Andreas Tille

2004-11-20, 5:49 pm

On Sat, 16 Oct 2004, sean finney wrote:

> deb http://people.debian.org/~seanius/policy/examples/ ./
> deb-src http://people.debian.org/~seanius/policy/examples/ ./
> ...
> so please, let me know what you think!
I just wonder what to do exactly now if I start building a package
which could use all these database-common/* templates which
you are providing in the database-common package at this place?

Could you do an upload to experimental? My package will go to
experimental also and thus we could start testing this stuff in
a closed environment without doing any harm to other things.

Kind regards and thanks for your work

Andreas.

--
http://fam-tille.de

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

sean finney

2004-11-20, 5:49 pm

hey andreas,

On Sat, Nov 20, 2004 at 06:23:46PM +0100, Andreas Tille wrote:
> I just wonder what to do exactly now if I start building a package
> which could use all these database-common/* templates which
> you are providing in the database-common package at this place?

i've just returned from VAC, during which i've done quite a bit of
work, mostly on documentation. i'll try and update the page this weekend.

> Could you do an upload to experimental? My package will go to
> experimental also and thus we could start testing this stuff in
> a closed environment without doing any harm to other things.

this sounds like a good plan, i'll upload this after i do the update
and some final testing of the last set of changes i've made.

sean

--

Andreas Tille

2004-11-21, 8:47 pm

On Sat, 20 Nov 2004, sean finney wrote:

> work, mostly on documentation. i'll try and update the page this weekend.
Great.

I have two remarks while working through database-common-0.5:

1. IMHO it is not good to use the same default in the template for

Template: database-common/postgresql/dbname
Type: string
Default: ${pkg}

Template: database-common/postgresql/app-user
Type: string
Default: ${pkg}

There is no reason that ${pkg} is the default for dbname and
app-user. I'd suggest to use a different variable here. If not
at least the later template would be unuseable for me.

2. I did not found a template for the text which should be displayed
if the passwords for app-pass and app-pass2 should missmatch.
moreover it would probably a good idea to provide a shell function
which could be loaded by the configure script which consistently
handles password insertion.

Kind regards

Andreas.

--
http://fam-tille.de

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Andreas Tille

2004-11-22, 5:52 pm

On Sun, 21 Nov 2004, Andreas Tille wrote:

> I have two remarks while working through database-common-0.5:
>
> 1. IMHO it is not good to use the same default in the template for
>
> Template: database-common/postgresql/dbname
> Type: string
> Default: ${pkg}
>
> Template: database-common/postgresql/app-user
> Type: string
> Default: ${pkg}
>
> There is no reason that ${pkg} is the default for dbname and
> app-user. I'd suggest to use a different variable here. If not
> at least the later template would be unuseable for me.
>
> 2. I did not found a template for the text which should be displayed
> if the passwords for app-pass and app-pass2 should missmatch.
> moreover it would probably a good idea to provide a shell function
> which could be loaded by the configure script which consistently
> handles password insertion.
Third remark:

3a) Ask on install (priority high, default "yes":
Should the change of the server configuration be done automatically?
3b) Ask on purge (priority low, default "yes":
Should the configuration change be reverted?

Kind regards

Andreas.

--
http://fam-tille.de

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Andreas Tille

2004-12-02, 6:06 pm

On Sat, 20 Nov 2004, sean finney wrote:

> this sounds like a good plan, i'll upload this after i do the update
> and some final testing of the last set of changes i've made.
I've found your stuff at

http://people.debian.org/~seanius/policy/examples/

from today and I'm really impressed. My question is now whether
I can help anything to get PostgreSQL into the same state as
you prepared for mysql.

Kind regards

Andreas.

--
http://fam-tille.de

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Andreas Tille

2004-12-02, 6:06 pm

On Sat, 20 Nov 2004, sean finney wrote:
[vbcol=seagreen]
> On Sat, Nov 20, 2004 at 06:23:46PM +0100, Andreas Tille wrote:
More questions on your version 0.7:

- I asked in previous mail what to do for PostgreSQL support. While
having a quick view on the code I wonder if just using a variable
for the database server most of the code could be shared between
databases servers. Is this to naive?
- The application I want to package (GnuMed) has a bootstrapping
script using a configuration file which cares for installation
of several SQL code files in the correct sequence. This
bootstrap script has to know the database administrator password.
Formerly I did this the following way

...
db_get gnumed/pgsql/admin-pass
insert_passwd_into_temporary_config_file
$RET
bootstrap_gnumed_database --config temporary_config_file
rm temporary_config_file

My problem is now: How to address gnumed/pgsql/admin-pass in
your dbconfig-common framework?

Kind regards

Andreas.

--
http://fam-tille.de

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

sean finney

2004-12-02, 6:06 pm

hi andreas,

On Thu, Dec 02, 2004 at 10:32:50PM +0100, Andreas Tille wrote:
> More questions on your version 0.7:
>
> - I asked in previous mail what to do for PostgreSQL support. While
> having a quick view on the code I wonder if just using a variable
> for the database server most of the code could be shared between
> databases servers. Is this to naive?

most of the script stuff could be shared in between the two, yeah. i
designed the system such that it could eventually handle supporting
multiple database types, as well as packages that support multiple
database types themselves. then, i proceeded to start with what i know

currently, i'm using code from wwwconfig-common for doing the actual
db stuff, and there is pgsql support in that package, so i don't think
implementing pgsql support would be initially too hard.

> - The application I want to package (GnuMed) has a bootstrapping
> script using a configuration file which cares for installation
> of several SQL code files in the correct sequence. This
> bootstrap script has to know the database administrator password.
> Formerly I did this the following way

so dbconfig-common provides two hooks for running
install/bootstrap/upgrade code. the first is just a plain sql file,
the second is a shell script that can effectively do anything. i
have not tested the latter at all, but have written code that i
think will work. the dbconfig-common-using.html document provides
the details. however, in both cases i don't have it set up to run as
the database administrator, that was really a design choice and not a
limitation, and if it turns out it's not worth it i could have it run
as administrator instead.

> ...
> db_get gnumed/pgsql/admin-pass
> insert_passwd_into_temporary_config_file
$RET
> bootstrap_gnumed_database --config temporary_config_file
> rm temporary_config_file
>
> My problem is now: How to address gnumed/pgsql/admin-pass in
> your dbconfig-common framework?

the big question in my mind is what about the package needs
administrative access? this might stem from my not understanding
differences between mysql and postgresql.

sean

--

Andreas Tille

2004-12-03, 2:47 am

On Thu, 2 Dec 2004, sean finney wrote:

> most of the script stuff could be shared in between the two, yeah. i
> designed the system such that it could eventually handle supporting
> multiple database types, as well as packages that support multiple
> database types themselves. then, i proceeded to start with what i know

>
> currently, i'm using code from wwwconfig-common for doing the actual
> db stuff, and there is pgsql support in that package, so i don't think
> implementing pgsql support would be initially too hard.
Well, my question is basically: Should I just copy and search+replace
the mysql stuff to pgsql (which is error prone for future changes which
are quite possible) Or should we do something like inserting a variable
in all these scripts for the database and use a parameter or even do
something like

<script>.any
ln -s <script>.any <script>.mysql
ln -s <script>.any <script>.pgsql
ln -s <script>.any <script>.<whatever>

and obtain the database from $0 ?

> so dbconfig-common provides two hooks for running
> install/bootstrap/upgrade code. the first is just a plain sql file,
> the second is a shell script that can effectively do anything. i
> have not tested the latter at all, but have written code that i
> think will work. the dbconfig-common-using.html document provides
> the details. however, in both cases i don't have it set up to run as
> the database administrator, that was really a design choice and not a
> limitation, and if it turns out it's not worth it i could have it run
> as administrator instead.
I see no problem in providing a script there which runs as root and
does a
su - <dbadmin> real_bootstrap_script

>
> the big question in my mind is what about the package needs
> administrative access? this might stem from my not understanding
> differences between mysql and postgresql.
Well, I guess this might be solved by "su" as mentioned above, but
my problem is, who to access the values the user have input in the
debconf questions? I need to know some of these values in the
script.

Moreover I have a further problem: The database consists of a basic
server and than there are some tables for different locales which I
wnt to provide in different packages (because you need only one locale
package, for instance either for Germany or for Australia but not both).
The problem is, that I have to ensure that the common server is installed
*first* before any locale server would be installed (because the locale
server relay on the existance of certain tables.

However, I can not relay on the fact that dependent packages are installed
in a certain sequence thus it might occure that the postinst ot the
common server was not finished when the locale server postinst is running.
My idea for a solution in the locale postinst was:

1. check whether common server is existant (and has the right version)
2. a) if yes run locale bootstrap script
b) if no, touch /var/run/<packagename>
3. Install /etc/apt/apt.conf.d/90<packagename> :
DPkg {
Post-Invoke {"test -f /var/run/<packagename> && run_locale_server_bootstrap"}
}
4. DPkg Post-Invoke cares for locale servers if necessary

What do you think about this?

Kind regards

Andreas.

--
http://fam-tille.de

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Karsten Hilbert

2004-12-17, 7:52 am

> >but something to point out: dbconfig-common already performs the
Well, see, the GnuMed bootstrapping does a lot more advanced
things regarding "the database user". There's users and groups
with varying levels of access to the database.

However, if dbconfig-common creates the admin account we just
use it. We can also deal with the fact that the database is
pre-created, no problem.
[vbcol=seagreen]
> 2. From the application point of view I could ask people to
> include an option which prevents the bootstrap script from
> doing the work which is just done. I guess this is no big deal
> for the very responsive authors.
Agree. We might need to double-check but I think we are in
good shape on that already.
[vbcol=seagreen]
I think you need to be very clear on what you mean here. There
is an admin account for *PostgreSQL* (eg. postgres in most
cases) but there's also an admin account for the database
"gnumed" inside PostgreSQL (usually called gm-dbowner). The
latter one owns all objects in that database and grants rights
to other user groups.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Andreas Tille

2004-12-17, 7:52 am

On Fri, 17 Dec 2004, Karsten Hilbert wrote:

> Well, see, the GnuMed bootstrapping does a lot more advanced
> things regarding "the database user". There's users and groups
> with varying levels of access to the database.
>
> However, if dbconfig-common creates the admin account we just
> use it. We can also deal with the fact that the database is
> pre-created, no problem.
Fine.

> Agree. We might need to double-check but I think we are in
> good shape on that already.
I guessed this. BTW, do you think that the daily snapshot service
will be started soon or should I switch back to check out from CVS.
I'd prefer the snapshot because this is a certain file which I
could refer to.

> I think you need to be very clear on what you mean here. There
> is an admin account for *PostgreSQL* (eg. postgres in most
> cases)
On Debian GNU/Linux the user postgres has no password (by default).
You can only "su postgres" and use the ident method from localhost.

> but there's also an admin account for the database
> "gnumed" inside PostgreSQL (usually called gm-dbowner). The
> latter one owns all objects in that database and grants rights
> to other user groups.
I was talking about this user as "administrator" of the GnuMed
database. I see no need to store the password of gm-dbowner
in any file inside the file system. Thus it should be avoided.

Kind regards

Andreas.

--
http://fam-tille.de

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Matthijs Mohlmann

2004-12-17, 5:52 pm

On Thu, 2004-12-16 at 15:34 +0100, Andreas Tille wrote:
> On Thu, 16 Dec 2004, Olaf van der Spek wrote:
>
> Can you tell me any reason why I should store a clear text password
> in a text file for *my* application only because *other* applications
> would require this?
>
> Kind regards
>
> Andreas.
>
> --
> http://fam-tille.de
>

Why not create a user in mysql / PostgreSQL that has only rights to
create databases and populates them ?

First time installing dbconfig will ask for the admin password to create
the user and packages that needs some install to databases can use that
account to create that database.

Hmm.. think it isn't possible then dbconfig needs an dependency on
postgresql and mysql.. and i think that's not preferable.

Matthijs Mohlmann

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

sean finney

2004-12-19, 5:49 pm

hey andreas, karsten,

sorry for the delay, i've been doing a fairly good job of distracting
myself with half-life 2 in the past week

On Thu, Dec 16, 2004 at 05:30:30PM +0100, Andreas Tille wrote:
> The applilcation I want to package comes with a quite complex bootstrap
> script which does *all* setup (including creating the database and
> adding an admin account). So what we could do here:
>
> 1. From a Debian point of view provide an option for debconf which
> just tells postinst not to create the database etc, because
> the bootstrap script would take over this job.
> Just provide the data which are needed in a defined interface
> instead.

this would certainly be possible, and as we've discussed there are
already hooks for this. however, i think it's good practice to
isolate what needs to be done with administrative privileges
if at all possibile.

> 2. From the application point of view I could ask people to
> include an option which prevents the bootstrap script from
> doing the work which is just done. I guess this is no big deal
> for the very responsive authors.

i really think this would be ideal. patching the already existing
installer script to have a --noadmin option would then let the bulk
of the work remain in the script (which gives you the benefit of
still having something that works on non-debian systems),

> URL?
> I hope you would announce new versions or just move the package to
> experimental to keep people informed.

i can continue uploading debs to experimental if you'd like. currently
the authoratative source of the latest version is:

deb http://people.debian.org/~seanius/policy/examples/ ./
deb-src http://people.debian.org/~seanius/policy/examples/ ./

also, i've started an alioth project:

https://alioth.debian.org/projects/dbconfig-common/

to which i've migrated my cvs tree for the project. i've also
started a mailing list, so if there's sufficient interest we
could use that as a channel for announcements/commits.

> While this is only cosmetics I would prefer storing the database
> specific stuff in separate directories. If we will have more databases
> it would be more easy to read.

folks shouldn't need to read it

> This would be nice.

and it's definitely on my roadmap. there are also other
security-related shortcomings that still need to be addressed,
mostly with passing information on the command line (or storing
sensitive data in the environment at all, for that matter).

> If all else fails: dd if=/dev/zero of=/dev/hda ;-)

that's a sure way to secure your system, too!

> Well, if this is really the case than it would save a certain amount
> of time for me. While I think this is a perfectly reasonable
> requirement I remember times were this was not the case and I had
> trouble to work around this.

someone will need to clarify this, but i believe it is the case. i
know that the biggest problem with the package configuration process
is that the pre-configuration doesn't have any guarantees about
dependencies, which makes it difficult to use anything outside
of base in the .config scripts.

On Fri, Dec 17, 2004 at 11:47:00AM +0100, Karsten Hilbert wrote:
> Well, see, the GnuMed bootstrapping does a lot more advanced
> things regarding "the database user". There's users and groups
> with varying levels of access to the database.

are these actual mysql/pgsql database users, or users stored inside the
gnumed database? currently, dbconfig-common doesn't do anything for the
latter, and i don't know that it's a generalizable enough of a problem
that it's worth trying. however, it provides you a way for setting up
the database with whatever you want to put into it.

sean

--

Andreas Tille

2004-12-20, 2:47 am

On Sun, 19 Dec 2004, sean finney wrote:

>
> this would certainly be possible, and as we've discussed there are
> already hooks for this. however, i think it's good practice to
> isolate what needs to be done with administrative privileges
> if at all possibile.
ACK.
But if I understand Karsten right, bootstraping GnuMed is something more than
creating a database and creating some tables.

So the idea came into my mind to create a system acount for the application
database maintainer (in this case gm-dbowner) with the same system login
parameters as postgres (only possibility ist to su gm-dbowner) and enable
only local ident authentication work. Then we do not need to store any
password and the job can be done as this user.

>
> i really think this would be ideal. patching the already existing
> installer script to have a --noadmin option would then let the bulk
> of the work remain in the script (which gives you the benefit of
> still having something that works on non-debian systems),
Definitely.

> i can continue uploading debs to experimental if you'd like. currently
> the authoratative source of the latest version is:
>
> deb http://people.debian.org/~seanius/policy/examples/ ./
> deb-src http://people.debian.org/~seanius/policy/examples/ ./
I would love if you would start with experimental upload. I guess that over
Christmas time and keeping in mind that also others will give half-live 2
a chance (not me - never tried it) will not really speed up propagating new
packages to the Debian mirrors. Because GnuMed will depend from your package
I would love if it would be available soon.

> also, i've started an alioth project:
>
> https://alioth.debian.org/projects/dbconfig-common/
>
> to which i've migrated my cvs tree for the project. i've also
> started a mailing list, so if there's sufficient interest we
> could use that as a channel for announcements/commits.
Just subscribed.

> folks shouldn't need to read it

Sure, but if you like patches ...

Kind regards

Andreas.

--
http://fam-tille.de

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Karsten Hilbert

2004-12-20, 7:50 am

> >> 1. From a Debian point of view provide an option for debconf which
> ACK.
> But if I understand Karsten right, bootstraping GnuMed is something more
> than creating a database and creating some tables.
Correct. But none of that is any business of dbconfig-common
proper.

> So the idea came into my mind to create a system acount for the application
> database maintainer (in this case gm-dbowner) with the same system login
> parameters as postgres (only possibility ist to su gm-dbowner) and enable
> only local ident authentication work. Then we do not need to store any
> password and the job can be done as this user.
This is likely best practice and very desirable in an
environment as controllable as Debian.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Karsten Hilbert

2004-12-20, 7:50 am

> > 2. From the application point of view I could ask people to
>
> i really think this would be ideal. patching the already existing
> installer script to have a --noadmin option would then let the bulk
> of the work remain in the script (which gives you the benefit of
> still having something that works on non-debian systems),
AFACT right now I think we should already transparently be
able to do this (eg. test and use if exist, --noadmin not
necessary). Actually trying will likely turn up a bug or two
but we'll deal with that easily. Our current code is intended
to a) accept an existing database, b) accept not needing to
create the database user that will own the GnuMed database, c)
try to continue working in case we cannot become root and/or
postgres in order to do a) or b) -- which isn't necessary
if a) and b) are done by dbconfig-common already.

> On Fri, Dec 17, 2004 at 11:47:00AM +0100, Karsten Hilbert wrote:
>
> are these actual mysql/pgsql database users,
Yes.

> or users stored inside the gnumed database?
No.

> currently, dbconfig-common doesn't do anything for the
> latter, and i don't know that it's a generalizable enough of a problem
> that it's worth trying.
I don't think so.

If dbconfig-common creates gm-dbowner (which is a database
owner intended to own all GnuMed database objects and which has
create-db and create-user rights) that would be enough. We
would then just rely on that and create "our" users/groups
under that account. dbconfig-common would just need to make
sure the "gnumed" database is created such that it is owned by
gm-dbowner.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Andreas Tille

2004-12-20, 5:51 pm

On Mon, 20 Dec 2004, Karsten Hilbert wrote:

> AFACT right now I think we should already transparently be
> able to do this (eg. test and use if exist, --noadmin not
> necessary). Actually trying will likely turn up a bug or two
> but we'll deal with that easily. Our current code is intended
> to a) accept an existing database, b) accept not needing to
> create the database user that will own the GnuMed database, c)
> try to continue working in case we cannot become root and/or
> postgres in order to do a) or b) -- which isn't necessary
> if a) and b) are done by dbconfig-common already.
Fine. Trust me that I'll report any bug I might detect. ;-)

> Yes.
To be exact only pqsql because GnuMed does not work with MySQL.

> If dbconfig-common creates gm-dbowner (which is a database
> owner intended to own all GnuMed database objects and which has
> create-db and create-user rights) that would be enough. We
> would then just rely on that and create "our" users/groups
> under that account. dbconfig-common would just need to make
> sure the "gnumed" database is created such that it is owned by
> gm-dbowner.
I prefer this way to go. Is there any change to the bootstrap script
necessary?

Kind regards

Andreas.

--
http://fam-tille.de

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Karsten Hilbert

2004-12-20, 5:51 pm

> >If dbconfig-common creates gm-dbowner (which is a database
> I prefer this way to go. Is there any change to the bootstrap script
> necessary?
Not that I am aware of off the top of my head.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

sean finney

2004-12-21, 5:56 pm

hi karsten,

On Mon, Dec 20, 2004 at 11:02:00AM +0100, Karsten Hilbert wrote:
> but we'll deal with that easily. Our current code is intended
> to a) accept an existing database, b) accept not needing to
> create the database user that will own the GnuMed database, c)
> try to continue working in case we cannot become root and/or
> postgres in order to do a) or b) -- which isn't necessary
> if a) and b) are done by dbconfig-common already.

cool, then we're probably okay.

> Yes.
> No.

well dbconfig-common can handle the creation of one user already, it'd
probably be simplest to create that user, and use it to dole out other
users+privileges. that may mean that you'd have to do something in your
bootstrap script to grant any extra privileges that the user would need
to do so.

> If dbconfig-common creates gm-dbowner (which is a database
> owner intended to own all GnuMed database objects and which has
> create-db and create-user rights) that would be enough. We
> would then just rely on that and create "our" users/groups
> under that account. dbconfig-common would just need to make
> sure the "gnumed" database is created such that it is owned by
> gm-dbowner.

i don't think the user is by default granted create user and create
db rights, at least in mysql. i'm really out of my area of expertise
with pgsql, so it may be different there. in mysql, at least, that
would be of some concern to me as a sysadmin/dba that one of my database
applications could potentially have full administrative access to
all the databases on my system.

sean

--

Karsten Hilbert

2004-12-21, 5:56 pm

> > > are these actual mysql/pgsql database users,
>
> well dbconfig-common can handle the creation of one user already, it'd
> probably be simplest to create that user, and use it to dole out other
> users+privileges. that may mean that you'd have to do something in your
> bootstrap script to grant any extra privileges that the user would need
> to do so.
Well, but to grant extra rights to that user I'd have to
become a user with even more priviledges - which is what we'd
want to avoid in the first place. If dbconfig-common is trying
to be helpful it needs to create the user with all the
necessary rights.

>
> i don't think the user is by default granted create user and create
> db rights, at least in mysql. i'm really out of my area of expertise
> with pgsql, so it may be different there.
No, same thing.

> in mysql, at least, that
> would be of some concern to me as a sysadmin/dba that one of my database
> applications could potentially have full administrative access to
> all the databases on my system.
a) our applications don't use that user
b) the user only has create-database and create-user which
means it can create new databases and delete databases
owned by itself, same with users: create new ones and
delete those created by itself
c) the user does not have administrative access to other
databases
d) in fact, that user does not have "administrative" access at
all in that that would be something generic, it only has
the added rights to manage "it's" databases/users

I don't think there's a real security concern there.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Oliver Elphick

2004-12-23, 7:47 am

On Tue, 2004-12-21 at 20:27 +0100, Karsten Hilbert wrote:
....
> No, same thing.
>
> a) our applications don't use that user
> b) the user only has create-database and create-user which
> means it can create new databases and delete databases
> owned by itself, same with users: create new ones and
> delete those created by itself
> c) the user does not have administrative access to other
> databases
> d) in fact, that user does not have "administrative" access at
> all in that that would be something generic, it only has
> the added rights to manage "it's" databases/users

In PostgreSQL, any user that can create other users is the equivalent of
superuser; he can do anything to any database. It is not acceptable for
a database application package to create such users. It is OK to create
a user that can create databases, however.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================

"And this shall be a sign unto you; Ye shall find the
babe wrapped in swaddling clothes, lying in a manger."
Luke 2:12

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Karsten Hilbert

2004-12-23, 7:48 am

> In PostgreSQL, any user that can create other users is the equivalent of
> superuser;
Or so the 7.4 docs say.

> he can do anything to any database.
If that were so ...

> It is not acceptable for
> a database application package to create such users.
.... this would hold true.

However, if I create a table as "postgres" in a database and
then connect as user "gm-dbowner" (which is our application
admin account with CREATEUSER and CREATEDB rights) I can *not*
insert into that table. Which seems to go contrary to what the
docs say.

Also, usesuper in pg_user is FALSE for gm-dbowner.

What is the definitive deal ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Oliver Elphick

2004-12-23, 7:48 am

On Thu, 2004-12-23 at 11:52 +0100, Karsten Hilbert wrote:
> Or so the 7.4 docs say.
>
> If that were so ...
>
> ... this would hold true.
>
> However, if I create a table as "postgres" in a database and
> then connect as user "gm-dbowner" (which is our application
> admin account with CREATEUSER and CREATEDB rights) I can *not*
> insert into that table. Which seems to go contrary to what the
> docs say.
>
> Also, usesuper in pg_user is FALSE for gm-dbowner.
>
> What is the definitive deal ?

The createuser script will set usesuper to true if the --adduser option
is given; so does the CREATE USER statement if the CREATEUSER option is
given. You can subsequently set usesuper to false, but I don't feel at
all happy with the idea of creating a superuser at any point. Better to
have the existing postgres user create any other needed users.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================

"And this shall be a sign unto you; Ye shall find the
babe wrapped in swaddling clothes, lying in a manger."
Luke 2:12

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org

Ola Lundqvist

2004-12-27, 7:47 am

Hello

On Mon, Oct 25, 2004 at 01:30:39PM +0200, José Luis Tallón wrote:
> 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.

Yes! This package should really be split into separate parts. Personally
I want to get rid of it all as most of this should(?) or could be provided
by the package that should be configured. Apache2 do no longer need
wwwconfig-common as it has an excelent way to be configured and php is
beginning to be a lot better.

It is just the database part left and you seem to make a good work on this
part.

Regards,

// Ola

> Oliver Elphick wrote:
>
> 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.
>
> Ok... assuming this applies to user setup also :-?
>
> Hmmm.....
> 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.
>
> ...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)
>
> 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 )
>
> Best,
> J.L.
>
>
> --
> To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
> with a subject of "unsubscribe". Trouble? Contact
> listmaster@lists.debian.org
>
>

--
--------------------- Ola Lundqvist ---------------------------
/ opal@debian.org Annebergsslingan 37 \
| opal@lysator.liu.se 654 65 KARLSTAD |
| +46 (0)54-10 14 30 +46 (0)70-332 1551 |
| http://www.opal.dhs.org UIN/icq: 4912500 |
\ gpg/f.p.: 7090 A92B 18FE 7994 0C36 4FE4 18A1 B1CF 0FE5 3DD9 /
---------------------------------------------------------------

--
To UNSUBSCRIBE, email to debian-devel-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org