Thread: transparently log a right error

Hi list,

How could I transparently log an error where user miss a right,
I know how to write a RULE, but not this way:

ie:
* user don't have a direct access to tables but to procedures that
   manipulates tables.

* He try to make a: SELECT * FROM table5
   which normally returns: "ERROR: right refused for relation table5"

* I wanna log the user's name into a separate table, but still
   having PG reporting the usual error (NOT using regular logs)

something like:
INSERT INTO security.crime(default, session_user, 'SELECT', 'table5');

Of course, if it could be generic, it would be better :)

JY
--
No one born with a mouth and a need is "innocent".
        -- Greg Bear

On 26/11/2009 11:56 PM, Jean-Yves F. Barbier wrote:
> Hi list,
>
> How could I transparently log an error where user miss a right,
> I know how to write a RULE, but not this way:
>
> ie:
> * user don't have a direct access to tables but to procedures that
>    manipulates tables.
>
> * He try to make a: SELECT * FROM table5
>    which normally returns: "ERROR: right refused for relation table5"
>
> * I wanna log the user's name into a separate table, but still
>    having PG reporting the usual error (NOT using regular logs)

That sort of thing has a significant issue that makes it hard to use
effectively:

You're trying to record events about possibly read-only actions by the
user. The user can extract information, then ROLLBACK their transaction.
If you're recording their actions as a log within the database its self,
your log will be rolled back as well.

PostgreSQL doesn't have autonomous transaction support, which would be
required to let the log table changes be retained even if the user rolls
back the transaction. It could be done with dblink, but not particularly
well.

Honestly, processing the regular text logs (or using csv log mode) is
your best bet at this point.

--
Craig Ringer