Re: seeking advices for function

On Wed, 22 Jun 2011 15:07:16 -0500, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

...
>
> I think it's much better to use the database log to record security
> violations.

Ok, so I suppose I have to use such a program as pg_fouine (or even just a
script with greps) and email results to the DBA.

> Wrapping SQL with a function like this is going to be a
> problem factory. For example, it's a total optimization fence if you
> ever need to do something like join against your 'view'. IMO, it's a
> total non-starter.

This function is to be used against only one table; for joined queries,
I intend to use the same kind of function, however involving all needed tables.

The goal is (if possible) eliminate views because if I've got 200 user
profiles, I'll be obliged to generate 200 x (many)viewS.

> If you *must* log to a table in a view definition, or want to sneakily
> hook custom behaviors to a view generally, you can do something like
> this.
>
> say your view is:
> CREATE VIEW v as SELECT * FROM foo;
>
> organize your plpgsql function like this:
> CREATE FUNCTION priv_check(_view text) RETURNS bool AS
> $$
> BEGIN
> IF NOT has_table_privilege(session_user, _view, 'SELECT') THEN
> /* insert into log via dblink (see dblink docs) */
> RAISE ...
> END IF;
>
> RETURN true;
> END;
> $$ LANGUAGE PLPGSQL;
>
> now you can work up your view like this:
> CREATE VIEW v as SELECT * FROM foo WHERE (SELECT priv_check('foo'));
>
> I'm pretty sure postgres is going to be smart enough to run priv_check
> only once per select from the view in all reasonable cases. dblink
> remains the only way to emit records you want to keep from a
> transaction that you want to roll back without recovering.

Ok, I keep that idea in mind :), thanks.

> merlin

--
X-rated movies are all alike ... the only thing they leave to the
imagination is the plot.