OS 10.1 27575 connecting to Postrgesql 13

[Doug Easterbrook]

hi Graeme:

My suggestion is only about security of connection — and I assume you are logging in with the omnis $login command and a user id/password.

if you use PGadmin and look in your list of users in the database with postgres 13, you should see any of the SQL userids with an ENCRYPTED PASSWORD that is based on 'SCRAM-SHA-256’.   kind of like below.

CREATE ROLE myLoginId WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  CREATEROLE
  NOREPLICATION
  ENCRYPTED PASSWORD 'SCRAM-SHA-256$4096:3GqIo+6oU0CZUAoacpQLIQ==$ZY8HovRcbaxEsGXmef1LaGMwYAZgsKp28rEMaSy0HYw=:Am5vEw4+dWVSHFr9NqpxnZlQqPyFTfqJ2n2ij0ZcxTE=';


or, if you upgraded the db from earlier postgres, it may still look like below with an ‘md5’ at the beginning of the ENCRYPTED PASSWORD.

CREATE ROLE "myLoginId" WITH
  LOGIN
  SUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  ENCRYPTED PASSWORD 'md5c1c58fafef0f66b765dbe136f23755a2';




older databases use  ‘MD5’ encryption and you can still use that to create users in postgres 13 via SQL

the SCRAM-SHA-256 is postgres new way of creating a 1-way hash on the password — and that hash is more secure than MD5.     SCRAM was introduced in postgres 11.

more discussion on this is https://www.postgresql.org/docs/11/auth-password.html




now,   if you can’t connect to a database with a password that is SCRAM-SHA-256 , then I suspect you’ll need to update your libpq.dll to one that supports at least postgres 11.


do have a look at your users via pgadmin..        depending on what you indicate, I can send you a version of libpq that will work for authenticated logins that we’ve been using for a long while. with postgres 11,12,13 and 14.



Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On October 24, 2021, at 4:22 PM, Graeme Harris <gfhwalkabout at fastmail.fm> wrote:
> 
> Thank you Andy and Doug,
> 
> I tried both your suggestion with no success. Andy switch hostname to IP address just changed the wording on the error message to the IP Address.
> 
> Doug I changed the trust to scram-sha-256 in pg-hba.conf but still getting error message from omnis, pgadmin etc. are able to connect. I tried changing scram-sha-256 turn it on or off in postrges.conf but didn't seem to make a difference. Maybe other setting need adjusting in postgres.conf I'm not sure.
> 
> Graeme
> 
> 
> On 10/23/21 6:00 pm, Doug Easterbrook wrote:
> 
> you might want to change the line
> 
> host    all                all                192.168.0.0/24         trust
> 
> to
> 
> host    all                all                192.168.0.0/24         scram-sha-256
> 
> or, at the very least
> 
> host    all                all                192.168.0.0/24         MD5
> 
> 
> that way postgres will at least use some sort of authentication when logging people in who are on workstations other that the localhost.
> 
> 
> if you use ’trust’ everywhere, then there is no login authentication at all.
> 
> 
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
> 
> 
> On 10/23/21 6:11 pm, Andy Hilton wrote:
>> Graeme
>> 
>> So part 2 to check here is what data you are passing to the DAM to open your connection ? Are you passing a hostname or an IP address as the ‘hostname’ - if using a name as the hostname, try using the IP address (which would kind of force IP4 !)
>> 
>> 
>> Andy Hilton
>> Totally Brilliant Software Inc
>> Phone (US) : (863) 409 4870
>> Phone (UK) : 0207 193 8582
>> Web : www.totallybrilliant.com <http://www.totallybrilliant.com/>
>> Helpdesk : http://totallybrilliant.kayako.com
>> Email : andyh at totallybrilliant.com
>> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com