Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\))
Subject: Re: Evidence of Ownership Issues During Restoration of Extension
 Member Objects
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: 
 <CABaoBc4QzQHBFKGvAa99BpcfyAgjqH_JKrSox9P62dkhfOpDdw@mail.gmail.com>
Date: Mon, 27 Jan 2025 21:47:15 -0800
Cc: pgsql-bugs@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <CFCF382B-42DD-4490-ADDD-75D255FC7BD4@thebuild.com>
References: 
 <CABaoBc6UyWJ=v4NgmC15SkBtBq_g0GBRgZPrdks2q6=ke0-4Mg@mail.gmail.com>
 <CABaoBc4QzQHBFKGvAa99BpcfyAgjqH_JKrSox9P62dkhfOpDdw@mail.gmail.com>
To: AKASH <akashbhujbal7051@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/CFCF382B-42DD-4490-ADDD-75D255FC7BD4%40thebuild.com>
Precedence: bulk


> On Jan 27, 2025, at 21:29, AKASH <akashbhujbal7051@gmail.com> wrote:
>     =E2=80=A2 Create a database, install the pg_trgm extension, create =
a role (app_user), and change ownership of the similarity function to =
app_user.
>     =E2=80=A2 Export the database pg_dump to create a dump file.
>     =E2=80=A2 Restore the dump into a target environment where it =
app_user does not exist.
>     =E2=80=A2 Ownership of the similarity function reverts to the =
restoring user.
>     =E2=80=A2 Applications dependent on app_user permissions fail with =
permission errors.

It's not clear to me exactly why you need to have different parts of an =
extension owned by different roles.  I believe the correct analogy is =
that a table is owned by a single role, but that does not prevent it =
from being accessed and used by other roles that have appropriate =
permissions.  Similarly, there's no need for individual components of =
extensions to have different owners.  You can grant the right =
permissions to the roles that need access to the extension without =
having to grant them ownership.  If you want more fine-grained access =
control, where some roles can use some components of an extension and =
others can't, it's best to wrap the extension functions or views in =
user-defined wrappers, and then grant permissions appropriately onto =
those.

In your particular example, that's not a PostgreSQL bug, but a bug in =
your process.  You need to make sure that the target system has the =
correct roles before restoring into it.  PostgreSQL doesn't (and I feel =
safe to say never will) create roles on the fly, and dumping and =
restoring global objects like roles is a separate step from a =
pg_dump/pg_restore.=