PostgreSQL Extensions in VMware Data Services Manager v1.5

I had an interesting query earlier this week in relation to PostgreSQL Extensions in VMware Data Services Manager (DSM) v1.5. We do not give out superuser (postgres user) privileges when creating databases via DSM, although it is still possible to find those credentials and access the database with those privileges if necessary. Instead, we focus on the default user that we create when we deploy a database, a user that we have named ‘dbaas’. The query was how should extensions be added to PostgreSQL if ‘dbaas’ is not a superuser? The answer lies in our DSM documentation where we describe how to work with extensions. In a nutshell, we provide an alternate method of managing PostgreSQL extensions via user-defined functions (UDFs). This avoids having to give users ‘superuser’ permissions, and avoids a potential security concern. And since the UDFs are in the template1 database, it means that any PostgreSQL database created from template1 have access to them. With all that said, let’s see it in action.

For the purposes of this demo, I am going to add two extensions. The first is ‘earthdistance‘, which can calculate distances between two points of latitude and longitude on the Earth’s surface. It does have a dependency on another extension however. This is the ‘cube‘ extension which is a data type for multidimensional cubes. Let’s begin by first listing all available extensions. Then we will install the ‘earthdistance‘ and ‘cube‘ extensions before finally using their functions to calculate a distance between two points on the Earth’s surface.

dbaas=> SELECT * from pg_available_extensions;
        name        | default_version | installed_version |                                            comment                                            
 refint             | 1.0            |                  | functions for implementing referential integrity (obsolete)
 lo                 | 1.1            |                  | Large Object maintenance
 pgautofailover     | 2.0            |                  | pg_auto_failover
 pg_surgery         | 1.0            |                  | extension to perform surgery on a damaged relation
 moddatetime        | 1.0            |                  | functions for tracking last modification time
 pg_walinspect      | 1.0            |                  | functions to inspect contents of PostgreSQL Write-Ahead Log
 dblink             | 1.2            |                  | connect to other PostgreSQL databases from within a database
 intagg             | 1.1            |                  | integer aggregator and enumerator (obsolete)
 tsm_system_time    | 1.0            |                  | TABLESAMPLE method which accepts time in milliseconds as a limit
 bloom              | 1.0            |                  | bloom access method - signature file based index
 tablefunc          | 1.0            |                  | functions that manipulate whole tables, including crosstab
 pgstattuple        | 1.5            |                  | show tuple-level statistics
 earthdistance      | 1.1            |                  | calculate great-circle distances on the surface of the Earth
 file_fdw           | 1.0            |                  | foreign-data wrapper for flat file access
 isn                | 1.2            |                  | data types for international product numbering standards
 pgaudit            | 1.7            |                  | provides auditing functionality
 xml2               | 1.1            |                  | XPath querying and XSLT
 btree_gist         | 1.7            |                  | support for indexing common datatypes in GiST
 pg_prewarm         | 1.2            |                  | prewarm relation data
 tsm_system_rows    | 1.0            |                  | TABLESAMPLE method which accepts number of rows as a limit
 citext             | 1.6            |                  | data type for case-insensitive character strings
 unaccent           | 1.1            |                  | text search dictionary that removes accents
 dict_int           | 1.0            |                  | text search dictionary template for integers
 btree_gin          | 1.3            |                  | support for indexing common datatypes in GIN
 intarray           | 1.5            |                  | functions, operators, and index support for 1-D arrays of integers
 insert_username    | 1.0            |                  | functions for tracking who changed a table
 sslinfo            | 1.2            |                  | information about SSL certificates
 adminpack          | 2.1            |                  | administrative functions for PostgreSQL
 orafce             | 3.25           |                  | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
 hstore             | 1.8            |                  | data type for storing sets of (key, value) pairs
 pgcrypto           | 1.3            |                  | cryptographic functions
 cube               | 1.5            |                  | data type for multidimensional cubes
 postgres_fdw       | 1.1            |                  | foreign-data wrapper for remote PostgreSQL servers
 dict_xsyn          | 1.0            |                  | text search dictionary template for extended synonym processing
 seg                | 1.4            |                  | data type for representing line segments or floating-point intervals
 autoinc            | 1.0            |                  | functions for autoincrementing fields
 amcheck            | 1.3            |                  | functions for verifying relation integrity
 old_snapshot       | 1.0            |                  | utilities in support of old_snapshot_threshold
 pg_freespacemap    | 1.2            |                  | examine the free space map (FSM)
 pageinspect        | 1.11           |                  | inspect the contents of database pages at a low level
 ltree              | 1.2            |                  | data type for hierarchical tree-like structures
 pg_visibility      | 1.2            |                  | examine the visibility map (VM) and page-level visibility info
 pg_buffercache     | 1.3            |                  | examine the shared buffer cache
 plpgsql            | 1.0            | 1.0              | PL/pgSQL procedural language
 pg_stat_statements | 1.10           | 1.10             | track planning and execution statistics of all SQL statements executed
 tcn                | 1.0            |                  | Triggered change notifications
 fuzzystrmatch      | 1.1            |                  | determine similarities and distance between strings
 pg_trgm            | 1.6            |                  | text similarity measurement and index searching based on trigrams
 pgrowlocks         | 1.2            |                  | show row-level locking information
 uuid-ossp          | 1.1            |                  | generate universally unique identifiers (UUIDs)
(50 rows)

The extensions that we are interested in are earthdistance and cube, as highlighted in blue above. We use the “dms_create_extension” UDF to install them. Note that if we try to add the earthdistance extension prior to adding cube, we get the following dependency failure:

dbaas=> select dms_create_extension('earthdistance');
NOTICE:  Running create extension sql: CREATE EXTENSION IF NOT EXISTS earthdistance
ERROR:  required extension "cube" is not installed
HINT:  Use CREATE EXTENSION ... CASCADE to install required extensions too.
PL/pgSQL function dms_create_extension(text) line 6 at EXECUTE

To add these extensions to our database, run these commands in the following order.

dbaas=> select dms_create_extension('cube');
NOTICE: Running create extension sql: CREATE EXTENSION IF NOT EXISTS cube

(1 row)

dbaas=> select dms_create_extension('earthdistance');
NOTICE: Running create extension sql: CREATE EXTENSION IF NOT EXISTS earthdistance

(1 row)

And that is how simple it is to install PostgreSQL extensions in a database that has been provisioned by DSM as the ‘dbaas’ user. Again, ‘dbaas’ is not a superuser like ‘postgres’, which is a design decision based on security concerns.

You can now examine the installed extensions by using the \dx command.

dbaas=> \dx
                                            List of installed extensions
        Name        | Version |  Schema    |                              Description                              
 cube               | 1.5     | public     | data type for multidimensional cubes
 earthdistance      | 1.1     | public     | calculate great-circle distances on the surface of the Earth
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

There are also UDFs to update and drop any extensions that have been added to a database.

We can now test the functions. As per the official documentation for the earthdistance extension, the function ll_to_earth() returns the location of a point on the surface of the Earth given its latitude and longitude in degrees. The function earth_distance() returns the great circle distance between two points on the surface of the Earth. So, for example, if I wanted to find the distance between my home town in Ireland to say, Salto in Brazil, I would make a call similar to the following:

dbaas=> select earth_distance(ll_to_earth(51.95, -7.85056),ll_to_earth(-23.20083, -47.28694)) as distance_in_meters;

(1 row)

The value returned is in meters. If I wanted to display kilometers or miles, I would simply add a divide by 1000 or divide by 1609.344 accordingly to the result.

dbaas=> select earth_distance(ll_to_earth(51.95, -7.85056),ll_to_earth(-23.20083, -47.28694)) / 1000 as distance_in_kms;

(1 row)

dbaas=> select earth_distance(ll_to_earth(51.95, -7.85056),ll_to_earth(-23.20083, -47.28694)) / 1609.344 as distance_in_miles;

(1 row)


And there we have it. This is a very simple example, but hopefully it demonstrates how easy it is to add PostgreSQL extensions to a PostgreSQL database that has been provisioned via VMware Data Services Manager. There is no need to grant superuser privileges to users to install extensions, which could be a security concern. It can be achieved through user-defined functions that we have specifically added to the PostgreSQL database template. A much safer approach.