Re: CASE eval issue

On Tue, 2011-10-18 at 19:24 -0700, David E. Wheeler wrote:
> >> select id,
> >> case when has_column_privilege('mytable', 'secure_column', 'SELECT')
> >> then 'NA'
> >> else secure_column
> >> end as secure_column
> >> from mytable where id=12345;

> Yeah, I guess it does evaluate the column. (And yes, I meant to reverse the `then` and `else` clauses, sorry.)

It doesn't actually evaluate the column. CASE statements do force an
evaluation order, try:

SELECT CASE WHEN TRUE THEN 1/1 ELSE 1/0 END;

That succeeds, so obviously it's not evaluating the 1/0.

What's happening is that the permissions check is happening before the
query runs. PostgreSQL (at this point) has no way to know whether the
has_column_privilege will return TRUE or FALSE, or whether that will be
consistent with the declared permissions on the table.

> So you might need to use a function. Something like this (untested):

Yes, a function could work. Did you reverse the conditional again
though?

But to take a step back: why are you trying to solve the problem this
way? Would using a view over the allowed columns work?

Regards,
Jeff Davis
(once a PDXPUG member, always a PDXPUG member ;)