https://prefect.io logo
f

Felix Horvat

05/12/2022, 9:49 AM
i want to use PostgresExecute - how do i pass the password to this task? it only seems to be possible through the run method?
1
a

Anna Geller

05/12/2022, 11:42 AM
I think this is how you can do this
ok you're right, it is through the run method and this was intentional so that you can pass Secrets as data dependencies:
Copy code
execute = PostgresExecute(db_name="", user="", ...)

with Flow() as flow:
    pwd = PrefectSecret("POSTGRES_DB_PASSWORD")
    execute(password=pwd)
d

Dylan Lim

05/20/2022, 6:28 AM
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)
Copy code
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):
Copy code
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()
a

Anna Geller

05/20/2022, 1:51 PM
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
Copy code
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
Copy code
conn.run(db_name="name", user=...)
I think the main issue you are facing is #1 - if you want a quick workaround to your issue, try doing this instead:
Copy code
from prefect.client import Secret
...
def load(record):
  pwd = Secret(PASSWORD).get() # this way you retrieve it directly
  try:
    conn = PostgresExecute()
    conn.run(db_name="name", user=...)
    return
  except Exception as e:
    raise e
d

Dylan Lim

05/20/2022, 3:11 PM
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:
Copy code
mutation {
  set_secret( input: { 
    name: "PASSWORD", 
    value: "PASSWORD"
  } ) {
    success
  }
}
Response:
Copy code
{
  "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
a

Anna Geller

05/20/2022, 3:18 PM
you can set secrets directly through UI, this is much easier than via GraphQL
d

Dylan Lim

05/20/2022, 3:22 PM
Oh! Found it. Thanks again!
a

Anna Geller

05/20/2022, 3:22 PM
nice work!
26 Views