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

Arthur Zakirov <a.zakirov@postgrespro.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