BUG #18347: problem with restore functional index - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18347
Logged by:          David Turoň
Email address:      david.turon@linuxbox.cz
PostgreSQL version: 16.2
Operating system:   Debian 16.2-1.pgdg120+2
Description:

Hello,

I have some issue with restoring index due missing grants - index is created
before grants.

root@8c8da0755196:/# psql postgres postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))

CREATE ROLE user_a;
CREATE ROLE user_b;

CREATE SCHEMA a AUTHORIZATION user_a;
CREATE SCHEMA b AUTHORIZATION user_b;

GRANT USAGE ON SCHEMA b TO user_a;

CREATE FUNCTION b.f(text) RETURNS TEXT AS $$ SELECT lower($1) $$ LANGUAGE
SQL IMMUTABLE;
ALTER FUNCTION b.f(text) OWNER TO user_b;

CREATE FUNCTION a.f(text) RETURNS TEXT AS $$ SELECT b.f($1) $$ LANGUAGE SQL
IMMUTABLE;
ALTER FUNCTION a.f(text) OWNER TO user_a;


CREATE TABLE a.test(data text);
ALTER TABLE a.test OWNER TO user_a;
CREATE INDEX ON a.test (a.f(data));

INSERT INTO a.test VALUES ('ASDF');
---------------------------------------------------------------------------
now all is working, function a.f(text) use function from schema "b"
a.f(text) -> b.f(text)
user_a is owner of schema "a"
user_b is owner of schema "b"
but when i want dump & restore

root@8c8da0755196:/# pg_dump -Fc -U postgres postgres > postgres.pg_dump
root@8c8da0755196:/# createdb test -U postgres 
root@8c8da0755196:/# pg_restore -U postgres -d test -1 postgres.pg_dump


       
pg_restore: error: could not execute query: ERROR:  permission denied for
schema b
LINE 1:  SELECT b.f($1) 
                ^
QUERY:   SELECT b.f($1) 
CONTEXT:  SQL function "f" during inlining
Command was: CREATE INDEX test_f_idx ON a.test USING btree (a.f(data));

in dump all is in correct order:
root@8c8da0755196:/# pg_restore -l postgres.pg_dump 
;
; Archive created at 2024-02-16 09:38:01 UTC
;     dbname: postgres
;     TOC Entries: 13
;     Compression: gzip
;     Dump Version: 1.15-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 16.2 (Debian 16.2-1.pgdg120+2)
;     Dumped by pg_dump version: 16.2 (Debian 16.2-1.pgdg120+2)
;
;
; Selected TOC Entries:
;
6; 2615 16386 SCHEMA - a user_a
7; 2615 16387 SCHEMA - b user_b
3358; 0 0 ACL - SCHEMA b user_b
219; 1255 16389 FUNCTION a f(text) user_a
218; 1255 16388 FUNCTION b f(text) user_b
217; 1259 16390 TABLE a test user_a
3350; 0 16390 TABLE DATA a test user_a
3206; 1259 16395 INDEX a test_f_idx user_a

but restore put grants after index creation ..., so lets restore all but
without index
root@8c8da0755196:/# pg_restore -l postgres.pg_dump | wc -l


       
23
#last line is index creation ...
root@8c8da0755196:/# pg_restore -l postgres.pg_dump | head -n 22 >
/tmp/without_index
root@8c8da0755196:/# pg_restore -l postgres.pg_dump | tail -n 1 >
/tmp/only_index  

#restore all schema without index
root@8c8da0755196:/# pg_restore -U postgres -d test -1 -L /tmp/without_index
postgres.pg_dump 

#restore just index
root@8c8da0755196:/# pg_restore -U postgres -d test -1 -L /tmp/only_index
postgres.pg_dump 
this works well without exception ...

I am not sure if it is bug or again strange use of postgres, bad design
...its not working on 14.10 too. Thanks for any tips ho to do it better:)

David