Обсуждение: Password issue revisited
Fellow PostgreSQL fans,
Last year there was a pretty lengthy discussion (Tom Lane offered a lot of insights) on this list about deprecating the PGPASSWORD environmental variable. I understand the security issues here very well. However, up through version 8.1, it has been easy to use pg_dump and pg_restore from other applications (PHP, Java, etc.) by capturing the Password prompt on stderr and sending the password on stdin. No more. Now, this interaction is done on low-level I/O data streams. Also, it appears from the documentation that the PGPASSFILE environmental variable has been deprecated for pg_dump and pg_restore. It appears the only way these utilities can run from a script or other application is to ensure that the user specified in the command-line has a .pgpass file.
I would like to ask that we return to outputting the Password prompt on stderr and accepting password input on stdin. Here are the reasons.
1. I don't see that this would pose a major security risk. In fact, in applications where the user enters the password for each session, the password need never be saved to disk, which seems a definite security advantage. Some folks have noted that .pgpass is a plain text file, hence it could be vulnerable.
2. PostgreSQL has a tradition of respecting generally accepted standards. The use of high-level input/output is a standard for many programming languages.
3. PostgreSQL has a tradition of cross-platform compatibility. Use of high-level input/output allows cross-platform applications (e.g., Java) to interact with PostgreSQL in a straightforward and standardized fashion.
4. Low level input/output is considerably more difficult and less reliable for other applications to access and work with.
Thanks for considering this matter.
Michael Schmidt
Michael Schmidt wrote: > Fellow PostgreSQL fans, Last year there was a pretty lengthy discussion > (Tom Lane offered a lot of insights) on this list about deprecating > the PGPASSWORD environmental variable. I understand the security issues > here very well. However, up through version 8.1, it has been easy to > use pg_dump and pg_restore from other applications (PHP, Java, etc.) > by capturing the Password prompt on stderr and sending the password on > stdin. No more. Now, this interaction is done on low-level I/O data > streams. Also, it appears from the documentation that the PGPASSFILE > environmental variable has been deprecated for pg_dump and pg_restore. > It appears the only way these utilities can run from a script or other > application is to ensure that the user specified in the command-line > has a .pgpass file. > > I would like to ask that we return to outputting the Password prompt > on stderr and accepting password input on stdin. Here are the reasons. Unlikely. > 1. I don't see that this would pose a major security risk. In fact, > in applications where the user enters the password for each session, > the password need never be saved to disk, which seems a definite > security advantage. Some folks have noted that .pgpass is a plain text > file, hence it could be vulnerable. This doesn't work if the command actually is taking data from stdin. > 2. PostgreSQL has a tradition of respecting generally accepted > standards. The use of high-level input/output is a standard for many > programming languages. It is _not_ typical to read a password from stdin. I am sure you can find some tools that do this, but most do not. > 3. PostgreSQL has a tradition of cross-platform compatibility. Use > of high-level input/output allows cross-platform applications (e.g., > Java) to interact with PostgreSQL in a straightforward and standardized > fashion. > 4. Low level input/output is considerably more difficult and less > reliable for other applications to access and work with. The change is to read from /dev/tty. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
"Michael Schmidt" <michaelmschmidt@msn.com> writes:
> Also, it appears
> from the documentation that the PGPASSFILE environmental variable has
> been deprecated for pg_dump and pg_restore.
Eh? Certainly not ... where did you get that idea?
> I would like to ask that we return to outputting the Password prompt on
> stderr and accepting password input on stdin.
I think it's unlikely we will revert that, as it's been a done deal for
several releases now (since 7.4 in fact).
regards, tom lane
Tom Lane wrote: > "Michael Schmidt" <michaelmschmidt@msn.com> writes: > > Also, it appears > > from the documentation that the PGPASSFILE environmental variable has > > been deprecated for pg_dump and pg_restore. > > Eh? Certainly not ... where did you get that idea? I assumed he meant the PASSWORD environment variable. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Michael Schmidt wrote: > Fellow PostgreSQL fans, > 1. I don't see that this would pose a major security risk. In > fact, in applications where the user enters the password for each > session, the password need never be saved to disk, which seems a > definite security advantage. Some folks have noted that .pgpass is > a plain text file, hence it could be vulnerable. Yes it is a plain text file but if you want to use it then you need to ensure the security is sufficient on the file or it won't be used. As per the manual - > The permissions on .pgpass must disallow any access to world or group; > achieve this by the command chmod 0600 ~/.pgpass. If the permissions > are less strict than this, the file will be ignored. (The file > permissions are not currently checked on Microsoft Windows, however.) So this security feature should be something that gets added to the windows version. But otherwise the security of the user's account that has a .pgpass file is the decider on whether it is vulnerable. -- Shane Ambler pgSQL@007Marketing.com Get Sheeky @ http://Sheeky.Biz
Mr. Lane and Mr. Momjian,
Well, I asked and I got an answer. So be it. Regarding how I concluded that PGPASSFILE was deprecated for pg_dump, I offer the following.
1. The documentation for pg_dump in the manual (Section VI) includes a section labeled "Environment". This lists PGDATABASE, PGHOST, PGPORT, and PGUSER. It also says "default connection parameters" but there is no hyperlink or reference to another manual section to explain/define this term.
2. Neither the Index nor the Table of Contents has an entry for "connection parameters" or "default connection parameters".
3. The application help (pg_dump --help) provides no further information.
4. The pg_dump documentation in Section VI has a "See Also" section, with a link to 29.12. In 29.12 there is a list of variables that "can be used to select default connection parameters" for PQconnectdb, PQsetdbLogin, and PQsetdb (no mention of pg_dump). The list includes those mentioned in the pg_dump documentation () see 1 above) along with several others (including PGPASSFILE). I was left wondering why would some of these be mentioned in the pg_dump section and not the others? Perhaps those not mentioned can't be used by pg_dump?
Given the above ambiguities, I was not able to conclude that pg_dump would accept the PGPASSFILE environment variable.
Michael Schmidt
"Michael Schmidt" <michaelmschmidt@msn.com> writes:
> ... Regarding how I concluded
> that PGPASSFILE was deprecated for pg_dump, I offer the following.
> 1. The documentation for pg_dump in the manual (Section VI) includes a
> section labeled "Environment". This lists PGDATABASE, PGHOST, PGPORT,
> and PGUSER. It also says "default connection parameters" but there is
> no hyperlink or reference to another manual section to explain/define
> this term.
Yeah. There is a link down in "See Also" but the incomplete
"Environment" section of these man pages seems misleading.
Rather than try to maintain complete lists in each of the
client-application man pages, I propose we remove those sections
completely, and just rely on the See Also links to section 29.12.
regards, tom lane
Added to TODO for Win32:
o Check .pgpass file permissions
---------------------------------------------------------------------------
Shane Ambler wrote:
> Michael Schmidt wrote:
> > Fellow PostgreSQL fans,
>
> > 1. I don't see that this would pose a major security risk. In
> > fact, in applications where the user enters the password for each
> > session, the password need never be saved to disk, which seems a
> > definite security advantage. Some folks have noted that .pgpass is
> > a plain text file, hence it could be vulnerable.
>
> Yes it is a plain text file but if you want to use it then you need to
> ensure the security is sufficient on the file or it won't be used.
>
> As per the manual -
>
> > The permissions on .pgpass must disallow any access to world or
> group; > achieve this by the command chmod 0600 ~/.pgpass. If the
> permissions
> > are less strict than this, the file will be ignored. (The file
> > permissions are not currently checked on Microsoft Windows, however.)
>
>
> So this security feature should be something that gets added to the
> windows version. But otherwise the security of the user's account that
> has a .pgpass file is the decider on whether it is vulnerable.
>
>
> --
>
> Shane Ambler
> pgSQL@007Marketing.com
>
> Get Sheeky @ http://Sheeky.Biz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Are we sure we want to do this? (Sorry, didn't notice this thread last time) The default on *all* windows versions since NT 4.0 (which is when the directory we use was added) will put this file in a protected directory. The only case when it's not protected by default is if you're usnig FAT filesystem, in which case there is nothing you can do about it anyway. On unix, the file will often be created in outside-readable mode by default, depending on how your OS is set up. So to reach a situation where the file lives in an unprotected directory, you must actively open up the directory in question. Which is hidden from default view, so you really need to know what you're doing to get there. Not to mention it's a pain to define what permissions are ok and what are not. We're talking ACLs and not filemodes - so how do you decide which accounts are ok to have access, and which are not? //Magnus On Tue, Feb 20, 2007 at 09:49:00AM -0500, Bruce Momjian wrote: > > Added to TODO for Win32: > > o Check .pgpass file permissions > > --------------------------------------------------------------------------- > > Shane Ambler wrote: > > Michael Schmidt wrote: > > > Fellow PostgreSQL fans, > > > > > 1. I don't see that this would pose a major security risk. In > > > fact, in applications where the user enters the password for each > > > session, the password need never be saved to disk, which seems a > > > definite security advantage. Some folks have noted that .pgpass is > > > a plain text file, hence it could be vulnerable. > > > > Yes it is a plain text file but if you want to use it then you need to > > ensure the security is sufficient on the file or it won't be used. > > > > As per the manual - > > > > > The permissions on .pgpass must disallow any access to world or > > group; > achieve this by the command chmod 0600 ~/.pgpass. If the > > permissions > > > are less strict than this, the file will be ignored. (The file > > > permissions are not currently checked on Microsoft Windows, however.) > > > > > > So this security feature should be something that gets added to the > > windows version. But otherwise the security of the user's account that > > has a .pgpass file is the decider on whether it is vulnerable.
Magnus Hagander wrote: > Are we sure we want to do this? (Sorry, didn't notice this thread last > time) > > The default on *all* windows versions since NT 4.0 (which is when the > directory we use was added) will put this file in a protected directory. > The only case when it's not protected by default is if you're usnig FAT > filesystem, in which case there is nothing you can do about it anyway. > On unix, the file will often be created in outside-readable mode by > default, depending on how your OS is set up. > > So to reach a situation where the file lives in an unprotected > directory, you must actively open up the directory in question. Which is > hidden from default view, so you really need to know what you're doing to > get there. > > Not to mention it's a pain to define what permissions are ok and what > are not. We're talking ACLs and not filemodes - so how do you decide > which accounts are ok to have access, and which are not? OK, I added a comment to fe-connect.c explaining why we don't need to check the permissions of .pgpass, and removed the TODO. Thanks. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Magnus Hagander wrote: >> Are we sure we want to do this? (Sorry, didn't notice this thread last >> time) >> >> The default on *all* windows versions since NT 4.0 (which is when the >> directory we use was added) will put this file in a protected directory. >> The only case when it's not protected by default is if you're usnig FAT >> filesystem, in which case there is nothing you can do about it anyway. >> On unix, the file will often be created in outside-readable mode by >> default, depending on how your OS is set up. I believe that .pgpass on *nix won't be used if it is readable by anyone except the current user. From the docs - The permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. (The file permissions are not currently checked on Microsoft Windows, however.) I would think that if they are using FAT filesystem (which is only partially supported for developers benefit) then they can't use pgpass. >> So to reach a situation where the file lives in an unprotected >> directory, you must actively open up the directory in question. Which is >> hidden from default view, so you really need to know what you're doing to >> get there. >> >> Not to mention it's a pain to define what permissions are ok and what >> are not. We're talking ACLs and not filemodes - so how do you decide >> which accounts are ok to have access, and which are not? I would say the same as the *nix version - if it is readable or writable by anyone except the current user it is potentially at risk, the current user connecting to pgsql is the only use for this file. Which I believe is the whole point of the TODO entry, stop anyone using the pgpass file without proper security. The other thing to consider is that pgpass is the file referenced by PGPASSFILE - the user can set this to point to a file anywhere on any drive available. It is users who only think they know what they are doing that create and modify it by hand and then kick up a fuss when it causes trouble. If we want the windows clients to be used then I do think that the security decisions should not be dropped for windows clients. > OK, I added a comment to fe-connect.c explaining why we don't need to > check the permissions of .pgpass, and removed the TODO. Thanks. > -- Shane Ambler pgSQL@Sheeky.Biz Get Sheeky @ http://Sheeky.Biz
>>> The default on *all* windows versions since NT 4.0 (which is when the >>> directory we use was added) will put this file in a protected directory. >>> The only case when it's not protected by default is if you're usnig FAT >>> filesystem, in which case there is nothing you can do about it anyway. >>> On unix, the file will often be created in outside-readable mode by >>> default, depending on how your OS is set up. > > I believe that .pgpass on *nix won't be used if it is readable by anyone > except the current user. No, root can always read it. On unix, there is one "root". On windows, the concept of administrator is less clear. > From the docs - > The permissions on .pgpass must disallow any access to world or group; > achieve this by the command chmod 0600 ~/.pgpass. If the permissions are > less strict than this, the file will be ignored. (The file permissions > are not currently checked on Microsoft Windows, however.) > > I would think that if they are using FAT filesystem (which is only > partially supported for developers benefit) then they can't use pgpass. If they are using FAT, the obviously don't care about the security of the system anyway, so it's not a problem, IMHO. So we only have to care about people who use NTFS. >>> So to reach a situation where the file lives in an unprotected >>> directory, you must actively open up the directory in question. Which is >>> hidden from default view, so you really need to know what you're >>> doing to >>> get there. >>> >>> Not to mention it's a pain to define what permissions are ok and what >>> are not. We're talking ACLs and not filemodes - so how do you decide >>> which accounts are ok to have access, and which are not? > > I would say the same as the *nix version - if it is readable or writable > by anyone except the current user it is potentially at risk, the current > user connecting to pgsql is the only use for this file. > Which I believe is the whole point of the TODO entry, stop anyone using > the pgpass file without proper security. Again, it's a lot harder to actually define it on Windows. What if your user has access only through a group? What about DENY permissions. Things like that. > The other thing to consider is that pgpass is the file referenced by > PGPASSFILE - the user can set this to point to a file anywhere on any > drive available. That's a very valid point though, didn't think about that. Still doesn't take away the "how" part, though, but it does take away part of the "why" part. //Magnus
Tom Lane wrote: > "Michael Schmidt" <michaelmschmidt@msn.com> writes: > > ... Regarding how I concluded > > that PGPASSFILE was deprecated for pg_dump, I offer the following. > > > 1. The documentation for pg_dump in the manual (Section VI) includes a > > section labeled "Environment". This lists PGDATABASE, PGHOST, PGPORT, > > and PGUSER. It also says "default connection parameters" but there is > > no hyperlink or reference to another manual section to explain/define > > this term. > > Yeah. There is a link down in "See Also" but the incomplete > "Environment" section of these man pages seems misleading. > > Rather than try to maintain complete lists in each of the > client-application man pages, I propose we remove those sections > completely, and just rely on the See Also links to section 29.12. I think we can conclude that adding libpq in the "See Also" section of the documentation isn't sufficient. I have removed that mention, and added this text to the bottom of the "Environment" section for each utility: + This utility, like most other <productname>PostgreSQL</> utilities, + also uses the environment variables supported by <xref + linkend="libpq-envars" endterm="libpq">. I have backpatched this to 8.2.X. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Magnus Hagander wrote: > Are we sure we want to do this? (Sorry, didn't notice this thread last > time) > > The default on *all* windows versions since NT 4.0 (which is when the > directory we use was added) will put this file in a protected directory. > Is there truly such a thing on a windows PC? All it takes is one Virus or Malware to gain access to the PC and anything stored in the user profile is easy picking. The virus and malware creators may not know about the pg_pass file now, but they will eventually. What about having a wallet type system where the user can create a pass phrase to protect a generated key that would get loaded once per session. That is how KDE allows users to store passwords. I work at a large financial institution and if the auditors knew about the pg_pass being plain text, they would pretty much ban it's use. Anytime a password is sitting on a non encrypted file system, regardless of it's permissions it is potentially at risk. -- Tony
Tony Caduto wrote: > Magnus Hagander wrote: >> Are we sure we want to do this? (Sorry, didn't notice this thread last >> time) >> >> The default on *all* windows versions since NT 4.0 (which is when the >> directory we use was added) will put this file in a protected directory. >> > Is there truly such a thing on a windows PC? All it takes is one Virus > or Malware to gain access to the PC and anything stored in the > user profile is easy picking. > The virus and malware creators may not know about the pg_pass file now, > but they will eventually. > What about having a wallet type system where the user can create a pass > phrase to protect a generated key that would get > loaded once per session. That is how KDE allows users to store passwords. > > I work at a large financial institution and if the auditors knew about > the pg_pass being plain text, they would pretty much ban > it's use. > > Anytime a password is sitting on a non encrypted file system, regardless > of it's permissions it is potentially at risk. If we wanted to do that, we could use the Windows API that's available to do this. The idea with the pgpass flie is to have it compatible with the unix version. //Magnus
Magnus Hagander <magnus@hagander.net> writes:
> Tony Caduto wrote:
>> What about having a wallet type system where the user can create a pass
>> phrase to protect a generated key that would get
>> loaded once per session. That is how KDE allows users to store passwords.
> If we wanted to do that, we could use the Windows API that's available
> to do this. The idea with the pgpass flie is to have it compatible with
> the unix version.
More to the point, that's far outside the scope of this project. Use a
PAM auth module that you like, or Kerberos or whatever. I'm way past
tired of "let's put yet another authentication technology in libpq" requests.
regards, tom lane
Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: >> Tony Caduto wrote: >>> What about having a wallet type system where the user can create a pass >>> phrase to protect a generated key that would get >>> loaded once per session. That is how KDE allows users to store passwords. > >> If we wanted to do that, we could use the Windows API that's available >> to do this. The idea with the pgpass flie is to have it compatible with >> the unix version. > > More to the point, that's far outside the scope of this project. Use a > PAM auth module that you like, or Kerberos or whatever. I'm way past > tired of "let's put yet another authentication technology in libpq" requests. Just to make things clear, this wouldn't be about another auth method. Windows has an API to store arbitrary passwords in a "secure way". At least it does in XP+, not sure if it was in 2000. Not saying it's a good idea, but it's not another auth tech for libpq. //Magnus
Magnus Hagander wrote: > Just to make things clear, this wouldn't be about another auth method. > Windows has an API to store arbitrary passwords in a "secure way". At > least it does in XP+, not sure if it was in 2000. Would it really solve Tony's problem though? I'm not familiar with the API you're thinking of, but do be useful to us it must be able to give the unencrypted passwords back to us, and therefore anything else pretending to be us. Regards, Dave.
Dave Page wrote: > Magnus Hagander wrote: > >> Just to make things clear, this wouldn't be about another auth method. >> Windows has an API to store arbitrary passwords in a "secure way". At >> least it does in XP+, not sure if it was in 2000. > > Would it really solve Tony's problem though? I'm not familiar with the > API you're thinking of, but do be useful to us it must be able to give > the unencrypted passwords back to us, and therefore anything else > pretending to be us. yeah, but it pops up a GUI notification for you. It's what IE uses to store things like passports. It's also used, IIRC, by the new RDP client that's available, and a few more. Did a quick check, and it's XP/2003 only. See http://msdn2.microsoft.com/en-us/library/aa302353.aspx. //Magnus
Magnus Hagander wrote: > Dave Page wrote: >> Magnus Hagander wrote: >> >>> Just to make things clear, this wouldn't be about another auth method. >>> Windows has an API to store arbitrary passwords in a "secure way". At >>> least it does in XP+, not sure if it was in 2000. >> Would it really solve Tony's problem though? I'm not familiar with the >> API you're thinking of, but do be useful to us it must be able to give >> the unencrypted passwords back to us, and therefore anything else >> pretending to be us. > > yeah, but it pops up a GUI notification for you. It's what IE uses to > store things like passports. It's also used, IIRC, by the new RDP client > that's available, and a few more. > Did a quick check, and it's XP/2003 only. See > http://msdn2.microsoft.com/en-us/library/aa302353.aspx. That would break all the non-interactive apps that we recommend using pgpass with to prevent storing passwords in even less secure places. Regards, Dave.
I assume this is not a TODO. --------------------------------------------------------------------------- Magnus Hagander wrote: > >>> The default on *all* windows versions since NT 4.0 (which is when the > >>> directory we use was added) will put this file in a protected directory. > >>> The only case when it's not protected by default is if you're usnig FAT > >>> filesystem, in which case there is nothing you can do about it anyway. > >>> On unix, the file will often be created in outside-readable mode by > >>> default, depending on how your OS is set up. > > > > I believe that .pgpass on *nix won't be used if it is readable by anyone > > except the current user. > > No, root can always read it. On unix, there is one "root". On windows, > the concept of administrator is less clear. > > > > From the docs - > > The permissions on .pgpass must disallow any access to world or group; > > achieve this by the command chmod 0600 ~/.pgpass. If the permissions are > > less strict than this, the file will be ignored. (The file permissions > > are not currently checked on Microsoft Windows, however.) > > > > I would think that if they are using FAT filesystem (which is only > > partially supported for developers benefit) then they can't use pgpass. > > If they are using FAT, the obviously don't care about the security of > the system anyway, so it's not a problem, IMHO. So we only have to care > about people who use NTFS. > > > >>> So to reach a situation where the file lives in an unprotected > >>> directory, you must actively open up the directory in question. Which is > >>> hidden from default view, so you really need to know what you're > >>> doing to > >>> get there. > >>> > >>> Not to mention it's a pain to define what permissions are ok and what > >>> are not. We're talking ACLs and not filemodes - so how do you decide > >>> which accounts are ok to have access, and which are not? > > > > I would say the same as the *nix version - if it is readable or writable > > by anyone except the current user it is potentially at risk, the current > > user connecting to pgsql is the only use for this file. > > Which I believe is the whole point of the TODO entry, stop anyone using > > the pgpass file without proper security. > > Again, it's a lot harder to actually define it on Windows. What if your > user has access only through a group? What about DENY permissions. > Things like that. > > > > The other thing to consider is that pgpass is the file referenced by > > PGPASSFILE - the user can set this to point to a file anywhere on any > > drive available. > > That's a very valid point though, didn't think about that. > > Still doesn't take away the "how" part, though, but it does take away > part of the "why" part. > > //Magnus > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +