How to get Database identifier using SQL
Often there is a need for client programs to find "where am I logged into?". This blog post provides a few ways to know more to answer this question.
When more and more applications are breaking out of the regular (database + application) architecture, this gets more and more important in scenarios when you're not 100% sure "where" you just logged into.
Some common scenarios, where this is helpful are:
- Proxy between application and Database
- Load Balancer (application Load balancers & Database Load Balancers)
- Serverless Database Clusters
- Auto Scaling Instances (for e.g. EC2 based)
- To confirm whether the Database server quietly changed since last invocation (in the current session)
PSQL only - :HOST variable
When connected via PSQL, the HOST variable can tell which server you're currently connected to.
postgres10@postgres=> SELECT :'HOST' db;
- This variable can be set during session, so probably it is a good idea to use it only in trusted environments.
- This would need good testing with Proxy based architectures, since the value returned is what the client 'thinks' it connected to.
- Also note that if the client was connected via IP address, this method doesn't do a reverse DNS lookup, so what you'd get is the IP address.
→ psql -U postgres10 -h 172.31.45.165psql (15devel, server 10.13)Type "help" for help.postgres10@postgres=> SELECT :'HOST' db;db---------------172.31.45.165(1 row)
PSQL only - \conninfo
You are connected to database "postgres" as user "postgres10" on host "postgres10.xyz.ap-southeast-2.rds.amazonaws.com" (address "220.127.116.11") at port "54321".
RDS Only - Lambda invocation
Aurora Postgres only - aurora_replica_status() function
When connected to Aurora Postgres, one additional way for a client to know which of the servers it is connected to (in the cluster), it can use the Aurora function aurora_replica_status() that tells which server you're currently connected to. One additional benefit with this mechanism is that it is executed on the server, and thus returns a true picture of the servers present (and their role) in the cluster.
apg2@pg=> select server_id from aurora_replica_status();