Re: Have an encrypted pgpass file
On Tue, Jul 24, 2018 at 12:25:31PM +0200, Marco van Eck wrote: > Indeed having unencrypted password lying (.pgpass or PGPASSWORD or -W) > around is making my auditors unhappy, and forcing me to enter the password > over and over again. With a simple test it seems the password entered by > the user also stays in memory, since it is able to reset a broken > connection. Finding the password in memory is not trivial, but prevention > is always preferred. Sometimes the auditors are just wrong. Say you're using Kerberos, so you put service keys in... "keytab files" -- that's just like putting a password in a file because they are equivalent. Or server certificates and their private keys... -- where are you going to put them if not in some file? Sure, you could put keys in a TPM (except they are orders of magnitude too slow) or some other hardware token that doesn't suck (except those are expensive). But now you still need unattended access to the token, and who cares what the keys _are_ when you can just _use_ them to escalate privilege anyways?? Forcing attended operation of otherwise automatic systems is not a good idea, and it is very expensive too. A quick search turns up tools for finding cryptographic keys in memory. Passwords can't be much harder. > It might be an idea to wipe the password after the login, and decrypt/read > it again if it needs to reconnect. Would this make the solution more > secure? I had a quick look at the code and the patch would stay compact. > Please let me know of doing this would make sense. But you still wanted to automate things, no? You can't protect from local hosts compromises. You have to protect the host. Please, no security theater. Do you think the day after a massive data breach at your company you can tell the press "gee, I dunno how they got the password, it was only loaded in memory!" and save it from liability? (If there's no liability either way, that's a different problem.) Nico --
Re: Have an encrypted pgpass file
On Wed, Aug 1, 2018 at 05:33:39PM +0200, Marco van Eck wrote: > After explaining the patch to a college we identified potentially execution of > another user when it is defined in as a command parameter. To protect agains > it > I've removed the possibility to pass the 'passcommand'. With the result libpq > only allows the PGPASSCOMMAND environment variable, which can only be defined > by the executing user, and will be executed by the same user. It only reduces > the need of unencrypted password's in a file. > > I think this solution is secure enough, shall we solve this feature-request? [Toshi and Nico added as CC's] I think we need to step back and understand where we are going with our various encryption options. We have this feature under consideration, and we have a proposal for data-at-rest encryption, which encrypts writes to the file system: https://www.postgresql.org/message-id/CA%2BCSw_tb3bk5i7if6inZFc3yyf%2B9HEVNTy51QFBoeUk7UE_V%3Dw%40mail.gmail.com So, until now, we have only supported encryption at the "optimal" level, e.g. encrypted file system which encrypts the storage. We have relied on file permissions to protect access to the mounted file system, and have a similar approach to securing .pgpass. So, the question is whether we continue to offer only encryption at the "optimal" level, or whether we allow encryption at other levels. There are two reasons to support non-optimal encryption levels. First, there is the issue that different threat models require different encryption levels for protection. For example, someone might have the ability to _read_ a directory, but not the ability to modify it and therefore attack it by grabbing passwords as they are typed. An encrypted .pgpass would be secure from that attack, but a .pgpass stored on an encrypted file system would not. Second, there should always be some kind of unlocking action, either with a password, a hardware encryption device, or connection to a key server. Sometimes this unlocking action only makes sense at a certain level, e.g. a personal Yubikey works for .pgpass but might be odd for file system encryption. I sympathize with concerns that the encryption key might be stored unencrypted, or stored with the key that decrypts the encryption key. However, I don't think we can assume that there are no valid uses for encryption at non-optimal levels, and that they would only be used in insecure ways. As a practical example, this presentation: http://momjian.us/main/writings/crypto_hw_use.pdf shows how you might use a Yubikey to encrypt .pgpass. The private key can't be copied out of the Yubikey, but the private key wouldn't be used to encrypt .pgpass --- instead a key would be encrypted using the Yubikey's private key. An attacker wouldn't need to read the Yubikey private key but read the .pgpass password once it is decrypted. That could be done by modifying the gpg binary, the psql binary, or the gpg-agent script, but those all require modification of something, with proper permissions and possible detection. I don't know how valuable that would be to the average user, but I am sure it is useful for some users. I think with proper documentation and warnings, we could make these non-optimal encryption levels useful for the users who have threat models where they are useful, and hopefully minimize their misuse. -- Bruce Momjian 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 +
Re: Have an encrypted pgpass file
On Thu, 2 Aug 2018 at 10:41, I wrote: > Perhaps you could make your auditors happier by restricting that user's > permissions to only run a defined function, and make that function do the > work that the automation script wants? So even if the attacker can access > the password he will still only be able to run that function? (You could > even add DOS protection into the function to ensure it's only run so often, > if you were worried about that.) > > I realise (of course, after I sent this) that I misunderstood the thrust of your requirement, and that you want the ability to log in *your own user* without entering your own password. Apologies. Ignore me. Geoff
Re: Have an encrypted pgpass file
On Tue, 24 Jul 2018 at 11:25, Marco van Eck wrote: > Indeed having unencrypted password lying (.pgpass or PGPASSWORD or -W) > around is making my auditors unhappy, > With the greatest of respect, perhaps you need to get auditors who understand crypto better. Having a user that has the minimal permissions to perform the required tasks with a stored password that only the automation user can read is perfectly valid. Encrypting it with a key that must (perforce) be accessible using the same permissions that the user would need in order to to read the unencrypted password file is no more valid (look up "security through obscurity"). Perhaps you could make your auditors happier by restricting that user's permissions to only run a defined function, and make that function do the work that the automation script wants? So even if the attacker can access the password he will still only be able to run that function? (You could even add DOS protection into the function to ensure it's only run so often, if you were worried about that.) Geoff
Re: Have an encrypted pgpass file
Hello. I have had complaints several times on lack of this kind of feature. At Wed, 1 Aug 2018 17:33:39 +0200, Marco van Eck wrote in > After explaining the patch to a college we identified potentially execution > of another user when it is defined in as a command parameter. To protect > agains it I've removed the possibility to pass the 'passcommand'. With the > result libpq only allows the PGPASSCOMMAND environment variable, which can > only be defined by the executing user, and will be executed by the same > user. It only reduces the need of unencrypted password's in a file. Myabe we don't need the new environment variable by just allowing .pgpass be a script. If we put faith in the security of .pgpass, this won't be a problem, putting aside what the script actually does. > I think this solution is secure enough, shall we solve this feature-request? > > > Regards, Marco > > On Tue, Jul 24, 2018 at 4:00 PM Tom Lane wrote: > > > Marco van Eck writes: > > > Indeed having unencrypted password lying (.pgpass or PGPASSWORD or -W) > > > around is making my auditors unhappy, and forcing me to enter the > > password > > > over and over again. With a simple test it seems the password entered by > > > the user also stays in memory, since it is able to reset a broken > > > connection. Finding the password in memory is not trivial, but prevention > > > is always preferred. > > > > > It might be an idea to wipe the password after the login, and > > decrypt/read > > > it again if it needs to reconnect. Would this make the solution more > > > secure? I had a quick look at the code and the patch would stay compact. > > > Please let me know of doing this would make sense. > > > > We're basically not going to accept any added complication that's designed > > to prevent memory-inspection attacks, because in general that's a waste > > of effort. All you're doing is (slightly) reducing the attack window. > > > > regards, tom lane regards. -- Kyotaro Horiguchi NTT Open Source Software Center
Re: Have an encrypted pgpass file
After explaining the patch to a college we identified potentially execution
of another user when it is defined in as a command parameter. To protect
agains it I've removed the possibility to pass the 'passcommand'. With the
result libpq only allows the PGPASSCOMMAND environment variable, which can
only be defined by the executing user, and will be executed by the same
user. It only reduces the need of unencrypted password's in a file.
I think this solution is secure enough, shall we solve this feature-request?
Regards, Marco
On Tue, Jul 24, 2018 at 4:00 PM Tom Lane wrote:
> Marco van Eck writes:
> > Indeed having unencrypted password lying (.pgpass or PGPASSWORD or -W)
> > around is making my auditors unhappy, and forcing me to enter the
> password
> > over and over again. With a simple test it seems the password entered by
> > the user also stays in memory, since it is able to reset a broken
> > connection. Finding the password in memory is not trivial, but prevention
> > is always preferred.
>
> > It might be an idea to wipe the password after the login, and
> decrypt/read
> > it again if it needs to reconnect. Would this make the solution more
> > secure? I had a quick look at the code and the patch would stay compact.
> > Please let me know of doing this would make sense.
>
> We're basically not going to accept any added complication that's designed
> to prevent memory-inspection attacks, because in general that's a waste
> of effort. All you're doing is (slightly) reducing the attack window.
>
> regards, tom lane
>
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index d67212b831..ae526a35c8 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -1099,6 +1099,16 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
+
+ passcommand
+
+
+ Specifies the command to run to get the contents of store passwords
+ (see ).
+
+
+
+
connect_timeout
@@ -7231,6 +7241,16 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
+
+
+
+ PGPASSCOMMAND
+
+ PGPASSCOMMAND behaves the same as the connection parameter.
+
+
+
@@ -7486,7 +7506,7 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
- The Password File
+ The Password File / Password Command
password file
@@ -7494,6 +7514,9 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
.pgpass
+
+ password command
+
The file .pgpass in a user's home directory can
@@ -7509,7 +7532,25 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
- This file should contain lines of the following format:
+ The command which produces passwords to connect if the connection needs a
+ password (and no password has been specified otherwise).
+ Alternatively, a password command can be specifiedin the environment
+ variable PGPASSCOMMAND. It is not possible to specify the
+ command as a connect parameter since it could be executed by another user.
+
+ examples:
+
+PGPASSCOMMAND = "gpg -q -d pgpass.gpg"
+PGPASSCOMMAND = "curl http://passwords/really-unsecure-pgpass;
+PGPASSCOMMAND = "my-own-secure-pgpass-script"
+
+
+ NOTE: if a .pgpass is present the command will not be
+ issued
+
+
+
+ This file/command-output should contain lines of the following format:
hostname:port:database:username:password
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index bd7dac120d..db0d5e6d81 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -204,6 +204,13 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
"Database-Password-File", "", 64,
offsetof(struct pg_conn, pgpassfile)},
+ /* PGPASSCOMMAND may only be passed as an environment variable to
+ since adding it to the connect_string could potentially allow the command
+ to run with the priviliges of the database-server */
+ {"", "PGPASSCOMMAND", NULL, NULL,
+ "Database-Password-Command", "", 64,
+ offsetof(struct pg_conn, pgpasscommand)},
+
{"connect_timeout", "PGCONNECT_TIMEOUT", NULL, NULL,
"Connect-timeout", "", 10, /* strlen(INT32_MAX) == 10 */
offsetof(struct pg_conn, connect_timeout)},
@@ -406,8 +413,8 @@ static int parseServiceFile(const char *serviceFile,
PQExpBuffer errorMessage,
bool *group_found);
static char *pwdfMatchesString(char *buf, const char *token);
-static char *passwordFromFile(const char *hostname, const char *port, const char *dbname,
- const char *username, const char *pgpassfile);
+static char *passwordFromFileOrCommand(const char *hostname, const char *port, const char *dbname,
+ const char *username, const char *pgpassfile, const char *pgpasscommand);
static void pgpassfileWarning(PGconn *conn);
static void default_threadlock(int
Re: Have an encrypted pgpass file
Marco van Eck writes: > Indeed having unencrypted password lying (.pgpass or PGPASSWORD or -W) > around is making my auditors unhappy, and forcing me to enter the password > over and over again. With a simple test it seems the password entered by > the user also stays in memory, since it is able to reset a broken > connection. Finding the password in memory is not trivial, but prevention > is always preferred. > It might be an idea to wipe the password after the login, and decrypt/read > it again if it needs to reconnect. Would this make the solution more > secure? I had a quick look at the code and the patch would stay compact. > Please let me know of doing this would make sense. We're basically not going to accept any added complication that's designed to prevent memory-inspection attacks, because in general that's a waste of effort. All you're doing is (slightly) reducing the attack window. regards, tom lane
Re: Have an encrypted pgpass file
Indeed having unencrypted password lying (.pgpass or PGPASSWORD or -W)
around is making my auditors unhappy, and forcing me to enter the password
over and over again. With a simple test it seems the password entered by
the user also stays in memory, since it is able to reset a broken
connection. Finding the password in memory is not trivial, but prevention
is always preferred.
It might be an idea to wipe the password after the login, and decrypt/read
it again if it needs to reconnect. Would this make the solution more
secure? I had a quick look at the code and the patch would stay compact.
Please let me know of doing this would make sense.
Regards, Marco
On Tue, Jul 24, 2018 at 4:56 AM Thomas Munro
wrote:
> On Tue, Jul 24, 2018 at 2:10 PM, Craig Ringer
> wrote:
> >> Grabbing it from a process's memory is a bit harder than grabbing
> contents
> >> of a file, but not much harder. If the agent is remote then that's
> harder,
> >> but you can just ask the script to decrypt the pgpass for you, so
> again, not
> >> much of a win.
> >>
> >> Even with a hardware crypto offload device the advantage here seems to
> be
> >> mainly limited to making it harder to capture data from backups or
> >> file-lifting attacks. Anything that can execute code or commands on the
> host
> >> can still get the credentials.
> >
> > To be clear I'm not saying not to do this. I think it'd make more sense
> to
> > do it via an agent and socket, where libpq learns to ask the agent for
> > credentials (and certs!). That way we could finally support libnss, etc.
> > It's not perfect, and it doesn't magically make unattended storage
> secure,
> > but it sure helps slow down file-based password theft.
>
> Yeah, the idea that you can defend yourself against root is obviously
> not a good one. But the Apple keychain example (see the commands I
> showed earlier) does seem to protect you against some threat
> scenarios: if your computer is stolen, that password can't be
> extracted from the disk. You'd need to unlock the keychain first by
> logging in. It's not 'unattended': you have to be in attendance to
> unlock the keychain.
>
> I'm deeply suspicious of unattended use cases for encrypted passwords
> though. I have heard requests for this. Encrypting it with a key
> that is present in the configuration file, derived from well known
> things, or stored in a nearby file doesn't change that. The encrypted
> secret + easily available key is still a secret you must protect
> exactly as hard. I suspect it is a misapplication of the advice that
> you should never store (other people's) passwords in your database --
> instead you should store something that allows you to check if *they*
> have the password, without storing the password itself. But that
> doesn't mean that you should throw away your own passwords: instead
> you should recognise that they are secrets, and treat them as such.
>
> An idea I wondered about: if the goal is to allow psql (not libpq) to
> use a secret from ,
> then perhaps psql could have a machine-friendly
> --read-password-from-stdin feature for use by wrapper scripts (it
> seems to be hard to send passwords to -W, though maybe I'm just doing
> something stupid). Or if you could wrap it in a script that provides
> one end of a named pipe as PGPASSFILE (is that 'on disk'?). Or uses a
> temporary file in tmpfs with swap not configured (is that 'on disk'
> yet? Yeah, I bet that's against the rules...) Of course you could
> write a wrapper script that sets PGPASSWORD, but some people don't
> like putting secrets in environment variables. Supposedly there are
> operating systems where anyone can see your environment (which?), but
> on the systems I know only root can. Then you run into the thorny
> question of why you don't trust root not to peer at your
> /proc/{$PID}/environ (or local equivalent), but do trust them not to
> core dump your whole process and kernel. (It is interesting that
> Linux pam_exec.so chooses to pass the username via env var PAM_USER
> but send the password via a pipe connected to stdin, considering all
> the ways root could intercept that.)
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>
Re: Have an encrypted pgpass file
On Tue, Jul 24, 2018 at 2:10 PM, Craig Ringer wrote:
>> Grabbing it from a process's memory is a bit harder than grabbing contents
>> of a file, but not much harder. If the agent is remote then that's harder,
>> but you can just ask the script to decrypt the pgpass for you, so again, not
>> much of a win.
>>
>> Even with a hardware crypto offload device the advantage here seems to be
>> mainly limited to making it harder to capture data from backups or
>> file-lifting attacks. Anything that can execute code or commands on the host
>> can still get the credentials.
>
> To be clear I'm not saying not to do this. I think it'd make more sense to
> do it via an agent and socket, where libpq learns to ask the agent for
> credentials (and certs!). That way we could finally support libnss, etc.
> It's not perfect, and it doesn't magically make unattended storage secure,
> but it sure helps slow down file-based password theft.
Yeah, the idea that you can defend yourself against root is obviously
not a good one. But the Apple keychain example (see the commands I
showed earlier) does seem to protect you against some threat
scenarios: if your computer is stolen, that password can't be
extracted from the disk. You'd need to unlock the keychain first by
logging in. It's not 'unattended': you have to be in attendance to
unlock the keychain.
I'm deeply suspicious of unattended use cases for encrypted passwords
though. I have heard requests for this. Encrypting it with a key
that is present in the configuration file, derived from well known
things, or stored in a nearby file doesn't change that. The encrypted
secret + easily available key is still a secret you must protect
exactly as hard. I suspect it is a misapplication of the advice that
you should never store (other people's) passwords in your database --
instead you should store something that allows you to check if *they*
have the password, without storing the password itself. But that
doesn't mean that you should throw away your own passwords: instead
you should recognise that they are secrets, and treat them as such.
An idea I wondered about: if the goal is to allow psql (not libpq) to
use a secret from ,
then perhaps psql could have a machine-friendly
--read-password-from-stdin feature for use by wrapper scripts (it
seems to be hard to send passwords to -W, though maybe I'm just doing
something stupid). Or if you could wrap it in a script that provides
one end of a named pipe as PGPASSFILE (is that 'on disk'?). Or uses a
temporary file in tmpfs with swap not configured (is that 'on disk'
yet? Yeah, I bet that's against the rules...) Of course you could
write a wrapper script that sets PGPASSWORD, but some people don't
like putting secrets in environment variables. Supposedly there are
operating systems where anyone can see your environment (which?), but
on the systems I know only root can. Then you run into the thorny
question of why you don't trust root not to peer at your
/proc/{$PID}/environ (or local equivalent), but do trust them not to
core dump your whole process and kernel. (It is interesting that
Linux pam_exec.so chooses to pass the username via env var PAM_USER
but send the password via a pipe connected to stdin, considering all
the ways root could intercept that.)
--
Thomas Munro
http://www.enterprisedb.com
Re: Have an encrypted pgpass file
On 24 July 2018 at 05:53, Jeff Janes wrote: > On Wed, Jul 18, 2018 at 5:52 PM, Tom Lane wrote: > >> Thomas Munro writes: >> > On Thu, Jul 19, 2018 at 5:46 AM, Marco van Eck >> wrote: >> >> Since .pgpass files contain plain-text passwords, I searched for an >> >> alternative. >> >> In the attached patch I've added the possibility to run a command to >> produce >> >> the content of the pgpass file, in exactly the same format. >> >> > ... Here you side step those questions completely and make that the end >> > user's problem. I like it. >> >> ... but doesn't this just encourage people to build hacks that aren't >> really any more secure than the unreadable-file approach? In fact, >> I'm afraid this would be an attractive nuisance, in that people would >> build one-off hacks that get no security vetting and don't really work. >> >> I'd like to see a concrete example of a use-case that really does add >> security; preferably one short and useful enough to put into the docs >> so that people might copy-and-paste it rather than rolling their own. >> It seems possible that something of the sort could be built atop >> ssh-agent or gpg-agent, for instance. >> > > If the goal is not unattended operation but just unannoying operation, I > think the first example he provided is already that use-case. If you > already have gpg configured to use gpg-agent, then it just works. You get > encryption-at-rest, and you don't have to type in your password repeatedly > in the same continuous shell session. > ... and the attacker steals the key from gpg-agent. Grabbing it from a process's memory is a bit harder than grabbing contents of a file, but not much harder. If the agent is remote then that's harder, but you can just ask the script to decrypt the pgpass for you, so again, not much of a win. Even with a hardware crypto offload device the advantage here seems to be mainly limited to making it harder to capture data from backups or file-lifting attacks. Anything that can execute code or commands on the host can still get the credentials. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Have an encrypted pgpass file
On Wed, Jul 18, 2018 at 5:52 PM, Tom Lane wrote: > Thomas Munro writes: > > On Thu, Jul 19, 2018 at 5:46 AM, Marco van Eck > wrote: > >> Since .pgpass files contain plain-text passwords, I searched for an > >> alternative. > >> In the attached patch I've added the possibility to run a command to > produce > >> the content of the pgpass file, in exactly the same format. > > > ... Here you side step those questions completely and make that the end > > user's problem. I like it. > > ... but doesn't this just encourage people to build hacks that aren't > really any more secure than the unreadable-file approach? In fact, > I'm afraid this would be an attractive nuisance, in that people would > build one-off hacks that get no security vetting and don't really work. > > I'd like to see a concrete example of a use-case that really does add > security; preferably one short and useful enough to put into the docs > so that people might copy-and-paste it rather than rolling their own. > It seems possible that something of the sort could be built atop > ssh-agent or gpg-agent, for instance. > If the goal is not unattended operation but just unannoying operation, I think the first example he provided is already that use-case. If you already have gpg configured to use gpg-agent, then it just works. You get encryption-at-rest, and you don't have to type in your password repeatedly in the same continuous shell session. Cheers, Jeff
Re: Have an encrypted pgpass file
On Wed, Jul 18, 2018 at 11:19 PM, Tom Lane wrote: > Sorry, I don't buy that line of argument. The *only* reason for this > feature to exist is if it allows ready creation of security solutions > that are actually more secure than a non-world-readable .pgpass file. > That's a much higher bar than many people realize to begin with ... > and if it comes along with huge risk of security foot-guns, I do not > think that it's going to be a net advance. I don't think I agree with this objection. First, not doing anything won't be a net advance, either. Second, your objection seems akin to saying "we're not going to let you drive because you might crash the car". There are *some* people who should not be allowed to get behind the wheel, but your proposal seems analogous to banning *everyone* from driving on the theory that car crashes are bad. I think that's an overreaction. I agree that there's probably a risk, but why can't we just document best practices? Really, I'm not sure that it's right to suppose that you're calling a shell script specifically. If it's a Perl, Python, Ruby, etc. script the risk is probably much less -- you're going to take $ARGV[1] or the equivalent and shove it in a string variable, and after that it's not really any more or less risky than any other string variable you've got. You could of course perform an ill-considered interpolation into a shell command, but that's true of any string that originates from a user in any situation, and if you're a somewhat-knowledgeable programmer you probably won't. Generally you have to do *extra* work to make things safe in the shell, whereas in a scripting language you just have to not screw up. foo($thingy) is safe in Perl; foo $thingy is unsafe in the shell. Of course mistakes are possible and we can avoid all the mistakes by not providing the feature, but to me, that doesn't seem like the way to go. > One reason I'd like to see a concrete use-case (or several concrete > use-cases) is that we might then find some design that's less prone > to such mistakes than "here, run this shell script" is going to be. I think that the most common use case is likely to be to get the data from a local or remote keyserver. For example, when I'm logged in, my keychain is available to provide passwords; when I log out, those passwords aren't accessible any more. Or, when the server is in the datacenter where it's supposed to be located, it can pull the data from some other machine in that data center whose job it is provide said data; when the server is physically stolen from the datacenter and taken to some other location, the other machine isn't there and necessary credentials are no longer available (or even if the other machine *is* there, it probably requires a manually-entered password to start the key service, which the thief may not have). > I'm vaguely imagining exec'ing a program directly without a layer > of shell quoting/evaluation in between; but not sure how far that > gets us. It's not a bad thought, although it might not help that much if it causes somebody who would have written PGPASSCOMMAND="this that" to instead set PGPASSCOMMAND="thisthat.sh" where that file contains #!/bin/bash this that ...which seems like a likely outcome. > Another question that ought to be asked somewhere along here is > "how well does this work on Windows?" ... True. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Have an encrypted pgpass file
Sorry Tom (and others), I didn't notice my change affected libpq. Though I would really like the possibility to have an encrypted way of presenting the pgpassfile. Would it be more secure if the script / command is passed as a compile option to libpg and the variable only contains the filename of the encrypted file or only the arguments to pass to the command and use the original pgpassfile to decrypt. As always the security of the library / command in the hands of the person who compiles it. So assume the library is compiled with PGPASSDECRYPTCOMMAND=/usr/bin/gpg PGPASSFILE=pgpass.gpg PGPASSARGUMENTS="-q -d" In the end libpq would call: '/usr/bin/gpg -q -d pgpass.gpg' The only thing I'm wondering, is it flexible enough for use cases different than mine? Or should I make a static variable for it so the user of the libpq can define it if they want to use the feature, and if not defined ignore the feature? I can make a new patch, if this is the direction we want to go. Best regards, Marco van Eck On Sat, Jul 21, 2018 at 7:29 AM Tom Lane wrote: > Isaac Morland writes: > >>> It would also provide a *very* fertile source of shell-script-injection > >>> vulnerabilities. (Whaddya mean, you tried to use a user name with a > >>> quote mark in it?) > > > If I understand the proposal correctly, the pgpass program would run on > the > > client, invoked by libpq when a password is needed for a connection. So > the > > risk relates to strange things happening on the client when the client > > attempts to connect as a strangely-named user or to a strangely-named > > database or host, not to being able to break into the server. > > Yeah. The most obvious scenario for trouble is that somebody enters > a crafted user name on a website, and that results in bad things happening > on an application-server machine that tried to pass that user name to > a database server. The DB server itself isn't compromised, but the app > server could be. > > If we were putting this sort of feature into psql, it wouldn't be such > a risk, but if it's in libpq then I fear it is. libpq underlies a lot > of client-side code. > > regards, tom lane >
Re: Have an encrypted pgpass file
Isaac Morland writes: >>> It would also provide a *very* fertile source of shell-script-injection >>> vulnerabilities. (Whaddya mean, you tried to use a user name with a >>> quote mark in it?) > If I understand the proposal correctly, the pgpass program would run on the > client, invoked by libpq when a password is needed for a connection. So the > risk relates to strange things happening on the client when the client > attempts to connect as a strangely-named user or to a strangely-named > database or host, not to being able to break into the server. Yeah. The most obvious scenario for trouble is that somebody enters a crafted user name on a website, and that results in bad things happening on an application-server machine that tried to pass that user name to a database server. The DB server itself isn't compromised, but the app server could be. If we were putting this sort of feature into psql, it wouldn't be such a risk, but if it's in libpq then I fear it is. libpq underlies a lot of client-side code. regards, tom lane
Re: Have an encrypted pgpass file
On 20 July 2018 at 17:22, Tels wrote: > Moin, > > > It would also provide a *very* fertile source of shell-script-injection > > vulnerabilities. (Whaddya mean, you tried to use a user name with a > > quote mark in it?) > > Little Bobby Tables, we call him. :) > > I'm also concerned that that would let anybody who could alter the > environment then let arbitrary code be run as user postgres. Is this > something that poses a risk in addition to the current situation? > If I understand the proposal correctly, the pgpass program would run on the client, invoked by libpq when a password is needed for a connection. So the risk relates to strange things happening on the client when the client attempts to connect as a strangely-named user or to a strangely-named database or host, not to being able to break into the server.
Re: Have an encrypted pgpass file
Moin, On Wed, July 18, 2018 7:25 pm, Tom Lane wrote: > Alvaro Herrera writes: >> Seems to me that passing %-specifiers to the command would make it more >> useful (%u for "user", "host" etc) -- your command could refuse to give >> you a password for the superuser account for instance but grant one for >> a read-only user. > > It would also provide a *very* fertile source of shell-script-injection > vulnerabilities. (Whaddya mean, you tried to use a user name with a > quote mark in it?) Little Bobby Tables, we call him. :) I'm also concerned that that would let anybody who could alter the environment then let arbitrary code be run as user postgres. Is this something that poses a risk in addition to the current situation? Best regards, Tels
Re: Have an encrypted pgpass file
On Thu, Jul 19, 2018 at 5:19 AM Tom Lane wrote: > "Joshua D. Drake" writes: > > On 07/18/2018 04:25 PM, Tom Lane wrote: > >> This is exactly the kind of area in which I'm concerned for the > >> possibility of sloppily-written scripts being a net negative for > >> security. > > > Although I appreciate the concern, can we not worried about this? Your > > argument basically boils down to: Dumb will be Dumb. That will not > > change no matter what we do as is obvious by the number of people STILL > > using postgres as their connected web app user. The usability of this > > feature if fleshed out correctly is pretty large. > > Sorry, I don't buy that line of argument. The *only* reason for this > feature to exist is if it allows ready creation of security solutions > that are actually more secure than a non-world-readable .pgpass file. > That's a much higher bar than many people realize to begin with ... > and if it comes along with huge risk of security foot-guns, I do not > think that it's going to be a net advance. > > One reason I'd like to see a concrete use-case (or several concrete > use-cases) is that we might then find some design that's less prone > to such mistakes than "here, run this shell script" is going to be. > I'm vaguely imagining exec'ing a program directly without a layer > of shell quoting/evaluation in between; but not sure how far that > gets us. > > Another question that ought to be asked somewhere along here is > "how well does this work on Windows?" ... > > regards, tom lane > The reason I wanted to have this feature since having an unencrypted .pgpass will give the administrator access to it's content. Nothing more nothing less. If the script can get the content the user can do it as well. In my case I use gpg to decrypt a pgpass-file, and use psql to connect to a bunch of remote postgresql databases. If the administrator trie the same she will be asked to present my yubikey. Since our security department forbids my to have unencrypted password on any filesystem, I have to type the password too many times. My PGPASSCOMMAND is set to "gpg -q -d ~/etc/pgpass.gpg" @Alvaro I also thought of adding arguments (actually my first implementation) but it will make the usage more complex since you have to write a command to deliver the password. The content of the pgpass-file is already well defined, making it easy to use. I have no idea why it shouldn't work on Windows, it's just running a command or script, just like 'type .pgpass' or 'psql -At -F, -c "select 'localhost',5432,'db','db','db'"' Regards, Marco van Eck
Re: Have an encrypted pgpass file
"Joshua D. Drake" writes: > On 07/18/2018 04:25 PM, Tom Lane wrote: >> This is exactly the kind of area in which I'm concerned for the >> possibility of sloppily-written scripts being a net negative for >> security. > Although I appreciate the concern, can we not worried about this? Your > argument basically boils down to: Dumb will be Dumb. That will not > change no matter what we do as is obvious by the number of people STILL > using postgres as their connected web app user. The usability of this > feature if fleshed out correctly is pretty large. Sorry, I don't buy that line of argument. The *only* reason for this feature to exist is if it allows ready creation of security solutions that are actually more secure than a non-world-readable .pgpass file. That's a much higher bar than many people realize to begin with ... and if it comes along with huge risk of security foot-guns, I do not think that it's going to be a net advance. One reason I'd like to see a concrete use-case (or several concrete use-cases) is that we might then find some design that's less prone to such mistakes than "here, run this shell script" is going to be. I'm vaguely imagining exec'ing a program directly without a layer of shell quoting/evaluation in between; but not sure how far that gets us. Another question that ought to be asked somewhere along here is "how well does this work on Windows?" ... regards, tom lane
Re: Have an encrypted pgpass file
On 07/18/2018 04:25 PM, Tom Lane wrote: Alvaro Herrera writes: Seems to me that passing %-specifiers to the command would make it more useful (%u for "user", "host" etc) -- your command could refuse to give you a password for the superuser account for instance but grant one for a read-only user. It would also provide a *very* fertile source of shell-script-injection vulnerabilities. (Whaddya mean, you tried to use a user name with a quote mark in it?) This is exactly the kind of area in which I'm concerned for the possibility of sloppily-written scripts being a net negative for security. Although I appreciate the concern, can we not worried about this? Your argument basically boils down to: Dumb will be Dumb. That will not change no matter what we do as is obvious by the number of people STILL using postgres as their connected web app user. The usability of this feature if fleshed out correctly is pretty large. JD regards, tom lane -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc *** A fault and talent of mine is to tell it exactly how it is. *** PostgreSQL centered full stack support, consulting and development. Advocate: @amplifypostgres || Learn: https://postgresconf.org * Unless otherwise stated, opinions are my own. *
Re: Have an encrypted pgpass file
Alvaro Herrera writes: > Seems to me that passing %-specifiers to the command would make it more > useful (%u for "user", "host" etc) -- your command could refuse to give > you a password for the superuser account for instance but grant one for > a read-only user. It would also provide a *very* fertile source of shell-script-injection vulnerabilities. (Whaddya mean, you tried to use a user name with a quote mark in it?) This is exactly the kind of area in which I'm concerned for the possibility of sloppily-written scripts being a net negative for security. regards, tom lane
Re: Have an encrypted pgpass file
On 2018-Jul-18, Marco van Eck wrote: > Since .pgpass files contain plain-text passwords, I searched for an > alternative. > In the attached patch I've added the possibility to run a command to > produce the content of the pgpass file, in exactly the same format. In this > way I could use gpg or any other command to decrypt a pgpass file. It will > prefer the .pgpass file and will not call the command. > > This would be my environment variable, to have no plain-text password: > PGPASSCOMMAND="gpg -q -d pgpass.gpg" > > Other usages of the variable: > PGPASSCOMMAND="cat pgpass" > PGPASSCOMMAND="curl http://passwords/really-unsecure-pgpass; > PGPASSCOMMAND="my-own-secure-pgpass-script" > > The submitted patch does it's job, though the command could throw errors. > > What do you think of this solution? Seems to me that passing %-specifiers to the command would make it more useful (%u for "user", "host" etc) -- your command could refuse to give you a password for the superuser account for instance but grant one for a read-only user. Or grant a password for the (hypothetical) pg_backup user to the account doing the backups, but not to anyone else. Maybe if the root/postgres user runs the program, all passwords are printed for the instances in localhost/127.0.0.1. That way, a client-side centralized security policy is just a SMOP. Maybe there are reasons why this doesn't make sense and I'm not seeing them -- if you do please point'em out. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Have an encrypted pgpass file
On Thu, Jul 19, 2018 at 9:52 AM, Tom Lane wrote: > Thomas Munro writes: >> On Thu, Jul 19, 2018 at 5:46 AM, Marco van Eck >> wrote: >>> Since .pgpass files contain plain-text passwords, I searched for an >>> alternative. >>> In the attached patch I've added the possibility to run a command to produce >>> the content of the pgpass file, in exactly the same format. > >> ... Here you side step those questions completely and make that the end >> user's problem. I like it. > > ... but doesn't this just encourage people to build hacks that aren't > really any more secure than the unreadable-file approach? In fact, > I'm afraid this would be an attractive nuisance, in that people would > build one-off hacks that get no security vetting and don't really work. > > I'd like to see a concrete example of a use-case that really does add > security; preferably one short and useful enough to put into the docs > so that people might copy-and-paste it rather than rolling their own. +1 > It seems possible that something of the sort could be built atop > ssh-agent or gpg-agent, for instance. Another example would be the Apple keychain system. I think the command would be something like "/usr/bin/security find-generic-password -a someaccount -s somekeychain -w", and you'd have to have stored it with something like "/usr/bin/security add-generic-password -a someaccount -s somekeychain -w". -- Thomas Munro http://www.enterprisedb.com
Re: Have an encrypted pgpass file
Thomas Munro writes: > On Thu, Jul 19, 2018 at 5:46 AM, Marco van Eck wrote: >> Since .pgpass files contain plain-text passwords, I searched for an >> alternative. >> In the attached patch I've added the possibility to run a command to produce >> the content of the pgpass file, in exactly the same format. > ... Here you side step those questions completely and make that the end > user's problem. I like it. ... but doesn't this just encourage people to build hacks that aren't really any more secure than the unreadable-file approach? In fact, I'm afraid this would be an attractive nuisance, in that people would build one-off hacks that get no security vetting and don't really work. I'd like to see a concrete example of a use-case that really does add security; preferably one short and useful enough to put into the docs so that people might copy-and-paste it rather than rolling their own. It seems possible that something of the sort could be built atop ssh-agent or gpg-agent, for instance. regards, tom lane
Re: Have an encrypted pgpass file
> On Jul 18, 2018, at 14:33, Thomas Munro wrote: > Here you side step those questions completely and make that the end > user's problem. I like it. +1. This is a clever solution, since any kind of key vault or other system could be dropped in there. -- -- Christophe Pettus x...@thebuild.com
Re: Have an encrypted pgpass file
On Thu, Jul 19, 2018 at 5:46 AM, Marco van Eck wrote: > Since .pgpass files contain plain-text passwords, I searched for an > alternative. > In the attached patch I've added the possibility to run a command to produce > the content of the pgpass file, in exactly the same format. In this way I > could use gpg or any other command to decrypt a pgpass file. It will prefer > the .pgpass file and will not call the command. > > This would be my environment variable, to have no plain-text password: > PGPASSCOMMAND="gpg -q -d pgpass.gpg" > > Other usages of the variable: > PGPASSCOMMAND="cat pgpass" > PGPASSCOMMAND="curl http://passwords/really-unsecure-pgpass; > PGPASSCOMMAND="my-own-secure-pgpass-script" Hi Marco I've heard requests for encrypted .pgpass files before, and I've always been a bit confused about how an unattended system is supposed to decrypt them. If the key is in the configuration file or local filesystem, it feels like you haven't really added much security over a plaintext password, since an attacker who can steal the .pgpass file can steal those things too. There are other database systems out there where passwords are held in an encrypted form but with only a small amount of digging on the internet you can find out how to decrypt them. Seems a bit bogus, at first glance anyway. Here you side step those questions completely and make that the end user's problem. I like it. -- Thomas Munro http://www.enterprisedb.com
Have an encrypted pgpass file
Hi, Since .pgpass files contain plain-text passwords, I searched for an alternative. In the attached patch I've added the possibility to run a command to produce the content of the pgpass file, in exactly the same format. In this way I could use gpg or any other command to decrypt a pgpass file. It will prefer the .pgpass file and will not call the command. This would be my environment variable, to have no plain-text password: PGPASSCOMMAND="gpg -q -d pgpass.gpg" Other usages of the variable: PGPASSCOMMAND="cat pgpass" PGPASSCOMMAND="curl http://passwords/really-unsecure-pgpass; PGPASSCOMMAND="my-own-secure-pgpass-script" The submitted patch does it's job, though the command could throw errors. What do you think of this solution? Best regards, Marco van Eck pgpasscommand_v1.patch Description: Binary data
