Grokking Technology

Storing Plone users in an SQL database

In the past, a couple of Plone sites we have built had large numbers of users for whom it was easier to store their details in an SQL Backend rather than Plone itself. When it came to bringing one of those system up-to-date it was appropriate to review the process and perhaps use a different approach such as a full-on LDAP deployment. During this review process, we learned how to deploy a new product and figured a solution to a possible use case.

Deploying pas.plugins.sqlalchemy

Unfortunately the help documents on the web did not help us install this product and it took some research to discover much simpler instructions buried deep in the plone.users mailinglist ; so deep it remains hidden. Here is what we did:

Provided you have set the precedence of sql in the User Adder plugins (step 7) you can add new users and they will be stored in your SQL database. Notice that the settings in step 4 apply throughout an instance. If you have several Plone instances within a single Zope instance, then each Plone instance with the PAS SQL Plugin activated will share the same user SQL database - as in the Use Case below.

Use Case for multiple sites

Note: the following notes only apply for a group of Plone sites; although the use case may be general, this solution is specific to Plone

Suppose you require a number of related sites all of which relate broadly to the same group of users:

In your sites, you want to allow members to be able to logon and authenticate themselves. Then depending on the site and the specific user, various roles can be assigned to allow access to different types of content. When deploying this plugin, user information is stored in the database but user and group permissions remains in Plone. Using this behaviour we can use group definitions to control who can do what in specific sites; all with a minimum of tweaking the Plone instances. First the minimal changes:

  1. set sql to the top of all its relevant plugin types except for Group Management - we want Plone instances to drive this part of user management
  2. set Intranet/Extranet Workflow as the default for each site
  3. for the internally_published state, under the Permissions tab, switch on Authenticated permission for both view and access options (this is optional and depends what you want Lapsed Members to see)
  4. activate the changes for the workflow
  5. in each site, add a membership Group with a roles you want all Active members to have
  6. for each site, add additional groups for any special subsets you may want setting roles as required
  7. add members in one site assigning each to appropriate groups for that site
  8. in additional sites, add members to any groups peculiar to these individual sites

Having done all this, you should now get the following behaviour

These behaviours are easily managed simply by adding users to one or more groups. Further, a new user only needs added in one site and usually Group membership can be assigned at that time (except for Groups which are specific to individual sites). Active Membership can also be controlled by an external application updating the SQL database independent of any Plone instance i.e. for lapsed members, remove their appropriate Group membership via the database. This procedure will affect all related sites immediately without having to do anything else.

Page created on Sun 25 Apr 2021 by Andy Ferguson
Tag list: plone sql