Postgres database secret engine fails to connect when password contains URI-reserved characters such as @ or /

[davoustp]

Describe the bug Using a configured Postgres database connection (see https://developer.hashicorp.com/vault/api-docs/secret/databases/postgresql#configure-connection ) with URI:

postgresql://{{username}}:{{password}}@postgres-database-host:5432/database-name

fails when the password contains URI-reserved characters, such as @ or /.

To Reproduce

• Enable the Postgres database engine in Vault
• Configure a connection with a URI containing the {{username}} and {{password}} templates
• Attempt to use this connection with a database role => this fails with error:

  error verifying connection: failed to connect to `host=postgres-database-host user={{username}} database=database-name`: failed SASL auth (FATAL: password authentication failed for user "{{username}}")

Expected behavior The database engine should be able to successfully connect to the database.

Environment:

• Vault Server Version : 1.13.0
• Vault CLI Version: 1.13.0
• Server Operating System/Architecture: linux x86_64

Additional context

The problem stems from the fact that these values must be URI percent encoded as described in https://www.postgresql.org/docs/15/libpq-connect.html#LIBPQ-CONNSTRING:

The connection URI needs to be encoded with percent-encoding if it includes symbols with special meaning in any of its parts.

This percent encoding must be done for ANY template-rendered value: this means that this problem also exists with {{username}} (any character is allowed by Postgres when using double-quoted identifiers as described in https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS . Unicode characters can even be used for identifiers (role/user name here) as well which can contain URI-reserved characters:

Unicode characters can be specified in escaped form by writing a backslash followed by the four-digit hexadecimal code point number or alternatively a backslash followed by a plus sign followed by a six-digit hexadecimal code point number.

Note that when using GH-19616 (available with version 1.14.0), the scram-sha-256 value is base64 encoded, which means that it can still contain URI-reserved characters such as / and +(see https://datatracker.ietf.org/doc/html/rfc4648#section-4), and problem is even more difficult to spot.

Finally, using a Keyword/Value Connection Strings may help to work around the issue (the pgx library supports this) but some characters still need to be escaped, which brings us to square one: templated values must be escaped.

[raymonstah]

Hi @davoustp, thanks for opening this issue. Unfortunately I'm having a bit of trouble reproducing it.

Can you help me out?

I have a docker-compose file that starts up a Postgres container with a username and password both containing the characters @ and /.

I then set up the Postgres engine in Vault and I'm able to do so succesfully.

➜  cat docker-compose.yml
# Use postgres/example user/password credentials
version: '3.1'

services:

  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: "postgres@/foo"
      POSTGRES_PASSWORD: "password@/bar"
      POSTGRES_DB: db
    ports:
      - 5432:5432

➜  vault write database/config/my-postgresql-database \
    plugin_name="postgresql-database-plugin" \
    allowed_roles="*" \
    connection_url="postgresql://{{username}}:{{password}}@localhost:5432/db" \
    username='postgres@/foo' \
    password='password@/bar' \
password_encryption=SCRAM-SHA-256
Success! Data written to: database/config/my-postgresql-database

Also, the engine does seem to support escaping of the credentials based on the code here.

[maxb]

➜ vault write database/config/my-postgresql-database \

Yes, but now do something that causes an attempt to use this configuration to connect to the database...

Also, the engine does seem to support escaping of the credentials based on the code here.

net.PathEscape is incorrect for encoding this portion of an URL.

Sadly, the Go stdlib does not provide a simple utility function with the correct encoding rules. However it can be approximated via url.User(input).String() (for both user and password).

[raymonstah]

Yes, but now do something that causes an attempt to use this configuration to connect to the database...

I believe setting up the configuration also verifies the connection to the database, but regardless, here are a few examples I've tried:

Create new roles:

vault write database/roles/my-role \
db_name="my-postgresql-database" \
creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; \
GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" \
default_ttl="1h" \
max_ttl="24h"
Success! Data written to: database/roles/my-role

vault read database/creds/my-role
Key                Value
---                -----
lease_id           database/creds/my-role/x65umpayRkksYLDssFYwIYBu
lease_duration     1h
lease_renewable    true
password           TYUsqREfue0-UOeHvrbF
username           v-token-my-role-np1C9f0WsdKMv5EyTIwe-1690835722

Rotate root:

vault write -f database/rotate-root/my-postgresql-database
Success! Data written to: database/rotate-root/my-postgresql-database

[maxb]

I believe setting up the configuration also verifies the connection to the database

My apologies, you are correct. I verified the source code wasn't doing the correct encoding, and assumed this would be why testing hadn't detected the problem.

@davoustp I did my own testing, and was not able to reproduce your observations exactly:

• / gets escaped
• @ does not get escaped, which is a technical violation of RFC3986, but much to my surprise the Go URL parser figures out how to correctly interpret the URL anyway
• • does not get escaped, and this is RFC compliant
• : does not get escaped, this is an actual bug, and breaks things when it occurs in a username

@raymonstah It appears that a username containing the : character is the only case in which I'm able to reproduce the original reporter's findings of a problem.

[davoustp]

Hi @maxb @raymonstah Sorry that I was so long to answer your questions - was away for a while.

I've been checking this again, and it turns out that the problem that I described (using the @ or / character) was rooted on my side (an invisible character was added by a script, and for some reason my editor had non-printable character display turned off, so I did not spot it).

Apologies for the false alarm - even though you discovered one bug with the : character on username.

Thanks for the code ref: these attributes are properly escaped by the net/url package, so it should work as expected (unless the net/url package is flawed somewhere).

https://cs.opensource.google/go/go/+/refs/tags/go1.21.0:src/net/url/url.go;l=96-101 and https://github.com/golang/go/issues/5684 probably hints at why some escaped and some not.