Обсуждение: RLS policy dump/restore failure due to elided type-casts

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[] ));


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.

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

Karl Czajkowski <karlcz@isi.edu> writes:
>   CREATE POLICY delete_stuff ON stuff
>   FOR DELETE USING ('example attribute value' = ANY ( ((SELECT current_attributes()))::text[] ));

Just out of curiosity, why are you doing it like that, and not simply

USING ('example attribute value' = ANY (current_attributes()))

It seems like you're going out of your way to complicate matters.

            regards, tom lane

On Apr 20, David G. Johnston modulated:

> 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)"
>

Aha!  Thanks for that.  Now I realize there is a much simpler
workaround:

  CREATE POLICY delete_stuff ON stuff
  FOR DELETE USING ('example attribute value' = ANY ( current_attributes() ));

Somehow I fell into a pattern of writing scalar subqueries and I
forgot to just write the function invocation directly as an
expression!  This policy seems to round-trip through pg_dump
successfully. :-)

By the way, we expose our session context parameter as an array result
partly because we have other scenarios where we store row-level
application ACLs in array-typed columns and may want to do
array-to-array intersection tests in a policy expression.  It was my
intuition that gin indexing of those array-based ACL columns would
then make these policy expressions pretty fast to evaluate.


Karl

On Apr 20, Tom Lane modulated:
> Karl Czajkowski <karlcz@isi.edu> writes:
> >   CREATE POLICY delete_stuff ON stuff
> >   FOR DELETE USING ('example attribute value' = ANY ( ((SELECT current_attributes()))::text[] ));
>
> Just out of curiosity, why are you doing it like that, and not simply
>
> USING ('example attribute value' = ANY (current_attributes()))
>
> It seems like you're going out of your way to complicate matters.
>
>             regards, tom lane
>

Going out of my way to complicate matters is my specialty. :-)

I spend more of my time writing programs that generate and transform
SQL from domain-specific languages, rather than writing SQL by
hand. In this case, I think I composed the subquery reflexively
without thinking that there is a more direct idiom for this case...


Karl

Karl Czajkowski <karlcz@isi.edu> writes:
> On Apr 20, Tom Lane modulated:
>> Just out of curiosity, why are you doing it like that, and not simply
>> USING ('example attribute value' = ANY (current_attributes()))
>> It seems like you're going out of your way to complicate matters.

> Going out of my way to complicate matters is my specialty. :-)

;-).  Nonetheless, I agree this is a bug in ruleutils -- it should be
able to emit the expression in a way that will re-parse the same way.
Will look into fixing it.

            regards, tom lane