473,836 Members | 1,604 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Synchronising multiple common fields among tables

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.use rs"
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_u sers
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_pke y 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_p key 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

Nov 11 '05 #1
1 2601
do*@cus.org.uk (Dominic Marks) wrote in message news:<20******* *************@c us.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_sto rage (
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_sto rage sus USING (uid);

CREATE OR REPLACE RULE "INSERT" AS ON INSERT TO samba_users DO INSTEAD
(
INSERT INTO users (uid,gid,userna me,password,ful lname) VALUES
(NEW.uid,NEW.gi d,NEW.username, NEW.password,NE W.fullname);
INSERT INTO samba_users_sto rage (uid,date,expir ed,newpassword)
VALUES (NEW.uid,NEW.da te,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_sto rage 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_sto rage WHERE uid = OLD.uid;
);

CREATE TABLE postfix_users_s torage (
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','sec ret','tom
hebbron',curren t_date,0,0);

SELECT * FROM users;
SELECT * FROM samba_users_sto rage;
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
Nov 11 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2609
by: Beyonder | last post by:
I have five tables in my database, there are actually NO common fields between them, not even a KEY or ID or anything like that, except for the "body" of a blob field. and that text is not identical, just a portion of that text is identical. each table has 5 fields, all different except the blob, which is called "message", so normally I use something like: select * from table1 where message like '%apple%';
8
3356
by: Jan van Veldhuizen | last post by:
The UPDATE table FROM syntax is not supported by Oracle. I am looking for a syntax that is understood by both Oracle and SqlServer. Example: Table1: id name city city_id 1 john newyork null
1
1421
by: Michael Thomas | last post by:
Hi everyone Having a slight problem synchronising tables in MS Access 2002. I have two tables: Products (StockCode*, Description, CostPrice, SellingPrice) and
0
1333
by: Chris Naylor | last post by:
Right, further to previous question not so long ago... I have a fairly complex access database that has a number of linked tables in it. The main table of this database is a details one that has a large number of fields in it that I have mapped to a HanDBase table on my PDA. I've got my two databases mapping the data in the appropriate fields on each table. The only problem is that because the tables in the Access file are linked to a...
22
23392
by: Matthew Louden | last post by:
I want to know why C# doesnt support multiple inheritance? But why we can inherit multiple interfaces instead? I know this is the rule, but I dont understand why. Can anyone give me some concrete examples?
19
2947
by: Shwetabh | last post by:
Hi, I have two tables: Code and Color. The create command for them is : create table Color( Partnum varchar(10), Eng_Color char(10), Span_Color char(20), Frch_Color char(20), CONSTRAINT pkPartnum PRIMARY KEY(Partnum)
52
6370
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible variations(combination of fields), - then act on each group in some way ...eg ProcessRs (oRs as RecordSet)... the following query will get me the distinct groups
4
2471
by: Dave White | last post by:
Hello Everyone, I have created two tables to track my students' lessons. Each student is responsible for most, but not all. of the lessons. I've tried a junction table but I can't figure out an easy way to assign one or multiple lessons to a student. tbl_Students
4
1923
daoxx
by: daoxx | last post by:
Hi I'm using MS Access 2002 / VBA 6. I've a database with several tables / forms, and some code. Everything is working fine. I have 2 fields in each table. Each of these fields contain names from employees. What I need to find among all these 2 fields, is the most common name (the one that appears the most) in both fields together. Some of the fields may be empty. I need to perform this with a button, and I would also like that the...
0
9677
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10560
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10607
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10262
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9389
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7798
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5662
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5838
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4468
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.