i want to use PostgresExecute - how do i pass the password to this task? it only seems to be possible through the run method?

I think this is how you can do this

Hey there, jumping on this thread - I’m trying to get PostgresExecute to work since I want to register a flow, but when I run it I get an authentication error. Strangely enough, when I replicate the task by using

psycopg2

and run the flow on my local machine, it works. Could anyone help me? For context, I’m testing a simple write to a postgres database; it’s a single-row

record

Task using PostgresExecute: (this fails with FATAL: password authentication failed for user …, SSL off)

def load(record):
  pwd = PrefectSecret(PASSWORD)
  try:
    conn = PostgresExecute()
    conn.run(db_name="name", user=...)
    return
  except Exception as e:
    raise e

Task using psycopg2 (this works):

def load(record):
  try:
    conn = psycopg2.connect(user="admin", password=...)
    cursor = conn.cursor()
    query = """INSERT INTO test_table (col) values (%s)"""
    values = (record,)
    cursor.execute(query, values)
    conn.commit()
  except Exception as e:
    raise e
  finally:
    if conn:
      cursor.close()
      conn.close()

there are a couple of issues here: #1 You can only call this line within a Flow block, not within a task - ideally pass it to your task as data dependency

pwd = PrefectSecret(PASSWORD)

#2 You shouldn't have to use try-except blocks when using Prefect #3 Usually you would initialize the task and then call it within your Flow, doing .run() is supported but discouraged

conn.run(db_name="name", user=...)

Thanks @Anna Geller! I managed to get it to work on local 🙂. I’m trying to set it up on Prefect Cloud but when I try to run the mutation to set the secret, it throws me this error. Mutation:

mutation {
  set_secret( input: { 
    name: "PASSWORD", 
    value: "PASSWORD"
  } ) {
    success
  }
}

Response:

{
  "errors": [
    {
      "path": [
        "set_secret"
      ],
      "message": "Expected type JSON!, found \"PASSWORD\"; Expecting value: line 1 column 1 (char 0)",
      "extensions": {
        "code": "INTERNAL_SERVER_ERROR",
        "exception": {
          "message": "Expected type JSON!, found \"PASSWORD\"; Expecting value: line 1 column 1 (char 0)",
          "locations": [
            {
              "line": 2,
              "column": 45
            }
          ],
          "path": null
        }
      }
    }
  ],
  "data": {
    "set_secret": null
  }
}

I saw in a separate thread that you mentioned that this should only occur when using server and not cloud - but I’m using this on the cloud interface and still get this error

you can set secrets directly through UI, this is much easier than via GraphQL

Oh! Found it. Thanks again!

nice work!