Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux