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.