Login To Website Against Accounts in PostgreSQL Database

I am in the middle of migrating from an old Zope2/Python2 server to the latest Zope5/Python3 and am having an issue due to SimpleUserFolder and CookieCrumbler no longer being available as they once were. I know I can install Products.CMFCore and get a new version of CookieCrumbler, but it's SimpleUserFolder that is really causing the issue.

We have all of our usernames/password stored in a PostgreSQL database and had a python script that would return the info through a modded SimpleUserFolder.py file. This way, when logging in, not only would it check against Zope users, but it would also auth against the users in the database.

This new version of CookieCrumbler doesn't really provide all the functionality I need like the old one did, and since SimpleUserFolder doesn't exist anymore, I can't mod a script to perform the same functions.

I tried installing Products.CMFCore in order to get CookieCrumbler, but this new version doesn't have all of the same options such as specifying the Login Page ID, Logout Page ID, and Not Authorized ID. Plus, even if I could mod CookieCrumbler.py to provide these extra options, I still don't have a way to call to the python script which in turn calls to the database and allows users to login.

I know I've written a lot but I wanted to be very clear on the situation.

Here's an even simpler explanation of the workflow I need:

  1. User visits the login page
  2. User enters their username and password
  3. The system authenticates the user again the user accounts in the PGSQL database and either successfully logs them in or says not auth'd
  4. The user is also given a cookie with specific variables (name, password, etc.) so that when they are logged in they are only allowed to access the portions of the site available to them

Would love some help with this. Thanks in advance.

The proper solution is a PAS plugin that deals with the authentication against Postgres.

1 Like

I had a very similar situation, ie CookieCrumbler, SimpleUserFolder, but PW in ZODB.

While I had support by gocept, we found no way to make this work on Python 3 / Zope 4, so we migrated to Products.PluggableAuthService, which works like a charm.

OK, I have PluggableAuthService up and running. Talk about limited docs on how to use this thing. Spent half a day figuring out how to add a plugin.

Is there any way to modify the tokens that CookieAuthService uses? For example, I want to specifically set the login and password tokens as something other than __ac_name and __ac_password. I also need to specifically set the logout and not auth'd pages like I used to do with Cookie Crumbler.

Still figuring out how to interface with the PG DB but I wanted to update with the next issue.

This is not pure Zope at the moment (may a refactoring is possible):

If you do not use it you may want steal ideas from it :slight_smile:

I looked at that before and unfortunately that is for Zope2, not Zope5. I even tried installing it via pip and it starting trying to install all kinds of stuff including the old Zope. Hasn't been updated in 4 years and is not compatible with the current PAS.

I've actually spent the entire day trying to duplicate CookieAuthHelper just to modify the cookie params I needed to change. I finally have it where I can add the interface but the plugin never shows up in the drop down list.

Moving to PAS seems like it might not be such a great idea, especially if the answer is "write you own plugins to do what you need it to do". That's a bit crazy considering all I'm trying to do is login to a secure website against a PG database and issue a cookie.

If someone can provide actual assistance, rather than just saying you should use X, I'm all ears, but the docs are terrible and incomplete, and reading the source code to guess how to do something is kind of crazy for this simplistic an ask.

You should understand the PAS philosophy (and try to adhere to it).
PAS splits all (most) aspects regarding authentication into subtasks, each one described by an interface. You have e.g. an extraction task (take information from the request which might be relevant for authentication), an authentication task (look at the info from the potential extractions and try to authenticate a user with it), a challenge task (interact with the user to get his login information), ...
Plugins can implement one or several of those interfaces; they are activated for some of those interfaces (and then play a part in the overall process). Sometimes, the order is important.

If all you want is to compare login information against user descriptions in a Postgres database, you only need a corresponding authentication plugin. You should be able to use available plugins for most other subtasks. If you have in addition special requirements for other subtasks (e.g. use your own input names (rather than the standard ones)), you would adapt other plugins as well (likely by deriving from a standard plugin, e.g. the CookieAuthHelper).

To make one of your plugins available, you must register it. Look at the corresponding registrations for the standard plugins in Products.PluggableAuthServiice:__init__.py (--> registerMultiPlugin).

1 Like

As far as the auth against the db, I'm lost. Saying I need a corresponding auth plugin is fairly obvious. Not being rude by saying that, but I know I need something that will auth against the db. The issue is, there doesn't seem to be anything available to do that, and from what I understand, that would mean I would need to write a plugin from scratch to accomplish this simple task.

If I am misunderstanding or missing something, please let me know.

Been looking at this ScriptablePlugin for PAS and I'm thinking that perhaps there is a way to use that to write a python script and a zsql method using the existing SQLAlchemy DB connection to auth users against the database.

Is this possible? If so, specifically, how?

You already have found out that you need an authentication plugin. Thus, look at an example, e.g. ZODBUserManager. ZODBUserManager stores user information in the ZODB and authenticates against that. What you need is authenticate against user information in a Postgres. Thus adapt ZODBUserManager.authenticateCredentials to look in your Postgres table for user information rather than in the ZODB. It really is simple.

I go this working after a while:

1.) Install PAS and Products.SQLAlchemyDA
2.) Add "Pluggable Auth Service" to a folder you want to protect
3.) Add the "Cookie Auth Helper" Plugin to acl_users
4.) Add "ZODB User Manager" in case you want ZODB Users to have access and/or if you want a fallback.
5.) Add "Scriptable Plugin" - i called it psql_scripts
6.) To "psql_scripts" add a "Z SQL" named "fetch_user_by_name" with a variable "login":

SELECT "id", "username", "password" FROM auth_user WHERE username=<dtml-sqlvar login type="string"> LIMIT 1;

Where you have to adopt it for your db.
7.) Add a "Script" and call it: "authenticateCredentials" with the variable "credentials":

login = credentials.get("login", None)
password = credentials.get("password", None)

if not login or not password:
    return (None, None)
    
for user in context.fetch_user_by_name(login=login):
    if not user.username == login:
        continue

    # Check password
    if not password == user.password:
        return (None, None)
    
    return (user.id, user.username)

return (None, None)

Now you need to enable it all:

Hope it works for you @kittonian else we see us afterwards :slight_smile:

Kind regards,
René

Plone Foundation Code of Conduct