Apache 2 based WebDAV server with quota using MySQL and SSL on Debian

Debian Apache 2 WebDAV server with quota using SSL and MySQL for authentication

Introduction

As part of my work I had to setup a download/upload site for our customers
with the following brief:

Part of the Web site would have to be accessible by anybody
Other part of the Web site would need authentication in order
to download software, patches, etc...
Some of the customers should be allowed to upload files on the Web site
yet within a set quota.
Uploading, downloading and managing the Web site content would have to be easy
and it was decided that it would have to rely on HTTP.
This is because:
1) in some organizations FTP isn't always allowed and active FTP can sometime cause
a problem with firewalls.
2) using SFTP or SSH to manage the site would require specialized client software.

Therefore WebDAV was chosen as the protocol to manage the site with a patched
version of the Apache 2 WebDAV module to provide quota.

Addionally all the WebDAV traffic and authentication is done through HTTPS.
This is because Windows XP clients simply refuse authentication of a WebDAV
directory, and in any case has the benefit of making the management of the site far more
secure regardless of the WebDAV client and platform used.

One thing to bear in mind is that I had to use Apache 2 instead of Apache 1.3.x
as there is no WebDAV patch, for Apache 1.3.x

The following will explain how to setup Apache 2, HTTPS, WebDAV, recompile
the WebDAV module to support quota and use MySQL to provide authentication
and give different access control to the various part of the site.

Configuration overview

The configuration will be done in the following order, starting simple and gradually
get more complex as it make problem solving easier.

installing apache 2 and the WebDAV module
creating an SSL certificate (including setting up the CA) for the Web server
configuring apache to serve HTTPS pages
enabling WebDAV and configuring simple authentication
recompiling the apache WebDAV module to provide quota
configuring apache to use MySQL for more complex authentication
configuring the site to provide all the functionalities mentioned in
the introduction.
Footnote, comments/suggestions

Credits

I should give credits and thanks to the following, which without them I would have never
been able to achieve my project:

Saqib Ali's "Apache based WebDAV server with LDAP for authentication and SSL" HOWTO
(http://www.tldp.org/HOWTO/Apache-WebDAV-LDAP-HOWTO/)
http://bignosebird.com/notebook/mysqlauth.shtml
http://www.twilight-systems.com/flacco/mozcal/mozcal-webdav-apache2-rh9.html
Nate Carlson's IPSec using Openswan "Howto" (http://www.natecarlson.com/linux/ipsec-x509.php)

Installing Apache 2 and the WebDAV module

# apt-get install apache2 libapache-mod-dav
Reading Package Lists... Done
Building Dependency Tree... Done
The following extra packages will be installed:
apache-common apache2-common apache2-mpm-worker apache2-utils libapr0 libxmltok1 openssl ssl-cert
Suggested packages:
apache apache-ssl apache-perl apache2-doc lynx www-browser ca-certificates
The following NEW packages will be installed:
apache-common apache2 apache2-common apache2-mpm-worker apache2-utils libapache-mod-dav libapr0 libxmltok1 openssl
ssl-cert
0 upgraded, 10 newly installed, 0 to remove and 0 not upgraded.
Need to get 3141kB of archives.
After unpacking 10.1MB of additional disk space will be used.
Do you want to continue? [Y/n] y

Creating a certificate for the Web server

To serve Web pages with the HTTPS protocol, the Web server will require a certificate.
If you are already familiar with certificate management on Linux then skip ahead to
configure apache to serve HTTPS pages, otherwise the following steps will explain how
to set up you own certificate authority and then create a certificate for your Web server.

Create a certificate authority

Note: most of the following was copied and slightly modified from Nate Carlson's excellent IPSec Web page (http://www.natecarlson.com/linux/ipsec-x509.php)

Edit the file "/usr/lib/ssl/misc/CA.sh", and change the line that says 'DAYS="days 365"' to a very high number
(this sets how long the certificate authority's certificate is valid.). This is necessary as the certificate authority must
last longer than the Web server certificate. I generally set it to 3560 (which roughly amount to 10 years).

Run the command 'CA.sh -newca'. Follow the prompts, as below. Example input is in red, and my comments are in blue.
Be sure to not use any non-alphanumeric characters, such as dashes, commas, plus signs, etc.
These characters may make things more difficult for you.

# /usr/lib/ssl/misc/CA.sh -newca
CA certificate filename (or enter to create)
(enter)
Making CA certificate ...
Using configuration from /usr/lib/ssl/openssl.cnf
Generating a 1024 bit RSA private key
.............................................................................+++
........................................+++
writing new private key to './demoCA/private/./cakey.pem'
Enter PEM pass phrase:(enter password) This is the password you will need to create any other certificates.
Verifying password - Enter PEM pass phrase:(repeat password)
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:US(enter) Enter your country code here
State or Province Name (full name) [Some-State]:State(enter) Enter your state/province/county here
Locality Name (eg, city) []:City(enter) Enter your city here
Organization Name (eg, company) [Internet Widgits Pty Ltd]:ExampleCo(enter) Enter your company name here (or leave blank)
Organizational Unit Name (eg, section) []:IT(enter) OU or department, if you like. You can leave it blank if you want.
Common Name (eg, YOUR name) []:CA(enter) The name of your Certificate Authority
Email Address []:ca@example.com(enter) E-Mail Address

Create the certificate for your Web server

# /usr/lib/ssl/misc/CA.sh -newreq
Using configuration from /usr/lib/ssl/openssl.cnf
Generating a 1024 bit RSA private key
...................................+++
...............................+++
writing new private key to 'newreq.pem'
Enter PEM pass phrase:(enter password) Password to encrypt the new cert's private key with - you'll need this!
Verifying password - Enter PEM pass phrase:(repeat password)
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:US(enter)
State or Province Name (full name) [Some-State]:State(enter)
Locality Name (eg, city) []:City(enter)
Organization Name (eg, company) [Internet Widgits Pty Ltd]:ExampleCo(enter)
Organizational Unit Name (eg, section) []:(enter)
Common Name (eg, YOUR name) []:host.example.com(enter)The host name of your Web server
Email Address []:user@example.com(enter) (optional)

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:(enter)
An optional company name []:(enter)
Request (and private key) is in newreq.pem

What we just did is generate a Certificate Request - this is the same type of request that you would send to Thawte or Verisign to get a generally-accepted SSL certificate. For our uses, however, we'll sign it with our own CA:

# /usr/lib/ssl/misc/CA.sh -sign
Using configuration from /usr/lib/ssl/openssl.cnf
Enter PEM pass phrase:(password you entered when creating the ca)
Check that the request matches the signature
Signature ok
The Subjects Distinguished Name is as follows
countryName :PRINTABLE:'US'
stateOrProvinceName :PRINTABLE:'State'
localityName :PRINTABLE:'City'
organizationName :PRINTABLE:'ExampleCo'
commonName :PRINTABLE:'host.example.com'
emailAddress :IA5STRING:'user@example.com'
Certificate is to be certified until Feb 13 16:28:40 2012 GMT (3650 days)
Sign the certificate? [y/n]:y(enter)

1 out of 1 certificate requests certified, commit? [y/n]y(enter)
Write out database with 1 new entries
Data Base Updated
(certificate snipped)
Signed certificate is in newcert.pem

Next, move the output files to names that make a bit more sense for future reference, where "host.example.com..."
in the example below is the name of your Web server.

# mv newcert.pem host.example.com.pem
# mv newreq.pem host.example.com.key

Configuring apache to serve HTTPS pages

Copy the above 2 certificates files in /etc/apache2/ssl.
and make sure you change the files to be only readable by root

# chmod 400 /etc/apache2/ssl/host*

As well for convenience you may want to remove the passphrase from the RSA private key which will
stop to prompt for the passphrase when starting or restarting apache. However if you prefer to keep
the passphrase, skip to the next step.

First make a backup of the encrypted key

# cp /etc/apache2/ssl/host.example.com.key /etc/apache2/ssl/host.example.com-bkp

Then re-write the key with encryption. You will be prompted for the original encrypted Key passphrase

# openssl rsa -in /etc/apache2/host.example.com.key-bkp -out /etc/apache2/ssl/host.example.com.key
Enter pass phrase for /etc/apache2/ssl/host.example.com.key-bkp:
writing RSA key

Edit the file "/etc/apache2/mods-available/ssl.conf" and add the following at the bottom.

SSLCertificateFile /etc/apache2/ssl/host.example.com.pem
SSLCertificateKeyFile /etc/apache2/ssl/host.example.com.key

listen 443

Enable HTTPS by creating symbolic links to the "SSL" files in /etc/apache2/mods-available/ to /etc/apache2/mods-enabled

# ln -s /etc/apache2/mods-available/ssl.conf /etc/apache2/mods-enabled/ssl.conf
# ln -s /etc/apache2/mods-available/ssl.load /etc/apache2/mods-enabled/ssl.load

Create a site configuration file

# touch /etc/apache2/sites-available/testwebdav

and copy the following in it:

replacing WEB_server_IP_address by the IP address of your own server

"Enable" the site by creating a symbolic link to the configuration file in "/etc/apache2/sites-enabled/"

# ln -s /etc/apache2/sites-available/testwebdav /etc/apache2/sites-enabled/testwebdav

WebDAV directory

Now we need to create the Webdav directory and create a test file in it so we can test
HTTPS and WebDAV functionalities.
Create the WebDAV directory

# mkdir /var/www/webdav

and change it's ownership to the account and group under which apache is running
(under Debian the apache user and group are both www-data).

# chown www-data:www-data /var/www/webdav/

Then create a test file in the WebDAV directory.

# echo "hello world" > /var/www/webdav/test.txt
# chown apache:apache /var/www/webdav/test.txt
# chmod 640 /var/www/webdav/flacco/test.txt

Now reload apache to make the changes effective.

# /etc/init.d/apache2 reload

You may want to check that apache is running HTTP and HTTPS by using the "netstat -anpt" commnand

# netstat -ant
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN     1870/portmap
tcp        0      0 127.0.0.1:692           0.0.0.0:*               LISTEN     2636/famd
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN     2455/exim4
tcp6       0      0 :::80                   :::*                    LISTEN     5395/apache2
tcp6       0      0 :::22                   :::*                    LISTEN     2470/sshd
tcp6       0      0 :::443                  :::*                    LISTEN     5395/apache2

If everything is OK you should see that apache2 is listening on port 80 and more importantly
on port 443, as in the above example. If not consult your the apache log file (/var/log/apache2/)
Now open the following URL in your Web browser

https://WEB_server_IP_address/test.txt

replacing "WEB_server_IP_address" by the IP address of your server or it's FQN
This should ask you to accept a certificate, and you have once accepted it should display:

hello world

Enabling WebDAV and configuring simple authentication

Enable WebDAV and apache simple authentication by once again creating
a symbolic link to the modules in /etc/apache2/mods-available to /etc/apache2//mods-enabled

# ln -s /etc/apache2/mods-available/auth_anon.load /etc/apache2/mods-enabled/auth_anon.load
# ln -s /etc/apache2/mods-available/dav_fs.conf /etc/apache2/mods-enabled/dav_fs.conf
# ln -s /etc/apache2/mods-available/dav_fs.load /etc/apache2/mods-enabled/dav_fs.load
# ln -s /etc/apache2/mods-available/dav.load /etc/apache2/mods-enabled/dav.load

Create a simple authentication file and set it's permission so only the apache account can read it.

# htpasswd -c /etc/apache2/passwd.dav test
# chown root:www-data /etc/apache2/passwd.dav
# chmod 640 /etc/apache2/passwd.dav

Now you need to modify the site configuration file to enable WebDAV and authentication..
Do it according the example below (added directives in red).

<VirtualHost WEB_server_IP_address:443>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav
    CustomLog /var/log/apache2/access.log combined

    <IfModule mod_ssl.c>
        SSLEngine on
        SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown
    </IfModule>

    <Directory /var/www/webdav>
            DAV On
            AuthType Basic
            AuthName "test"
            AuthUserFile /etc/apache2/passwd.dav
            <Limit PUT POST DELETE PROPFIND PROPPATCH MKCOL COPY MOVE LOCK UNLOCK>
                Require user test
            </Limit>
    </Directory>

</VirtualHost>

Again we need to reload apache to make the changes effective

# /etc/init.d/apache2 reload

Now to test WebDAV you need a "WebDAV client".

Internet Explorer on Windows 2000 and Windows XP.
Windows 2000 and XP built in WebDAV support has the
advantage of having nothing to be installed, however it has
the disadvantage of not being able to create files but just copy
or rename them (but all directory operation are supported).
Novel Netdrive http://www.loyola.edu/5555/netdrive/installingnetdrive/
Novel Netdrive is a "freeware" and unlikeWindows built in WebDAV support,
Netdrive support all files and directories operations. However its
disadvantage is that you have to install it.
Other WebDAV clients http://www.webdav.org/projects/

I will be using Windows built in WebDAV support through Internet Explorer
as it is adequate for the remaining of this article

In Internet Explorer select "File" => "Open", open the URL

https://WEB_server_IP_address/

and tick the check box next to "Open as Web Folder" (this is very important otherwise it won't
open the folder using the WebDAV protocol and all you'll be able to do is to view the files but
not modify them).

Once again you will be prompted to accept a certificate but now additionally you will also be prompted to
provide a user name and password.

Note :If you are constantly prompted for the user name and password, either they are incorrect or there is a problem with
authentication. It could be that apache cannot read the password file because there is a syntax error or that apache doesn't
have the permission to read the file. Look in "/var/log/apache/error.log" to find out more.

To test WebDAV functionalities, in Internet Explorer, right click on "test.txt" (the test file we created earlier),
select "Rename", rename the file and press the "Enter" key.

Enabling public browsing access to the site

You may want to have your site "browsable" or a least 1 part of the site (see later on for more granular access).
To do so modify the site configuration file to enable WebDAV and authentication..
Do it according the example below (added directive in red):

<VirtualHost WEB_server_IP_addres:80>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav

</VirtualHost>

<VirtualHost WEB_server_IP_addres:443>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav
    CustomLog /var/log/apache2/access.log combined

    <IfModule mod_ssl.c>
        SSLEngine on
        SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown
    </IfModule>

    <Directory /var/www/webdav>
            DAV On
            AuthType Basic
            AuthName "test"
            AuthUserFile /etc/apache2/passwd.dav
            <Limit PUT POST DELETE PROPFIND PROPPATCH MKCOL COPY MOVE LOCK UNLOCK>
                Require user test
            </Limit>
    </Directory>

</VirtualHost>

Once again reload apache to make the changes effective

# /etc/init.d/apache2 reload

And open the following URL in your Web browser (but not as a Web Folder).

http://WEB_server_IP_address/test.txt

replacing "WEB_server_IP_address" by the IP address of your server or it's FQN
Which should display:

hello world

Recompiling the apache WebDAV module to provide quota

Briefly said in order to provide quota with WebDAV, you will need the apache2 source, patch the WebDAV
modules and recompile them.

Recompiling the apache WebDAV modules

On Debian Sarge, the apache version I used was 2.0.54

Obtain the Apache 2.0.54 source from http://archive.apache.org/dist/httpd/httpd-2.0.54.tar.gz
Obtain the Apache WebDAV modules patch from http://www.geocities.jp/t_sat7/webdav/webdav.html
(I used http://leche.goodcrew.ne.jp/webdav/webdav-2.0.54-quota-2.3any.txt or webdav-2.0.54-quota-2.3any.txt)

Note if you are using a later version of apache 2 than 2.0.54 obtain the source from http://httpd.apache.org/ and the
corresponding WebDAV patch from http://www.geocities.jp/t_sat7/webdav/webdav.html

# tar -xvzf httpd-2.0.54.tgz
(snip)
# cd httpd-2.0.54
# patch -p2 < /location/where/the/patch/is/webdav-2.0.54-quota-2.3any.txt
patching file modules/dav/main/mod_dav.c
patching file modules/dav/main/quotachk.h
patching file modules/dav/main/quotachk.c
patching file modules/dav/main/config5.m4
patching file configure
# ./configure --enable-modules=most --enable-mods-shared=all
(snip)
# make

Once the compilation has completed, verify that you have the 2 WebDAV modules binaries
(they should be located in /httpd-2.0.54/modules/dav/).

# ls -l ./modules/dav/fs/.libs/mod_dav_fs.so
-rwxr-xr-x 1 root root 217493 2006-033-24 10:10 ./modules/dav/fs/.libs/mod_dav_fs.so
# ls -l ./modules/dav/main/.libs/mod_dav.so
-rwxr-xr-x 1 root root 417579 2006-033-24 10:09 ./modules/dav/main/.libs/mod_dav.so

Make a backup of the of your current apache modules

# mv /usr/lib/apache2/modules/mod_dav.so /usr/lib/apache2/modules/mod_dav.so-bkp
# mv /usr/lib/apache2/modules/mod_dav_fs.so /usr/lib/apache2/modules/mod_dav_fs.so-bkp

and copy the new modules to the apache module directory

# cp ./modules/dav/main/.libs/mod_dav.so /usr/lib/apache2/modules/mod_dav.so
# cp ./modules/dav/fs/mod_dav_fs.so /usr/lib/apache2/modules/mod_dav_fs.so

Enabling quota for the site

To enable quota for the site use the "DAVSATMaxAreaSize" directive with size limit specified in Kbytes.
Again the example below shows the added directive in red.

<VirtualHost WEB_server_IP_address:80>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav

</VirtualHost>

<VirtualHost WEB_server_IP_address:443>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav
    CustomLog /var/log/apache2/access.log combined

    <IfModule mod_ssl.c>
        SSLEngine on
        SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown
    </IfModule>

    <Directory /var/www/webdav>
            DAV On
            # DAVSATMaxAreaSize: the size is specificed in Kbytes
            # since each blocks are 4K each
            # add about 50K above the limit wanted
            DAVSATMaxAreaSize 150
            AuthType Basic
            AuthName "test"
            AuthUserFile /etc/apache2/passwd.dav
            <Limit PUT POST DELETE PROPFIND PROPPATCH MKCOL COPY MOVE LOCK UNLOCK>
                Require user test
            </Limit>
    </Directory>

</VirtualHost>

A few words about the quota limit before moving on

One important thing you should be aware of is the quota limit is dependent on the block size of your Web server file
system and may cause the quota limit to be reached quicker than you would expect.
Here is an example:
If you have set the quota limit to 50 Kbyte and your file system has a block size of 4 Kbyte and say all files in the
WebDAV directory amount to a total of 48 Kbyte, when you copy a 1Kbyte text file from a windows system to
the WebDAV directory, this1Kbyte text file will occupy a 4 Kbytes block on the WebDAV folder thus exceeding
the limit even though you thought you had 2 Kbytes free.

An easy workaround is to set the limit a little bit higher than required. For example a strict limit for a quota of 150 Kbyte
would be to set the quota to 152 Kbyte. However in practice I personally add 50 Kbytes over the required limit.

Now this time in order use the "quota enabled" WebDAV modules and quota directives for the site,
you have to restart apache.

# /etc/init.d/apache2 restart

After that restart your WebDAV session with Internet Explorer or your favorite WebDAV client and copy a file in order
to exceed the 150 Kbyes limit.

At the same time you may want to see what is happening on the server in real time

tail -f /var/log/apache2/error.log

Once you reach or exceed the quota limit Internet Explorer will report the message:
"An error occurred copying some or all of the selected files"
(NetDrive will properly report that the file copy failed because the storage space has been exceeded)
and an error like the following example will be written in /var/log/apache2/error.log

[Fri Mar 24 12:26:13 2006] [error] [client 10.44.10.1] File does not exist: /var/www/webdav/impunx.log
[Fri Mar 24 12:26:13 2006] [error] WebDAV-Quota: Directory `/var/www/webdav/' size `404KB' is over `150KB'!

Configuring apache to use MySQL for more complex authentication

Installing MySql

# apt-get install mysql-server libapache2-mod-auth-mysql
Reading Package Lists... Done
Building Dependency Tree... Done
The following extra packages will be installed:
libdbd-mysql-perl libdbi-perl libmysqlclient12 libnet-daemon-perl libplrpc-perl mysql-client mysql-common
Suggested packages:
dbishell mysql-doc
The following NEW packages will be installed:
libapache2-mod-auth-mysql libdbd-mysql-perl libdbi-perl libmysqlclient12 libnet-daemon-perl libplrpc-perl mysql-client
mysql-common mysql-server
0 upgraded, 9 newly installed, 0 to remove and 0 not upgraded.
Need to get 5233kB of archives.
After unpacking 12.6MB of additional disk space will be used.
Do you want to continue? [Y/n] y

Creating a MySQL database

The following steps will outline how to create a MySQL database. This is the first of several databases
that need to be created.

The default database name and table to use with apache are respectively: http_auth and mysql_auth,
however you can use any database name you want providing that you specify it the site configuration file
(more later). In my case the I called the 1st database "sysadmins".

# mysqladmin -uroot -p create sysadmins
Enter password::

Now create the table.

# mysqladmin -uroot -p create sysadmins
# mysql -uroot -p

mysql> use sysadmins
Database changed
mysql> create table mysql_auth
    -> (
    -> username char(50) not null,
    -> passwd char(25),
    -> groups char(25)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create unique index mysqlauthix1 on mysql_auth(username);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now create a user which will be used for apache to read the database

mysql> grant select on sysadmins.* to apache@localhost identified by '1pach2'; (apache@localhost user is the user and '1pach2' is the password)
Query OK, 0 rows affected (0.00 sec)

Now insert in the table a user which we will use later for WebDAV authentication.

mysql> insert into mysql_auth (username, passwd, groups) values ('admin','1dm3n','sysadmins');
Query OK, 1 row affected (0.01 sec)

Then log out of mysql and then to check everything is OK try to log into mysql as the apache user that was
just created and test if you can read the "mysql_auth" table. It should allow to log on (otherwise you have an error
with the user name or password), allow you to select the database (otherwise there is a grant problem with the database),
and return the details of the user that was inserted.

delldebian:/etc/apache2/mods-enabled# mysql -uapache -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36 to server version: 4.0.24_Debian-10sarge1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sysadmins
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from mysql_auth;
+----------+--------+-----------+
| username | passwd | groups |
+----------+--------+-----------+
| admin | 1dm3n | sysadmins |
+----------+--------+-----------+
1 row in set (0.00 sec)

Configuring apache to use MySQL authentication

Create the apache mysql configuration file

# touch /etc/apache2//mods-available/auth_mysql.confs

and specify the host, user and password of the apache user created earlier to read the databases.

Auth_MySQL_Info localhost apache 1pach2

Now enable the apache mysql authentication modules

# ln -s /etc/apache2/mods-available/auth_mysql.load /etc/apache2/mods-enabled/auth_mysql.load
# ln -s /etc/apache2/mods-available/auth_mysql.conf /etc/apache2/mods-enabled/auth_mysql.con

Reconfiguring the site to use MySQL authentication

Once again in the example below I have added in red the extra directives to use MySQL authentication
and which database to use for that particular directory.
Taking care of which database to use is done with the Auth _MySQL_DB parameter followed
but the database name.
Pay also attention to the line in blue which I have commented out (in production I deleted it).
Failure to comment it out or delete it will result in apache still using the file "/etc/apache2/passwd.dav"
for authentication instead the MySQL database and any attempts to authenticate with any user in the
database will fail.

<VirtualHost WEB_server_IP_address:80>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav

</VirtualHost>

<VirtualHost WEB_server_IP_address:443>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav
    CustomLog /var/log/apache2/access.log combined

    <IfModule mod_ssl.c>
        SSLEngine on
        SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown
    </IfModule>

    <Directory /var/www/webdav>
            DAV On
            # DAVSATMaxAreaSize: the size is specificed in Kbytes
            # since each blocks are 4K each
            # add about 50K above the limit wanted
            DAVSATMaxAreaSize 150
            AuthType Basic
            AuthName "test"
            #AuthUserFile /etc/apache2/passwd.dav
            <Limit PUT POST DELETE PROPFIND PROPPATCH MKCOL COPY MOVE LOCK UNLOCK>
                Auth_MySQL_DB sysadmins
                Auth_MySQL_Encrypted_Passwords off
                Require user admin
            </Limit>
    </Directory>

</VirtualHost>

Now restart apache to load the MySQL authentication modules and take the MySQL directives into account.

# /etc/init.d/apache2 restart

After that restart your WebDAV session with Internet Explorer or your favorite WebDAV client and now log
with the "admin" user account created above.

Configuring the site to provide all the functionalities mentioned in
the introduction

So far I have covered the basics and all the extra functionalities are nothing more than some
variation of what has been covered.
The functionalities that am I going to give to the site are:

allow and delegate management of some part the site to a group of operators
make some part of site only visible and accessible to some customers
allow some customers to upload files (within a set quota) to a restricted part of the site
use phpmyadmin to allow the people in the operators group to manage the list
of allowed customers.Using phpmyadmin also makes some of database management tasks
much easier and quicker.

Installing phpmyadmin

# apt-get install phpmyadmin libapache2-mod-php4
Reading Package Lists... Done
Building Dependency Tree... Done
The following extra packages will be installed:
apache2-mpm-prefork libapache-mod-php4 php4 php4-mysql
Suggested packages:
php4-pear php4-gd php5-gd
The following packages will be REMOVED:
apache2-mpm-worker
The following NEW packages will be installed:
apache2-mpm-prefork libapache-mod-php4 libapache2-mod-php4 php4 php4-mysql phpmyadmin
0 upgraded, 6 newly installed, 1 to remove and 0 not upgraded.
Need to get 1815kB/6220kB of archives.
After unpacking 17.3MB of additional disk space will be used.
Do you want to continue? [Y/n] y

Now that phpmyadmin has been, is a good idea to secure and restrict it's access.
The first thing I do is move the phpmyadmin symbolic link from the Web 's root directory into
the WebDAV directory so I can control how it accessed from the site configuration file.

# mv /var/www/phpmyadmin /var/www/webdav/phpmyadmin

Then modify the site configuration file to restrict the access.
Again, following is the updated site configuration files with the added
directive in red which I'll explain after (thought the comments should make it
mostly self explanatory).

The following prevents indexing the phpmyadmin symbolic link, thus hiding it from the root the WebDAV site.
Mind you if people knew the URL they can still accesss it. However as you'll see later, further levels of restriction
to access the phpmyadmin URL lay ahead.

     # Hide restricted access to phpmyadmin
     <Directory /var/www/webdav>
             IndexIgnore phpmyadmin
     </Directory>

For further security we restrict the use of phpmyadmin to only be accessible through HTTPS rather than plain HTTP
and redirect any HTTP access to HTTPS

# redirect http://Site_Name/phpmyadmin to https://Site_Name/phpmyadmin
redirect /phpmyadmin https://Site_Name/phpmyadmin

Then access to phpmyadmin is further restricted to a specific IP address or range see (http://httpd.apache.org/docs/
for further details about the "allow, deny apache directives). As well we require authentication and only members of
the "sysadmin" group are allowed to access the phpmyadmin Web page (and of course they have log on into phpmyadmin).

After having passed the authentication you will have to the use mysql root account to log into phpmyadmin. However later
we will create an "operators" group which we will allow their members to log into phpmyadmin using their own account rather
than using the root account as it is much safer.

   # restrict access of phpmyadmin to the sysadmins group
   <Directory /var/www/webdav/phpmyadmin>
       Order Deny,Allow
          Deny From all
          allow from IP_address, IP_address_range, etc...
        AuthType Basic
        AuthName "restricted access to phpmyadmin"
          Auth_MySQL_DB sysadmins
          Auth_MySQL_Encrypted_Passwords off
         require group sysadmins
    </Directory>

Creating the operators and customers groups

The purpose of the "operators" group is to provide a segmented part of the site that they can managed but not the whole site
(which can only be managed by the "sysadmins"). Therefore you don't have to create it if you don't want to.

The procedure for creating the "operators" and "customers" groups is almost identical to the procedure for creating
the "sysadmins" group.

# mysqladmin -uroot -p create operators
Enter password:

# mysqladmin -uroot -p create customers
Enter password:

delldebian:/home/dom# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 181 to server version: 4.0.24_Debian-10sarge1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use operators
Database changed
mysql> create table mysql_auth
    -> (
    -> username char(50) not null,
    -> passwd char(25),
    -> groups char(25)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create unique index mysqlauthix1 on mysql_auth(username);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> grant select on operators.* to apache@localhost identified by '1pach2';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into mysql_auth (username, passwd, groups) values ('operator','4p2r1t4r','operators');
Query OK, 1 row affected (0.00 sec)

mysql> use customers
Database changed
mysql> create table mysql_auth
    -> (
    -> username char(50) not null,
    -> passwd char(25),
    -> groups char(25)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create unique index mysqlauthix1 on mysql_auth(username);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> grant select on customers.* to apache@localhost identified by '1pach2';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into mysql_auth (username, passwd, groups) values ('joe','bl4g','customers');
Query OK, 1 row affected (0.00 sec)

Once you have created the "operators" and "customers" databases, we need to grant the members
of the "sysadmins" group the privileges to administer to the operator and customer database and also
grant the members of the "operators" group the privileges to administer to the customer database.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> grant all on operators.* to admin@localhost identified by '1dm3n';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on customers.* to admin@localhost identified by '1dm3n';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on customers.* to operator@localhost identified by '4p2r1t4r';
Query OK, 0 rows affected (0.00 sec)

Repeat the same grant process for every member of the "sysadmins" or "operators" group you are adding
to either databases. This grant process also allow members of the "sysadmins" and "operators" groups to log
into phpmyadmin

Now that we have the "operators" and "customers" databases we can allow the members of the "operators"
group to manage their segmented part of the site and the list of customers which are allowed to download
files.

Allowing the "operators" to manage part of the site

As always see the example below for the updated site configuration file, with the added directives in red
and the removed/commented out in blue. Pay particular attention the "Auth_MySQL_DB"directive,
making sure that the correct database is specified. Otherwise this will result in failures to log on.

<VirtualHost WEB_server_IP_address:80>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav

     # Hide restricted access to phpmyadmin
        <Directory /var/www/webdav>
             IndexIgnore phpmyadmin
        </Directory>

     # redirect http://Site_Name/phpmyadmin to https://Site_Name/phpmyadmin
        redirect /phpmyadmin https://Site_Name/phpmyadmin

</VirtualHost>

<VirtualHost WEB_server_IP_address:443>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav
    CustomLog /var/log/apache2/access.log combined

    <IfModule mod_ssl.c>
        SSLEngine on
        SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown
    </IfModule>

   # restrict access to phpmyadmin
   <Directory /var/www/webdav/phpmyadmin>
       Order Deny,Allow
          Deny From all
          allow from IP_address, IP_address_range, etc...
        AuthType Basic
        AuthName "restricted access to phpmyadmin"
          # Auth_MySQL_DB sysadmins
          Auth_MySQL_DB operators
          Auth_MySQL_Encrypted_Passwords off
          # require group sysadmins
          Require group operators
    </Directory>

# give admins full access to the WebDAV root directory
# upload is unlimited
    <Directory /var/www/webdav>
        DAV On
        # DAVSATMaxAreaSize: the size is specificed in Kbytes
        # since each blocks are 4K each
        # add about 50K above the limit wanted
        # DAVSATMaxAreaSize 150
        AuthType Basic
        AuthName "test"
            <Limit PUT POST DELETE PROPFIND PROPPATCH MKCOL COPY MOVE LOCK UNLOCK>
                Auth_MySQL_DB sysadmins
                Auth_MySQL_Encrypted_Passwords off
                # Require user admin
                Require group sysadmins
            </Limit>
    </Directory>

    # Give operators full access to the operator directory
# but not to the parent directory
    # upload is limited with quota (DAVSATMaxAreaSize)
    <Directory /var/www/webdav/downloads>
        DAV On
        # since blocks are 4K each add
        # about 50K above the limit
        # limit upload size to 2 Gigs (2 000 000K)
        DAVSATMaxAreaSize 2000050
        AllowOverride None
        Options None
        AuthType Basic
        AuthName "Restricted access to the downloads directory"
            <Limit GET PUT POST DELETE PROPFIND PROPPATCH MKCOL COPY MOVE LOCK UNLOCK>
                Auth_MySQL_DB operators
                Auth_MySQL_Encrypted_Passwords off
                Require group operators #!! if you copy this make sure the correct DB is used (Auth_MySQL_DB)
            </Limit>
     </Directory>

</VirtualHost>

Once you have updated the site configuration file, reload apache 2 and restart your WebDAVsession and in
Internet Explorer, select "File" => "Open" and open the URL

https://WEB_server_IP_address/downloads

replacing "WEB_server_IP_address" by the IP address of your server or it's FQN (and don't forget to tick
the check box next to "Open as Web Folder").

This time when you are prompted for a user name and password, use the "operator" account that was created earlier.
Once in your WebDAV session as an "operator", create the directory "restricted" (right click and select "New => Folder").
We will use that directory to host files which only authorized customers.will be able to download.

Allowing customers to download files from the restricted part of the site.

Restricting access to the "restricted" directory is a very much simplified version of the WedDAV
directive section that was created earlier (added directives shown below in red).

<VirtualHost WEB_server_IP_address:80>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav

    # Hide restricted access to phpmyadmin
    <Directory /var/www/webdav>
        IndexIgnore phpmyadmin
    </Directory>

    # redirect http://downloads.lansa.co.uk/phpmyadmin to https://downloads.lansa.co.uk/phpmyadmin
    redirect /phpmyadmin https://delldebian.lansa.co.uk/phpmyadmim

    # restricted access to "/downloads/restricted" directory
    # require authentication against list of customers
    <Directory /var/www/webdav/downloads/restricted>
        AuthType Basic
        AuthName "Restricted download accesss"
        Auth_MySQL_DB customers
        Auth_MySQL_Encrypted_Passwords off
        require group customers
    </Directory>

</VirtualHost>

<VirtualHost WEB_server_IP_address:443>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav
    CustomLog /var/log/apache2/access.log combined

    <IfModule mod_ssl.c>
        SSLEngine on
        SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown
    </IfModule>

   # restrict access to phpmyadmin
   <Directory /var/www/webdav/phpmyadmin>
       Order Deny,Allow
          Deny From all
          allow from 10.44.10.1
        AuthType Basic
        AuthName "restricted access to phpmyadmin"
          # Auth_MySQL_DB sysadmins
          Auth_MySQL_DB operators
          Auth_MySQL_Encrypted_Passwords off
          # require group sysadmins
          Require group operators
    </Directory>

# give admins full access to the WebDAV root directory
# upload is unlimited
    <Directory /var/www/webdav>
        DAV On
        # DAVSATMaxAreaSize: the size is specificed in Kbytes
        # since each blocks are 4K each
        # add about 50K above the limit wanted
        # DAVSATMaxAreaSize 150
        AuthType Basic
        AuthName "test"
            <Limit PUT POST DELETE PROPFIND PROPPATCH MKCOL COPY MOVE LOCK UNLOCK>
                Auth_MySQL_DB sysadmins
                Auth_MySQL_Encrypted_Passwords off
                # Require user admin
                Require group sysadmins
            </Limit>
    </Directory>

    # give operators full access to the operator directory
# but not to the parent directory
    # upload is limited with quota (DAVSATMaxAreaSize)
    <Directory /var/www/webdav/downloads>
        DAV On
        # since blocks are 4K each add
        # about 50K above the limit
        # limit upload size to 2 Gigs (2 000 000K)
        DAVSATMaxAreaSize 2000050
        AllowOverride None
        Options None
        AuthType Basic
        AuthName "Restricted access to the downloads directory"
            <Limit GET PUT POST DELETE PROPFIND PROPPATCH MKCOL COPY MOVE LOCK UNLOCK>
                Auth_MySQL_DB operators
                Auth_MySQL_Encrypted_Passwords off
                Require group operators #!! if you copy this make sure the correct DB is used (Auth_MySQL_DB)
            </Limit>
     </Directory>

</VirtualHost>

Once you have updated the site configuration file and reloaded apache 2, open the following URL
(replacing "WEB_server_IP_address" by the IP address of your server or it's FQN):

http://WEB_server_IP_address/downloads/restricted

Then when you are prompted for a user name and password, use the "customer" account that was created earlier.

Note: as you may have noticed from the above URL, you need to specify, the full path including the name
of the directory over which the authentication is performed. This is because the restriction directive has the
effect of hiding that directory.

Allowing customers to upload files

Allowing customers to upload files is fairly easy to achieve and uses more or less the same configuration
as the "operators" section.
1st create the upload directory

# mkdir /var/www/webdav/upload
# chown www-data:www-data /var/www/webdav/upload

(you can just as easily do it through a WebDAV session logged as admin).
Then modify the site configuration file (added directives shown below in red).

<VirtualHost WEB_server_IP_address:80>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav

    # Hide restricted access to phpmyadmin
    <Directory /var/www/webdav>
        IndexIgnore phpmyadmin
    </Directory>

    # redirect http://downloads.lansa.co.uk/phpmyadmin to https://downloads.lansa.co.uk/phpmyadmin
    redirect /phpmyadmin https://delldebian.lansa.co.uk/phpmyadmim

    # restricted access to "/downloads/restricted" directory
    # require authentication against list of customers
    <Directory /var/www/webdav/downloads/restricted>
        AuthType Basic
        AuthName "Restricted download accesss"
        Auth_MySQL_DB customers
        Auth_MySQL_Encrypted_Passwords off
        require group customers
    </Directory>

</VirtualHost>

<VirtualHost WEB_server_IP_address:443>

    Servername testwebdav.lansa.co.uk
    Documentroot /var/www/webdav
    CustomLog /var/log/apache2/access.log combined

    <IfModule mod_ssl.c>
        SSLEngine on
        SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown
    </IfModule>

   # restrict access to phpmyadmin
   <Directory /var/www/webdav/phpmyadmin>
       Order Deny,Allow
          Deny From all
          allow from 10.44.10.1
        AuthType Basic
        AuthName "restricted access to phpmyadmin"
          # Auth_MySQL_DB sysadmins
          Auth_MySQL_DB operators
          Auth_MySQL_Encrypted_Passwords off
          # require group sysadmins
          Require group operators
    </Directory>

    # give operators full access to the operator directory
    # but not to the parent directory
    # upload is limited with quota (DAVSATMaxAreaSize)
    <Directory /var/www/webdav/downloads>
        DAV On
        # since blocks are 4K each add
        # about 50K above the limit
        # limit upload size to 2 Gigs (2 000 000K)
        DAVSATMaxAreaSize 2000050
        AllowOverride None
        Options None
        AuthType Basic
        AuthName "Restricted access to the downloads directory"
            <Limit GET PUT POST DELETE PROPFIND PROPPATCH MKCOL COPY MOVE LOCK UNLOCK>
                Auth_MySQL_DB operators
                Auth_MySQL_Encrypted_Passwords off
                Require group operators #!! if you copy this make sure the correct DB is used (Auth_MySQL_DB)
            </Limit>
     </Directory>

    # allow customers full access to the upload directory
    # but not to the parent directory
    # upload is limited with quota (DAVSATMaxAreaSize)
    <Directory /var/www/webdav/upload>
        DAV On
        # since blocks are 4K each add
        # about 50K above the limit
        # limit upload size to 200 Megs (200 000K)
        DAVSATMaxAreaSize 200050
        AllowOverride None
        Options None
        AuthType Basic
        AuthName "Restricted access to the upload directory"
            <Limit GET PUT POST DELETE PROPFIND PROPPATCH MKCOL COPY MOVE LOCK UNLOCK>
                Auth_MySQL_DB customers
                Auth_MySQL_Encrypted_Passwords off
                Require group customers #!! if you copy this make sure the correct DB is used (Auth_MySQL_DB)
            </Limit>
     </Directory>

</VirtualHost>

Once you have updated your site configuration file, reload apache 2 and restart your WebDAVsession and in
Internet Explorer select "File" => "Open" and open the following URL:

https://WEB_server_IP_address/upload

replacing "WEB_server_IP_address" by the IP address of your server or it's FQN
(and don't forget to tick the check box next to "Open as Web Folder").

Then when you are prompted for a user name and password, use again the "customer" account.

Note: with the above configuration, any customers with a valid user name and password will be able to upload, download,
rename and delete files in the upload directory.

Using phpmyadmin to allow the people in the operators group to manage the list
of allowed customers

Managing the list of customers using phpmyadmin is fairly easy. However for those of you
not familiar with phpmyadmin, here a quick tutorial on how to do it. Open the following URL in your
Web browser:

https://WEB_server_IP_address/phpmyadmin

replacing "WEB_server_IP_address" by the IP address of your server or it's FQN

Then when you are prompted for a user name and password, use the "operator" account to get
past the authentication at the Web server level. Subsequently you will be presented with the
phpmyadmin login screen and once again use the "operator" account to login.

Once logged in phpmyadmin, click the "customers" database and the "browse" icon (1st from the left)
under the "Action" menu. From there you can add a customer by clicking "Insert new row", edit their
details (password change, etc...) by clicking the "Edit" icon (pencil icon) or delete them by clicking the
"Delete" icon (red cross icon) and if you want to disable and account either edit it's details or delete it.

Lastly remember the following (which, actually can be used to disable an account):

user name, password and groups are case sensitive
make sure that every customers are part of the "customers" group
and likewise any operators are part of the "operators" group.

Otherwise any issue with the above 2 will prevent successful login.

Footnote, comments/suggestions

Lastly, I think the title of this document is a bit of mouthful, so if anyone can come up
with a more concise one or has any other comments/suggestion to improve this HowTo please,
drop me line at dom2319@yahoo.co.uk

Debian Apache 2 WebDAV server with quota using SSL and MySQL for authentication

Introduction

Configuration overview

Credits

Installing Apache 2 and the WebDAV module

Creating a certificate for the Web server

Create a certificate authority

Create the certificate for your Web server

Configuring apache to serve HTTPS pages

WebDAV directory

Enabling WebDAV and configuring simple authentication

Enabling public browsing access to the site

Recompiling the apache WebDAV module to provide quota

Recompiling the apache WebDAV modules

Enabling quota for the site

A few words about the quota limit before moving on

Configuring apache to use MySQL for more complex authentication

Installing MySql

Creating a MySQL database

Configuring apache to use MySQL authentication

Reconfiguring the site to use MySQL authentication

Configuring the site to provide all the functionalities mentioned in the introduction

Installing phpmyadmin

Creating the operators and customers groups

Allowing the "operators" to manage part of the site

Allowing customers to download files from the restricted part of the site.

Allowing customers to upload files

Footnote, comments/suggestions

Configuring the site to provide all the functionalities mentioned in
the introduction