Re: CREATE RULE problem/question requesting workaround

On Mon, 2003-12-08 at 01:39, Tom Lane wrote:

>
> Is it really not possible to express what you need with plain old SQL
> permissions?  It seems like you are going out of your way to avoid the
> obvious solution.

Thanks-- as you can see, that is what the application was *designed* to
use.  The problem is porting the application which was designed under
the assumption that every user would have a database account into an
environment where that is not possible.  In the new environment, only a
few user accounts will exist and these will be used by many users.
Therefore I can only assume one user account for the entire application.

BTW, I have figured out the last of my issues, and I now have a working
solution after a EUREKA moment today...  FWIW, here is the solution.  In
writing the utilities to make this solution work, I have composed a
number of other helpful utilities which others may find of use.

Here is the solution:

CREATE SCHEMA shadow;

SELECT move_relation('my_table', 'public', 'shadow');
-- move_relation is a custom function I wrote

CREATE VIEW my_table AS
SELECT *, oid FROM shadow.my_table
WHERE (SELECT check_func());
-- I then programatically add insert, update, and delete rules
-- check_func() returns BOOL, controlling whether the query returns any
-- rows.  It can also raise an exception, causing the transaction to
-- abort.

I finally (a few moments ago) finally solved the last problem I was
having which was a permissions issue.  Now it works well and has no
known issues.  Not quite as elegant as I had hoped, but far more elegant
than I had feared.

Because this package also by necessity also contains a number of other
useful functions I may send it to the HACKERS list.

Best Wishes,
Chris Travers