Unable to write data to PostgreSQL from flux task

[vkhazin]

Sample code:

import "sql"

option task = {name: "export-to-rdbms", every: 24h}

connectionString =
    "postgresql://postgres:<secret>@<masked>.us-east-2.rds.amazonaws.com:5432/postgres"

// Reading data works!
// data =
//     sql.from(
//         driverName: "postgres",
//         dataSourceName: connectionString,
//         query: "SELECT measurement, field, value, \"timestamp\" FROM some_table;",
//     )
data =
    from(bucket: "some-data")
        |> range(start: -30d)
        |> map(fn: (r) => ({timestamp: r._time, measurement: r._measurement, field: r._field, value: r._value}))

// Writing data does not work!
data
    |> sql.to(driverName: "postgres", dataSourceName: connectionString, table: "some_table")

Error:

Unexpected error from query; Err: failed to initialize execute state: pq: password authentication failed for user "postgres": failed to initialize execute state: pq: password authentication failed for user "postgres"

[gdepue]

similar issues in this data explorer query --> "an internal error has occurred"

import "sql"

connectionString = "postgresql://login>:<password>@<postgres-instance.us-east-1.rds.amazonaws.com:5432/postgres"

sql.from( driverName: "postgres", dataSourceName: connectionString, query: "SELECT * FROM usernames;" )

=== as per https://docs.influxdata.com/flux/v0.x/query-data/sql/amazon-rds/

[kristinarobinson]

@danatinflux @vkhazin -- Are you using InfluxData's secrets? The error shown is a Postgres authentication error that the password or username is incorrect. This is not a UI issue unless secrets are being used.

[vkhazin]

There is no usage of InfluxData secrets. I am not going to argue whether it is a UI or a back-end issue. I am going to argue it is an issue that requires a timely resolution.

[nathanielc]

@vkhazin Thanks for the report, we have a large batch of updates to the sql.to and sql.from functions in the works. We'll add this bug to the testing to ensure it gets fixed.

[danatinflux]

@vkhazin I was able to get this working by removing network restrictions on the AWS/Postgres side:

Initially, I wasn't able to make connections to my OSS box until I added the top exclusion (with the /32 subnet). That all worked fine. Then, I tried the C2 version and again, no connectivity. However, when I added the very insecure 0.0.0.0/0 source (middle) it started to work.

Unfortunately, we don't currently have a list of IPs that a customer would be able to whitelist at this time as all the IPs for Cloud are dynamic.

[vkhazin]

@danatinflux thank you for your comments! The situation is different for me: the security group is configured for 0.0.0.0/0 access with no success. And the InfluxDB is able to read data using sql.from, which would not be possible with a security group not configured for wide open access. InfluxDB task with flux is failing to execute sql.to command to AWS RDS Postgres and is succeeding with the same command to AWS RDS MySql. Other than a problem with documented PostgreSql setup or with sql.to implementation I cannot explain the observation.

[danatinflux]

@vkhazin Please open up a support ticket with your examples so we can test.

[Marwes]

pq: password authentication failed for user "postgres"

Sounds like the postgres instance rejected the credentials. Perhaps the postgres user only have read access, but not write access?

[vkhazin]

The credentials had been tested with DBeaver client to confirm read and write access is available to the table.

[Marwes]

@vkhazin When spinning up a local postgres I am not seeing any issues with writing data to it. So I guess it might be RDS specific, or some other configuration specific to your instance.

[Marwes]

@vkhazin It would be helpful if you could make a reproduction for this that I could test. Ideally using a local postgres.

[vkhazin]

@Marwes the issue reported is specific to AWS RDS following the documentation from Influx Data

I don't see other possible explanation to the situation other than:

• there is no support for AWS RDS Postgresql
• the are specific settings required on the Flux side and on the AWS RDS side to resolve configuration issues
• there is a bug in the code preventing the integration from working and it is being resolved

Creating an AWS RDS instance is not a difficult task and does not require a considerable financial burden to reproduce the problem. Unless the integration is on as-is/best effort basis... “The light is much better here”

[abalone23]

I am able to write to my RDS postgres instance from InfluxDB Cloud Flux v0.150.1:

import "sql"
import "array"

 data = array.from(rows: [
   {url: "http://blah.com", "name A": "blah"}
 ])

data
  |> sql.to(
      driverName: "postgres",
      dataSourceName: "postgresql://postgres:<PASS>@<DB>.us-west-2.rds.amazonaws.com:5432/pgtestdb",
      table: "link table"
)

[nathanielc]

We are unable to reproduce this issue using the same setup on RDS postrgres. At this point every indication is that the original error message is correct and AWS RDS is rejecting the authentication credentials being passed to it. Please re-check your database credentials and auth configurations. If you learn more specifics about how to reproduce this issue we can reopen the issue and continue our investigation.