Re: Separation of clients' data within a database

John McCawley wrote:
> Oh, I see, so there's one master schema, and one customer schema, and
> the customer schema views are automatically filtered based on
> login...Makes sense...I will definitely try to implement this, thanks!
>
I've on-and-off toyed with the idea of accomplishing a similar objective
by using a temporary table (which are session specific, so different
logins would see their own temp table). Haven't worked through all the
details and so am not sure if it makes much sense this way verses using
a function to identify the current user, but here is a short script to
illustrate the idea:


CREATE SCHEMA universe;
SET search_path=universe, pg_catalog;

CREATE TABLE customer
(
  customer varchar(12) NOT NULL,
  CONSTRAINT customer_pkey PRIMARY KEY (customer)
);

CREATE TABLE invoice
(
  customer varchar(12) NOT NULL,
  invoice varchar(12) NOT NULL,
  CONSTRAINT invoice_pkey PRIMARY KEY (customer, invoice),
  CONSTRAINT "$1" FOREIGN KEY (customer) REFERENCES customer (customer)
);

INSERT INTO customer VALUES ('Alice');
INSERT INTO customer VALUES ('Bob');

INSERT INTO invoice VALUES ('Alice', 'inv a1');
INSERT INTO invoice VALUES ('Alice', 'inv a2');
INSERT INTO invoice VALUES ('Alice', 'inv a3');
INSERT INTO invoice VALUES ('Alice', 'inv a4');

INSERT INTO invoice VALUES ('Bob', 'inv b1');
INSERT INTO invoice VALUES ('Bob', 'inv b2');
INSERT INTO invoice VALUES ('Bob', 'inv b3');

SELECT * FROM customer NATURAL JOIN universe.invoice;
/*
 customer | invoice
----------+---------
 Alice    | inv a1
 Alice    | inv a2
 Alice    | inv a3
 Alice    | inv a4
 Bob      | inv b1
 Bob      | inv b2
 Bob      | inv b3
(7 rows)
*/

CREATE SCHEMA customer;
SET search_path=customer, pg_catalog;

CREATE TEMPORARY TABLE customer AS SELECT * FROM  universe.customer
WHERE customer = 'Alice';
SELECT * FROM customer NATURAL JOIN universe.invoice;
/*
 customer | invoice
----------+---------
 Alice    | inv a1
 Alice    | inv a2
 Alice    | inv a3
 Alice    | inv a4
(4 rows)
*/


DROP TABLE customer;
CREATE TEMPORARY TABLE customer AS SELECT * FROM  universe.customer
WHERE customer = 'Bob';
SELECT * FROM customer NATURAL JOIN universe.invoice;
/*
 customer | invoice
----------+---------
 Bob      | inv b1
 Bob      | inv b2
 Bob      | inv b3
(3 rows)
*/