Skip site navigation (1) Skip section navigation (2)

Re: CASE eval issue

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
Cc: Aaron Burt <aaron(at)bavariati(dot)org>, pdxpug(at)postgresql(dot)org
Subject: Re: CASE eval issue
Date: 2011-10-20 02:40:14
Message-ID: 1319078414.16256.35.camel@jdavis (view raw or flat)
Thread:
Lists: pdxpug
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 ;)


In response to

Responses

pdxpug by date

Next:From: Aaron BurtDate: 2011-10-20 19:56:58
Subject: Re: CASE eval issue
Previous:From: gabrielleDate: 2011-10-19 17:15:45
Subject: Reminder: October meeting tomorrow!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group