Re: Adding a pg_servername() function

Hi all,

FWIW, I too believe this is a feature that has been sorely missing for
years, leading us to use awful hacks like running "hostname" as PROGRAM and
retrieving its output.
Ideally that's what this function (or GUC) should return, what the system
believes its hostname to be. It doesn't even have to return any additional
hostnames it may have.

2 more points:
1. I too don't see it as relevant to the cluster's name
2. I agree that this may not be desirable for non-monitoring roles but
would not go so far as to require superuser. If a cloud provider doesn't
want this hostname exposed for whatever reason, they can hack Postgres to
block this. They don't seem to have trouble hacking it for other reasons.

Best regards,
Jimmy

Jimmy Angelakos
Senior Solutions Architect
EDB - https://www.enterprisedb.com/

On Thu, 10 Aug 2023 at 08:44, Laetitia Avrot <laetitia(dot)avrot(at)gmail(dot)com>
wrote:

> Dear Tom,
>
> Thank you for your interest in that patch and for taking the time to point
> out several things that need to be better. Please find below my answers.
>
> Le mer. 9 août 2023 à 16:04, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> a écrit :
>
>> I actually do object to this, because I think the concept of "server
>> name" is extremely ill-defined and if we try to support it, we will
>> forever be chasing requests for alternative behaviors.
>
>
> Yes, that's on me with choosing a poor name. I will go with
> pg_gethostname().
>
>
>> Just to start
>> with, is a prospective user expecting a fully-qualified domain name
>> or just the base name? If the machine has several names (perhaps
>> associated with different IP addresses), what do you do about that?
>> I wouldn't be too surprised if users would expect to get the name
>> associated with the IP address that the current connection came
>> through. Or at least they might tell you they want that, until
>> they discover they're getting "localhost.localdomain" on loopback
>> connections and come right back to bitch about that.
>>
>
> If there is a gap between what the function does and the user
> expectations, it is my job to write the documentation in a more clear way
> to set expectations to the right level and explain precisely what this
> function is doing. Again, using a better name as pg_gethostname() will also
> help to remove the confusion.
>
>
>>
>> Windows likely adds a whole 'nother set of issues to "what's the
>> machine name", but I don't know enough about it to say what.
>>
>
> Windows does have a similar gethostname() function (see here:
> https://learn.microsoft.com/en-us/windows/win32/api/winsock/nf-winsock-gethostname
> ).
>
>
>>
>> I think the upthread suggestion to use cluster_name is going to
>> be a superior solution for most people, not least because they
>> can use it today and it will work the same regardless of platform.
>>
>
> I don't think cluster_name is the same as hostname. True, people could use
> that parameter for that usage, but it does not feel right to entertain
> confusion between the cluster_name (which, in my humble opinion, should be
> different depending on the Postgres cluster) and the answer to "on which
> host is this Postgres cluster running?".
>
>
>> > (*) But we should think about access control for this. If you're in a
>> > DBaaS environment, providers might not like that you can read out their
>> > internal host names.
>>
>> There's that, too.
>
>
> Of course, this function will need special access and DBaaS providers will
> be able to not allow their users to use that function, as they already do
> for other features. As I said, the patch is only at the stage of POC, at
> the moment.
>
> Le mer. 9 août 2023 à 18:31, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> a écrit :
>
>>
>>
>> One concrete reason why I am doubtful about this is the case of
>> multiple PG servers running on the same machine. gethostname()
>> will be unable to distinguish them.
>>
>>
> And that's where my bad name for this function brings confusion. If this
> function returns the hostname, then it does seem totally legit and normal
> to get the same if 3 Postgres clusters are running on the same host. If
> people want to identify their cluster, they should use cluster_name. I
> totally agree with that.
>
> Why do I think this is useful?
>
> 1- In most companies I've worked for, people responsible for the OS
> settings, Network settings, and database settings are different persons.
> Also, for most companies I've worked with, maintaining their inventory and
> finding out which Postgres cluster is running on which host is still a
> difficult thing and error-prone to do. I thought that it could be nice and
> useful to display easily for the DBAs on which host the cluster they are
> connected to is running so that when they are called at 2 AM, their life
> could be a little easier.
>
> 2- In addition, as already pointed out, I know that pg_staviz (a
> monitoring tool) needs that information and uses this very dirty hack to
> get it (see
> https://github.com/vyruss/pg_statviz/blob/7cd0c694cea40f780fb8b76275c6097b5d210de6/src/pg_statviz/libs/info.py#L30
> )
>
> CREATE TEMP TABLE _info(hostname text);
> COPY _info FROM PROGRAM 'hostname';
>
> 3- Last but not least, as David E. Wheeler had created an extension to do
> so for Postgres 9.0+ and I found out a customer who asked me for this
> feature, I thought there might be out there more Postgres users who could
> find this feature helpful.
>
> I'm sorry if I'm not making myself clear enough about the use cases of
> that feature. If you still object to my points, I will simply drop this
> patch.
>
> Have a nice day,
>
> Lætitia
>