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.klaver@aklaver.com