From: | Timo Stolz <timo(dot)stolz(at)nullachtvierzehn(dot)de> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | Jonas Reinsch <jonas(dot)reinsch(at)pitchview(dot)de>, Laura Schlimmer <laura(dot)schlimmer(at)pitchview(dot)de> |
Subject: | 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 |
Date: | 2022-07-19 10:22:02 |
Message-ID: | 5c91267e-3b6d-5795-189c-d15a55d61dbb@nullachtvierzehn.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
Webwww.nullachtvierzehn.de
Mailtimo(dot)stolz(at)nullachtvierzehn(dot)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
Attachment | Content-Type | Size |
---|---|---|
dumped-schema with comments.sql | application/sql | 3.9 KB |
reproduce-bug.sh | application/x-shellscript | 376 bytes |
schema.sql | application/sql | 1.2 KB |
timo_stolz.vcf | text/vcard | 344 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Verite | 2022-07-19 11:16:21 | Re: BUG #17434: CREATE/DROP DATABASE can be executed in the same transaction with other commands |
Previous Message | Devrim Gündüz | 2022-07-19 09:00:34 | Re: BUG #17555: Missing rhel-9 repo |