meta | content | tags | dates | categories | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
database serverless-sql-database unable connect connection troubleshooting connection error |
|
|
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"
```
You either used the wrong password/IAM secret key, or the wrong username/IAM user ID/application ID.
Make sure that you use the correct user/application ID and secret key combination.
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
```
The IAM principal (user or application) does not have the necessary permissions to connect the database.
Grant one of the following permissions sets to the user or application:
ServerlessSQLDatabaseReadOnly
ServerlessSQLDatabaseReadWrite
ServerlessSQLDatabaseDataReadWrite
ServerlessSQLDatabaseFullAccess
Refer to the dedicated documentation for more information on the SQL operations allowed by each permissions set.
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
```
Serverless SQL Databases do not support TEMPORARY TABLES
or SUBSCRIPTION
yet. Refer to the known differences page for more information.
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.
The error message below appears when trying to use PREPARE TRANSACTION
in a Serverless SQL Database:
```
ERROR: prepared transactions are disabled
```
Serverless SQL Databases do not support PREPARE TRANSACTION
. Refer to the dedicated documentation for more information.
If you require this feature, we suggest you use a Managed Database for PostgreSQL.
The error message below appears when trying to connect to the Serverless SQL Database:
```
ERROR: cannot refresh materialized view "example-view" concurrently
```
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.
If you require this feature, we suggest you use a Managed Database for PostgreSQL.
The error message below appears when trying to connect to the Serverless SQL Database:
Error: pq: Database hostname wasn't sent to server
This error happens when the SQL client you are using does not support the TLS Server Name Indication (SNI) feature.
-
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/OpenTofu | No | Terraform/OpenTofu includes an older lib/pq version (v1.10.3) which does not support SNI. |