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
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?
(once a PDXPUG member, always a PDXPUG member ;)
In response to
pdxpug by date
|Next:||From: Aaron Burt||Date: 2011-10-20 19:56:58|
|Subject: Re: CASE eval issue|
|Previous:||From: gabrielle||Date: 2011-10-19 17:15:45|
|Subject: Reminder: October meeting tomorrow!|