Thread: BUG #15961: psql should be able to read password from stdin
The following bug has been logged on the website: Bug reference: 15961 Logged by: Robert Patrick Email address: rhpatrick@gmail.com PostgreSQL version: 11.4 Operating system: Amazon Linux Description: I understand why psql does not include a mechanism to pass the password on the command-line. Having worked with and built other similar applications at Oracle, I tried to pipe the password into stdin of the psql command like so: echo P0stgr3s | psql --username=postgres Unfortunately, psql ignores it and prompts me for the password. 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... psql should really accept the value for the password prompt from stdin.
On 2019-08-16 16:20, PG Bug reporting form wrote: > I understand why psql does not include a mechanism to pass the password on > the command-line. Having worked with and built other similar applications > at Oracle, I tried to pipe the password into stdin of the psql command like > so: > > echo P0stgr3s | psql --username=postgres > > Unfortunately, psql ignores it and prompts me for the password. The stdin for psql is already used for feeding SQL commands to execute (e.g., psql < file.sql), so we can't also use it for getting a password. It might be possible to create a new option to get the password from a file descriptor (different from stdin), but that's not a feature that exists today. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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? The documentation just says that it is not recommended to use it for security reasons, and it doesn't say that the variable is deprecated: https://www.postgresql.org/docs/current/libpq-envars.html I guess passing a password by command-line might have similar security problems. If it suits you then PGPASSWORD should work for you too. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
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
Tom Lane schrieb am 19.08.2019 um 16:07: > that's why there's no put-the-password-on-the-command-line option But there is one: using a connection URI https://www.postgresql.org/docs/current/libpq-connect.html#id-1.7.3.8.3.6
On Mon, Aug 19, 2019 at 12:40:05PM +0300, Arthur Zakirov wrote: > 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? The documentation just says that it is not > recommended to use it for security reasons, and it doesn't say that the > variable is deprecated: > https://www.postgresql.org/docs/current/libpq-envars.html PGPASSWORD isn't deprecated. The documentation states that 'is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps'. IOW, it's only not recommended if one plans to use Postgres on such systems. The only example for such an operating system I know is the ancient Solaris 9. That means under Solaris 9 a non-root user is able to read the environment variables of processes owned by other users. The also ancient Solaris 10 fixed that. Under Linux, the environment of a processed is only readable for the process-owning user and root. The PGPASSWORD handling in psql can be even made more secure under Linux: https://www.postgresql.org/message-id/15930-1a5562cad4a72900%40postgresql.org > I guess passing a password by command-line might have similar security > problems. If it suits you then PGPASSWORD should work for you too. Under Linux, passing the password on the command line is definitely more insecure because the argument vector of a process is world-readable. Best regards Georg