[Serdev] views on postgres tables

[Greg Fausak]

I've got a situation where I am doing SIP termination
for many different companies.  I want to give them access
to the database for information about thier account, but
I don't want them to be able to see other accounts.

I did a quick hack on the backend postgres that I am using.
I am using subscriber, alias, grp and location tables.  What I did
was deny all access to those tables to everyone and I created
views into those tables.  Each view is allowed to look at
a slice of the information based on database login information.
So, if I log in as company A I can only see company A subscribers,
locations, grps and aliases.

Although this was very easy in concept, implementation was a little
more difficult because of rules on insert/delete/update through a
view.  Postgres makes this possible with the syntax:

This creates the table...

create table basesubscriber
(
         domain  varchar(100) not null,
         user_id varchar(50) not null
	...
);

and the view...

create view subscriber as
select
av.domain,av.user_id
   from
         basesubscriber av
  where
         av.domain = however_you_related_the_current_user_to_the_domain;

then you can create insert, delete and update rules...

  create
   rule subscriber_insert
     as on insert
     to subscriber
     do instead
insert
   into
         basesubscriber (domain,user_id)
values (however_you_related_the_current_user_to_the_domain, NEW.user_id)

You can also disable insert, delete or update like ...
  create
   rule subscriber_delete
     as on delete
     to subscriber
     do instead nothing;

This is very handy, and has enabled me to give external
access to my database to my customers.  I am thinking about
checking the ddl in to create the postgres database and views.
The SER application continues to run without changes on top
of the views, and you can hand out different logins to different
people accessing the database to give them a view of just thier
accounts.  I am hesitant because the :
	however_you_related_the_current_user_to_the_domain
is probably going to be unique from site to site.  I simply create a
login name to the postgres database that matches the domain name, but
others will probably want a table to relate login name to domain.

Finally, I need the ability to change *all* table names.  location and
subscriber provide that ability, as does alias.  grp does not
provide that functionality.  Who maintains 'group'?  Can you add the
functionality to change the group table name?

---greg
Greg Fausak