How To Setup SQLPASPlugin to Authenticate Against A PostgreSQL Database

Documents the setup of SQLPASPlugin for SQL based authentication in Plone 2.5, using PostgreSQL.


Site Configuration Details:

Here is some information on the site that I set this up on:

  • Plone Version: 2.5

  • Zope Version: 2.9.3

  • Python Version: 2.4.3

  • Server OS: Debian GNU/Linux 3.1


A Note About Site With Existing Users

If you setup SQLPASPlugin on a site that already has users, there is no provision to preserve existing passwords. Users will have to reset their passwords to access the site. Also, if you want to encrypt passwords, ensure that you enable encryption before asking users to reset their passwords.


Creation of Database

SQLPASPlugin may be used to authenticate against any SQL Database for which there is a Zope connector available. For the purpose of this how to, we will use PostgreSQL. Assuming you have PostgreSQL installed, the first step is to create a new database and postgres user for SQLPASPlugin to use. You may also use an existing user if you prefer.


If you wish to create a new postgres user and database, you can use this script.


To use this script, execute the following command as the postgres user:


 psql template1 -f sqlpas_db.sql

If no errors are returned, your database is ready to accept users!


Installation of Psycopg

To connect to the Postgres database, Plone needs to use a database connector object which needs to be installed. To do this, download the latest version of pyscopg from this site.

After the download is finished, change to the directory you downloaded to, and do the following:

  • Extract the archive
     tar xvzf psycopg2-latest.tar.gz
  • Change to the extracted directory
     cd psycopg2-2.0.5.1
  • To build psycopg, you will also need libpq-fe.h which is a part of the posgres development package. If you are on Debian or Ubuntu, you can apt-get install it.
    sudo apt-get install libpq-dev
  • Build the product using the python that your Plone instance uses. If you are using installed Plone using the Unified installer, your python home is /opt/Plone-2.5/Python-2.4.3. 
    /opt/Plone-2.5/Python-2.4.3/bin/python setup.py build
  • Install the product into your python site-packages
    sudo /opt/Plone-2.5/Python-2.4.3/bin/python setup.py install
  • Copy the ZPsycopgDA directory to your instance Products folder
    sudo cp -R ZPsycopgDA /opt/Plone-2.5/instances/client1/Products
  • Restart Zope

If installation was sucessful, you should now see Z Psycopg 2 Database Connection as addable in the ZMI.


Setting Up The DB Connection in the ZMI

Once Zope has restarted, add a Z Psycopg 2 Database Connection to the root of your Plone site. You may name it anything you want, so long as you remember what you called it. Use the following configuration parameters for the Connection String field (or whatever you changed them to in the script):

dbname=sqlpasdb user=sqlpas password=sqlpas!password

Once you have filled in the required fields, click Save Changes. Zope should now tell you that the database connection is open. You cannot continue if the connection is not open.


Installation of SQLPASPlugin

  • Get the latest version of SQLPASPlugin
    cd /opt/Plone-2.5/instances/client1/Products
    svn export https://svn.plone.org/svn/collective/PASPlugins/SQLPASPlugin/trunk SQLPASPlugin
  • Make sure that the Product is owned by the plone user on your system
    sudo chown -R plone:users
  • Restart Zope
  • In Portal Quick Installer (Add/Remove Programs in the Plone Control Panel) install SQLPASPlugin

Configuring SQLPASPlugin (select connection, select encryption scheme)

It is now time to put it all together. In the Plone Control Panel you should see SQL Authentication under Add On Product Configuration near the bottom of the navigation menu. Click on it and select the database connection you just setup.

  • Use the link in the SQL Authentication Control Panel to go to the source_properties configuration screen. In col_mapping enter the following:
email/email
full_name/fullname
  • Save the changes. Your site will now authenticate against the newly created PostgreSQL database.

Enabling Encryption

By default, SQLPASPlugin currently stored passwords as plain text. You may configure it to use MD5 or SHA encryption algorithms.
  • In the root of your Plone site in the ZMI, go to the acl_users folder (note: the acl_users folder at the root of your plone site, NOT your Zope root).
  • Go to the source_users folder.
  • Select the properties tab.
  • In default_encryption enter sha or md5.


mysql, etc.

Posted by Flemming Bjerke at Jun 12, 2007 10:13 PM
SQLPASPlusin has many other opportunities. First of it can use mysql (ans sqlite but I haven't tried) as well postgresql. To make it work with mysql, I had to make a table called users, first, including the columns: username and password.

In the acl_user in your Plone site, you will see how two sql objects have been made. But, you can also add SQLRolePlugin from the plugin add menu. After you have created a roles database with at least the columns: username and rolename, you can administer roles from the database menu.

According to the code, there seems to be other opportunities as well.

using sqlpasplugin from svn at the date from this comment

Posted by Gabriel Ramirez at Oct 24, 2007 05:02 AM
is almost labeled near 1.0 but you need the following changes:
as the comment above shows, you need to create a table to store the user roles so the following is about postgresql:
create table sqlpas.roles (
  username varchar(64),
  roles text
);
grant all on table sqlpas.roles to sqlpas;
and after the steps to modify the encryption repeat the same steps but select source_roles to instead of source_users; to map the roles to the table and columns, so plone can grab the roles from the database.

if when you try to create a user in plone, it tells you which the user is duplicated, check your plone/zope logs for errors and if you see the following:

NameError: global name 'utf8' is not defined
the Psycopg2, 2.0.6 at this time, have the following bug:

http://www.initd.org/tracker/psycopg/ticket/190

so change the 44 line as the ticket, or search for self.encoding = enc and change to self.encoding = "utf8"

user in use or not valid error

Posted by Ed Crewe at Apr 09, 2008 10:21 AM
This will occur whenever user creation fails ... in my case following the above instructions fails for ver. 1.0
Add SQL authentication in plone creates acl_users/source_roles/sqlAddRoleForUser/query that expects a table:
create table roles(
username varchar(64),
rolename text
);
... or requires editing of the role queries (e.g. remove the non-essential column name specification).