Felix Horvat

    Felix Horvat

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

    Anna Geller

    4 months ago
    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:
    execute = PostgresExecute(db_name="", user="", ...)
    
    with Flow() as flow:
        pwd = PrefectSecret("POSTGRES_DB_PASSWORD")
        execute(password=pwd)
    Dylan Lim

    Dylan Lim

    4 months ago
    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()
    Anna Geller

    Anna Geller

    4 months ago
    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=...)
    I think the main issue you are facing is #1 - if you want a quick workaround to your issue, try doing this instead:
    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
    Dylan Lim

    Dylan Lim

    4 months ago
    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
    Anna Geller

    Anna Geller

    4 months ago
    you can set secrets directly through UI, this is much easier than via GraphQL
    Dylan Lim

    Dylan Lim

    4 months ago
    Oh! Found it. Thanks again!
    Anna Geller

    Anna Geller

    4 months ago
    nice work!