Installation, Connections, and Developer Tools

Connect with psql and GUI tools, create databases and roles, and understand connection strings.

Install PostgreSQL and Choose a Client

PostgreSQL can run locally, in a container, on a virtual machine, or as a managed cloud database. Common clients include psql, the official interactive terminal, and GUI clients such as pgAdmin. The server stores and manages the database; the client sends SQL statements and displays results.

Keep local development, testing, and production environments distinct. Record the server version and configuration used by your application. When learning, a local installation or disposable container is convenient, but do not expose a database port publicly without appropriate network restrictions and authentication.

Check the client version

psql --version
pg_isready

💡 psql --version reports the installed client version. pg_isready checks whether a PostgreSQL server is accepting connections at the selected host and port; a successful readiness check does not prove that a particular user has authorization to access a database.

Key Takeaways

The server and client are separate components.
psql is PostgreSQL's interactive terminal client.
A readiness check is not the same as an authenticated query.

Knowledge Check

What does `psql` primarily provide?

Connect with psql

A connection identifies a host, port, database, and role. Local installations may use socket connections, while remote deployments commonly use TCP. Authentication is controlled by server configuration, including pg_hba.conf for host-based access. Never assume that a connection works merely because the server process is running.

Use environment variables or a secret manager for credentials rather than embedding passwords in source code or shell history. Connection URIs are convenient, but credentials in a URI can be exposed in logs if handled carelessly.

Connect to a database

psql -h localhost -p 5432 -U app_user -d app_db
# Once connected:
\conninfo
\dt
\q

💡 The command selects a host, port, user, and database. In psql, \conninfo prints connection information, \dt lists tables visible in the current search path, and \q exits the client.

Key Takeaways

A connection includes host, port, role, and database.
Authentication rules control who can connect.
Protect credentials and avoid logging connection secrets.

Knowledge Check

Which psql meta-command lists tables in the current search path?

Create Databases and Roles

A PostgreSQL cluster can contain multiple databases, and each database can contain schemas and objects. Roles are used for login identities and privilege management; a role can have the LOGIN attribute or be used as a group role. Prefer least privilege: application roles should receive only the permissions they need, and migrations may use a separate role with controlled schema privileges.

A database owner, schema owner, and table privileges are distinct concepts. Grant the necessary rights explicitly and review default privileges for objects created in the future.

Create an application role

CREATE ROLE app_user LOGIN PASSWORD 'replace-with-a-secret';
CREATE DATABASE app_db OWNER app_user;
GRANT CONNECT ON DATABASE app_db TO app_user;

💡 This is illustrative SQL. Use a strong secret supplied securely, and follow your environment's password and role policy. Ownership grants broad control, so production role designs may separate database ownership from runtime access.

Key Takeaways

Roles are central to authentication and authorization.
Database ownership differs from individual table privileges.
Use least-privilege roles for application connections.

Knowledge Check

Which principle should guide permissions for an application database role?

Install PostgreSQL and Choose a Client

Connect with psql

Create Databases and Roles

STAY UPDATED WITH BYTES!