This extension implements extension whitelisting, and will actively prevent
users from installing extensions not in the provided list. Also, this
extension implements a form of sudo facility in that the whitelisted
extensions will get installed as if superuser. Privileges are droped before
handing the control back to the user.
You should have received that as a debian package or equivalent:
apt-get install postgresql-9.1-extension-whitelist
If that's not the case, install the server development packages then:
make
sudo make install
You need to define the list of extensions that are whitelisted, the user that performs the extension installing, and the error behavior.
-
local_preload_librariesAdd
pgextwlistto thelocal_preload_librariessetting. -
custom_variable_classesAdd
extwlistto thecustom_variable_classessetting if you're using 9.1, in 9.2 this setting disapeared. -
extwlist.extensionsList of extensions allowed for installation.
That's quite simple:
$ edit postgresql.conf, custom_variable_classes and extwlist.extensions
dim=# show extwlist.extensions;
show extwlist.extensions;
extwlist.extensions
---------------------
hstore,cube
(1 row)
dim=# create extension foo;
create extension foo;
ERROR: extension "foo" is not whitelisted
DETAIL: Installing the extension "foo" failed, because it is not on the whitelist of user-installable extensions.
HINT: Your system administrator has allowed users to install certain extensions. See: SHOW extwlist.extensions;
dim=# create extension hstore;
create extension hstore;
WARNING: => is deprecated as an operator name
DETAIL: This name may be disallowed altogether in future versions of PostgreSQL.
CREATE EXTENSION
dim=# \dx
\dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+--------------------------------------------------
hstore | 1.0 | public | data type for storing sets of (key, value) pairs
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
Even if you're not superuser:
dim=> select rolsuper from pg_roles where rolname = current_user;
select rolsuper from pg_roles where rolname = current_user;
rolsuper
----------
f
(1 row)
dim=> create extension hstore;
create extension hstore;
WARNING: => is deprecated as an operator name
DETAIL: This name may be disallowed altogether in future versions of PostgreSQL.
CREATE EXTENSION
dim=> create extension earthdistance;
create extension earthdistance;
ERROR: extension "earthdistance" is not whitelisted
DETAIL: Installing the extension "earthdistance" failed, because it is not on the whitelist of user-installable extensions.
HINT: Your system administrator has allowed users to install certain extensions. SHOW extwlist.extensions;
dim=> \dx
\dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+--------------------------------------------------
hstore | 1.0 | public | data type for storing sets of (key, value) pairs
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
dim=> drop extension hstore;
drop extension hstore;
DROP EXTENSION
The whitelisting works by overloading the ProcessUtility_hook and gaining
control each time a utility statement is issued. When this statement is a
CREATE EXTENSION, the extension's name is extracted from the parsetree
and checked against the whitelist.
The sudo part is not pretty. We edit the rolsuper attribute directly in
the catalogs then force a cache refresh and a CommandCounterIncrement() so
that next commands consider we are a superuser. Then we edit the rolsuper
attribute back to what it was before our command.