Bugzilla Bug 193081

The current installation mechanism of dspam with SQL database backends depends
on the assumption that every database engine runs locally on the mail server.
While this assumption is true for small installations using berkeley db, sqlite
(and so on) large scale installations have a separate database server
'somewhere' in the network.

The current dspam package installation process should be separated into two
stages:
Stage 1: Install of the package as it is (which means every necessary file
including the (by use flag) preselected database connectors.
Stage 2: Using the command "emerge --config =dspam-3.x.x" which triggers the
execution of the pkg_config() section in the ebuild. This should do the
finishing touches depending on the database backend setting the admin/installer
did in the dspam.conf configuration file (selecting database backends, setting
connection credentials and passwords)

This method has the following advantages:
1. The admin can use more than one database backend
2. Remote database backend installations are possible
3. The installation part of dspam is separate from the initialization part
(which can be done more than once for different DB set-ups)


Reproducible: Always

Steps to Reproduce:

*** This bug has been marked as a duplicate of bug 193073 ***

You're right, this isn't a duplicate, but it is very close to being marked as
invalid.

The emerge --config phase is meant to configure a fresh installed instance of
the package, therefore it shouldn't rely on data found in dspam.conf. That
being said, I admit the current pkg_config is less than perfect because it
relies on those pesky *.data files created on installation. 

> This method has the following advantages:
> 1. The admin can use more than one database backend

Don't see the point, but the current form supports multiple backends also, only
you have to run emerge --config twice if you wanna configure mysql and postgres
backends on the same box. 

> 2. Remote database backend installations are possible

As opposed to what we have now? Just modify the *.data file and dspam.conf
before running emerge --config and you will be able to configure the database
on another server.

> 3. The installation part of dspam is separate from the initialization part
> (which can be done more than once for different DB set-ups)

Still don't see the advantage. AFAIK dspam cannot use multiple backends at the
same time and having multiple dspam configurations on the same machine is
totally pointless.

If you want an improved pkg_config (one who wouldn't rely on current content of
the dspam.conf and remove the need of having *.data files) just propose a
patch. I would like a pkg_config who read database parameters from dspam.conf
as defaults and asks user to either confirm or alter them.

You don't have anything to say about my comments so I close this bug as INVALID
(the subject is obviously so anyway).

Not so fast ;-)

I'm working in my own business, which is a full time job, I don't have as much
time as a maintainer of a Gentoo package to respond.

Anyhow. I don't want to argue about technical insignificances. 

My point is that the Postgresql set-up of dspam is broken. It is not automatic
and probably never should be, the only thing it really must do is set-up dspam
correctly and NOT the Postgresql part.
BTW: In a real live environment there might even be a different administrator
which set-up the database, and (s)he probably would not like to see a script
doing stuff with the database. Especially with commands like "createlang" in
Postgresql which touch very security sensitive areas of a database system.

Long argument cut short:
1. The dspam routine 'must' only set-up it self.
2. I should only 'advise' the Admin on how to set-up up secondary systems ...
Customized commands for the Databases are a nice add-on ;-) 
3. It definitely should NOT touch other productive systems (like the database)
because they could be set-up in special way, which results not only a faulty
set-up of dspam, but also a broken database system with broader consequences.

So leave the bug closed if you like ... I don't really care about it very much,
I use an overlay with the correct set-up routine for me. 
But keep in mind that with this bug dspam will set many systems in danger of
being broken (auto-magically) by your set-up .... as well as the Security of
the whole system is in danger by an additional software package not being
actively used (an full blown installed Postgresql server with every 'security
hole' (in the future)) which needs to be patched by upgrading ... every Admin
will thank you for that and every security aware person won't like using the
dspam package ...

I have one full time job as a programmer and also I'm involved in 2 part time
jobs. Somehow I find time to respond on bugs like this, even though I don't
have a personal interest.

(In reply to comment #4)
> My point is that the Postgresql set-up of dspam is broken. It is not automatic
> and probably never should be, the only thing it really must do is set-up dspam
> correctly and NOT the Postgresql part.

Do you honestly think I would ever consider maintaining a pkg_config function
if it weren't the database setup? 

> BTW: In a real live environment there might even be a different administrator
> which set-up the database, and (s)he probably would not like to see a script
> doing stuff with the database. Especially with commands like "createlang" in
> Postgresql which touch very security sensitive areas of a database system.

No one force you to run pkg_config. Just do it manually if you must.

> Long argument cut short:
> 1. The dspam routine 'must' only set-up it self.

Selecting a few parameters in dspam.conf such as StorageDriver or PgSQLServer
isn't a reason to create pkg_config(), isn't it?

> 2. I should only 'advise' the Admin on how to set-up up secondary systems ...
> Customized commands for the Databases are a nice add-on ;-) 

Forward to him a sql script on how you would like to make your own database
then.

> 3. It definitely should NOT touch other productive systems (like the database)
> because they could be set-up in special way, which results not only a faulty
> set-up of dspam, but also a broken database system with broader consequences.
> 
> So leave the bug closed if you like ... I don't really care about it very much,
> I use an overlay with the correct set-up routine for me.

You use an overlay just because you don't like pkg_config?!? Ah... I forgot.
You also don't like having an unused Postgres server on your box... Time to ask
for minimal USE flag on dev-d/postgres perhaps?

> But keep in mind that with this bug dspam will set many systems in danger of
> being broken (auto-magically) by your set-up 

Interesting... How will my setup bork your server again? You give the address
of the Postgres server, root user and root password and expect the emerge to
check if database server is reachable?!? Geez... 

> .... as well as the Security of
> the whole system is in danger by an additional software package not being
> actively used (an full blown installed Postgresql server with every 'security
> hole' (in the future)) which needs to be patched by upgrading

Some security freaks wouldn't install gcc on their boxes because they see it as
a security hazard. We on the other hand prefer to fix real security holes, not
the perceived ones. 

> Interesting... How will my setup bork your server again? You give the address
> of the Postgres server, root user and root password and expect the emerge to
> check if database server is reachable?!? Geez... 
Well psql lets you do nasty things like execute shell commands. I personally
don't like those database shells, if they have an buffer overflow somewhere
(I'm sure they all do) you're doomed :-/

Anyway why not use a python script to call the set-up scripts as a wrapper. All
you need is a thin python sql binding (psycopg or something like that would
do). No servers required (even the install could be faster because the fat ass
database would not have been compiled) and everybody is happy! ;-))

Just my 50 cent

(In reply to comment #6)
> Anyway why not use a python script to call the set-up scripts as a wrapper. All
> you need is a thin python sql binding (psycopg or something like that would
> do). No servers required (even the install could be faster because the fat ass
> database would not have been compiled) and everybody is happy! ;-))

I'm not familiar with psycopg, but if it can execute sql scripts just like
psql, this is a good alternative. However this has nothing to do with this bug.

Just attach a patch to bug 193073 that would allow pkg_config() and dspam.cron
to use this alternative. The RDEPEND in this case will contain "postgres? ( ||
( dev-python/psycopg dev-db/postgresql ) )".

Well I don't know python and PostgreSQL very well. I would take me weeks to
write such a script. :-/

Created an attachment (id=131862) [details]
python, psycopg wrapper for pkg_config routines

Hi there!

Here a quick script which takes either 5 or 6 arguments, depending if a virtual
user set-up is wanted or not.

Usage: prepare-dspam-postgresql.py [database host] [dspam user] [dspam passwd]
[dspam dbname] [set-up DB script] [OPTIONAL: set-up virutal-user script]

Example: prepare-dspam-postgresql.py 192.168.5.5 dspam secr3t mydspam
/path/to/pgsql_objects.sql /path/to/pgsql_virtual_users.sql

It does take the pkg_config routines and embeds them into python using a
psycopg ( dev-python/psycopg ) connector. 

Alin, if you like a write another, smarter wrapper (reading the configuration
from dspam.conf) for the cron qurge.sql script!?

Typo in last sentence:
Alin, I write another 'smarter' wrapper (reading the configuration
from dspam.conf) for the cron qurge.sql script, if you like?!

(In reply to comment #11)
> Typo in last sentence:
> Alin, I write another 'smarter' wrapper (reading the configuration
> from dspam.conf) for the cron qurge.sql script, if you like?!

This piece of code must be executed by sh because it should run from pkg_config
and dspam.cron (don't wanna install a wrapper for such a simple task). If your
code satisfy this condition, post it here. If not, I will probably write some
awk code for it.

Ah, and another thing... I also need to execute the sql purge script found in
/etc/mail/dspam directory. For that reason, I want a database terminal like
psql, that would be capable to do database creation and purge the old data when
called from dspam.cron.

I also need some special env vars through I could pass authentication data to
the script (dspam.cron isn't interactive).

Sure no problem ... env vars or command args or both for the purge script? 

Ok, the attached script purge-postgresql.py you can either be called with
arguments: purge-postgresql.py [database host] [dspam user] [dspam passwd]
[dspam dbname] [purge DB script]

or without arguments, but the following environment vars set:
DSPAM_PgSQL_HOST, DSPAM_PgSQL_USER, DSPAM_PgSQL_PWD, DSPAM_PgSQL_DB and
DSPAM_PgSQL_PURGE_SQL

Created an attachment (id=131931) [details]
python, psycopg wrapper for daily (cron based) postgresql database clean-up

There is already a small problem embedded in the pgsql_objects.sql file.

The command:
create language plpgsql;
only work for PostgreSQL < 8.0
for PostgreSQL 8.0 and later you need to run:
createlang -Upostgres plpgsql <DBNAME>
Otherwise you will get an syntax error for the current (and also my python
wrapper, which I can fix) dspam database set-up.

Well, createlang is installed by postgresql. Please come up with an alternative
or I will be forced dev-lib/postgresql dependency. I will force >=libpq-8
dependency anyway, so you don't need to take previous postgres versions into
account.

Aside from that I almost finished the r7 revision, which is far better polished
version than the one before.

You were right about pkg_config not being able to configure any other sql
server than localhost. Sorry about that. 

Fixed in -r7, just the way you wanted. Changes occured in pkg_config:
  - edit database parameters of the dspam.conf
  - create database with whatever settings user specified in his dspam.conf
  - make database creation optionally
  - pgsql: use psycopg script when psql is missing (postgresql can be missing,
see the new RDEPEND)
  - pgsql: fix language creation

Changes occured in dspam.cron:
  - sql purge scripts no longer required
  - read database parameters directly from dspam.conf
  - pgsql: use psycopg script when psql is missing

I've just lost almost an entire weekend over this. The next person who will
have anything to add to dspam ebuild, better have patches and be prepared to
fix whatever I tell him to. 

Cheers Alin ... you are a * .... :-)