pg_dumpall and owner of the extension

From: kaido vaikla <kaido(dot)vaikla(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: pg_dumpall and owner of the extension
Date: 2024-01-23 14:31:30
Message-ID: CA+427g-YecPcvrHLJjoP92gDQy199qvvKCj_OvXhR3qmeu0zRQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

pg_pumpall (13.4) changes owner of the extension.

Source instance 13.4:
function boolop is part of extension intarray

diam=# \dx intarray
List of installed extensions
Name | Version | Schema | Description
----------+---------+--------+--------------------------------------------------------------------
intarray | 1.3 | iamapp | functions, operators, and index support for
1-D arrays of integers
(1 row)

diam=# \dn iamapp
List of schemas
Name | Owner
--------+-------------
iamapp | iamappadmin
(1 row)

diam=# \df+ iamapp.boolop
List of functions
-[ RECORD 1 ]-------+-----------------------------
Schema | iamapp
Name | boolop
Result data type | boolean
Argument data types | integer[], iamapp.query_int
Type | func
Volatility | immutable
Parallel | safe
Owner | iamappadmin
Security | invoker
Access privileges |
Language | c
Source code | boolop
Description | boolean operation with array

Export is done using pg_dumpall.

Dest instance 15.4:
After import (psql -a -f )
diam=# \dx intarray
List of installed extensions
Name | Version | Schema | Description
----------+---------+--------+--------------------------------------------------------------------
intarray | 1.5 | iamapp | functions, operators, and index support for
1-D arrays of integers
(1 row)

diam=# \dn iamapp
List of schemas
Name | Owner
--------+-------------
iamapp | iamappadmin
(1 row)

diam=# \df+ iamapp.boolop
List of functions
-[ RECORD 1 ]-------+-----------------------------
Schema | iamapp
Name | boolop
Result data type | boolean
Argument data types | integer[], iamapp.query_int
Type | func
Volatility | immutable
Parallel | safe
Owner | postgres
Security | invoker
Access privileges |
Language | c
Source code | boolop
Description | boolean operation with array

Manual says "The user who runs CREATE EXTENSION becomes the owner of the
extension".
So pg_dumpall is not aware who ran "CREATE EXTENSION"?

br
Kaido

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2024-01-23 15:30:43 Re: pg_dumpall and owner of the extension
Previous Message Holger Jakobs 2024-01-16 11:43:34 Re: failed to setup barman backup when Posgres is running in Podman Container