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

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com