PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Connect to Postgres Flexible Server via "az postgress" from PS1 Script when Password has characters needing escapes

I want to run a command on Azure Postgres Flexible Server via a PS1 (PSCore) script. The password can contain characters like < and ) that the PSCore parser chokes on.

I’m attempting to use the az postgres flexible-server execute command. I’m thinking this is more of a PS1 script issue than a Postgres or az postgres issue.

Example command:

az postgres flexible-server execute -n the-instance-name -u theName -p the%WeIRDbutSecure#PasswordE<JMCiOw<>IQ) -d theDatabase -q "select * from MyTable"

I get an error like:

ParserError:
Line |
   1 |  … he-instance-name -u theName -p the%WeIRDbutSecure#PasswordE<JMCiOw<>IQ) -d theDatabase  …
     |                                                                          ~
     | Unexpected token ')' in expression or statement.

I’ve tried assigning the%WeIRDbutSecure#PasswordE<JMCiOw<>IQ) to a local variable. That moved the problem to:

> was unexpected at this time.

I’ve tried breaking placing the entire string in double quotes as well as single quotes. Neither works — same > was unexpected at this time. error. I’ve also tried embedding single backtick characters within the string, as in:

the%WeIRDbutSecure#PasswordE`<JMCiOw`<`>IQ`)

but continue to get the same > was unexpected at this time. error.

My ultimate goal is to embed this command-line command in a PS1 script that I execute later.

Has anyone ever been able to pass a -p password that contained characters that PS1 would like to interpret at directives? How did you escape these characters or the entire password?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks @mklement0 for the great links. That was a set of good reads to refamiliarize myself with.

    It turned out in my case that I needed to surround the password with " characters.

    I failed to mention that I am calling az postgres flexible-server execute in a PS1 script that is itself called from another PS1 script, all running in pwsh.

    Ultimately what worked was, basically:

    # $rawPW is the raw password from a KV Secret or elsewhere
$p = """" + $rawPW + """"
...
az postgres flexible-server execute -n the-instance-name -u theName -p $p -d theDatabase -q "select * from MyTable"

  2. 0

    tl;dr

    Work around the problem by calling via cmd /c.
    A here-string is used to make use of embedded quoting easier (note the "..." enclosure of the -p argument):

    cmd /c @"
az postgres flexible-server execute -n the-instance-name -u theName -p "the%WeIRDbutSecure#PasswordE<JMCiOw<>IQ)" -d theDatabase -q "select * from MyTable"
"@

    • While not needed in this example, the use of an expandable here-string, based on " (double quotes, @"<newline>...<newline>"@) – as opposed to the single-quoted, verbatim variant, @'<newline>...<newline>'@ – allows you to embed PowerShell variable values (e.g. $foo) in the string; see about_Quoting_Rules for the documentation of all forms of PowerShell string literals.

    • While there are other workarounds,[1] the one above is conceptually straightforward and works as-in in both PowerShell editions (Windows PowerShell (the legacy, ships-with-Windows, Windows-only edition of PowerShell whose latest and last version is 5.1) as well as PowerShell (Core) 7 (the modern, cross-platform, install-on-demand edition)).

    Background information

    • Fundamentally, as in any shell, you need to quote words that contain shell metacharacters such as ) – see about_Quoting_Rules for details on "..." (expandable) and '...' (verbatim) strings.

      • Thus, normally (see next point), you’d need
        ... -p 'the%WeIRDbutSecure#PasswordE<JMCiOw<>IQ)' ...

    • Unfortunately, because az is implemented as a batch file (az.cmd), this is not enough in this case (as you’ve observed):

      • Behind the scenes, PowerShell (defensibly) passes arguments that don’t contain spaces without quoting when it constructs the process command line, so even if you properly quote your space-less argument on the PowerShell side, it is still placed unquoted on the process command line.

      • This would work for most CLIs on Windows, but doesn’t for cmd.exe (the interpreter of batch files (*.cmd, *.bat)), which – inappropriately – parses a batch file’s command line as if it had been submitted from inside a cmd.exe session.

        • As a result, unquoted metacharacters such as <, |, and & break such batch-file calls.

        • GitHub issue #15143 was a suggestion to make PowerShell accommodate the quirks of cmd.exe, along with other high-profile CLIs; sadly, it went nowhere.

    [1] Alternatives are:
    (a) Rely on Windows PowerShell‘s broken handling of embedded " characters in order to ensure "..." enclosure of arguments on the process command line; however, since the broken behavior is fixed in PowerShell 7.3+, more work is needed in the latter – see this answer
    (b) Use the stop-parsing symbol, --%, which, however, comes with severe limitations – see this answer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search