Thread: [GENERAL] pgpass file type restrictions
Hello,
I’m running into problems with the restriction on pgpass file types. When attempting to use something like an anonymous pipe for a passfile, psql throws an error stating that it only accepts plain files. If it matters, I'm trying to use that so I can pass a decrypted pgpassfile into postgres since my company is not allowed to have unencrypted credentials on disk (yes, I know that it's kind of silly to add one layer of abstraction, but it's an industry rule we can't avoid). I know that we can also just avoid using psql, but there are benefits to using it for simple scripts, so if we can make this work fairly easily we'd like to do that.
I looked around to see if I could figure out why that restriction was put there in the first place, but the only reference I found was this entry in the 8.2.6 release notes which I wasn’t able to trace back to anything in particular:
Fix libpq crash when PGPASSFILE refers to a file that is not a plain file (Martin Pitt)
I was also unable to find anything useful in the source code. There were no comments around this snippet indicating why it was limited to plain files (it was implemented this way back in 2005!):
if (!S_ISREG(stat_buf.st_mode))
{
}
Does anyone know why it’s set up to avoid using things like anonymous pipes (or anything but "plain files")?
Regards,
Matt
Desidero <desidero@gmail.com> writes: > I’m running into problems with the restriction on pgpass file types. When > attempting to use something like an anonymous pipe for a passfile, psql > throws an error stating that it only accepts plain files. > ... > Does anyone know why it’s set up to avoid using things like anonymous pipes > (or anything but "plain files")? A bit of digging in the git history says that the check was added here: commit 453d74b99c9ba6e5e75d214b0d7bec13553ded89 Author: Bruce Momjian <bruce@momjian.us> Date: Fri Jun 10 03:02:302005 +0000 Add the "PGPASSFILE" environment variable to specify to the password file. Andrew Dunstan and poking around in the mailing list archives from that time finds what seems to be the originating thread: https://www.postgresql.org/message-id/flat/4123BF8C.5000909%40pse-consulting.de There's no real discussion there of the check for plain-file-ness. My first guess would have been that the idea was to guard against symlink attacks; but then surely the stat call needed to have been changed to lstat? So I'm not quite sure of the reasoning. Perhaps Andrew remembers. > If it matters, > I'm trying to use that so I can pass a decrypted pgpassfile into postgres > since my company is not allowed to have unencrypted credentials on disk > (yes, I know that it's kind of silly to add one layer of abstraction, but > it's an industry rule we can't avoid). I cannot get excited about that proposed use-case, though. How is a pipe any more secure than a plain file with the same permissions? My thought is that you shouldn't be depending on passwords at all, but on SSL credentials or Kerberos auth, both of which libpq supports fine. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 10/19/2017 02:12 AM, Tom Lane wrote: > Desidero <desidero@gmail.com> writes: >> I’m running into problems with the restriction on pgpass file types. When >> attempting to use something like an anonymous pipe for a passfile, psql >> throws an error stating that it only accepts plain files. >> ... >> Does anyone know why it’s set up to avoid using things like anonymous pipes >> (or anything but "plain files")? > A bit of digging in the git history says that the check was added here: > > commit 453d74b99c9ba6e5e75d214b0d7bec13553ded89 > Author: Bruce Momjian <bruce@momjian.us> > Date: Fri Jun 10 03:02:30 2005 +0000 > > Add the "PGPASSFILE" environment variable to specify to the password > file. > > Andrew Dunstan > > and poking around in the mailing list archives from that time finds > what seems to be the originating thread: > > https://www.postgresql.org/message-id/flat/4123BF8C.5000909%40pse-consulting.de > > There's no real discussion there of the check for plain-file-ness. > My first guess would have been that the idea was to guard against > symlink attacks; but then surely the stat call needed to have been > changed to lstat? So I'm not quite sure of the reasoning. Perhaps > Andrew remembers. That was written 13 years ago. I'm afraid my memory isn't that good. > >> If it matters, >> I'm trying to use that so I can pass a decrypted pgpassfile into postgres >> since my company is not allowed to have unencrypted credentials on disk >> (yes, I know that it's kind of silly to add one layer of abstraction, but >> it's an industry rule we can't avoid). > I cannot get excited about that proposed use-case, though. How is a pipe > any more secure than a plain file with the same permissions? If it's not allowed to reside on disk, put it on a RAM disk? > > My thought is that you shouldn't be depending on passwords at all, but > on SSL credentials or Kerberos auth, both of which libpq supports fine. > Yeah, we need to be convincing people with high security needs to get out of the password game. It's a losing battle. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
I agree that it would be better for us to use something other than LDAP, but unfortunately it's difficult to convince the powers that be that we can/should use something else that they are not yet prepared to properly manage/audit. We are working towards it, but we're not there yet. It's not really an exuse, but until the industry password policies are modified to outright ban passwords, many businesses will probably be in this position.
In any event, is the use case problematic enough that it would prevent the proposed changes from being implemented? I could submit a patch to postgres hackers if necessary, but if it's undesirable I can figure out something else.
Regards,
Matt
In any event, is the use case problematic enough that it would prevent the proposed changes from being implemented? I could submit a patch to postgres hackers if necessary, but if it's undesirable I can figure out something else.
Regards,
Matt
On Thu, Oct 19, 2017 at 7:22 AM Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote:
On 10/19/2017 02:12 AM, Tom Lane wrote:
> Desidero <desidero@gmail.com> writes:
>> I’m running into problems with the restriction on pgpass file types. When
>> attempting to use something like an anonymous pipe for a passfile, psql
>> throws an error stating that it only accepts plain files.
>> ...
>> Does anyone know why it’s set up to avoid using things like anonymous pipes
>> (or anything but "plain files")?
> A bit of digging in the git history says that the check was added here:
>
> commit 453d74b99c9ba6e5e75d214b0d7bec13553ded89
> Author: Bruce Momjian <bruce@momjian.us>
> Date: Fri Jun 10 03:02:30 2005 +0000
>
> Add the "PGPASSFILE" environment variable to specify to the password
> file.
>
> Andrew Dunstan
>
> and poking around in the mailing list archives from that time finds
> what seems to be the originating thread:
>
> https://www.postgresql.org/message-id/flat/4123BF8C.5000909%40pse-consulting.de
>
> There's no real discussion there of the check for plain-file-ness.
> My first guess would have been that the idea was to guard against
> symlink attacks; but then surely the stat call needed to have been
> changed to lstat? So I'm not quite sure of the reasoning. Perhaps
> Andrew remembers.
That was written 13 years ago. I'm afraid my memory isn't that good.
>
>> If it matters,
>> I'm trying to use that so I can pass a decrypted pgpassfile into postgres
>> since my company is not allowed to have unencrypted credentials on disk
>> (yes, I know that it's kind of silly to add one layer of abstraction, but
>> it's an industry rule we can't avoid).
> I cannot get excited about that proposed use-case, though. How is a pipe
> any more secure than a plain file with the same permissions?
If it's not allowed to reside on disk, put it on a RAM disk?
>
> My thought is that you shouldn't be depending on passwords at all, but
> on SSL credentials or Kerberos auth, both of which libpq supports fine.
>
Yeah, we need to be convincing people with high security needs to get
out of the password game. It's a losing battle.
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 10/19/2017 09:20 AM, Desidero wrote: > I agree that it would be better for us to use something other than > LDAP, but unfortunately it's difficult to convince the powers that be > that we can/should use something else that they are not yet prepared > to properly manage/audit. We are working towards it, but we're not > there yet. It's not really an exuse, but until the industry password > policies are modified to outright ban passwords, many businesses will > probably be in this position. > > In any event, is the use case problematic enough that it would prevent > the proposed changes from being implemented? I could submit a patch to > postgres hackers if necessary, but if it's undesirable I can figure > out something else. > Please don't top-post on the PostgreSQL lists. You said you wanted to allow anonymous pipes, but I think what you really want is a named pipe. I don't see any reason in principle to disallow use of a named pipe as a password file. It could be a bit of a footgun, though, since writing to the fifo would block until it was opened by the client, so you'd need to be very careful about that. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Desidero wrote: > When attempting to use something like an anonymous pipe for a > passfile, psql throws an error stating that it only accepts plain files So the script doing that has access to the password(s) in clear text. Can't it instead push the password into the PGPASSWORD environment variable, avoiding creating .pgpass in any form? Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
"Daniel Verite" <daniel@manitou-mail.org> writes:
> Desidero wrote:
>> When attempting to use something like an anonymous pipe for a
>> passfile, psql throws an error stating that it only accepts plain files
> So the script doing that has access to the password(s) in clear text.
> Can't it instead push the password into the PGPASSWORD
> environment variable, avoiding creating .pgpass in any form?
On many platforms, it's possible for other users to see the environment
variables of a process. So PGPASSWORD is really quite insecure.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Tom Lane wrote: > On many platforms, it's possible for other users to see the environment > variables of a process. So PGPASSWORD is really quite insecure. As said in https://www.postgresql.org/docs/current/static/libpq-envars.html "PGPASSWORD behaves the same as the password connection parameter. Use of this environment variable is not recommended forsecurity reasons, as some operating systems allow non-root users to see process environment variables via ps; insteadconsider using a password file" I understand this in the context that PostgreSQL runs on many operating systems, including ancient ones. But in the case that the target platform is not afflicted by "the environment is public" problem, what's best between PGPASSWORD and .pgpass is a judgment call. Personally I'm unimpressed by the recommendation above seemingly favoring the latter, as if it hadn't its own problems. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Matt, * Desidero (desidero@gmail.com) wrote: > I agree that it would be better for us to use something other than LDAP, If you happen to be using Active Directory, then you should really be using Kerberos-based auth instead. AD includes both LDAP and a KDC and the LDAP half is really *not* the way to handle authentication in that environment (or, well, really, LDAP isn't a terribly secure option in any environment, but if it's all you've got and you're not allowed to change then I suppose there's not much to be done about it). Thanks! Stephen
On Thu, Oct 19, 2017 at 08:22:18AM -0400, Andrew Dunstan wrote: > > > On 10/19/2017 02:12 AM, Tom Lane wrote: > > Desidero <desidero@gmail.com> writes: > >> I’m running into problems with the restriction on pgpass file types. When > >> attempting to use something like an anonymous pipe for a passfile, psql > >> throws an error stating that it only accepts plain files. > >> ... > >> Does anyone know why it’s set up to avoid using things like anonymous pipes > >> (or anything but "plain files")? > > A bit of digging in the git history says that the check was added here: > > > > commit 453d74b99c9ba6e5e75d214b0d7bec13553ded89 > > Author: Bruce Momjian <bruce@momjian.us> > > Date: Fri Jun 10 03:02:30 2005 +0000 > > > > Add the "PGPASSFILE" environment variable to specify to the password > > file. > > > > Andrew Dunstan > > > > and poking around in the mailing list archives from that time finds > > what seems to be the originating thread: > > > > https://www.postgresql.org/message-id/flat/4123BF8C.5000909%40pse-consulting.de > > > > There's no real discussion there of the check for plain-file-ness. > > My first guess would have been that the idea was to guard against > > symlink attacks; but then surely the stat call needed to have been > > changed to lstat? So I'm not quite sure of the reasoning. Perhaps > > Andrew remembers. > > > > That was written 13 years ago. I'm afraid my memory isn't that good. I am coming in late here, but the thread does say: https://www.postgresql.org/message-id/200506100302.j5A32aj12016%40candle.pha.pa.us Another new addition is that we now will check to see that the password file is a regular file and not a symlink or something. This was part of your patch for PGPASSFILE but I extended it to ~/.pgpass too. Seems the stat, and not lstat, usage is a bug. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +