Solving connection errors

FATAL: password authentication failed for user

Problem

The error message below appears when trying to connect to the Serverless SQL Database:

FATAL: password authentication failed for user "example-fafb-4c6c-a406-911d9675a703"

Cause

You either used the wrong password/IAM secret key, or the wrong username/IAM user ID/application ID.

Solution

Make sure that you use the correct user/application ID and secret key combination.

FATAL: principal does not have permission to access database

Problem

The error message below appears when trying to connect to the Serverless SQL Database:

FATAL: principal example-fafb-4c6c-a406-911d9675a703 does not have permission to access database example-a2ea-4f6e-bbe5-791a1b6905ff

Cause

The IAM principal (user or application) does not have the necessary permissions to connect the database.

Solution

Create a new IAM policy or update an existing one to grant one of the following permissions sets to the user or application:

• ServerlessSQLDatabaseReadOnly
• ServerlessSQLDatabaseReadWrite
• ServerlessSQLDatabaseFullAccess

ERROR: permission denied to create temporary tables/subscription in database

Problem

The error messages below appear when trying to create TEMPORARY TABLES or SUBSCRIPTION in a Serverless SQL Database:

ERROR: permission denied to create temporary tables in database
ERROR: permission denied to create subscription

Cause

Serverless SQL Databases do not support TEMPORARY TABLES or SUBSCRIPTION yet. Refer to the known differences page for more information.

Solution

If you (or a third party tool you are using, especially ETL tools such as Airbyte, Fivetran or Meltano) require TEMPORARY TABLES, we suggest you use a Managed Database for PostgreSQL. SUBSCRIPTION is not supported by Managed Databases for PostgreSQL yet, but you can import data with ETL tools.

ERROR: prepared transactions are disabled

Problem

The error message below appears when trying to use PREPARE TRANSACTION in a Serverless SQL Database:

ERROR: prepared transactions are disabled

Cause

Serverless SQL Databases do not support PREPARE TRANSACTION. Refer to the dedicated documentation for more information.

Solution

If you require this feature, we suggest you use a Managed Database for PostgreSQL.

ERROR: cannot refresh materialized view concurrently

Problem

The error message below appears when trying to connect to the Serverless SQL Database:

ERROR: cannot refresh materialized view "example-view" concurrently

Cause

Serverless SQL Databases do not support REFRESH MATERIALIZED VIEW CONCURRENTLY. However, the REFRESH MATERIALIZED VIEW command is supported, only the CONCURRENTLY keyword is not supported. Refer to the dedicated documentation for more information.

Solution

If you require this feature, we suggest you use a Managed Database for PostgreSQL.

Database hostname was not sent to server

Problem

The error message below appears when trying to connect to the Serverless SQL Database:

Error: pq: Database hostname wasn't sent to server

Cause

This error happens when the SQL client you are using does not support the TLS Server Name Indication (SNI) feature.

Solution

• If you are using a third-party tool or library which allows you to input a connection string or optional connection parameters, add ?options=databaseid%3D{databaseid} at the end of your connection string:

  "postgresql://{username}:{password}@{host}:5432/{databasename}?sslmode=require&options=databaseid%3D{databaseid}"
  

  Or

  terraform {
    backend "pg" {
      conn_str = "postgresql://{username}:{password}@{host}:5432/{databasename}?sslmode=require&options=databaseid%3D{databaseid}"
    }
    required_providers {
      scaleway = {
        source = "scaleway/scaleway"
      }
    }
    required_version = ">= 0.13"
  }
  

• If you cannot modify the connection string or optional connection parameters, upgrade or change your client library. Refer to the table below for the most common libraries and tools supporting SNI.

  Language	Library / Tool	SNI support	Workaround to Partial Support
  Nodejs	node-postgres	Yes
  Nodejs	postgres.js	Yes
  Python	psycopg2	Yes
  Python	SQLAlchemy	Yes
  Java	pgjdbc	Yes
  Go	lib/pq	Partial	Upgrade lib/pq to v1.10.7, which supports SNI.
  Dart	postgres	Yes
  N/A	psql	Yes
  N/A	pgcli	Yes
  N/A	pgAdmin	Yes
  N/A	DBeaver	Yes
  N/A	Postico 2	Yes
  N/A	IntelliJ (SQL Module)	Yes
  N/A	navicat	No
  N/A	Stitch	No
  N/A	Terraform	No	Terraform includes an older lib/pq version (v1.10.3) which does not support SNI.