Date: Sat, 14 Mar 2020 19:49:17 -0400
From: Bruce Momjian <bruce@momjian.us>
To: randolph.a.strauss@nasa.gov, pgsql-docs@lists.postgresql.org
Subject: Re: create extension requires superuser?
Message-ID: <20200314234917.GD22336@momjian.us>
References: <158111077755.1088.5569143599421077335@wrigleys.postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <158111077755.1088.5569143599421077335@wrigleys.postgresql.org>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

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@momjian.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 +