Re: create extension requires superuser?

On Fri, Feb 7, 2020 at 09:26:17PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/10/sql-createextension.html
> Description:
>
> https://www.postgresql.org/docs/10/sql-createextension.html
> "For most extensions this means superuser or database owner privileges are
> needed"
> (this is the same text for version 12, but I haven't tried that version.)
>
> If I create a database, I can't CREATE EXTENSION postgis;
> unless I'm superuser. Is this bad documentation or is it just for this
> extension?
> It should be documented how to tell.

Well, our docs say:

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

Loading an extension requires the same privileges that would be required
to create its component objects. For most extensions this means
superuser or database owner privileges are needed. The user who runs
CREATE EXTENSION becomes the owner of the extension for purposes of
later privilege checks, as well as the owner of any objects created by
the extension's script.

Looking at the adminpack extension, I think anyone can install it, but
most/all of the functions internally call requireSuperuser(), and that
errors for non-superusers with:

only superuser may access generic file functions

Unfortunately, there is no SQL-level ability to check which functions
require super-user rights. It is buried in the C code.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +