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. CONTEXT: SQL statement "CREATE EXTENSION IF NOT EXISTS earthdistance" 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 dms_create_extension ---------------------- (1 row) dbaas=> select dms_create_extension('earthdistance'); NOTICE: Running create extension sql: CREATE EXTENSION IF NOT EXISTS earthdistance dms_create_extension ---------------------- (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; distance_in_meters -------------------- 9204656.163251864 (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; distance_in_kms ------------------- 9204.656163251864 (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; distance_in_miles ------------------- 5719.508174294535 (1 row) dbaas=>
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.
