Обсуждение: Connection to PostgreSQL Using Certificate: Wrong Permissions on Private Key File
Connection to PostgreSQL Using Certificate: Wrong Permissions on Private Key File
От
"Atkins-Trimnell, Angus Black"
Дата:
Hello, I am having trouble connecting to PostgreSQL 8.2 from an Apache2 web server using certificates. I have set up a CA on my server and used it to sign a certificate (server.crt) created in a signing request based on a private key (server.key), both of which reside in my PostgreSQL data directory. I have also made a copy of the CA certificate file as root.crt, which I have placed in the data directory so that PostgreSQL can check the certificate coming from the web server. The home directory of the web server contains a subdirectory .postgresql in which I have placed a private key (postgresql.key) and a certificate (postgresql.crt), based on the key, that was signed by the CA. I have tried various owner/group/permissions combinations for WEB_HOME/.postgresql/postgresql.key. I have had it set as root:root with 700 permissions, postgresql:root with 700, and postgresql:postgresql with 700. I've also tried these various combinations with 750 permissions. Regardless, when I try to connect, I get the following error message: Unable to connect to PostgreSQL server: private key file "/var/www/.postgresql/postgresql.key" has wrong permissions Is there something obvious that I am doing wrong? I've tried to stick as closely to the documentation as I could. Thanks. --Angus Atkins-Trimnell
"Atkins-Trimnell, Angus Black" <trimnell@uic.edu> writes: > I have tried various owner/group/permissions combinations for > WEB_HOME/.postgresql/postgresql.key. I have had it set as root:root with > 700 permissions, postgresql:root with 700, and postgresql:postgresql with > 700. I've also tried these various combinations with 750 permissions. > Regardless, when I try to connect, I get the following error message: > Unable to connect to PostgreSQL server: private key file > "/var/www/.postgresql/postgresql.key" has wrong permissions The code appears to want 700 and ownership equal to that of the process executing libpq, ie, the apache server. regards, tom lane
Re: Connection to PostgreSQL Using Certificate: Wrong Permissions on Private Key File
От
Kevin Hunter
Дата:
At 3:50p -0400 on Sat, 29 Mar 2008, Tom Lane wrote: >> Unable to connect to PostgreSQL server: private key file >> "/var/www/.postgresql/postgresql.key" has wrong permissions > > The code appears to want 700 and ownership equal to that of the > process executing libpq, ie, the apache server. I just checked the 8.3 documentation and didn't see any mention of the private key file requirements. Modulo the fact that I don't know the docs /that/ well, would a HINT be helpful here? Or a pointer to the correct doc section? Kevin
Kevin Hunter <hunteke@earlham.edu> writes: > At 3:50p -0400 on Sat, 29 Mar 2008, Tom Lane wrote: >> The code appears to want 700 and ownership equal to that of the >> process executing libpq, ie, the apache server. > I just checked the 8.3 documentation and didn't see any mention of > the private key file requirements. Modulo the fact that I don't know > the docs /that/ well, would a HINT be helpful here? Or a pointer to > the correct doc section? Hmm. The quality of the error message does seem to be fairly far short of what we expect for user-facing messages. We've got this: if (!S_ISREG(buf.st_mode) || (buf.st_mode & 0077) || buf.st_uid != geteuid()) { printfPQExpBuffer(&conn->errorMessage, libpq_gettext("private key file \"%s\" has wrong permissions\n"), fnbuf); ERR_pop_to_mark(); return 0; } versus this coding in the exactly parallel place on the backend side: if (!S_ISREG(buf.st_mode) || (buf.st_mode & (S_IRWXG | S_IRWXO)) || buf.st_uid != geteuid()) ereport(FATAL, (errcode(ERRCODE_CONFIG_FILE_ERROR), errmsg("unsafe permissions on private key file \"%s\"", SERVER_PRIVATE_KEY_FILE), errdetail("File must be owned by the database user and must have no permissions for \"group\" or \"other\"."))); Now libpq doesn't have any provision for DETAIL or HINT in its locally-generated messages at the moment, so we can't just duplicate the backend message, but we could do something like this example from elsewhere in libpq: if (stat_buf.st_mode & (S_IRWXG | S_IRWXO)) { fprintf(stderr, libpq_gettext("WARNING: password file \"%s\" has world or group read access; permission should be u=rw (0600)\n"), pgpassfile); return NULL; } I also wonder why we are bothering with explicit tests of the file ownership in these places. If it's mode 700 or less, and not owned by us, the subsequent attempt to read the file will fail anyway, no? That would give us two different failure messages (the explicit one versus "open failed: permission denied") for the two separate cases. That seems better than one error message covering two types of mistake. Lastly, I see that the libpq docs don't cover this very well: http://developer.postgresql.org/pgdocs/postgres/libpq-ssl.html says "The private key file must not be world-readable." which is true but fails to mention that it also mustn't be world-writable, world-executable, group-readable, group-writable, or group-executable. Barring objections I'll go make this nicer in HEAD. Back-patching is probably inappropriate because it'd change translated strings. regards, tom lane
> Now libpq doesn't have any provision for DETAIL or HINT in its > locally-generated messages at the moment, so we can't just duplicate > the backend message, but we could do something like this example > from elsewhere in libpq: > > if (stat_buf.st_mode & (S_IRWXG | S_IRWXO)) > { > fprintf(stderr, > libpq_gettext("WARNING: password file \"%s\" has world or group read access; permission should be u=rw(0600)\n"), > pgpassfile); > return NULL; > } > Hmmm... I'm not crazy about libpq printing error messages to stderr. The client application can't intercept those messages. And those messages will often get lost - many client applications don't have useful stderr streams (think GUI application on Win32). -- Korry -- Korry Douglas <korryd@enterprisedb.com> EnterpriseDB http://www.enterprisedb.com
"korry" <korry.douglas@enterprisedb.com> writes: > Hmmm... I'm not crazy about libpq printing error messages to stderr. Me neither, feel free to submit a patch. The basic problem here is that the obvious fix involves feeding the message to a PQnoticeProcessor callback, but these messages occur during connection setup and there's no way to have called PQsetNoticeProcessor yet. So I think you've got to invent some green-field API if you want to improve it, and that means nothing will happen out in the real world for three to five years :-( regards, tom lane