Said program stores password in memory for whenever connecting to DB. Someone (root) does a memory dump of the running process and the password is in there (somewhere). If the password was encrypted somehow then the key will be in the memory dump too (unless it is read from a file etc. but that could prove risky AND expensive). So, the program does not contain the password but when it is run, it must store the password in program memory. How to encrypt that? What do people do in such cases?

See also Hide DBI password in scripts. Ultimately, the password has to leave the perl process to be passed to the database. That usually happens in plaintext, and usually using network functions. So all that is needed to get the password is strace.

Theoretically, databases could implement Challenge–response authentication, so that the password is never transmitted, neither in plaintext nor encrypted. I've never seen that implemented AND used.

I guessed that recent versions of PostgreSQL could have some implementation, and I was right: Pg 10 and 11 have scram-sha-256, but it requires recent client libraries:

scram-sha-256

The method scram-sha-256 performs SCRAM-SHA-256 authentication, as described in RFC 7677. It is a challenge-response scheme that prevents password sniffing on untrusted connections and supports storing passwords on the server in a cryptographically hashed form that is thought to be secure.

This is the most secure of the currently provided methods, but it is not supported by older client libraries.

More details in Salted Challenge Response Authentication Mechanism.

Note that SCRAM-SHA-256 has to be configured at the server side, the client can't choose the authentication mechanism.

For other databases, RTFM.

Alexander