Maximum length or special character restrictions for PostgreSQL 13 user password?

I'm using a password generator to come up with a password that will be automatically pulled from Azure Key Vault and into the Postgres deployment. The passwords are manually added in Azure Key Vault with the UI and not CLI.

I've tried 256-character length passwords with and without special characters. Each time I then try to login using kubectl port-forward and pgAdmin, and I get a "password authentication failed."

I shorten it to like 16-characters and then it works fine.

So it seems like special characters aren't an issue, but length is. So just curious:

• What special characters, if any, will cause an issue in the password?

• What is the maximum length of a password?

Note: I've even dropped down to 128 characters and still the same issue.

The problem isn't when you are generating a password natively in the PostgreSQL instance, but when you are using the password on the client side.

A standard PostgreSQL ODBC connection string will look like this:

Driver={PostgreSQL};Server=IPaddress;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

If you replace mypassword with a value like this M;Sp=cia1P@ssword then you will have the following ODBC connection string:

Driver={PostgreSQL};Server=IPaddress;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=M;Sp=cia1P@ssword;

If you then go ahead and split up the ODBC connection string you will have the following items:

Driver={PostgreSQL};
Server=IPaddress;
Port=5432;
Database=myDataBase;
Uid=myUsername;
Pwd=M;
Sp=cia1P@ssword;

As you may notice, both ; and = can break things for ODBC connection strings. You may be using other possible clients and connection information. In the end you will have to try them out or consider reading this...

I've curated a list of special characters which I don't use when generating passwords in KeePass and which I have provided as a Blog article over on topanswers.xyz (Knowledge Communities). It has the slightly misleading title: Passwords for Databases.

Quoted from my article:

Universal Password Generator

In order to improve the procedure of generating passwords I started generating long passwords (15+ characters length) with the password generator supplied in KeePass.

My password generator excludes most of the following characters:

= equals sign
" double quote
' single quote
& ampersand
% percentage
^ caret (accent circumflex)
* asterisk
? question mark
¨ diaeresis (umlaut)
´ forward tick (accent grave)
back tick (accent acute)
/ forward slash
\ back slash
@ at sign (commercial at)
[ left square bracket
] right square bracket
{ left curly bracket
} right curly bracket
( left round bracket
) right round bracket
; semicolon
_ underscore
# hash


Reasons for Breaking

Some of the reasons for excluding certain characters are only because of certain RDBMS whereas other characters break silent installation files. I will try and list some of the obvious reasons and some other maybe not so obvious reasons why some characters fail when used in passwords.
= equal

• In an ODBC connection string the equals sign is used to separate parameter from value.

• In SQL Server silent installations an equal sign will break the password.

" double quote

• Breaks the password for parameter=value settings in ODBC connection strings when the parameter starts with a double quote.

• Breaks connection strings in various programming languages depending on the language used.

' single quote (similar to double quote)

• Breaks the password for parameter=value settings in ODBC connection strings when the parameter starts with a single quote.

• Breaks connection strings in various programming languages depending on the language used.

% percentage

-
Fails in SQL Server silent installations when used in a conjunction with a BAT/CMD file that runs the setup.exe which supplies the password for a login.
E.g contents of a SETUP.BAT file:

E:\setup.exe /ACTION=INSTALL /CONFIGURATIONFILE=%~dpn0.ini /IACCEPTSQLSERVERLICENSETERMS /SAPWD="MyPassword%WillFail"

@ at sign (commercial at)



• Breaks Oracle connection strings when using the notation: user/password@oracle_sid. The @ in the password is interpreted as the terminator between password and oracle_sid.

• Breaks some connection strings as observed by McNets.

& ampersand

• Used to break ODBC connection strings.

? question mark

• If I recall correctly, this used to break very old ODBC connections strings (or was it OLE DB), because the question mark was "asking for a parameter".

_ underscore

• I'm not 100% sure why the underscore eventually made it into my list of undesired characters for database passwords, but I think it was to do with posting an HTTP request with an underscore in the password for a certain web application. (shout out to Andriy M. for asking in the comments)

# hash`

• Apparently acccording to a comment, the hash can be used as a password in MySQL, but when it is stored in a configuration file, then anything after the hash is interpreted as a comment. (shout out to Scott N for mentioning this in the comments)

Other Characters

The other characters in my collection have made it there due to observations during my 26+ years in information technology and as a database administrator. They are probably not conclusive, but might help others when encountering errors trying to connect to a database. They might also help prevent issues when generating passwords and/or connection strings for various RDBMS and users.

StackExchange Database Administrators Q#286254, answer score: 4