Обсуждение: Connection to PostgreSQL Using Certificate: Wrong Permissions on Private Key File

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

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