PostgreSQL supports the SQL/MED (Management of External Data) specification, enabling access to external data sources through standard SQL queries. These sources—known as foreign data—are accessed via foreign data wrappers (FDWs), which are libraries that handle the connection and data exchange with the external systems.

A variety of FDWs are available for PostgreSQL, including several provided as contrib modules. Of particular interest for this project is the postgres_fdw extension, which facilitates access to other PostgreSQL instances.

In PostgreSQL, FDWs are managed using the SQL commands: CREATE FOREIGN DATA WRAPPER, ALTER FOREIGN DATA WRAPPER, and DROP FOREIGN DATA WRAPPER.

Notably, CREATE FOREIGN DATA WRAPPER requires superuser privileges. As a result, a foreign data wrapper can only be added to a database managed by CloudNativePG in an imperative way (by enabling superuser access or via a managed role with superuser privileges).

This feature aims to extend the CloudNativePG operator to support declarative configuration of foreign data wrappers through its Database custom resource. It should allow users to define FDWs and assign USAGE privileges to specified roles. The controller will be responsible for reconciling the desired state defined in Kubernetes with the actual state in PostgreSQL, consistent with existing support for databases, extensions, and schemas.

As part of the design, we should evaluate whether specific PostgreSQL extensions (like postgres_fdw) should be treated as a prerequisite dependency for a foreign data wrapper.

This feature serves as a foundation for future development in foreign servers.

Example

For example, this is how the postgres_fdw automatically defines the foreign data wrapper when the CREATE EXTENSION postgres_fdw is created:

CREATE FOREIGN DATA WRAPPER postgres_fdw
  HANDLER postgres_fdw_handler
  VALIDATOR postgres_fdw_validator;

A proposal of interface for the Database resource (not binding and to be refined during the development) could be:

apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
  name: cluster-example-app
spec:
  name: one
  owner: app
  cluster:
    name: cluster-example
  extensions:
  - name: postgres_fdw
  fdws:
  - name: postgres_fdw
    handler: postgres_fdw_handler
    validator: postgres_fdw_validator
    #ensure: present
    #owner: postgres
    #requires: postgres_fdw
    #options:
    #- name:
      #value:
      #ensure: present # to manage ADD and DROP
    usage:
    - app

The USAGE permission will give the app user the possibility to create foreign servers in the app database. In the future we might evaluate the possibility to create foreign servers in the same database resource.