Re: BUG #15961: psql should be able to read password from stdin

Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru> writes:
> On 16.08.2019 17:20, PG Bug reporting form wrote:
>> My use case is that I am writing terraform scripts to standup Sonarqube in
>> AWS. After creating the AWS RDS PostgreSQL database, I need to be able to
>> create the sonarqube user and grant access to the sonarqube database that
>> was created with the RDS instance. I can work around this using the
>> PGPASSWORD environment variable but that seems to be deprecated. I don't
>> want to use the .pgpass solution since I don't want/need the postgres user's
>> credentials in my EC2 instance where the Sonarqube server will be running...

> Is PGPASSWORD really deprecated?

It's insecure on some platforms (where other users can see a process's
environment variables). AWS may not be like that, not sure.

> I guess passing a password by command-line might have similar security
> problems. If it suits you then PGPASSWORD should work for you too.

Yeah, this. It's not very clear to me how such a feature wouldn't just
move the security problem somewhere else. Where is the program on the
front end of the pipe going to get the password from?

I don't think that Peter's objection has much force. We could just
say that a password-from-stdin feature is only allowed in combination
with -f and/or -c, so that there's no conflict as to what stdin should
provide. So given a bulletproof use-case, I wouldn't be averse to
adding such a feature. But if it's just moving the problem elsewhere,
I'm not in favor of it. We try to steer people away from insecure
password usage --- that's why there's no put-the-password-on-the-
command-line option. (I'm afraid that password-from-stdin would
encourage people to do "echo $PASSWORD | psql" and thereby be
completely vulnerable to command-line exposure hazards.)

In any case, that would be a new feature not a bug fix, so the very
earliest you could hope to see it would be v13 next year. For
shorter-term fixes, you could consider:

* Run psql in an environment where /dev/tty is a pseudo-tty that
you can feed input to. This fails to avoid any of the security
questions mentioned above, but at least it's a solution available
today.

* Don't use passwords. PG offers a bunch of other authentication
technologies that are way friendlier to use in scripts.

regards, tom lane