Problem Description
EDB Postgres Advanced Server in BigAnimal clusters comes with some extensions already created.
Two of them let create blinks with other PostgresSQL Servers, EDB Postgres Advanced Servers and Oracle Servers.
Db links are used to query data from different databases
Here below are listed the extension created when the cluster is deployed
The ones related to blinks are marked in bold
edb_admin=> \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-------------------------------------------------------------------
edb_dblink_libpq | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
edb_dblink_oci | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
If edb_admin user or any other database users try to create a dblink will get the following error:
edb_admin=> CREATE DATABASE LINK dblinktest
CONNECT TO edb_admin identified by 'xxxxxxxxxxx'
using postgres_fdw 'host=p-c6b0vj56vp7f3n6t05f0.30glixgayvwhtmn3.biganimal.io port=5432 dbname=edb_admin';
ERROR: permission denied to create database link
EDB Postgres Advanced Server has a system grant to let users creating dblinks
CREATE DATABASE LINK
In BigAnimal when a cluster is deployed edb_admin user has not that grant assigned
Solution
In order to get the blink a client needs to contact BigAnimal support and ask to be assigned that grant