Re: CASE eval issue

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