On Fri, 7 Mar 2025 at 15:37, Michael Banck <mba...@gmx.net> wrote: > On Fri, Mar 07, 2025 at 09:17:46AM -0500, Robert Haas wrote: > > Why wouldn't the cloud provider just change add 'trusted = true' to > > the relevant control files instead of doing this? > > That would be possible, but maybe the cloud provider is using > distribution packages and does not want to muck around in the file > system (as is usually frowned upon), or, maybe more likely, is using > container images based on (what I've seen most of them are) the Debian > packages and cannot (or does not want to anyway) muck around in the file > system easily.
Yeah exactly, having to do this for every extension that you onboard is quite a hassle to maintain. It seems much nicer to allow people to assign a single role and be done with it. Also many cloud providers have some slightly forked/extended postgres to allow this already. > Also, I think there is case to be made that a cloud provider (or site > admin) would like to delegate the decision whether users with CREATE > rights on a particular database are allowed to install some extensions > or not. Or rather, assign somebody they believe would make the right > call to do that, by granting pg_manage_extensions. I think this is a really good point. Adding trusted=true gives any database owner the ability to install these more dangerous extensions. While by using pg_manage_extensions you can limit this ability to the cluster administrator. > On the other hand, maybe trusted should be part of the catalog and not > (just) the extension control file, so that somebody with appropriate > permissions (like the cloud provider during instance bootstrap) could do > "ALTER EXTENSION foo (SET trusted|TRUSTED);" or whatever. ISTR that I > reviewed the discussion around trusted back then and did not see that > possiblity discussed at all, but I might be misremembering, it's been a > while. I think that would be hard because there's no record in the pg_extension for extensions that are not installed. So there's also no way to mark such an extension as trusted. To be able to do this we'd probably need a system-wide catalog. If we'd go this route then I think what we'd really want is a way to do: GRANT INSTALL ON EXTENSION TO user; And that seems orthogonal to having this pg_manage_extensions role, because then pg_manage_extensions could grant that permission to people.
