We have several Drupal sites, no I am not typing this blog on one. We needed a way to have single sign on with these Drupal sites. One of which is PostgreSQL Conference. There are a few modules out there that can do it, some don't work with PostgreSQL, some are usable but not user friendly (HTTP AUTH) and still others use external services such as OAuth. I didn't want any of these. I wanted a simpler, more flexible solution. I found it with a little PostgreSQL know-how and a modification to the Drupal settings.php file. The following is ten steps that assume we have three sites. At the end of the steps we will have single login between the three sites.. Step 1: Create users

psql -U postgres;
create user one with encrypted password 'foo';
create user two with encrypted password 'bar'
create user three with encrypted password 'baz';

Step 2: Create database and schemas

create database drupal;
\c drupal -- (assumes the use of psql)
create schema one authorization one;
create schema two authorization two;
create schema three authorization three;

Step 3: Sandbox users

alter user one set search_path = 'one';
alter user two set search_path = 'two';
alter user three set search_path = 'three';

Step 4: Install Drupal For the sake of brevity I am going to assume you have unpacked three copies of drupal in the same directory. Perhaps /home/www/one, /home/www/two, /home/www/three . At this point you would use your web browser and set up drupal normally. Just assign your users appropriately to each install and set your database to drupal. Step 5: Turn off caching (for testing) Go into the Drupal Administration pages and turn off caching for every install. Step 6: Alter users and sessions location This will break your installs initially. Don't fret. It does not really matter which one you pick but for consistency we will assume that the drupal install one is the canonical version.

alter table one.users set schema public;
alter table one.sessions set schema public;

Step 7: Fix perms

create role drupal user one,two,three;
alter table users owner to drupal;
alter table sessions owner to drupal;
grant insert,update,delete on users to drupal;
grant insert,update,delete on sessions to drupal;

Step 8: Modify settings.php Drupal offers the ability to use a single database for multiple installs using an array called db_prefix. Modify the value in each install to:

$db_prefix = array('users' => 'public.',
                 'sessions' => 'public.',);

Step 9: Test At this point you should be able to login to each site using the user/pass from the one install. To test it further add a new user to any of the installs and see if you can login on a different one. Step 10: Marvel (Oh and turn back on caching) That's right, marvel. No obnoxious plugins. Simple overhead. Works even if the installs aren't on the same machine (although you would need to modify pg_hba.conf and possibly postgresql.conf).