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

Re: CASE eval issue

From: Aaron Burt <aaron(at)bavariati(dot)org>
To: pdxpug(at)postgresql(dot)org
Subject: Re: CASE eval issue
Date: 2011-10-20 19:56:58
Message-ID: 20111020195658.GG21112@kailarose.respond2.com (view raw or flat)
Thread:
Lists: pdxpug
On Wed, Oct 19, 2011 at 07:40:14PM -0700, Jeff Davis wrote:
> 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.

Ah, that explains it.

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

I tried every variation of the conditional I could think of.

> 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?

I'm trying to minimally retool some code that does a naive
"select * from <table> where X" query - I want it to work nicely
when it runs as a user w/o privileges on a particular column.

The per-user view approach is interesting but involves defining the view at
deployment, and having an inconsistent table name in the code.  Similarly,
I'd rather not have to define a function whenever I deploy the app.

I think I'll try just having the code do the permissions check, and choose
its query string based on that.

And I haven't even checked if a user can have UPDATE or INSERT privileges
to a column, without SELECT privileges.

Thanks for the help,
  Aaron

In response to

pdxpug by date

Next:From: wesDate: 2011-10-21 15:10:55
Subject: Re: Specifying Host With psycopg2
Previous:From: Jeff DavisDate: 2011-10-20 02:40:14
Subject: Re: CASE eval issue

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