If a row-level security policy contains a set returning function, pg_dump returns an incorrect serialization of that policy if the return type of the function was altered - Mailing list pgsql-bugs

Dear PostgreSQL developers,

first, I love the database! Really. For years. Like a marriage. In a good way. Now, on to the bug.

Summary: If a row-level security policy contains a set returning function, pg_dump returns an incorrect serialization of that policy if the return type of the function was altered after the policy was created.

Affected versions: I was able to reproduce the problem on Ubuntu Linux 22.04 with PostgreSQL versions 13.4, 14.4, and 15beta2.

Steps to reproduce the issue: In the attachments, there is a minimal example to reproduce this bug. Please save both the attached files to a folder of your choice, and then run reproduce-bug.sh

If you prefer not to run the bash script, you can run the following code snippet instead.

createdb test
psql test < schema.sql
pg_dump test > dumped-schema.sql

createdb test_restored
psql test_restored < dumped-schema.sql

In schema.sql, we will:

1. Create a table (lines 1 to 8)
   
2. Create a function returning a row of this table (lines 10-22)
   
3. Create two policies using this function (lines 27 to 39)
   
4. Remove two columns of the table (lines 42 to 44)
   
5. Dump the schema to dumped-schema.sql (using pg_dump)
   

Actual and expected output:

In the dumped schema, the policies are serialized as follows:

CREATE POLICY administrate_accounts ON public.users USING ((EXISTS ( SELECT
   FROM public.my_account() my_account(id, first_name, last_name, display_name, is_admin)
  WHERE my_account.is_admin)));

CREATE POLICY manage_my_account ON public.users USING ((id IN ( SELECT my_account.id
   FROM public.my_account() my_account(id, first_name, last_name, display_name, is_admin))));

Instead of this, I would expect a serialization without an aliased FROM clause, because that's how I wrote these policies in the first place.

CREATE POLICY administrate_accounts ON public.users USING ((EXISTS ( SELECT
   FROM public.my_account()
  WHERE my_account.is_admin)));

CREATE POLICY manage_my_account ON public.users USING ((id IN ( SELECT my_account.id
   FROM public.my_account() )));

As you can see from the output, the outputted table alias contains five columns.

my_account(id, first_name, last_name, display_name, is_admin)

This is wrong. When the policy was added, the table actually had five columns. But when the policy was dumped, the table had only three columns left over. Thus, the table alias should look like this:

my_account(id, display_name, is_admin)

In the end, I fail at restoring both the policies. In both cases, the error message is "table "my_account" has 3 columns available but 5 columns specified"

Further platform details:

• Ubuntu 22.04 LTS
• Linux 5.15.0-41-generic x86_64
• Ubuntu GLIBC 2.35-0ubuntu3
• AMD Ryzen 5 5600G with Radeon Graphics
• 32G RAM

Please ask if I can help with further details. If you open a page for this issue, I would be glad to know about its URL.

All the best,
Timo

-- 
Timo Stolz
Geschäftsführer
#gerneperdu


Nullachtvierzehn UG (haftungsbeschränkt)
Konstanzer Straße 15
10707 Berlin

Tel +49 (0)30 284243-87
Fax +49 (0)30 284243-88
Web www.nullachtvierzehn.de
Mail timo.stolz@nullachtvierzehn.de

Sitz: Berlin
Amtsgericht: Charlottenburg
Handelsregister: HRB 233776 B

Geschäftsführung: Timo Stolz

Konto bei der GLS Gemeinschaftsbank eG
IBAN: DE18 4306 0967 1256 6159 00
BIC: GENODEM1GLS

USt-IdNr.: DE346169698