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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

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

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      15961
Logged by:          Robert Patrick
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

Peter Eisentraut-6
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


Reply | Threaded
Open this post in threaded view
|

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

a.zakirov
In reply to this post by apt.postgresql.org Repository Update
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
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Arthur Zakirov <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Thomas Kellerer
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



Reply | Threaded
Open this post in threaded view
|

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

Georg Sauthoff
In reply to this post by a.zakirov
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