postgres: could not access directory "C:/opt/PostgreSql/14/data": Permission denied

Question

Importantly: This is on Windows 10 and Postgres 14 (for newbies, Postgres is also known as PostgreSQL), with Cygwin providing the CLI.

On a fully functional, up and running, and apparently perfectly happy installation, when trying to reconfigure to turn on ssl support in order to permit JDBC connections (as the new releases don't support the older driver methods), after providing server.crt and server.key, I made a few changes to postgresql.conf (which defaults to ssl params commented out) and a reload didn't work.

The server, still running as I only did a reload (pg_ctl reload), reported in the log (fluff not included):

2022-07-17 19:45:57.907 PDT [8980] LOG:  could not access private key file "server.key": Permission denied

So, at that moment, thinking I was the owner of it all, I restarted the server (again using pg_ctl). The shutdown went fine, but trying a restart turned out to be a mistake, apparently! From pg_ctl:

$ pg_ctl.exe start
waiting for server to start....postgres: could not access directory "C:/opt/Post
greSql/14/data": Permission denied
 stopped waiting
pg_ctl: could not start server
Examine the log output.

Curious! SO, of course, I checked permissions. NOPE, I was not the owner of the directory (IE installation - this is Windows!) so, I performed chown <owner> on the two server files and then tried.

With still no joy, I remembered the group permissions and, following the official Postgres documentation, ran chmod og-rwx server.key but that didn't work either! Arg!

Now, on attempting to restart NOTHING goes in the log and the user running pg_ctl gets this:

$ pg_ctl.exe start
waiting for server to start....postgres: could not access directory "C:/opt/Post
greSql/14/data": Permission denied
 stopped waiting
pg_ctl: could not start server
Examine the log output.

SO, I tried turning off ssl inside the postgresql.conf file... And STILL NO JOY! -slap-forehead-

My next step? I tried restarting the box - this IS Windows, after all! But 'nix and Postgres don't require such actions?! Frankly, I don't see what good backing out the SSL files does with the config turned off... And, I guess to NOT my surprise, Postgres did NOT come back following reboot! (Previously it has.)

And that's why I'm asking for help figuring out why this darned thing won't come up.

Answer 1

Well, I figured it out.

I suppose this is a lesson for DBA types, like me, who don't use Postgres on Windows very often...

It turns out that while I was logged in as a user who could successfully signal a reload of the server and shut it down, I didn't have permissions to restart it! To my mind, that's just - uh - "Mind Blowing!"

As there's no su available on Cygwin, I just used an ssh session and logged in to the account that installed Postgres and, well, it came right up!

I turned SSL support in postgresql.conf back on and tried again. IT COMES UP!

When I tried JDBC, that failed with:

openVars(DriverManager.getConnection()) exception: org.postgresql.util.PSQLExcep
tion: The authentication type 10 is not supported. Check that you have configure
d the pg_hba.conf file to include the client's IP address or subnet, and that it
 is using an authentication scheme supported by the driver.

But that's a problem for a different day! Happy DBAing, people!