DAML JSON-API Query error " Endpoints.ServerError: org.postgresql.util.PSQLException: The server does not support SSL."

I am experimenting with the JSON-API and experimenting an error

I am using POSTMAN for testing and connecting to a local Postgress DB

Post / localhost:7575/v1/query

Body

{
“templateIds”: [“Main:CitizenInvitation”],
“query” : {“citizen”: “Alice”}
}

Response:

{
“result”: ,
“errors”: [
“Endpoints.ServerError: org.postgresql.util.PSQLException: The server does not support SSL.”
],
“status”: 501
}

Other JSON-APIs seem not to have this problem

Any suggestions for a fix ?

Which parameters are you supplying to the JSON API? In particular what are you passing to --query-store-jdbc-config?

Found the resolution

Switching from “POST” to “GET” resolves the issue

Please update the documentation

https://docs.daml.com/json-api/index.html#id14

POST is correct here please don’t just switch to GET. The GET request ignores the body and doesn’t perform any filtering which is probably why it doesn’t hit the postgres backend which is used for queries.

Yes. I noticed that just a minute ago

Here is my JSON-API Command

daml json-api --ledger-host localhost --ledger-port 6865 --http-port 7575 --max-inbound-message-size 4194304 --package-reload-interval 5s --application-id HTTP-JSON-API-Gateway --static-content “prefix=static,directory=./static-content” --query-store-jdbc-config “driver=org.postgresql.Driver,url=jdbc:postgresql://localhost:5432/test?&ssl=true,user=Bart,password=password,createSchema=false”

Remove the ssl=true part (and the comma afterwards) if your postgres instance is not running with TLS.

bartcant:

–query-store-jdbc-config “driver=org.postgresql.Driver,url=jdbc:postgresql://localhost:5432/test?&ssl=true,user=Bart,password=password,createSchema=false”

yes, this error is coming from the postgres JDBC driver. Just start json-api with

--query-store-jdbc-config “driver=org.postgresql.Driver,url=jdbc:postgresql://localhost:5432/test?&user=Bart,password=password,createSchema=false”

Remove the ssl=true part (and the comma afterwards) if your postgres instance is not running with TLS.

You are running with JSON API with query store search index (postgres backend). Only query endpoint uses it. Technically you don’t need query store search index if your dataset is reasonably small. For your local experiments you can drop --query-store-jdbc-config command line options completely.

removing it created errors on starting up the json-api
so I changed it

ssl=false

new error

{
“result”: ,
“errors”: [
“Endpoints.ServerError: org.postgresql.util.PSQLException: ERROR: relation "template_id" does not exist\n Position: 18”
],
“status”: 501
}

Looks like you didn’t create the schema so change it to createSchema=true run it once and then go back to createSchema=false

Here is the error when removing ssl=true
it is complaining it requires a user field although it is present

Error: Invalid JdbcConfig, must contain ‘user’ field
Try --help for more information.
daml-helper: Received ExitFailure 100 when running
Raw command: java “-Dlogback.configurationFile=C:\Users\bartc\AppData\Roaming\daml\sdk\1.1.1\daml-sdk/json-api-logback.xml” -jar “C:\Users\bartc\AppData\Roaming\daml\sdk\1.1.1\daml-sdk/daml-sdk.jar” json-api --ledger-host localhost --ledger-port 6865 --http-port 7575 --max-inbound-message-size 4194304 --package-reload-interval 5s --application-id HTTP-JSON-API-Gateway --static-content prefix=static,directory=./static-content --query-store-jdbc-config driver=org.postgresql.Driver,url=jdbc:postgresql://localhost:5432/test?&user=Bart,password=password,createSchema=false

   createSchema -- boolean flag, if set to true, the process will re-create database schema and terminate immediately.

--query-store-jdbc-config driver=org.postgresql.Driver,url=jdbc:postgresql://localhost:5432/test?&user=Bart,password=password,createSchema=false

?& – is the reason

Please try with:

--query-store-jdbc-config driver=org.postgresql.Driver,url=jdbc:postgresql://localhost:5432/test?user=Bart,password=password,createSchema=false

or just drop --query-store-jdbc-config option and make sure it runs without postgres search index first.

Createschema changes fixed it. Thanks for the help

cocreature:

Looks like you didn’t create the schema so change it to createSchema=true run it once and then go back to createSchema=false

Would a createSchemaIfMissing option be a useful feature?

anthony:

Would a createSchemaIfMissing option be a useful feature?

We considered and explicitly rejected such a feature in discussing #3386, because “I have correctly configured the database, it is just empty” is not the only reason the schema may appear to be absent. As that issue suggests, the command you use to normally start JSON API is far safer to deploy if it never uses DDL.

2 posts were split to a new topic: CreateSchema is emptying the database