Setting postgres listen_addresses to * also sets $PGHOST to *

[Alexnortung]

Describe the bug
Hello, I was having some issues with a program that tried to connect to postgres in my devenv project.
I have set listen_addresses to * so that it could be used without needing to use the socket file.
However, this meant that devenv set the $PGHOST environment variable to *, which made the program I was using try to connect to * instead of 0.0.0.0 for example.

This was really confusing, since I did not know that devenv was setting the $PGHOST and it was even more confusing that it would set it to something that was invalid.

To reproduce

{
  services = {
    postgres = {
      enable = true;
      listen_addresses = "*";
    };
  };
}

Expected behavior
I would expect devenv either to not set the env variable at all or set it to something that was still valid, like 0.0.0.0.

Version

Using flakes
devenv: 1.0.5

[sandydoo]

@Alexnortung, so is 0.0.0.0 a valid value for PGHOST? Where would the client connect to in this case?
Also, * seems to mean all ipv4 and ipv6 addresses. Not sure what the right solution is here.

[k3yss]

@sandydoo I did some research upon this and here are my findings:

so is 0.0.0.0 a valid value for PGHOST? Where would the client connect to in this case?

Yes, I think, this scenario can be used in local development. Checkout the PGHOST and host section here

Also, * seems to mean all ipv4 and ipv6 addresses. Not sure what the right solution is here.

Setting it to * means one client connecting to every host in the world, which is impractical. Also, PGHOST is for client, not server. The server doesn’t read the environment variable.

cc @Alexnortung

[k3yss]

in #1298 I am setting the value of PGHOST in case of * to be the socket file defined by runtimeDir as the PG documentation mentions to use it in case of unix file system (docs here)

[Alexnortung]

Yes, I think, this scenario can be used in local development. Checkout the PGHOST and host section here

Setting it to * means one client connecting to every host in the world, which is impractical. Also, PGHOST is for client, not server. The server doesn’t read the environment variable.

I opened this issue because I had trouble with a client trying to connect to the address given by PGHOST which was * and thus didn't work.

In regards to setting PGHOST to 0.0.0.0 I see that it doesn't really make sense.

[k3yss]

@Alexnortung Can you tell the steps to reproduce the client not connecting. I tried various command but for some reason PG always connected me to the database successfully. Right now, I am treating the documentation and logic as the source of truth.

[Alexnortung]

@Alexnortung Can you tell the steps to reproduce the client not connecting. I tried various command but for some reason PG always connected me to the database successfully. Right now, I am treating the documentation and logic as the source of truth.

Oh, I was using a third party client.
I was using pnpx create-medusa-app@preview which probably uses the npm pg client. So i guess that pg doesn't support PGHOST=*?

[polobo]

So yeah, using * as a PGHOST value is both valid and practical since it resolves to localhost, and when used as a listen_address, the server will be listening on localhost, this behavior just works. The bug is apparently with pg_client not accepting the * and transforming it to localhost like the libpq api does.

That all said, the two things are not the same semantically and forcing on to be the same value as the other, while convenient as functional most of the time (especially if your environment only cares about localhost), is inflexible. I'd suggest adding an optional pghost configuration setting that, when absent, preserves the current behavior. If present, and empty, results in PGHOST being unset. Otherwise the final value of PGHOST becomes whatever that setting specifies. Then the user can set the two values independently if needed.

David J.

[polobo]

Actually, most likely the OS libraries are deviating on whether * as a host name is transformed to localhost or not. Apparently macOS fails this while Linux is fine.