Monthly Archives: April 2019

Fun with dblink and Functions

Greenplum includes several optional modules from PostgreSQL. One of these is dblink which enables connecting to a remote PostgreSQL or Greenplum database with SQL.

First, run this command as gpadmin:

psql -f /usr/local/greenplum-db/share/postgresql/contrib/dblink.sql

Doc: https://gpdb.docs.pivotal.io/5180/utility_guide/dblink.html

If you are superuser like gpadmin, you can use the connect function to a database in the same cluster without specifying the password like this:

PERFORM dblink_connect('blog_test', 'dbname=blog_db');

So now you can execute some SQL in the other database. It is also in a separate transaction which gives you the ability to have an autonomous transaction inside of a Function.

v_sql := 'CREATE TABLE foo (id int) DISTRIBUTED BY (id)';
PERFORM dblink_exec('blog_test', v_sql, true);
PERFORM dblink_disconnect('pws_test');

If you do all of this as a superuser, the function will execute the dblink successfully but if you grant EXECUTE on the function to a non-superuser, it will fail. Why? Because by default, the Function executes as the user that is executing the Function, not the owner.

The easy fix for this is to use SECURITY DEFINER when creating the function. This means the Function executes as the owner of the Function. You could have all sorts of code in your Function that requires a superuser to execute and grant it to a non-superuser. This includes dblink!

Doc:https://gpdb.docs.pivotal.io/5180/ref_guide/sql_commands/CREATE_FUNCTION.html#topic1

Have fun!