Make bloom extension trusted, but can not drop with normal user

"Li EF Zhang" <bjzhangl@cn.ibm.com> writes:

Since pg13 support trusted extension, so I changed control file of bloom and make it trusted.

The fact that you can edit the file that way doesn't make it a supported
case.

regards, tom lane

Why do you think that the normal user should be allowed to drop
extensions? Extensions are additions to the RDBMS functionality. Do you
really want normal users to tinker with the database functionality and
reduce the number of index types you can create? I am not sure that I am
on board with that idea.

Regards

On 8/20/21 7:01 AM, Li EF Zhang wrote:

So, I want to know if extensions in contrib dir can be set "trusted"
ourselves? If we can, I think drop extension should be allowed by
normal user.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

On Sat, Aug 21, 2021 at 1:09 PM Mladen Gogala <gogala.mladen@gmail.com>
wrote:

Why do you think that the normal user should be allowed to drop
extensions?

The documentation.

https://www.postgresql.org/docs/current/sql-createextension.html

"This configuration gives the calling user the right to drop the extension,
but not to modify individual objects within it."

David J.

On Fri, Aug 20, 2021 at 6:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Li EF Zhang" <bjzhangl@cn.ibm.com> writes:

Since pg13 support trusted extension, so I changed control file of bloom

and make it trusted.

The fact that you can edit the file that way doesn't make it a supported
case.

Why does that matter here though? This isn't a question about a security
violation, it's one about the basic premise that a trusted extension is
owned by the creating user and thus can be dropped by them. During
installation, a trusted user is permitted to perform superuser actions by
virtue of the trusted flag. Since they are allowed to drop their own
extension it is at least plausible to assume that upon doing so the
dropping would be done as a superuser as well. That this is not the case
doesn't seem to be documented nor, going from the commit message for the
feature, does it seem intentional.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Fri, Aug 20, 2021 at 6:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Li EF Zhang" <bjzhangl@cn.ibm.com> writes:

Since pg13 support trusted extension, so I changed control file of bloom
and make it trusted.

The fact that you can edit the file that way doesn't make it a supported
case.

Why does that matter here though? This isn't a question about a security
violation, it's one about the basic premise that a trusted extension is
owned by the creating user and thus can be dropped by them.

My point was that randomly marking stuff as trusted is likely to cause
large problems, therefore we don't support doing it. Per the other
followup, this does work as-expected in v14. I doubt we'd take the risk
of moving superuserness checks around in v13 to make it work there.

regards, tom lane

On 8/24/21 7:40 AM, David G. Johnston wrote:

On Fri, Aug 20, 2021 at 6:26 AM Tom Lane <tgl@sss.pgh.pa.us
<mailto:tgl@sss.pgh.pa.us>> wrote:

"Li EF Zhang" <bjzhangl@cn.ibm.com <mailto:bjzhangl@cn.ibm.com>> writes:

Since pg13 support trusted extension, so I changed control file

of bloom and make it trusted.

The fact that you can edit the file that way doesn't make it a supported
case.

Why does that matter here though?  This isn't a question about a
security violation, it's one about the basic premise that a trusted
extension is owned by the creating user and thus can be dropped by
them.  During installation, a trusted user is permitted to perform
superuser actions by virtue of the trusted flag.  Since they are allowed
to drop their own extension it is at least plausible to assume that upon
doing so the dropping would be done as a superuser as well.  That this
is not the case doesn't seem to be documented nor, going from the commit
message for the feature, does it seem intentional.

To me the issue is that the extension was modified to trusted by an end
user not the extension author. I gotta believe there is more to the
trusted then a flag in the control file. It would not be surprising to
me that an ad hoc modification would fail.

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Aug 24, 2021 at 8:17 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

To me the issue is that the extension was modified to trusted by an end
user not the extension author. I gotta believe there is more to the
trusted then a flag in the control file. It would not be surprising to
me that an ad hoc modification would fail.

If the expected behavior here is that an ordinary user can drop a trusted
extension then I do not see how this error could present itself since, just
like extension creation, all the flag does is allow the user to become a
superuser for purposes of installing (or removing) the extension objects.
Per Tom, the pre-v14 drop behavior is indeed a bug. It is not going to be
back-patched, nor has the documentation been updated to say that DROP
EXTENSION is effectively prevented due to the existence of this bug (if you
really need superuser to install the extension it seems reasonable it
requires the same to drop it).

Per an adjacent thread [1]/messages/by-id/CAA3qoJkW4VzN4ixt-=u=tY1Di9YrzJNAhrxKdNhtQMJp2iD0Cg@mail.gmail.com this has apparently been fixed in v14 at [2]https://github.com/postgres/postgres/commit/b1d32d3e3230f00b5baba08f75b4f665c7d6dac6 -
but if so (not tested it myself) then it seems like an unexpected
side-effect since that particular commit seems like a pure refactoring.

David J.

[1]: /messages/by-id/CAA3qoJkW4VzN4ixt-=u=tY1Di9YrzJNAhrxKdNhtQMJp2iD0Cg@mail.gmail.com
/messages/by-id/CAA3qoJkW4VzN4ixt-=u=tY1Di9YrzJNAhrxKdNhtQMJp2iD0Cg@mail.gmail.com
[2]: https://github.com/postgres/postgres/commit/b1d32d3e3230f00b5baba08f75b4f665c7d6dac6
https://github.com/postgres/postgres/commit/b1d32d3e3230f00b5baba08f75b4f665c7d6dac6

On Wed, Aug 25, 2021 at 12:38 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Tue, Aug 24, 2021 at 8:17 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

To me the issue is that the extension was modified to trusted by an end
user not the extension author. I gotta believe there is more to the
trusted then a flag in the control file. It would not be surprising to
me that an ad hoc modification would fail.

If the expected behavior here is that an ordinary user can drop a trusted extension then I do not see how this error could present itself since, just like extension creation, all the flag does is allow the user to become a superuser for purposes of installing (or removing) the extension objects. Per Tom, the pre-v14 drop behavior is indeed a bug. It is not going to be back-patched, nor has the documentation been updated to say that DROP EXTENSION is effectively prevented due to the existence of this bug (if you really need superuser to install the extension it seems reasonable it requires the same to drop it).

I’d missed this discussion and it seems not to be fixed yet in v13.

Per an adjacent thread [1] this has apparently been fixed in v14 at [2] - but if so (not tested it myself) then it seems like an unexpected side-effect since that particular commit seems like a pure refactoring.

I have the same opinion on this. In v14 it seems to me that this has
been fixed by a side-effect of the commit b1d32d3. Looking at the
discussion of the commit, there is no discussion on this behavior
change.

While this behavior contradicts what the doc says ("This configuration
gives the calling user the right to drop the extension, but not to
modify individual objects within it) I also understand that it would
be better to avoid the risk of moving/removing superuser checks in
v13. So far the reported issues are only about making bloom trusted,
which seems not a common use case (right?). So probably we can come
back to this issue and discuss how to fix this issue in v13 when more
issues related to this bug are reported.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/