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
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,
In response to
pdxpug by date
|Next:||From: wes||Date: 2011-10-21 15:10:55|
|Subject: Re: Specifying Host With psycopg2|
|Previous:||From: Jeff Davis||Date: 2011-10-20 02:40:14|
|Subject: Re: CASE eval issue|