Tag: ERROR: permission denied to create extension “dblink”

From one of PostgreSQL AWS RDS instance, try to access tables of remote RDS instance with below command:

testuser=>select * from dblink('dbname=testrds port=5555
        host=testrds.rds.amazonaws.com 
        user=testuser password=passwd', 'SELECT cust_id, cust_name 
     from iamesh.customer' ) as cust ( id float, name varchar(50));

ERROR: function dblink(unknown, unknown) does not exist

LINE 1: select * from dblink('dbname=testrds port=5555 host=testrds.

HINT: No function matches the given name and argument types. 
     You might need to add explicit type casts.
SQL state: 42883
Character: 15

Show current installed extensions:

testuser=> select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10       | 11           | f              | 1.0        |           |
(1 rows)

Show available extensions, dblink extension is available to be installed:

testuser>SELECT * FROM pg_available_extensions order by 1;

        name             | default_version | installed_version |                                                       comment
-------------------------+-----------------+-------------------+----
...
..
dblink                   |1.2              |
plpgsql                  |1.0              |1.0
...
..
.

We can see dblink extension is NOT installed yet. To create “dblink” extension:

testuser>create extension dblink; 

ERROR: permission denied to create extension "dblink"
HINT: Must be superuser to create this extension.

Logon as super user, and retry again.

rdsdba=> create extension dblink;
CREATE EXTENSION

Try to access remote tables of remote instance again, and it is successful.

testuser=>select * from dblink('dbname=testrds port=5555
host=testrds.rds.amazonaws.com 
user=testuser password=passwd', 'SELECT cust_id, cust_name 
from iamesh.customer' ) as cust ( id float, name varchar(50));

     id     |                name
------------+-------------------------------------
 2001873700 | James Bond
 2001873800 | David Lee
...
..
.