Extension White Listing
PostgreSQL 9.1 includes proper extension support, as you might well know if you ever read this very blog here. Some hosting facilities are playing with PostgreSQL at big scale (hello Heroku!) and still meet with small caveats making their life uneasy.
To be specific, only superusers are allowed to install C coded stored procedures, and that impacts a lot of very useful PostgreSQL extension: all those shiped in the contrib package are coded in C. Now, Heroku is not giving away superuser access to their hosted customers in order to limit the number of ways they can shoot themselves in the foot. And given PostgreSQL security model, being granted database owner is mostly good enough for day to day operation.
See Andrew’s article
Mostly, but as we see, not completely good enough. How to arrange for a non
superuser to be able to still install a C-coded extension in his own
database? That’s quite dangerous as any bug causing a crash would mean a
PostgreSQL whole restart. So you not only want to empower
to database owners, you also want to be able to review and explicitely
list the allowed extensions.
Here we go:
pgextwlist is a PostgreSQL extensions implementing just that
idea. You have to tweak
local_preload_libraries so that it gets loaded
automatically and early enough, and you have to provide for the list of
authorized extensions in the
Let’s see a usage example, straight from the documentation:
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
As you can see, it allows non superusers to install an extension written in C.