Strictly speaking, if said hosting company has access to the unencrypted data store they don't need any passwords. And if said hosting company has access to the encryption keys, any encrypted data store might just as well be considered unencrypted. So your question then becomes: how much effort should we spend on making it hard for our business partners to exfiltrate our data?

The problem with these kinds of questions is that the GDPR does not define any threat models, it only mentions "proper processes" and "adequate safeguards". Whether active subversion (by law or by greed) by your service provider should be included in your data loss exposure/risk assessments is very much an open question. At my company we decided to exclude such questions from the GDPR compliance process, and only include these scenario's in the threat models for our security assessments (note: that's not to say they are treated in isolation -- the results from our security assessments do inform our GDPR decisions like which data can be hosted where, but we do not repeat those same risk assessments in the GDPR survey).

To give a more direct answer to your question: I would consider encryption-at-rest a minimal requirement for a company hosting our internal data. Regardless of whether they're inside or outside the EU, and whether we're looking to host internal data, sales data or customer data; not being able to offer encryption at rest would mean my company won't use your hosting services for non-public data. For us, this specific ability is a supplier maturity test: if you haven't given serious thought about securing your customer's data, maybe we shouldn't be in business together.

But that decision is driven more by a defense-in-depth strategy about overall data security than by a specific GDPR requirement.

Encrypted at rest or not, the hosting company could easily dump the encryption keys out of memory while the server is running. If you're an American citizen, the government can just directly go after you or your company. If American law enforcement can get access to the data (i.e. by plugging the server into a UPS and carting it out of the data center) you're violating the GDPR at the very least; both attempts at skirting around the lack of American privacy guarantees were defeated by the American government refusing to provide sufficient data protection laws for European citizens, after all, choosing to uphold the PATRIOT ACT (and other such laws) over the digital business of EU customers.

Something as simple as a database password definitely doesn't fly as far as I know based on reading through the GDPR. Maybe it's legal if you apply enough tricks, you should consult a lawyer if you want to know your workaround is sufficient.

However, by default, storing PII of EU citizens (+UK citizens, I believe, they've implemented the GDPR before they left) with American companies is not legal. I can see how in theory a remote disk drive with fully end-to-end encrypted traffic (encrypted inside the EU, merely stored abroad, the decryption key never leaving the EU) may be allowed, but if the data gets decrypted on the American end I'm pretty sure you're out of luck. Otherwise, any form of TLS would be enough to avoid the GDPR, and that's definitely not the case.

Encryption at rest doesn't protect you. In fact, may even be legally required, regardless of where you store your data. The GDPR doesn't specify any exact security measures, but you do have to try your hardest to secure any PII you may process or store and encryption at rest is one of the easiest steps you can take to do so. You should make a conscious decision of what data may leak to where, the impact of the leak, and ways to counteract such problems.