Mediawiki with Postgres on Debian

A short guide to install Mediawiki on Debian with PostgreSQL 9.1.With a fix for this error:

"Attempting to connect to database "postgres" as superuser "postgres"... error: No database connection"

Installing packages

The server is still using Debian Squeeze, but I expect it would quite the same for the new Debian Wheezy. Here I used squeeze-backports.

 Add the backports repository if needed:

echo "deb http://backports.debian.org/debian-backports squeeze-backports main contrib non-free" >> /etc/apt/sources.list

Install everything:

apt-get update
apt-get -t squeeze-backports install apache2 postgresql-9.1 postgresql-contrib php5-pgsql
apt-get -t squeeze-backports install imagemagick libdbd-pg-perl
apt-get -t squeeze-backports install mediawiki

I use a separate IP for the wiki, so need to add it to the interface:

mcedit /etc/network/interfaces

# wiki on it's own IP
auto eth0:3
iface eth0:3 inet static
    address 192.168.10.4
    netmask 255.255.255.0

/etc/init.d/networking restart

Apache configuration

# I use the mod_rewrite module in Apache
a2enmod rewrite

# I prefer the config file in sites-enabled
# (but it's really just a symlink to /etc/mediawiki/apache.conf):
mv /etc/apache2/conf.d/mediawiki.conf /etc/apache2/sites-enabled

My virtual host config:

<VirtualHost *:80>
    ServerName wiki.example.lan
    ServerAlias wiki.example.lan
    ServerAdmin [email protected]
    DocumentRoot /docs/www-wiki

    ErrorLog /var/log/apache2/wiki-error.log
    CustomLog /var/log/apache2/wiki-access.log combined

    ServerSignature On

    Alias /icons/ "/usr/share/apache2/icons/"

    RewriteEngine On
    RewriteRule ^/w(iki)?/(.*)$  http://%{HTTP_HOST}/index.php/$2 [L,NC]

    <Directory /docs/www-wiki/>
        Options +FollowSymLinks
        AllowOverride All
        # Default is Deny. Exceptions listed below with "Allow ...":
        Order Deny,Allow
        Deny from All
        Satisfy any
        # LAN
        Allow from 192.168.10.0/24
        # VPN
        Allow from 10.0.10.0/24

# If using LDAP
#        AuthType Basic
#        AuthName "Example Wiki. Requires user name and password"
#        AuthBasicProvider ldap
#        AuthzLDAPAuthoritative on
#        AuthLDAPURL ldap://localhost:389/ou=People,dc=example,dc=lan?uid
#        AuthLDAPGroupAttribute memberUid
#        AuthLDAPGroupAttributeIsDN off
#        Require ldap-group cn=users,ou=Groups,dc=example,dc=lan
    </Directory>

    # some directories must be protected
    <Directory /docs/www-wiki/config>
        Options -FollowSymLinks
        AllowOverride None
    </Directory>

    <Directory /docs/www-wiki/upload>
        Options -FollowSymLinks
        AllowOverride None
    </Directory>

    <Directory "/usr/share/apache2/icons">
        Options Indexes MultiViews
        AllowOverride None
        Order allow,deny
        Allow from all
    </Directory>
</VirtualHost>

Moving files

I used a directory other than the default /var/lib/mediawiki. So I had to move things over:

cp -rp /var/lib/mediawiki /docs/www-wiki

The only tricky part, with the fix:

Before starting the web configurator in http://wiki.example.lan/config/ you need to define a password for the "postgres" database user. Mediawiki will start the psql client as the www-data system user, but with the -U argument to set the user to "postgres". Even if you defined a password for the system user "postgres", this is not the password of the database user "postgres".

So you need to start psql as the postgres system user, which you can do as root using sudo -c, and then set the password inside the psql client:

sudo -u postgres psql
psql (9.1.9)
Type "help" for help.

postgres=# \password
Enter new password:
Enter it again:
postgres=# \q

If you don't do this, the Mediawiki config will end with this error:

Attempting to connect to database "postgres" as superuser "postgres"... error: No database connection

And a big pink and unhelpful error box below.

The Postgresql log (tail /var/log/postgresql/postgresql-9.1-main.log) will show:

FATAL:  password authentication failed for user "postgres"

Finally

Now you just have to move LocalSettings.php to /etc/mediawiki/.

And if you used a different install root, you have to edit it to change the MW_INSTALL_PATH:

define('MW_INSTALL_PATH','/docs/www-wiki');

The Access ODBC PostgreSQL boolean mess

I like using PostgreSQL with an MS Access frontend for various little database applications, but always had trouble with boolean fields. Finally, after all sorts of tests, I think I nailed down what is needed to make it work, and avoid all these errors like "operator does not exist: boolean = integer", "Data Type mismatch in criteria expression", "invalid input syntax for type boolean: "-" (#7)", and another one which I can't remember. What we want is simple:

• Booleans should be usable in Access queries with simple statements like SELECT x FROM y WHERE z; (and no silly stuff like where z=true, z='t', z=-1 or the insane where cbool(z)=true)
• Booleans should appear as check boxes in Access forms and tables

I still don't quite understand why this is not hanlded transparently by the ODBC driver, but anyway here is what seems to be needed with PostgreSQL 7.4:

• In PostgreSQL, make sure your boolean fields have a default value. Access cannot handle NULLs in booleans.
• Configure the ODBC driver for "Bools as Char": no, "True is -1": yes.
• Create these functions and operators in your database and/or in template1 for new databases:

  CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
  AS '$libdir/plpgsql', 'plpgsql_call_handler'
  LANGUAGE c;
  
  CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
  
  CREATE FUNCTION inttobool(integer, boolean) RETURNS boolean
  AS '
  begin
    if $1=0 and not $2 then
    return true;
    elsif $1<>0 and $2 then
    return true;
    else
            return false;
    end if;
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION inttobool(boolean, integer) RETURNS boolean
  AS '
  begin
    return inttobool($2, $1);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION notinttobool(boolean, integer) RETURNS boolean
  AS '
  begin
    return not inttobool($2,$1);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION notinttobool(integer, boolean) RETURNS boolean
  AS '
  begin
  return not inttobool($1,$2);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE OPERATOR = (
  PROCEDURE = inttobool,
  LEFTARG = boolean,
  RIGHTARG = integer,
  COMMUTATOR = =,
  NEGATOR = <>
  );
  
  CREATE OPERATOR <> (
  PROCEDURE = notinttobool,
  LEFTARG = integer,
  RIGHTARG = boolean,
  COMMUTATOR = <>,
  NEGATOR = =
  );
  
  CREATE OPERATOR = (
  PROCEDURE = inttobool,
  LEFTARG = integer,
  RIGHTARG = boolean,
  COMMUTATOR = =,
  NEGATOR = <>
  );
  
  CREATE OPERATOR <> (
  PROCEDURE = notinttobool,
  LEFTARG = boolean,
  RIGHTARG = integer,
  COMMUTATOR = <>,
  NEGATOR = =
  );

  After linking the tables in Access, if you want check boxes in table view, you need to go into table design mode. It will show an error because it's a linked table; just ignore it. Select your boolean field(s) and set their Lookup -> Display Control to Check Box.

Update: See also this page.

The Access ODBC PostgreSQL boolean mess

I like using PostgreSQL with an MS Access frontend for various little database applications, but always had trouble with boolean fields. Finally, after all sorts of tests, I think I nailed down what is needed to make it work, and avoid all these errors like "operator does not exist: boolean = integer", "Data Type mismatch in criteria expression", "invalid input syntax for type boolean: "-" (#7)", and another one which I can't remember. What we want is simple:

• Booleans should be usable in Access queries with simple statements like SELECT x FROM y WHERE z; (and no silly stuff like where z=true, z='t', z=-1 or the insane where cbool(z)=true)
• Booleans should appear as check boxes in Access forms and tables

I still don't quite understand why this is not hanlded transparently by the ODBC driver, but anyway here is what seems to be needed with PostgreSQL 7.4:

• In PostgreSQL, make sure your boolean fields have a default value. Access cannot handle NULLs in booleans.
• Configure the ODBC driver for "Bools as Char": no, "True is -1": yes.
• Create these functions and operators in your database and/or in template1 for new databases:

  CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
  AS '$libdir/plpgsql', 'plpgsql_call_handler'
  LANGUAGE c;
  
  CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
  
  CREATE FUNCTION inttobool(integer, boolean) RETURNS boolean
  AS '
  begin
    if $1=0 and not $2 then
    return true;
    elsif $1<>0 and $2 then
    return true;
    else
            return false;
    end if;
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION inttobool(boolean, integer) RETURNS boolean
  AS '
  begin
    return inttobool($2, $1);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION notinttobool(boolean, integer) RETURNS boolean
  AS '
  begin
    return not inttobool($2,$1);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE FUNCTION notinttobool(integer, boolean) RETURNS boolean
  AS '
  begin
  return not inttobool($1,$2);
  end;
  '
  LANGUAGE plpgsql;
  
  CREATE OPERATOR = (
  PROCEDURE = inttobool,
  LEFTARG = boolean,
  RIGHTARG = integer,
  COMMUTATOR = =,
  NEGATOR = <>
  );
  
  CREATE OPERATOR <> (
  PROCEDURE = notinttobool,
  LEFTARG = integer,
  RIGHTARG = boolean,
  COMMUTATOR = <>,
  NEGATOR = =
  );
  
  CREATE OPERATOR = (
  PROCEDURE = inttobool,
  LEFTARG = integer,
  RIGHTARG = boolean,
  COMMUTATOR = =,
  NEGATOR = <>
  );
  
  CREATE OPERATOR <> (
  PROCEDURE = notinttobool,
  LEFTARG = boolean,
  RIGHTARG = integer,
  COMMUTATOR = <>,
  NEGATOR = =
  );

  After linking the tables in Access, if you want check boxes in table view, you need to go into table design mode. It will show an error because it's a linked table; just ignore it. Select your boolean field(s) and set their Lookup -> Display Control to Check Box.

Update: See also this page.