Postgres.app

Alex Clay aclay at mac.com
Mon Aug 23 19:03:13 UTC 2021


Hi Michael,

Glad you got it working and thank you for sharing your solution!

Alex

> On Aug 23, 2021, at 14:19, Michael Houlberg <michael at houlbergdevelopment.com> wrote:
> 
> To those interested, I finally got my system working.  In the end, I ditched the Postgres.app because the installation is very non-standard.  That doesn’t mean it can’t work fine for anyone else, or that I couldn't have gotten it working for myself.  Instead I went with the more “standard” version available through pestgresql.org.
> 
> I didn’t have to modify the postgresql.conf file to modify the listener, and I learned that settings in this file can be edited through pgAdmin on the Configuration tab.  I didn’t make any changes myself from the defaults.
> 
> In order to edit the pg_hba.conf file I changed permissions in the Finder for /Library/PostgreSQL/13/data by adding the Admin user with read/write access.  I made the same change to the file itself, then I could easily edit with BBEdit.  I just feel better using BBEdit than an editor in the terminal, my personal preference.
> 
> I added this line above the others:
> host    all             all             0.0.0.0/0         password
> and changed all but the replication lines to say trust for local connections
> At that point I could connect to the server from another client machine running pgAdmin.
> 
> All that was left was to add the PG libraries to my Omnis app runtime. I followed the tech note:
> https://www.omnis.net/developers/resources/technotes/tnsq0040.jsp
> 
> I copied the libraries from Library/PostgreSQL/13/lib into Contents/Frameworks/
> libpq.5.13.dylib
> libpq.5.dylib (a symbolic link)
> I found I needed to rename the symbolic link to libpq.dylib to match the tech note.
> 
> Then into Contents/lib (first, creating the lib folder)
> libcypto.1.1.dylib
> libssl.1.1.dylib
> 
> At the point all is well.
> 
> Michael Houlberg
> Houlberg Development, LLC
> 
>> On Aug 20, 2021, at 1:54 AM, Alex Clay via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>> 
>> Hi Michael,
>> 
>> You'll need libpq on the client computer for Omnis to connect to PostgreSQL whether you want SSL or not. I checked the macOS development installer for 10.1 27275 and don't see one bundled in there. I recommend copying in libpq.dylib to your Frameworks folder in Omnis.
>> 
>> On a side node, try Omnis Studio 10.1 29237 for macOS Big Sur. That's what our current shipping version is and it's FAR more stable on Big Sur than 27575.
>> 
>> I would expect pgAdmin to work. The name can be anything, like "Dev Server". It's the IP, port (5432 by default), username, database, and password that matter for connecting.
>> 
>> What message does pgAdmin give you when you try to connect to the server IP?
>> 
>> The pg_hba.conf file looks good. Did you restart postgres after moving that line to the top?
>> 
>> Alex
>> 
>>> On Aug 19, 2021, at 17:03, Michael Houlberg <michael at houlbergdevelopment.com> wrote:
>>> 
>>> Alex,
>>> 
>>> Thanks for the tips on scram-256, I’ll look at that later once I’ve actually connected.
>>> 
>>> The server computer is running on Catalina 10.15.7 because it’s an older machine.  The client computer is running Big Sur 11.1.  I’m using Omnis 10.1 27575 on both, so not the latest.  I don’t see any libpq.x files at all in the Omnis Frameworks folder on either machine.  Do I really need to extract these files from the Archives?  I’m running Postgres using the Postgres.app version which places it’s files in the Application Support area.  I don’t see files with those names anywhere in there.  The technote you reference seems to indicate I’d need to do this for OpenSSL support, I’m not interested in that right now.
>>> 
>>> I installed pgAdmin on the “other” computer to see if it could connect to the server computer.  Is it just supposed to find it?  It doesn’t.  I tried to use the Add New Server button to locate the server, but either it isn’t seeing it or I don’t know what I’m doing.  It insists on a “Name” and seems to allow anything in there, but under the Server Group popup, nothing is listed.  I tried entering the IP number, (alone or with port), of the computer which is acting as server, but no luck.  So maybe it’s the pg_hba.conf file after all?
>>> 
>>> Here’s what’s in there right now after moving my line to the top:
>>> 
>>> # TYPE  DATABASE        USER            ADDRESS                 METHOD
>>> host	all				all				0.0.0.0/0				md5
>>> # "local" is for Unix domain socket connections only
>>> local   all             all                                     trust
>>> # IPv4 local connections:
>>> host    all             all             127.0.0.1/32            trust
>>> # IPv6 local connections:
>>> host    all             all             ::1/128                 trust
>>> # Allow replication connections from localhost, by a user with the
>>> # replication privilege.
>>> local   replication     all                                     trust
>>> host    replication     all             127.0.0.1/32            trust
>>> host    replication     all             ::1/128                 trust
>>> 
>>> Thanks,
>>> Michael Houlberg
>>> Houlberg Development, LLC
>>> 
>>>> On Aug 18, 2021, at 5:26 PM, Alex Clay via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>>>> 
>>>> Hi Michael,
>>>> 
>>>> While it won't help with your connection woes, I recommend you update from md5 to scram-256, which is much more secure. If you're setting up a new environment in PG 13, there's not much you should need to do beside change the allowed authentication mechanism and generate a fresh password.
>>>> 
>>>> https://www.postgresql.org/docs/current/auth-password.html
>>>> https://www.cybertec-postgresql.com/en/from-md5-to-scram-sha-256-in-postgresql/
>>>> https://blog.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram
>>>> 
>>>> The "Client or interface function not available" sounds more like an issue with the client application being unable to locate the PostgreSQL libpq library (I'm guessing this is an Omnis client). You might try using the following instructions to install a current version of this library: https://www.omnis.net/developers/resources/technotes/tnsq0040.jsp
>>>> 
>>>> You could verify this is an issue in Omnis and not an issue with the PostgreSQL server by connecting with an alternate client app from the client computer, like pgAdmin. If you can connect there, then the issue is with Omnis.
>>>> 
>>>> FWIW your connection line looks like it will work—you've basically got wide-open access (using a password) for non-SSL connections to all databases for all roles (users) from all IPv4 addresses.
>>>> 
>>>> Also note that lines in pg_hba.conf are processed from the top down, so if there is a line preceding the one you added that blocks your connection, then your configuration line will never apply. You could move this line to the top and reload PostgreSQL to ensure it's processed first.
>>>> 
>>>> Alex
>>>> 
>>>>> On Aug 18, 2021, at 18:21, Michael Houlberg <michael at houlbergdevelopment.com> wrote:
>>>>> 
>>>>> OK, I’ve made some real progress.  After years of primarily using MySQL, I’m giving PostgreSQL a look again.
>>>>> 
>>>>> I installed the Postgres.app again with PostgreSQL 13, but this time also installed pgAdmin4.  I’ve created the database, created a user/role, gave the user login capabilities, set privileges up for the database and so forth.  Logging on with a localhost connection is all good.
>>>>> 
>>>>> Now I want to connect from another computer on the LAN.
>>>>> 
>>>>> I went into postgresql.conf and editing this line:
>>>>> listen_addresses = '*'		# what IP address(es) to listen on;
>>>>> 
>>>>> I went into pg_hba.conf and added this line:
>>>>> host	all				all				0.0.0.0/0				md5
>>>>> 
>>>>> I restarted the server.
>>>>> 
>>>>> But the other machine still has this error:
>>>>> “Client or interface function not available”
>>>>> 
>>>>> Any help appreciated,
>>>>> Michael Houlberg
>>>>> Houlberg Development, LLC
>>>>> 
>>>>>> On Aug 17, 2021, at 11:18 PM, Sten-Erik Björling <s-e.bjorling at enviro.se> wrote:
>>>>>> 
>>>>>> Hi,
>>>>>> 
>>>>>> In addition to the use of PG Admin and tools like DBeaver for DB admin I also recommend that you look into https://pgtune.leopard.in.ua helping you to optimise the settings in Postgres for increased performance. If that is needed…
>>>>>> 
>>>>>> To access the server from other devices you have to add an entry in the pg_hba.conf file:
>>>>>> 
>>>>>> host	all				all				0.0.0.0/0				md5
>>>>>> 
>>>>>> Take care, all the best…
>>>>>> 
>>>>>> Stene
>>>>>> ______
>>>>>> 
>>>>>>> 17 aug. 2021 kl. 20:32 skrev Michael Houlberg <michael at houlbergdevelopment.com>:
>>>>>>> 
>>>>>>> $listers:
>>>>>>> 
>>>>>>> I’ve been looking at the Postgres app available at postgresapp.com which has some nice features.  Mainly it’s really easy to install, and when used with the Postico application, easy to administer.  My thinking is that I could specify this for users who are not too tech savvy and they could easily set themselves up.
>>>>>>> 
>>>>>>> But I have a couple of questions.
>>>>>>> 
>>>>>>> Can I set up roles and users?  It seems to ignore this completely.
>>>>>>> 
>>>>>>> Can I connect from another computer on the LAN?  It seems like it is intended as a single-user environment.
>>>>>>> 
>>>>>>> Has anyone got some experience with this?
>>>>>>> 
>>>>>>> Thanks,
>>>>>>> Michael Houlberg
>>>>>>> Houlberg Development, LLC
>>>>>> 
>>>>>> _____________________________________________________________
>>>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
>>>>> 
>>>>> _____________________________________________________________
>>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
>>>> 
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
>>> 
>>> _____________________________________________________________
>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 



More information about the omnisdev-en mailing list