Re: CREATE RULE problem/question requesting workaround

On Tue, 2003-12-09 at 00:54, Tom Lane wrote:
> Chris Travers <chris@travelamericas.com> writes:

>
> I guess I'm questioning the assumption that that's not possible.
> Having many users share an account when you want to enforce different
> permissions for each user seems like a fundamentally bad idea.  Postgres
> users are sufficiently lightweight entities that I think you could and
> should just make more of 'em.
>
>             regards, tom lane

Moving the system onto a server operated by a shared hosting company--
they have limits to the number of users I can use.  This is an attempt
to circumvent that limit and allow for the app to be aware of a much
larger number.  In most other web apps, they simply use a user table and
do no permissions enforcement, delegating that to the web app.

However, I don't want to go this route because the web app is more
exposed (security-wise) than the database server.  It would also require
a huge degree of retrofitting into the app.  I have actually solved all
the major technical hurdles and am in the final stages of assembling my
solution.  The solution provides for:

1:  A table of database user accounts which are allowed to bypass these
triggers (f. ex. for backup/restore of the database).

2:  A customizable view permission system which can be used to block
rows as well as queries.

3:  A number of utility functions which form the building block of these
allow you to:
    * Request a table of the column names in the table.
    * Move a table from one schema to another
    * Check to see if a relation is really a table (and/or a view).

The permissions are checked against the same catalog which is used to
store permissions metadata in the standard version of the software
(permissions are assigned in "levels" to "modules", and each "module"
usually contains many tables).  In the standard version, these
permissions are translated into database permissions and GRANTED as
appropriate.  In the shared hosting version, the permission triggers
simply check this table, so it is not that different.

Best Wishes,
Chris Travers