Thread: Securing .pgpass File?
Hey, So, I've searched around through the archives, and it seems this has come up a couple times in the past. But one scenario that was never explored was when using one .pgpass file in a cluster of servers, in which case it makes sense to save it in source control, or something like puppet/bcfg. So my question is this: Has anyone come up with a good solution for distributing a .pgpass file that doesn't expose it to anyone who has access to the distribution mechanism? I ask because several people can access and make pull requests to our configuration management system, but except for .pgpass, none of these files contain plain-text passwords. We have dozens of systems running PostgreSQL, and manually setting up each one is a waste of time; we have configuration management for a reason. Am I just missing something, here? Thanks, everyone! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
You should never put your passwords (or private keys) in source control; it would be better to use the puppet/bcfg option. Generally you want to keep your sensitive and less sensitive info separate. If you have passwords in version control and later want to delete them, you have to mess with your version control history, and backups, and so on. You really don't want to compromise the quality of your version control. And if you ever put passwords in version control, you should change those passwords, so the copies in version control can be left alone, but no attacker can use them (assuming those aren't human-chosen and provide clues to discerning how they choose other newer passwords). -- Darren Duncan Shaun Thomas wrote: > Hey, > > So, I've searched around through the archives, and it seems this has > come up a couple times in the past. But one scenario that was never > explored was when using one .pgpass file in a cluster of servers, in > which case it makes sense to save it in source control, or something > like puppet/bcfg. So my question is this: > > Has anyone come up with a good solution for distributing a .pgpass file > that doesn't expose it to anyone who has access to the distribution > mechanism? > > I ask because several people can access and make pull requests to our > configuration management system, but except for .pgpass, none of these > files contain plain-text passwords. We have dozens of systems running > PostgreSQL, and manually setting up each one is a waste of time; we have > configuration management for a reason. > > Am I just missing something, here? > > Thanks, everyone! >
On 10/01/2012 12:19 PM, Darren Duncan wrote: > You should never put your passwords (or private keys) in source control; > it would be better to use the puppet/bcfg option. That was kind of my point. Puppet / Bcfg2 have the same problem. About a dozen people have access to our bcfg2 repo than I would want to know the contents of .pgpass. We have twenty machines. If I ever change that file, I have to change it in 20 places. I'd love to put it in bcfg2, but that necessitates allowing anyone with access to bcfg2 the ability to read it. No go. You basically just reiterated my question back to me. ;) I'd like to *stop* manually copying the files around, but can't because they're completely plain text. It doesn't matter if it's source control, puppet, bcfg2, cfengine, or anything else; unauthorized people can read them, and I rather they didn't. Encrypted passwords would be nice, but apparently this isn't an option. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
On Oct 1, 2012, at 10:26 AM, Shaun Thomas <sthomas@optionshouse.com> wrote: > On 10/01/2012 12:19 PM, Darren Duncan wrote: > >> You should never put your passwords (or private keys) in source control; >> it would be better to use the puppet/bcfg option. > > That was kind of my point. Puppet / Bcfg2 have the same problem. About a dozen people have access to our bcfg2 repo thanI would want to know the contents of .pgpass. > > We have twenty machines. If I ever change that file, I have to change it in 20 places. I'd love to put it in bcfg2, butthat necessitates allowing anyone with access to bcfg2 the ability to read it. No go. > > You basically just reiterated my question back to me. ;) I'd like to *stop* manually copying the files around, but can'tbecause they're completely plain text. It doesn't matter if it's source control, puppet, bcfg2, cfengine, or anythingelse; unauthorized people can read them, and I rather they didn't. > > Encrypted passwords would be nice, but apparently this isn't an option. If the passwords were encrypted, you'd also need to distribute the password to decrypt the password. You could obfuscate the passwords (with something that's somewhat equivalent to rot13) which would help with shoulder surfing,but you'd still be distributing a secret that's equivalent to a password. That's something you could do without anysupport from postgresql though - just deobfuscate as part of the distribution process. Authentication that isn't based on a secret token would be one way to sidestep the issue - source IP based, for instance. Cheers, Steve
On Mon, Oct 1, 2012 at 10:26 AM, Shaun Thomas <sthomas@optionshouse.com> wrote: > On 10/01/2012 12:19 PM, Darren Duncan wrote: > >> You should never put your passwords (or private keys) in source control; >> it would be better to use the puppet/bcfg option. > > > That was kind of my point. Puppet / Bcfg2 have the same problem. About a > dozen people have access to our bcfg2 repo than I would want to know the > contents of .pgpass. > > We have twenty machines. If I ever change that file, I have to change it in > 20 places. I'd love to put it in bcfg2, but that necessitates allowing > anyone with access to bcfg2 the ability to read it. No go. Who are those people? Do they have administrative access to the 20 machines? If so, it seems to me that the game is already over. If not, what mechanism do you use to keep them out? Perhaps that mechanism could be extended to cover this case as well; or use host-based authentication on the PG server. > You basically just reiterated my question back to me. ;) I'd like to *stop* > manually copying the files around, but can't because they're completely > plain text. It doesn't matter if it's source control, puppet, bcfg2, > cfengine, or anything else; unauthorized people can read them, and I rather > they didn't. I'm not familiar with those tools, at least not at an administrative level. Don't they allow tiered access so that some things can have stricter access controls? > Encrypted passwords would be nice, but apparently this isn't an option. I don't see how that can work. It sounds like an infinite regress. How do you distribute the key without exposing it? Cheers, Jeff
On 10/01/2012 02:05 PM, Jeff Janes wrote: > Who are those people? Do they have administrative access to the 20 > machines? If so, it seems to me that the game is already over. If > not, what mechanism do you use to keep them out? Perhaps that > mechanism could be extended to cover this case as well; or use > host-based authentication on the PG server. Anyone from a NoC guy, DBA, or systems admin. In the case of the systems admins, yeah, they have root and could see everything anyway. But someone just administering Nagios settings would get access to .pgpass if we put it into bcfg2. Yuck. :) Host-based works if you want everything to work under the super-user admin user. That's not necessarily true for some one-off utility that should have a better sandbox around. Creating a whole user on the box to run one or two tools seems a bit silly as a work-around. It doesn't solve connections that *must* be remote, such as replication, or third-party tools which connect over TCP like Bucardo, or Slony, either. Each of these have a specialized user on our systems, and use .pgpass to avoid unnecessary password proliferation. > I'm not familiar with those tools, at least not at an administrative > level. Don't they allow tiered access so that some things can have > stricter access controls? No. :( I can't remember about Puppet since I haven't used it in so long, but bcfg2 is basically just a giant directory structure, and we put ours in GIT for safekeeping and to track changes. Implementing ACLs in GIT is a bit of a PITA, so we're avoiding that as a last resort. > I don't see how that can work. It sounds like an infinite regress. > How do you distribute the key without exposing it? No idea. That's why I asked. ;) I figured we can't be the only company out there with a bunch of servers who is tired of manually copying a .pgpass file everywhere, and someone has since devised something workable. It doesn't hurt to ask before I spend a bunch of time building something. I try to avoid NIH syndrome when possible. I'm fine with that answer, but I had to at least broach the subject. Thanks, Jeff. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
On Monday, October 01, 2012 03:10:43 PM Shaun Thomas wrote: > I can't remember about Puppet since I haven't used it in so long, but > bcfg2 is basically just a giant directory structure, and we put ours in > GIT for safekeeping and to track changes. Implementing ACLs in GIT is a > bit of a PITA, so we're avoiding that as a last resort. You could maybe put the pgpass stuff in a separate repo with different permissions and set it up as as submodule. Haven't tried it.
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > Has anyone come up with a good solution for distributing a .pgpass file > that doesn't expose it to anyone who has access to the distribution > mechanism? No, you cannot easily keep it in version control/puppet securely. One way is to have an external script that does the jobs of puppet, e.g. for $server in @list do cp pgpass $server/... Alternatively, use gpg to encrypt the pgpass file, then put *that* into version control and distribute it. Then have a script on the server that decrypts it into place. Yes, you have to manually distribute the encryption key to the servers, but it is a one-time event, and you can push out changes to the pgpass file easily, and automate the decrypt-on-the-server bit, including by puppet itself. It's not clear what the exact threat model is here, but you could also simply not use pgpass, and find some other means to authenticate. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201210011859 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlBqIOsACgkQvJuQZxSWSshUhgCgtRGVCRLs9F+KPu2RR+rmOVeq 7T8An1ZPdvlEkciRuLiioi2LbSJUTl2f =GEi7 -----END PGP SIGNATURE-----
On 2012-10-01, Shaun Thomas <sthomas@optionshouse.com> wrote: > On 10/01/2012 12:19 PM, Darren Duncan wrote: > >> You should never put your passwords (or private keys) in source control; >> it would be better to use the puppet/bcfg option. > > That was kind of my point. Puppet / Bcfg2 have the same problem. About a > dozen people have access to our bcfg2 repo than I would want to know the > contents of .pgpass. > Ccould you put a script or binary containing an encrypted .pgpass in the repository instead? would that solve the problem? -- ⚂⚃ 100% natural
On Mon, Oct 01, 2012 at 11:02:39PM -0000, Greg Sabino Mullane wrote: > > Has anyone come up with a good solution for distributing a .pgpass file > > that doesn't expose it to anyone who has access to the distribution > > mechanism? > > No, you cannot easily keep it in version control/puppet securely. In my experience we've handled this in a few ways: - Postgres has ident auth, which avoids the use of password for local accounts, which helps in a lot of cases. - Puppet has the concept of an "external node classifier". This is a script which gets called with the name of the node, and can return data which is then available to the puppet run. So the password is stored elsewhere (in our case Foreman) which means you only need to worry about the people who can access the Foreman server or the puppet server. Note people who can check arbitrary things into the repo and view the puppet output would be able to get the password by printing it to the debug log. - Punt. Check in the password but set the access controls so it only work for very few IPs, then you only need to worry about people who can log into *those* machines. Which is controlled by public SSH keys which you can check-in safely. Not super safe, but for read-only accounts for e.g. nagios might be ok. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
Attachment
On 10/02/2012 04:19 PM, Martijn van Oosterhout wrote: > - Punt. Check in the password but set the access controls so it only > work for very few IPs, then you only need to worry about people who > can log into *those* machines. Which is controlled by public SSH > keys which you can check-in safely. Not super safe, but for > read-only accounts for e.g. nagios might be ok. I think this is the right answer for us. :) I'll just go through each specialized user we have now (replication, performance metrics, slony) and make IP->IP locked-down cases for all of them. It just feels wrong, somehow. :) Thanks again, everyone! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email