On Wed, Apr 20, 2016 at 5:35 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> 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 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)" Hope that helps - I could probably explain better but hopefully its not necessary.. David J. > >> >> 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. >> >>