Re: RLS policy dump/restore failure due to elided type-casts

On Wed, Apr 20, 2016 at 5:35 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 04/20/2016 05:18 PM, Karl Czajkowski wrote:
>
>> Our row level policies require very explicit casts in order to be
>> accepted by the DB, but those casts are discarded in the dumped policy
>> statements. Thus, an attempt to reload the dump file fails. I'm not
>> sure if the underlying problem is that the cast shouldn't be required
>> in the first place, or if the normalization applied to the policy
>> expression is just incorrect.
>>
>> Below is a trivialized example that shows the problem while removing
>> complexity found in our real policies. We are doing this to implement
>> differentiated policies in a web application, where the web server
>> stores authenticated web client context into session parameters and
>> our policies check against those attributes rather than DB roles.
>>
>> To work around this, we have to maintain our policies in a separate
>> SQL file, manually edit the dumps, and reapply our working policies.
>> This is obviously undesirable in the long run, where dumps taken as
>> periodic backups are not directly usable to reconstruct the DB...
>>
>>
>> Karl
>>
>>
>> Create a test database with these contents:
>>
>> CREATE FUNCTION current_attributes() RETURNS text[]
>> STABLE AS $$
>> BEGIN
>> RETURN (
>> SELECT array_agg(value)
>> FROM
>> json_array_elements_text(current_setting('ourapp.attributes')::json)
>> );
>> EXCEPTION WHEN OTHERS THEN
>> RETURN NULL::text[];
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> CREATE TABLE stuff (
>> value text PRIMARY KEY
>> );
>>
>> CREATE POLICY delete_stuff ON stuff
>> FOR DELETE USING ('example attribute value' = ANY ( ((SELECT
>> current_attributes()))::text[] ));
>>
>
>
> Trying to figure out how this works in the case above. Looks to me like
> you are comparing a text value to a text[].
>
> Also why the the cast to text[], when the return value from the function
> is text[]?
>
>
The problem here is that:

scalar = ANY (array)
is different than
scalar = ANY (subquery)

The normalization in ?ruleutils? is munging things so that a query that is
written as "scalar = ANY(array) is transformed into one that is "scalar =
ANY(subquery)"

The extra parentheses and the cast are needed to force a "scalar =
ANY(array)" where the basic expression looks like "scalar = ANY(subquery)"

Hope that helps - I could probably explain better but hopefully its not
necessary..

David J.

>
>>
>> The output of pg_dump (and similarly the '\d' command in psql) drops
>> the cast:
>>
>> CREATE POLICY delete_stuff ON stuff
>> FOR DELETE TO PUBLIC USING (('example attribute value'::text = ANY ((
>> SELECT current_attributes() AS current_attributes))));
>>
>>
>> And this causes an error when executing the dump file on a new database:
>>
>> ERROR: operator does not exist: text = text[]
>> HINT: No operator matches the given name and argument type(s). You
>> might need to add explicit type casts.
>>
>>
​