Database Reference
In-Depth Information
On any system, there can be more than one database server. Each database server listens on
exactly one "well-known" network port, which cannot be shared between servers on the same
system. The default port number for PostgreSQL is
5432
, which has been registered with IANA,
and is uniquely assigned to PostgreSQL. (You can see it used in the
/etc/services
file on
most *nix servers). The port number can be used to uniquely identify a specific database server
if many exist.
A database server is also sometimes known as a "database cluster", because the PostgreSQL
server allows you to define one or more databases on each server. Each connection request
must identify exactly one database identified by its dbname. When you connect, you will only
be able to see database objects created within that database.
A database user is used to identify the connection. By default, there is no limit on the number
of connections for a particular user, though there is a later recipe to restrict that. In more
recent versions of PostgreSQL, users are referred to as login roles, though many clues remind
us of the earlier naming, and it still makes sense in many ways. A login role is a role that has
been assigned the
CONNECT
privilege.
Each connection will typically be authenticated in some way. This is defined at the server,
so is not optional at connection time if the administrator has configured the server to
require authentication.
Once you've connected, each connection can have one active transaction at a time and one
fully active statement at any time.
The server will have a defined limit on the number of connections it can serve, so a
connection request can be refused if the server is oversubscribed.
Inspecting your connection information
If you want to confirm you've connected to the right place and in the right way, you can execute
some or all of the following commands:
SELECT inet_server_port();
This shows the port on which the server is listening.
SELECT current_database();
Shows the current database.
SELECT current_user;
This shows the current userid.
SELECT inet_server_addr();
Shows the IP address of the server that accepted the connection.
A user's password is not accessible using general SQL for obvious reasons.
