Re: Getting rid of "accept incoming network connections" prompts on OS X

If you do any Postgres development on OS X, you've probably gotten
seriously annoyed by the way that, every single time you reinstall the
postmaster executable, you get a dialog box asking whether you'd like
to allow it to accept incoming network connections. (At least, you
do unless you disable the OS firewall, which is not a great idea.)
It's particularly awful to run "make check-world" in this environment,
because you get a pop-up for each test install.

My Salesforce colleagues researched how to fix this, and found out
that it can be suppressed if you sign the postgres executable, which
you can easily do with a self-signed certificate. Once you've allowed
or denied network connections for a signed executable, you don't get
prompted again when the executable is replaced, so long as it's at
the same file path and signed with the same certificate. So you only
have to dismiss the dialogs once more during a check-world run, and
you're done seeing them. (Tested on Mavericks and Yosemite, have not
tried anything older.)

Accordingly, we'd like to propose something like the attached patch
to add an optional signing step to the build process. It lacks any
documentation ATM, but if there are not objections to the basic idea
I'll write some.

regards, tom lane

One of the queries in ri_triggers.c has be a little baffled.

For (relatively) obvious reasons, a FK insert triggers a SELECT 1 FROM
pk_rel ... FOR KEY SHARE.
For not-so-obvious reasons, a PK delete triggers a SELECT 1 FROM fk_rel ...
FOR KEY SHARE.

I can't see what the lock on fk_rel achieves. Both operations are already
contending for the lock on the PK row, which seems like enough to cover
every eventuality.

And even if the lock serves a purpose, KEY SHARE is an odd choice, since the
referencing field is, in general, not a "key" in this sense.

-----
aaaaaaa
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Getting-rid-of-accept-incoming-network-connections-prompts-on-OS-X-tp5823819p5823890.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Tue, Oct 21, 2014 at 1:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> If you do any Postgres development on OS X, you've probably gotten
> seriously annoyed by the way that, every single time you reinstall the
> postmaster executable, you get a dialog box asking whether you'd like
> to allow it to accept incoming network connections. (At least, you
> do unless you disable the OS firewall, which is not a great idea.)
> It's particularly awful to run "make check-world" in this environment,
> because you get a pop-up for each test install.

Ugh. This must be new in Mavericks, because I don't get any such
behavior on 10.8.5.

What an awful, awful behavior.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Oct 21, 2014 at 1:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> If you do any Postgres development on OS X, you've probably gotten
>> seriously annoyed by the way that, every single time you reinstall the
>> postmaster executable, you get a dialog box asking whether you'd like
>> to allow it to accept incoming network connections.

> Ugh. This must be new in Mavericks, because I don't get any such
> behavior on 10.8.5.

Hm, I thought it went further back than that ... I remember having put up
with it for some time now.

regards, tom lane

On 10/21/14 1:16 PM, Tom Lane wrote:
> If you do any Postgres development on OS X, you've probably gotten
> seriously annoyed by the way that, every single time you reinstall the
> postmaster executable, you get a dialog box asking whether you'd like
> to allow it to accept incoming network connections.

I used to, but somehow I don't see this anymore. Just to be sure, I
made sure the firewall is on, checked that postgres is not in the
exception list, rebooted, built postgresql from scratch, ran make check,
but no pop-up.

I'm on Yosemite. Maybe this was changed.

On Fri, Oct 24, 2014 at 7:18 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On 10/21/14 1:16 PM, Tom Lane wrote:
>> If you do any Postgres development on OS X, you've probably gotten
>> seriously annoyed by the way that, every single time you reinstall the
>> postmaster executable, you get a dialog box asking whether you'd like
>> to allow it to accept incoming network connections.
>
> I used to, but somehow I don't see this anymore. Just to be sure, I
> made sure the firewall is on, checked that postgres is not in the
> exception list, rebooted, built postgresql from scratch, ran make check,
> but no pop-up.
>
> I'm on Yosemite. Maybe this was changed.

I've never seen it on any version of OS X (I've worked my way from
Panther to Yosemite). There must be more to it...

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Oct 24, 2014 at 8:26 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> On Fri, Oct 24, 2014 at 7:18 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> > On 10/21/14 1:16 PM, Tom Lane wrote:
> >> If you do any Postgres development on OS X, you've probably gotten
> >> seriously annoyed by the way that, every single time you reinstall the
> >> postmaster executable, you get a dialog box asking whether you'd like
> >> to allow it to accept incoming network connections.
> >
> > I used to, but somehow I don't see this anymore. Just to be sure, I
> > made sure the firewall is on, checked that postgres is not in the
> > exception list, rebooted, built postgresql from scratch, ran make check,
> > but no pop-up.
> >
> > I'm on Yosemite. Maybe this was changed.
>
> I've never seen it on any version of OS X (I've worked my way from
> Panther to Yosemite). There must be more to it...
>
FWIW, with firewall at on, I am used to see this annoying popup window when
starting an instance manually, make check never complains though.
--
Michael

Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> On Fri, Oct 24, 2014 at 8:26 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>> On Fri, Oct 24, 2014 at 7:18 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>> On 10/21/14 1:16 PM, Tom Lane wrote:
>>>> If you do any Postgres development on OS X, you've probably gotten
>>>> seriously annoyed by the way that, every single time you reinstall the
>>>> postmaster executable, you get a dialog box asking whether you'd like
>>>> to allow it to accept incoming network connections.

>>> I used to, but somehow I don't see this anymore. Just to be sure, I
>>> made sure the firewall is on, checked that postgres is not in the
>>> exception list, rebooted, built postgresql from scratch, ran make check,
>>> but no pop-up.
>>>
>>> I'm on Yosemite. Maybe this was changed.

>> I've never seen it on any version of OS X (I've worked my way from
>> Panther to Yosemite). There must be more to it...

I see it every darn time I've changed the postmaster executable.
Maybe there is a difference in security settings? I have the firewall
enabled and in Settings->Security->General, "Allow apps downloaded from:
Mac App Store and identified developers", which I think is the default.
[ experiments... ] Hm, setting that to "Anywhere" doesn't change the
results anyway.

> FWIW, with firewall at on, I am used to see this annoying popup window when
> starting an instance manually, make check never complains though.

Ah. pg_regress sets listen_addresses to empty so that no TCP ports
are opened, hence no firewall complaints from "make check". However,
as soon as you start a normal installation, you get the complaint,
as even an open port on 127.0.0.1 is enough to provoke it.

Peter, Dave: maybe you have tweaked things to keep listen_addresses
empty and rely only on Unix-socket connections?

regards, tom lane

On Wed, Oct 22, 2014 at 2:14 AM, edward745 <cedward345(at)gmail(dot)com> wrote:
> One of the queries in ri_triggers.c has be a little baffled.
>
> For (relatively) obvious reasons, a FK insert triggers a SELECT 1 FROM
> pk_rel ... FOR KEY SHARE.
> For not-so-obvious reasons, a PK delete triggers a SELECT 1 FROM fk_rel ...
> FOR KEY SHARE.
>
> I can't see what the lock on fk_rel achieves. Both operations are already
> contending for the lock on the PK row, which seems like enough to cover
> every eventuality.
>
> And even if the lock serves a purpose, KEY SHARE is an odd choice, since the
> referencing field is, in general, not a "key" in this sense.

Please don't post unrelated questions onto existing mailing list
threads. Start a new thread for a new topic.

Thanks,

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Oct 24, 2014 at 3:39 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter, Dave: maybe you have tweaked things to keep listen_addresses
> empty and rely only on Unix-socket connections?
Should be so. The target of this feature is development on OSX, right?
And most of the time development would be done only on the local
machine, machine being most of the time a laptop. So instead of adding
an optional step in configure to enforce the creation of a
certificate, why not simply encourage people to use listen_addresses =
'' on OSX by documenting it? Even when working on replication or
related things on a local machine, it is possible to simply pass the
socket directory...
--
Michael

Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> On Fri, Oct 24, 2014 at 3:39 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Peter, Dave: maybe you have tweaked things to keep listen_addresses
>> empty and rely only on Unix-socket connections?

> Should be so. The target of this feature is development on OSX, right?
> And most of the time development would be done only on the local
> machine, machine being most of the time a laptop. So instead of adding
> an optional step in configure to enforce the creation of a
> certificate, why not simply encourage people to use listen_addresses =
> '' on OSX by documenting it? Even when working on replication or
> related things on a local machine, it is possible to simply pass the
> socket directory...

Some clients (eg JDBC) don't support Unix-socket connections AFAIK, so
this seems like a rather restricted solution.

regards, tom lane

On 10/24/14 10:27 AM, Tom Lane wrote:
> Some clients (eg JDBC) don't support Unix-socket connections AFAIK, so
> this seems like a rather restricted solution.

While this is a valid point, they're actually working on fixing that.

On 10/24/14 9:39 AM, Tom Lane wrote:
> Peter, Dave: maybe you have tweaked things to keep listen_addresses
> empty and rely only on Unix-socket connections?

I can confirm that I do get the popup when starting an installed
postmaster with the default settings.

Given that this doesn't affect "make check" anymore, I'm unsure about
this patch. There is a lot of magic in the configure change. I don't
know what to pass as the configure option argument, so can't really
evaluate that. I'd like to see an explanation for what is done there.

I'm afraid there is security ridicule potential. We are essentially
adding an option to patch out an operating system security feature that
the user chose. Some might find that neat and ship binaries built that
way. Because it's "--with-codesign" and not
"--with-codesign-for-devel-dont-use-in-production".

Have we dug deep enough into the firewall configuration to evaluate
other options? Can we, for example, exclude a port range?

I could see adding this as a contrib script if we don't find a better way.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Given that this doesn't affect "make check" anymore, I'm unsure about
> this patch. There is a lot of magic in the configure change. I don't
> know what to pass as the configure option argument, so can't really
> evaluate that. I'd like to see an explanation for what is done there.

As I said, I'd not written any docs. The argument that would get passed
there is just a name identifying the signing certificate you want to use.
Most of the documentation would be about how to create such a cert, no
doubt. (It's pretty simple to make a self-signed cert using Apple's
keychain utility, but it would require some explanation.)

> I'm afraid there is security ridicule potential. We are essentially
> adding an option to patch out an operating system security feature that
> the user chose. Some might find that neat and ship binaries built that
> way. Because it's "--with-codesign" and not
> "--with-codesign-for-devel-dont-use-in-production".

Yeah, that would be a risk :-(. However, for the typical case of a
self-signed certificate, nothing much would happen because no one
else's machine would even have the same certificate let alone trust it.

> Have we dug deep enough into the firewall configuration to evaluate
> other options? Can we, for example, exclude a port range?

Not that I've been able to detect. Any simple way to do that would
presumably open up exactly the security hole Apple is trying to close,
so I'd bet against there being one. (It is annoying that the firewall
triggers on ports bound to 127.0.0.1, though --- it's not apparent why
that's a security risk. Perhaps there's some way to adjust that choice?)

> I could see adding this as a contrib script if we don't find a better way.

Meh. That's just a less convenient packaging of the same code, with
the same potential for misuse.

regards, tom lane

I wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Have we dug deep enough into the firewall configuration to evaluate
>> other options? Can we, for example, exclude a port range?

> Not that I've been able to detect. Any simple way to do that would
> presumably open up exactly the security hole Apple is trying to close,
> so I'd bet against there being one. (It is annoying that the firewall
> triggers on ports bound to 127.0.0.1, though --- it's not apparent why
> that's a security risk. Perhaps there's some way to adjust that choice?)

And a bit of experimentation later: it seems that on Yosemite (and
probably earlier OS X versions), "localhost" maps to all three of these
addresses:
127.0.0.1
::1
fe80:1::1
Binding to 127.0.0.1 does not trigger the firewall popup. Binding
to ::1 doesn't, either. But binding to fe80:1::1 does. So the
easy fix, for a default installation, is to keep the postmaster
from binding to that last address.

I'm not sufficiently up on my IPv6 to be sure exactly what that third
address does. Perhaps it is a bug in the firewall logic that it
considers that address external? If it *is* externally accessible,
what the heck is the OS doing including it in "localhost"?

(Not sure if it's relevant, but I've got IPv6 set to "link-local only"
in network preferences.)

regards, tom lane

On 10/25/14 2:52 PM, Tom Lane wrote:
> And a bit of experimentation later: it seems that on Yosemite (and
> probably earlier OS X versions), "localhost" maps to all three of these
> addresses:
> 127.0.0.1
> ::1
> fe80:1::1
> Binding to 127.0.0.1 does not trigger the firewall popup. Binding
> to ::1 doesn't, either. But binding to fe80:1::1 does. So the
> easy fix, for a default installation, is to keep the postmaster
> from binding to that last address.
>
> I'm not sufficiently up on my IPv6 to be sure exactly what that third
> address does. Perhaps it is a bug in the firewall logic that it
> considers that address external?

I think that's exactly it. I have filed a bug with Apple about it.

For the time begin, I think it's a reasonable workaround to comment out
the line in /etc/hosts.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On 10/25/14 2:52 PM, Tom Lane wrote:
>> And a bit of experimentation later: it seems that on Yosemite (and
>> probably earlier OS X versions), "localhost" maps to all three of these
>> addresses:
>> 127.0.0.1
>> ::1
>> fe80:1::1
>> Binding to 127.0.0.1 does not trigger the firewall popup. Binding
>> to ::1 doesn't, either. But binding to fe80:1::1 does. So the
>> easy fix, for a default installation, is to keep the postmaster
>> from binding to that last address.
>>
>> I'm not sufficiently up on my IPv6 to be sure exactly what that third
>> address does. Perhaps it is a bug in the firewall logic that it
>> considers that address external?

> I think that's exactly it. I have filed a bug with Apple about it.

> For the time begin, I think it's a reasonable workaround to comment out
> the line in /etc/hosts.

Hmm ... I was about to object that that seemed likely to break other
stuff, but on poking around I notice that my non-laptop Yosemite machine
has no such entry at all. (That one was originally Mountain Lion, and was
upgraded to Mavericks and then Yosemite, whereas my laptop was Mavericks
on delivery.) Even more interesting, there's a "/etc/hosts~orig" file on
my laptop that does not have the entry.

A little bit of data gathering later:
wife's laptop (10.9.5): has it
dromedary (10.6.8): has it
prairiedog (10.4.11): doesn't have it

So it looks like Apple has been using this for awhile but it's not really
essential to anything. What worries me a bit is that the evidence on my
laptop suggests there may be code somewhere (like System Preferences) that
will edit the file, so that a manual removal might not "keep".
We shall see.

regards, tom lane

[ blast-from-the-past department ]

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On 10/25/14 2:52 PM, Tom Lane wrote:
>> And a bit of experimentation later: it seems that on Yosemite (and
>> probably earlier OS X versions), "localhost" maps to all three of these
>> addresses:
>> 127.0.0.1
>> ::1
>> fe80:1::1
>> Binding to 127.0.0.1 does not trigger the firewall popup. Binding
>> to ::1 doesn't, either. But binding to fe80:1::1 does. So the
>> easy fix, for a default installation, is to keep the postmaster
>> from binding to that last address.
>>
>> I'm not sufficiently up on my IPv6 to be sure exactly what that third
>> address does. Perhaps it is a bug in the firewall logic that it
>> considers that address external?

> I think that's exactly it. I have filed a bug with Apple about it.

This came up again today, and I've confirmed that the issue still exists
in current macOS. Did you get any response to your bug report, and if
so what did they say?

BTW, I seem to have typo'd in the above-quoted message --- what's
actually in the problematic line, at least on my Mac, is

fe80::1%lo0 localhost

which is not quite the same address I wrote above. It's definitely
a link-local address though, and the binding to %lo0 does seem to
be understood by macOS, at least to the extent that netstat reports
it:

$ netstat -a -n
Active Internet connections (including servers)
Proto Recv-Q Send-Q Local Address Foreign Address (state)
tcp4 0 0 127.0.0.1.5432 *.* LISTEN
tcp6 0 0 fe80::1%lo0.5432 *.* LISTEN
tcp6 0 0 ::1.5432 *.* LISTEN
... unrelated stuff ...

So either the kernel isn't really treating it as link-local on the
loopback interface, or the firewall doesn't understand that it's
link-local. Either way, sure seems like an Apple bug.

regards, tom lane

On 26/07/2018 23:45, Tom Lane wrote:
> This came up again today, and I've confirmed that the issue still exists
> in current macOS. Did you get any response to your bug report, and if
> so what did they say?

There hasn't been any response to the radar. I think our analysis is
correct, it's an OS bug.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services