I have just upgraded from F25 to F26. The upgrade worked fine, except for PostgreSQL which moved from 9.5 to 9.6.

Now, the only authentication method that works is 'trust', which is clearly unsuitable.

I have hacked pg_ident.conf and pg_hba.conf to no avail.

If some kind soul could show me the obvious error I am making, I would be very grateful.

Doug

Hmmm, it's strange you can connect with trust at the moment. I wouldn't have thought postgres would actually be running:

You will need to download the upgrade package if you don't have it yet:

and run the upgrade setup to upgrade the actual databases:

then you should see a message saying your old files are moved to /var/lib/pgsql/data-old so you will need to do a diff between /var/lib/data/pgsql/pg_hba.conf and /var/lib/data-old/pgsql/pg_hba.conf and carry over any old config info you want.

Finally restart postgresql:

Thanks very much for replying dysonsimmons.

Sadly, I have already done all of the above, before posting my question. The only way I can make PostgreSQL available to psql and pgAdminIII is to add "host all all 192.168.0.100/16 trust" to my pg_hba.conf, but this does not make it accessible by LibreOffice which is my main concern. (Note: I chose the /16 suffix randomly and don't know if it achieves anything).

I am nerving myself to dive into the security documentation for pg 9.6, to see if I can understand enough to get it working. Shudder!

Kind regards,
Doug.

Ah ok bugger. I've never used it with LibreOffice. Can you give me some basic instructions so I can reproduce the issue? I still want to help you sort it out if I can!

You should be able to access using psql or pgAdminIII on the same machine postgres is on using a username and password (you want to use username and password corret?) with the following change:

From:

To:

If you want access from another machine (suggested by the IP range you provided) you definitely don't want /16. That is a class b netmask of 65k hosts. Most home setups would be /24 (255.255.255.0). If you have a look at the computer you want to access postgres from (assuming there is no nat in the middle) for its network connections netmask you will be able to determine what to put in pg_hba.conf.

You may need to install the LibreOffice PostgreSQL driver:here. Then, open LibreOffice Base. It will ask you if you want to 'Connect to an existing database' (third option in the dialog). On my system, the PostgreSQL driver is at the bottom of the drop-down list. Click next and type in the appropriate URL details - for me, they are 'postgresql://localhost:5432/ABPA'. Click next and you will be asked for username and (optionally) password. Fill in your details and click 'Test Connection' - this is where my system fails. The actual error message is 'An error has occurred: Error connecting to the server: FATAL: password authentication failed for user "postgres"'

Thanks a million for sticking with me!

Yes, I want to use passwords and I want to allow computers on the local network (192.168.0.*) to connect to pg.

I have changed my pg_hba.conf to set "host all all 192.168.0.0/24 trust" and that much still works. However, I am getting a feeling there must be some kind of 'security server' missing from my system, because changing 'trust' to 'md5' (or 'peer', or 'password', or ident ...) still breaks my ability to connect using PgAdminIII. Even changing to '127.0.0.1/32 all all md5' I can connect via psql - without being prompted for a password - but PgAdminII returns the error.

Given the above, I am unsure if I am fighting a PostgreSQL problem or a Fedora26 security problem.

To summarise:
with 'host all all 127.0.0.1/32 md5' I can connect with psql but not PgAdminII or LibreOffice.
with 'host all all 192.168.0.100/24 trust' I can connect with both psql and PgAdminII but not LibreOffice.
with any kind of security other than 'trust' I cannot connect with PgAdminIII.
I cannot connect with LibreOffice using any combination I have tried.

Sigh!

Got it! I changed my pg_hdb.conf to contain ONLY these specifications:

host all all 0.0.0.0/0 md5
host all all ::1/128 md5

Now I can connect using psql, PgAdminIII, PgModeler and LibreOffice Basic - in other words, my problem has been fixed. Obviously I had garbled my .conf during my attempts to force it into submission.

Thanks for your help, dysonsimmons, as it encouraged me to keep looking.

(Takes note to self: if all else fails, RTFM).

Great to know you got your access back!

Now that you have it working I would attempt to restrict the IP and netmask again to the smallest range suitable for your requirements.

Good job sticking with it and reading the docs!

Good point. I am comfortable with changing the IPv4 address to 192.168.0.0/24 again, but have never fiddled with IPv6. How should I define that to allow access only from the local network? I have Googled, but my search terms are obviously not adequate. "8-[

This doesn't really answer your question but the easiest thing to do is not include the IPv6 line as I'm assuming you aren't using IPv6 in your network.

I thought so too, until I discovered pgModeler connects via IPv6; at least, without that line in the .config Modeler does not run and with that line it does run. Curious.

Oh yeah you're right. Good to know and to learn something new!

Double checking the IP6 address ::1/128 is the loopback range so leave it but maybe just change it from md5 to ident which is the default.

Glad to be of service. "8-D

I'll try ident and see what happens, but I must admit I adhere to the rule "if it ain't broke, don't fix it". I'll let you know how I get on.

---------------

Later:

With 'ident', pgModeler returns an error trying to connect. Switched it back to md5 and all was sweetness and light. I think I'll leave it alone, because I only need pgModeler on this machine.

Many thanks for all the help. "8-)