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
