Re: CREATE RULE problem/question requesting workaround

From: Chris Travers <chris(at)travelamericas(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: CREATE RULE problem/question requesting workaround
Date: 2003-12-07 16:05:08
Message-ID: 1070813106.4455.81.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, 2003-12-08 at 00:46, Tom Lane wrote:
> Chris Travers <chris(at)travelamericas(dot)com> writes:
> > Is there any way to try to enforce a rewrite
> > rule in such a away as to do the select query as normal, but if
> > necessary raise an exception (to abort the select)?
>
> No.
>
> You need to rethink your permissions design. One idea that you *can*
> make work is to have a view that everyone can read, but are only able to
> see selected rows in. pg_stats works that way, and I think there are
> examples in the information schema as well.
>
Not that this will help me at the moment, but any plans to remove the
requirement that SELECT rules be unconditional, and use DO INSTEAD? I
can see where lifting this restriction could cause some headaches for
those implementing these sorts of rules (infinite recursion, etc), but
it would be really nice to have for these types of circumstances
(restricting the tables that must allow everyone read access to 2 or
so).

The way I would like to this implemented would be:
1) For all rules but DO INSTEAD on a select statement, the query results
are discarded. This could allow utility functions to be "triggered" by
select statements.

2) A different option would be to allow statement-level triggers for
select queries. (We don't need tuple-level triggers, which would
probably be overkill).

The reason I am trying to do this is because I am trying to create a
simple, lightweight layer to port a program designed for dedicated
hosting (and database permissions) into a shared hosting environment. I
can get all permissions to be enforced aside from select permissions.
Adding every table as a view is not going to be likely to be feasible
since it will involve a major maintenance nightmare. Depending on
requirements, we may look at allowing most of the database to be
readable to all users, but this is not ideal and I am looking for
maintainable workarounds. I do thank you for your suggestion and we may
use it for one or 2 tables.

Best Wishes,
Chris Travers

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-12-07 16:46:25 Re: CREATE RULE problem/question requesting workaround
Previous Message Seum-Lim Gan 2003-12-07 16:02:28 Re: pg_hba.conf change in 7.4