[temporal] Unable to connect to external PostgreSQL database

[judiennetSC]

Hi everyone,

I'm trying to Configure the Airbyte Database using an external RDS PostgreSQL DB. Here is my setup used for the Deployment on AWS EC2:
OS: tried Amazon Linux 2023 AMI (ebs, hvm, kernel-6.1) & Amazon Linux 2 AMI (ebs, hvm, kernel-5.10)
Architecture: tried x86_64 and arm64
Instance type: EC2, tried t4g.large and t3.large
Docker version 20.10.latest
Docker Compose version 2.21.0
AWS RDS: PostgreSQL v15.3
parameter group: default.postgres15. EDIT: THIS WAS CONTAINING THE ISSUE. SEE THE REPLIES

When relaunching the ./run-ab-platform.sh -b after applying the changes in the .env file as explained in the doc (and making a source .env etc.), I get the following errors in the docker compose logs. "db" succeeds to connect, but not "airbyte-temporal":

airbyte-temporal                  | [Fx] ERROR          Failed to initialize custom logger: could not build arguments for function "go.uber.org/fx".(*App).constructCustomLogger.func2
airbyte-temporal                  | 2023-09-15T15:06:59.182Z    ERROR   Unable to create SQL database.  {"error": "unable to connect to DB, tried default DB names: postgres,defaultdb, errors: [pq: no pg_hba.conf entry for host \"10.0.1.182\", user \"postgres\", database \"postgres\", no encryption pq: no pg_hba.conf entry for host \"10.0.1.182\", user \"postgres\", database \"defaultdb\", no encryption]", "logging-call-at": "handler.go:94"}
airbyte-temporal                  | 2023-09-15T15:06:59.202Z    ERROR   Unable to connect to SQL database.      {"error": "pq: no pg_hba.conf entry for host \"10.0.1.182\", user \"postgres\", database \"temporal\", no encryption", "logging-call-at": "handler.go:52"}
airbyte-temporal                  | 2023-09-15T15:06:59.224Z    ERROR   Unable to create SQL database.  {"error": "unable to connect to DB, tried default DB names: postgres,defaultdb, errors: [pq: no pg_hba.conf entry for host \"10.0.1.182\", user \"postgres\", database \"postgres\", no encryption pq: no pg_hba.conf entry for host \"10.0.1.182\", user \"postgres\", database \"defaultdb\", no encryption]", "logging-call-at": "handler.go:94"}
airbyte-temporal                  | 2023-09-15T15:06:59.245Z    ERROR   Unable to connect to SQL database.      {"error": "pq: no pg_hba.conf entry for host \"10.0.1.182\", user \"postgres\", database \"temporal_visibility\", no encryption", "logging-call-at": "handler.go:52"}
airbyte-temporal                  | 2023-09-15T15:06:59.265Z    ERROR   Unable to connect to SQL database.      {"error": "pq: no pg_hba.conf entry for host \"10.0.1.182\", user \"postgres\", database \"temporal\", no encryption", "logging-call-at": "handler.go:73"}
airbyte-temporal                  | 2023-09-15T15:06:59.285Z    ERROR   Unable to connect to SQL database.      {"error": "pq: no pg_hba.conf entry for host \"10.0.1.182\", user \"postgres\", database \"temporal_visibility\", no encryption", "logging-call-at": "handler.go:73"}
airbyte-temporal                  | [Fx] ERROR          Failed to initialize custom logger: could not build arguments for function "go.uber.org/fx".(*App).constructCustomLogger.func2

• docker-compose.yaml file is untouched. Default one (of version="3.8").
• .env file is as follow:

### SHARED ###
VERSION=0.50.29

# default block stuff...

### DATABASE ### -> I just changed this part
# Airbyte Internal Job Database, see https://docs.airbyte.io/operator-guides/configuring-airbyte-db
DATABASE_USER=postgres
DATABASE_PASSWORD=secret_password
DATABASE_HOST=airbyte-testing.xxxxx.region.rds.amazonaws.com
DATABASE_PORT=5432
DATABASE_DB=postgresql
# translate manually DATABASE_URL=jdbc:postgresql://${DATABASE_HOST}:${DATABASE_PORT}/${DATABASE_DB} (do not include the username or password here)
# Here I tried the URL with double quotes "" too. But I still get the same error.
DATABASE_URL=jdbc:postgresql://airbyte-testing.xxxxx.region.rds.amazonaws.com:5432/postgresql?sslmode=require&ssl=true
JOBS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION=0.40.26.001

# Back to default
# Airbyte Internal Config Database, defaults to Job Database if empty. Explicitly left empty to mute docker compose warnings.
CONFIG_DATABASE_USER=
CONFIG_DATABASE_PASSWORD=
CONFIG_DATABASE_URL=
CONFIGS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION=0.40.23.002

### AIRBYTE SERVICES ###
TEMPORAL_HOST=airbyte-temporal:7233
INTERNAL_API_HOST=airbyte-server:8001
CONNECTOR_BUILDER_API_HOST=airbyte-connector-builder-server:80
WEBAPP_URL=http://localhost:8000/
# Although not present as an env var, required for webapp configuration.
CONNECTOR_BUILDER_API_URL=/connector-builder-api
AIRBYTE_API_HOST=airbyte-api-server:8006

### JOBS ###
# Relevant to scaling.
SYNC_JOB_MAX_ATTEMPTS=3
SYNC_JOB_MAX_TIMEOUT_DAYS=3
SYNC_JOB_INIT_RETRY_TIMEOUT_MINUTES=5
JOB_MAIN_CONTAINER_CPU_REQUEST=
JOB_MAIN_CONTAINER_CPU_LIMIT=
JOB_MAIN_CONTAINER_MEMORY_REQUEST=
JOB_MAIN_CONTAINER_MEMORY_LIMIT=

NORMALIZATION_JOB_MAIN_CONTAINER_MEMORY_LIMIT=
NORMALIZATION_JOB_MAIN_CONTAINER_MEMORY_REQUEST=
NORMALIZATION_JOB_MAIN_CONTAINER_CPU_LIMIT=
NORMALIZATION_JOB_MAIN_CONTAINER_CPU_REQUEST=

### LOGGING/MONITORING/TRACKING ###
TRACKING_STRATEGY=segment
JOB_ERROR_REPORTING_STRATEGY=logging
# Although not present as an env var, expected by Log4J configuration.
LOG_LEVEL=INFO


### APPLICATIONS ###
# Worker #
WORKERS_MICRONAUT_ENVIRONMENTS=control-plane
# Cron #
CRON_MICRONAUT_ENVIRONMENTS=control-plane
# Relevant to scaling.
MAX_SYNC_WORKERS=5
MAX_SPEC_WORKERS=5
MAX_CHECK_WORKERS=5
MAX_DISCOVER_WORKERS=5
MAX_NOTIFY_WORKERS=5
SHOULD_RUN_NOTIFY_WORKFLOWS=true
# Temporal Activity configuration
ACTIVITY_MAX_ATTEMPT=
ACTIVITY_INITIAL_DELAY_BETWEEN_ATTEMPTS_SECONDS=
ACTIVITY_MAX_DELAY_BETWEEN_ATTEMPTS_SECONDS=
WORKFLOW_FAILURE_RESTART_DELAY_SECONDS=

# etc.

In terms of security & network:

1. I've put both the EC2 instance and the RDS DB behind the same VPC & Security Group.
2. With the following ingress rules (terraformed):

# Accesses from VPN
  ingress {
    protocol        = "-1"
    from_port       = 0
    to_port         = 0
    security_groups = [var.vpn_security_group_id]
  }
  # Allow self communication
  ingress {
    protocol  = "-1"
    from_port = 0
    to_port   = 0
    self      = true
  }
  # Used for airbyte_db
  ingress {
    protocol    = "tcp"
    from_port   = 5432
    to_port     = 5432
    cidr_blocks = ["0.0.0.0/0"]
  }

3. Egress is FULLY ALL ALLOWED
4. Both have associated/accessible public IP addresses

Do you have any idea why I'm getting these connection errors for airbyte-temporal?
Thank you for your help!

[judiennetSC]

Edit: I succeeded in fixing this issue.

In some of the latest versions of PostgreSQL (ex: v15), and if your cloud provider is AWS (I don't know for the other ones), you will need to change the parameter rds.force_ssl to 0 (off). Link to this doc.

The root cause issue is coming from the docker-compose.yaml file, part airbyte-temporal: we should have a full DATABASE_URL variable being used here, instead of a mix of HOST + DB_NAME + etc. single ones. => This will ensure URL extra parameters such as sslmode=require to be taken into account and therefore, work.

Thanks!

[willi-mueller]

On GCP, I've been successful with the following settings in .env

DATABASE_USER=postgres
DATABASE_PASSWORD=${DATABASE_PASSWORD}
DATABASE_HOST=10.89.208.3 # private IP
DATABASE_PORT=5432
DATABASE_DB=postgres

# translate manually DATABASE_URL=jdbc:postgresql://${DATABASE_HOST}:${DATABASE_PORT}/${DATABASE_DB} (do not include the username or password here)
DATABASE_URL="jdbc:postgresql://10.89.208.3:5432/postgres?ssl=true&sslmode=require"

[dx0x58]

@willi-muellerI had a similar problem with a Golang app and AWS. Adding sslmode=require fixed it. It saved my day! Thank you!

[willi-mueller]

Thank you @dx0x58, I'm glad it was helpful to share my findings!

[xlfishbone]

On AWS RDS I also had to update the SQL_TLS environment variables for the airbyte-temporal service in the docker-compose.yaml. Per suggestion from a similar issue.

#11157 (comment)

  - SQL_TLS=true      
  - SQL_TLS_DISABLE_HOST_VERIFICATION=true      
  - SQL_TLS_ENABLED=true      
  - SQL_HOST_VERIFICATION=false