Adding ACL notion to existing tables - Mailing list pgsql-general

Hello !

I would like known your appreciation, and to get some advices and tips
about a design issue about adding ACL notion.

I have several *existing* tables that manages some user's items which
are quite different (postal adresses, photos, climbing_skills,...).
Please note that "Users" are not PostgreSQL users, they are records in a
"member" table.

####################################
-- USER table
CREATE SEQUENCE seq_member_id;
CREATE TABLE members
(
   pk_member_id INT8 NOT NULL DEFAULT NEXTVAL('seq_member_id');
   ... (some user related fields)...
);

-- ITEMS tables
CREATE SEQUENCE seq_item_type_a_id;
CREATE TABLE item_type_a
(
   pk_item_id INT8 NOT NULL DEFAULT NEXTVAL('seq_item_type_a_id'),
   fk_member_id INT8 NOT NULL,
   ... (some specific fields) ...
   CONSTRAINT fkey_member_id FOREIGN KEY (fk_member_id) REFERENCES
members(pk_member_id)
);

CREATE SEQUENCE seq_item_type_b_id;
CREATE TABLE item_type_b
(
   pk_item_id INT8 NOT NULL DEFAULT NEXTVAL('seq_item_type_b_id'),
   fk_member_id INT8 NOT NULL,
   ... (some other specific fields) ...
   CONSTRAINT fkey_member_id FOREIGN KEY (fk_member_id) REFERENCES
members(pk_member_id)
);
####################################

Several years laters, there's a need for theses users to be able to
define ACE (Access Control Entries) for their items. So, I have to add
an ACL (Access Control List) notion.

I thought of two solution :
- Solution A
- Solution B


# SOLUTION A.
####################################

CREATE TABLE acl_ace_item
(
   item_type VARCHAR(40) NOT NULL,
   item_id INT8 NOT NULL,
   ace_label VARCHAR(100) NOT NULL,
   CONSTRAINT PRIMARY KEY (item_type, item_id),
   CONSTRAINT chk_item_type CHECK item_type IN ('item_type_a',
'item_type_b'),
   CONSTRAINT chk_acl_uniqueness UNIQUE (item_type, item_id)
);

NOTA : The 'item_type' field will indicates which table need to be
JOINed. I don't known if I can put a JOIN in a CASE structure, but I
have to give a try or find another way.

PRO : Avoid any changes to the existing 'item_type_XXX' tables.

CONS : a) If the user delete a 'item_type_a', 'item_type_b' record, I
will have orphan record in the acl_ace_item table.
        b) No way to have a FOREIGN KEY CONSTRAINT on 'item_id' field
since it can be an id from 'item_type_a', 'item_type_b'.

####################################



# SOLUTION B.
####################################

CREATE SEQUENCE seq_acl_item_id;
CREATE TABLE acl_item
(
   pk_acl_id INT8 NOT NULL DEFAULT NEXTVAL('seq_acl_item_id'),
   CONSTRAINT pkey_acl_item PRIMARY KEY(pk_acl_id)
);

CREATE TABLE acl_ace_item
(
   fk_acl_id INT8 NOT NULL,
   ace_label VARCHAR(100) NOT NULL,
   CONSTRAINT chk_ace_uniqueness UNIQUE (fk_acl_id, ace_label),
   CONSTRAINT fkey_acl_id FOREIGN KEY (fk_acl_id) REFERENCES
acl_item(pk_acl_id);
);

ALTER TABLE item_type_a ADD COLUMN fk_acl_id INT8;
ALTER TABLE item_type_a ADD CONSTRAINT fkey_acl_id FOREIGN KEY
(fk_acl_id) REFERENCES acl_item(pk_acl_id);
ALTER TABLE item_type_a ADD CONSTRAINT chk_uniqueness_acl_id UNIQUE
(fk_acl_id);
ALTER TABLE item_type_b ADD COLUMN fk_acl_id INT8;
ALTER TABLE item_type_b ADD CONSTRAINT fkey_acl_id FOREIGN KEY
(fk_acl_id) REFERENCES acl_item(pk_acl_id);
ALTER TABLE item_type_b ADD CONSTRAINT chk_uniqueness_acl_id UNIQUE
(fk_acl_id);


NOTA : - The 'acl_item' table could appears to be useless, but it
enables me to avoid orphan records in the 'acl_ace_item' items.
        - A NULL 'fk_acl_id' value in 'item_type_XXX' tables would means
NO defined ACL, and so free access.
        - No records in the 'acl_ace_item' table for an existing acl_id
would also means NO defined ACL, and so free access.

PRO : a) JOIN between 'item_type_XXX' tables and acl_ace_item could be
faster than SOLUTION A.

CONS : a) If the user delete a 'item_type_a', 'item_type_b' record, I
will have orphan record in the acl_ace_item table.
        b) No way to have a UNIQUE CONSTRAINT that would cover existing
'item_type_XXX', so it is possible to have a same fk_acl_id value in the
'item_type_a' and the 'item_type_b' table. Which is a situation I would
avoid.

####################################

As you can see, I'm not really convinced for SOLUTION A or SOLUTION B as
they have both some CONS. And they will both require some stored
procedures to check references integrity. Solution B seems to be less
weird to me.

What would you do in that kind of situation ? Which solution would you
take ? Would you uses another design to solve that problem ?

Feel free to leave your appreciation about that problem. Your advices,
tips or some interesting URLs are welcome also !

Thanks in advance !

Kind regards,

--
Bruno Baguette - bruno.baguette@gmail.com