Synchronising multiple common fields among tables

[Dominic Marks]

Hi,

(I apologise if this is the wrong list, I haven't posted
to a postgresql.org mailing list before, general seemed
like a good catch-all)

I am trying to implement a centralised authentication system
using PostgreSQL as the central repository for user and password
information. I want to keep a table which contains core user
details like the username and password and then use this data
from a number of other tables which will use the username and
password as well as adding their own service-specific fields.

My Plan:
Each service will have it's own table which to which
it has full priviledges. It can update the password and
other information which is linked from the central
username and password table and this will cascade back
up so that the users password for all the services is
changed. For Example, Samba is going to be accessing
this database via PAM so I would like it to be possible
for users to change their password from their Windows PC
and have their password for all their other services
updated at the same time. I don't want the services
to have any direct access to the central users table.

Example Tables:

core=# \d users
Table "public.users"
Column | Type | Modifiers
----------+-----------------------+-----------
uid | integer | not null
gid | integer | not null
username | character varying(32) | not null
password | character varying(64) | not null
fullname | character varying(64) | not null
Indexes: users_pkey primary key btree (uid)

core=# \d samba_users
Table "public.samba_users
Column | Type | Modifiers
-------------+-----------------------------+-----------
uid | integer | not null
gid | integer | not null
username | character varying(32) | not null
password | character varying(64) | not null
fullname | character varying(64) | not null
date | timestamp without time zone | not null
expired | integer | not null
newpassword | integer | not null
Indexes: samba_users_pkey primary key btree (uid)

core=# \d postfix_users
Table "public.postfix_users"
Column | Type | Modifiers
----------+------------------------+-----------
uid | integer | not null
gid | integer | not null
username | character varying(32) | not null
password | character varying(64) | not null
fullname | character varying(64) | not null
homedir | character varying(128) | not null
maildir | character varying(128) | not null
Indexes: postfix_users_pkey primary key btree (uid)

As these example tables hopefully illustrate the uid, gid,
username, password, and fullname fields should be linked
among all of the tables - but independantly accessed via
a service specific table.

Is this is a stupid plan ?

I had a number of ideas about how to implement this, one was
using a separate VIEW for each services table. However I
have discovered that PostgreSQL only supports read-only views
at the current time.

I also thought that I would be able to use a foreign key to
link the records together. I would like to link the records
on their uid and then have updates to the linked fields
cascaded back up to the main user table. I haven't been able
to figure out how to do this without making the password
field UNIQUE, which won't work very well.

My final idea was that I could use a TRIGGER as a method to
update the linked fields, I think I will be able to do this
if I create two functions for each service table which then
uses an UPDATE to synchronise the tables. This to me though
seems to be the ugliest way of doing what I want.

Is this my only option ?
Am I being stupid in general ?

NOTE: If you haven't guessed already a lot of this is new to
me, but I'll do my best to work with whatever advice I'm
given.

Tips, comments, suggestions all appreciated.

Thanks,
--
Dominic
<dom at cus.org.uk>

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Hebbron]

do*@cus.org.uk (Dominic Marks) wrote in message news:<20********************@cus.org.uk>...

Hi, I had a number of ideas about how to implement this, one was
using a separate VIEW for each services table. However I
have discovered that PostgreSQL only supports read-only views
at the current time.
PostgreSQL does allow writable views - you must define a rule to
define the SQL to execute when an INSERT, UPDATE or DELETE is executed
against the view. See the PostgreSQL docs for CREATE RULE.

I also thought that I would be able to use a foreign key to
link the records together. I would like to link the records
on their uid and then have updates to the linked fields
cascaded back up to the main user table. I haven't been able
to figure out how to do this without making the password
field UNIQUE, which won't work very well.

My final idea was that I could use a TRIGGER as a method to
update the linked fields, I think I will be able to do this
if I create two functions for each service table which then
uses an UPDATE to synchronise the tables. This to me though
seems to be the ugliest way of doing what I want.

Is this my only option ?
Am I being stupid in general ?

NOTE: If you haven't guessed already a lot of this is new to
me, but I'll do my best to work with whatever advice I'm
given.

Tips, comments, suggestions all appreciated.

Try the following for an exmple of using rule on an view
CREATE TABLE users (
uid int PRIMARY KEY,
gid int NOT NULL,
username char(32) NOT NULL,
password char(32) NOT NULL,
fullname char(64) NOT NULL
);
CREATE TABLE samba_users_storage (
uid int NOT NULL PRIMARY KEY REFERENCES users ON DELETE CASCADE
ON UPDATE CASCADE,
date timestamp NOT NULL,
expired integer NOT NULL,
newpassword integer NOT NULL
);

CREATE OR REPLACE VIEW samba_users AS
SELECT u.*, sus.date, sus.expired, sus.newpassword FROM users u
INNER JOIN samba_users_storage sus USING (uid);

CREATE OR REPLACE RULE "INSERT" AS ON INSERT TO samba_users DO INSTEAD
(
INSERT INTO users (uid,gid,username,password,fullname) VALUES
(NEW.uid,NEW.gid,NEW.username,NEW.password,NEW.ful lname);
INSERT INTO samba_users_storage (uid,date,expired,newpassword)
VALUES (NEW.uid,NEW.date,NEW.expired,NEW.newpassword);
);

CREATE OR REPLACE RULE "UPDATE" AS ON UPDATE TO samba_users DO INSTEAD
(
UPDATE users SET username = NEW.username, password = NEW.password,
fullname = NEW.fullname WHERE uid = OLD.uid;
UPDATE samba_users_storage SET date = NEW.date, expired =
NEW.expired, newpassword = NEW.newpassword WHERE uid = OLD.uid;
);

CREATE OR REPLACE RULE "DELETE" AS ON DELETE TO samba_users DO INSTEAD
(
DELETE FROM samba_users_storage WHERE uid = OLD.uid;
);

CREATE TABLE postfix_users_storage (
uid int NOT NULL PRIMARY KEY REFERENCES users ON DELETE CASCADE
ON UPDATE CASCADE,
homedir char(128) NOT NULL,
maildir char(128) NOT NULL
);

INSERT INTO samba_users VALUES (1,1,'tom','secret','tom
hebbron',current_date,0,0);

SELECT * FROM users;
SELECT * FROM samba_users_storage;
SELECT * FROM samba_users;

Hope that helps - sorry I don't have time to complete the postfix
table - but use the samba view/rules as a template.

--
Tom Hebbron