I want to write a query which should result me the following details:
- Host,
- Port,
- Username.
Like we get in the PgAdmin as shown in the below picture:
As per a_horse_with_no_name said in this answer gives me only port number.
Asked
Active
Viewed 1.6k times
3
-
1What does the query `SELECT * FROM pg_settings` show you? – Tim Biegeleisen Jan 06 '16 at 07:21
-
@TimBiegeleisen, There are many things out there around 227 rows. – MAK Jan 06 '16 at 07:28
3 Answers
8
SELECT CURRENT_USER usr
,inet_server_addr() host -- use inet_client_addr() to get address of the remote connection
,inet_server_port() port -- use inet_client_port() to get port of the remote connection
Vivek S.
- 19,945
- 7
- 68
- 85
-
After running your query I get the host `::1`? Does that mean `localhost`? – MAK Jan 06 '16 at 07:32
-
-
-
@MAK check this `select usename, client_addr from pg_catalog.pg_stat_activity;` – Vivek S. Jan 06 '16 at 07:42
-
1@MAK `::1` is the IPv6 version of localhost: https://en.wikipedia.org/wiki/Localhost – moooeeeep Jan 06 '16 at 08:07
4
Try it for host Name
select *
from pg_settings
where name = 'listen_addresses'
Sathish
- 4,419
- 4
- 30
- 59
-
-
@MAK am check the `short_desc` column read that description so only posted – Sathish Jan 06 '16 at 07:42
-
I belive start `*` or `localhost` would be the most popular results :) – Vao Tsun Jan 06 '16 at 07:49
0
Building on w͏̢in̡͢g͘̕ed̨p̢͟a͞n͏͏t̡͜͝he̸r̴'s answer above;
SELECT CURRENT_USER usr, :'HOST' host, inet_server_port() port;
This uses psql's built in HOST variable, documented here
professorcolm
- 125
- 5
-
An interesting approach, but worth noting: this only works in the `psql` tool, not using other clients, and for a Unix socket connection it returns the socket path. – EM0 Jul 16 '20 at 12:45