Plone SQLAlchemy PAS Plugin

In addition to Plone instances, we have PHP applications heavily relying on PostgreSQL. Then there is a need to have them sharing the same user accounts. Luckily Zope2 product PluggableAuthService (PAS) serves such needs well. For our case pas.plugins.sqlalchemy is an ideal fit.

With instructions, my working installation is for:

Ubuntu = 12.04
postgresql-server-dev = 9.1
Plone = 4.1.5
SQLAlchemy = 0.7.7
pas.plugins.sqlalchemy = 0.3
psycopg2 = 2.4.5
z3c.saconfig = 0.13
zope.sqlalchemy = 0.7

Here are some hints worth notice. First I add these lines in devlop.cfg:

eggs +=
    psycopg2
    pas.plugins.sqlalchemy

zcml +=
    pas.plugins.sqlalchemy

And the "zcml-additional" parameter goes in [instance] section of base.cfg:

zcml-additional =
    <configure xmlns="http://namespaces.zope.org/zope"
               xmlns:db="http://namespaces.zope.org/db">
      <include package="z3c.saconfig" file="meta.zcml" />
      <db:engine name="pas" url="postgresql://postgres:mypass@localhost/plonepas" />
      <db:session name="pas.plugins.sqlalchemy" engine="pas" />
    </configure>

If you use postgres instead postgresql in the URL format, a warning will be added in the log:

SADeprecationWarning: The SQLAlchemy PostgreSQL dialect has been renamed from 'postgres' to 'postgresql'. The new URL format is postgresql[+driver]://<user>:<pass>@<host>/<dbname>

Run bin/buildout -c devlop.cfg and activate SQLAlchemy PAS in Site Setup.

Now let's see how it works. By default, the admin creates an account, and the account data will appear in ZMI /mysite/acl_users/source_users.

Go /mysite/acl_users/plugins, check User_adder Plugins and move sql up as the first Active Plugin, this will switch account data stored in SQL tables.

Check /mysite/acl_users/sql for more info about SQLAlchemy user/group/prop manager.

Here is the list of relations:

$ psql -d plonepas
psql (9.1.3)
Type "help" for help.

plonepas=# \d
                 List of relations
 Schema |        Name        |   Type   |  Owner
--------+--------------------+----------+----------
 public | group_members      | table    | postgres
 public | groups             | table    | postgres
 public | principals         | table    | postgres
 public | principals_id      | sequence | postgres
 public | role_assignment_id | sequence | postgres
 public | role_assignments   | table    | postgres
 public | users              | table    | postgres
(7 rows)

plonepas=# \d users
                    Table "public.users"
      Column      |            Type             | Modifiers
------------------+-----------------------------+-----------
 id               | integer                     | not null
 login            | character varying(64)       |
 password         | character varying(64)       |
 salt             | character varying(12)       |
 enabled          | boolean                     | not null
 email            | character varying(40)       |
 portal_skin      | character varying(20)       |
 listed           | integer                     |
 login_time       | timestamp without time zone |
 last_login_time  | timestamp without time zone |
 fullname         | character varying(40)       |
 error_log_update | double precision            |
 home_page        | character varying(40)       |
 location         | character varying(40)       |
 description      | text                        |
 language         | character varying(20)       |
 ext_editor       | integer                     |
 wysiwyg_editor   | character varying(10)       |
 visible_ids      | integer                     |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "ix_users_login" UNIQUE, btree (login)
    "ix_users_email" btree (email)
    "ix_users_enabled" btree (enabled)
    "ix_users_fullname" btree (fullname)
Foreign-key constraints:
    "users_id_fkey" FOREIGN KEY (id) REFERENCES principals(id)

By switching, one of source_users or sql plugins can be chosen to provide authentication service. For example, when using sql plugin, after changing the user's password via Plone user interface, you will see in the SQL table the corresponding columns, password and salt, values updated.