6.4. PostgreSQL

This backend is very similar to the MySQL one, except that it uses PostgreSQL (and thus add constraints to ensure tables integrity). The PostgreSQL backend can (and should) be preferred to MySQL when possible, as the design is better done and uses the real possibilities of a database, like sequences, constraints, etc.

The default logging channel for the postgresql backend is 27.

6.4.1. Installation

You should first create the wzdftpd user.


# su postgres
$ psql -f createusers.sql template1
You must allow the user wzdftpd to connect using a password: edit file
/etc/postgresql/pg_hba.conf
and add

host   wzdftpd     wzdftpd      127.0.0.1         255.255.255.255   md5
Restart the server.

Create the initial table structure using the following:


$ psql -Uwzdftpd -f tables.sql wzdftpd
Password:
Default password is "wzdftpd" (you should change it in file createusers.sql !).

To use the PostgreSQL backend, add the following to you config file:

backend = /path/to/wzdftpd/share/wzdftpd/backends/libwzdpgsql.so
backend_param_pgsql = login:password@host:base[:port]

The port is optional and will be defaulted to 5432.

6.4.2. Schema and Constraints

The main tables are users and groups. They are related with a n:n relation, implemented using the ugr table (user-group relations).

Other tables are linked using 1:n relations to materialize the fact that one user or group can have several associated IP adresses. Users have userip, groups have groupip.

Finally, stats for users are implemented in a separate table, stats, with a 1:1 relation (users have only one stat line).

To ensure integrity of the data, some constraints are enforced: unicity constraints, to avoid having several objects with same id, and references constraints, to ensure that a relation referencing an user is associated with an existing user (and that you can't delete an object which is referenced elsewhere).

6.4.3. TODO

Work in progress !