Intermediate databases - Epita 2025

form

skatai.com/courses/dbsql/dbsql_server/

Setting up your local install

 --- # Today

Prerequisites

You should have

  • a running postgresql 17 or 16 install on your machine
  • and/or pgAdmin installed

Server vs databases

You connect to a postgres server

On that server you may have zero, one or multiple databases.

When you launch postgres on your local, you are actually launching the postgres server.

You connect and query the database on the command line using psql or in pgAdmin.

server vs database

Users

The postgres user is the super user.

It can do anything on the server, create and manage databases, users, drop everything etc ... handle with caution.

Installing postgreSQL also creates a user with your machine user name : alexis in my case

The username (alexis) user is not as powerful as the postgres user

Attention, on Mac, there is no **postgres** user.
The superuser is your **username**

users and privileges

What are Privileges?

Privileges are permissions that control what actions a user can perform in PostgreSQL. Think of them as "rights" or "allowed actions" : like SELECT (reading data), INSERT (adding data), or CREATE (making new objects).

Similar to file permissions on a Linux system.

PostgreSQL User Access Levels

What is TEMP privilege?

TEMP (or TEMPORARY) privilege allows a user to create temporary tables in a database.

Example:

-- With TEMP privilege, you can do this:
CREATE TEMP TABLE my_temp_data (
    id INT,
    value TEXT
);
-- This table disappears when you disconnect

So when PUBLIC has TEMP privilege by default, it means any user who connects to the database can create temporary tables for their work session.

Connecting to the server

You can connect in the terminal or with pgAdmin

In the terminal you use psql to connect

psql takes the following parameters

PostgreSQL port is by default 5432 and does not have to be specified each time.

Examples

So to connect to a local instance of a postgres server with user bob and database database_name

psql -U bob  -d database_name

To connect to a remote server IP: 123.456.789.246, same user and database

psql -U bob -h 123.456.789.246 -d database_name

How to check your connection

to see your current connection status run

psql -c "\conninfo"

if it complains, add postgres as the database name

psql -c "\conninfo" -d postgres

This should return:

You are connected to database "postgres" as user "alexis" via socket in "/tmp" at port "5432".

Connect to the server in pgAdmin

  1. Open pgAdmin
  2. Right-click on "Servers" in the left panel and choose "Create" ```bash "Server..."
  3. In the "Create - Server" dialog:

General tab:

Name: Give it a name like "Local PostgreSQL"

Connection tab:

to connect to a remote server, just change the connection tab parameters (host, user, password)

On windows

Same with the other parameters

server configuration

There are 2 configuration files for a postgres server

You have to restart your server everytime you modify these files

Config file location

Find the location of the postgresql.conf file with show config_file; in a PSQL session

similarly for the pg_hba.conf file with show hba_file; in a PSQL session

postgresql.conf

General server configuration

This file controls most of the global settings for the PostgreSQL server. It includes:

  • Resource allocation (memory, CPU)
  • Default storage locations
  • Replication settings
  • Client connection defaults
  • Query planner settings
  • Logging and statistics
  • Autovacuum settings
  • Client/server communication parameters
  • Locale and formatting
  • Error handling

Key points:

  • Affects the overall behavior and performance of the PostgreSQL server
  • Changes typically require a server restart to take effect
  • Located in the data directory

Example settings:

  • max_connections = 100
  • shared_buffers = 128MB
  • log_destination = 'stderr'

important parameters in postgresql.conf

important parameters in postgresql.conf

Logging (for development/debugging)

Performance

commented out values

When a line is commented out in postgresql.conf, PostgreSQL uses its built-in default value.

pg_hba.conf

Role: Client authentication control

This file controls WHO can connect, FROM WHERE, to WHICH database, and HOW they authenticate.

"HBA" stands for "host-based authentication".

It specifies:

Key points:

Example entries:

# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
host all all 127.0.0.1/32 md5
host production app_user 192.168.1.0/24 scram-sha-256

pg_hba.conf parameters

File Format (each line):

TYPE    DATABASE    USER    ADDRESS         METHOD

Common Entry Types:

# Local Unix socket connections
local   all         all                     trust      # No password (dangerous!)
local   all         all                     md5        # Password required
local   all         all                     peer       # OS username must match

# TCP/IP connections from localhost
host    all         all     127.0.0.1/32    md5        # IPv4 local
host    all         all     ::1/128         md5        # IPv6 local

# Allow specific user to specific database
host    myapp_db    john    192.168.1.0/24  md5        # John from local network

# Allow connections from any IP (careful!)
host    all         all     0.0.0.0/0       md5        # Any IPv4

Connection Types:

Authentication Methods:

Important:

.psqlrc

.psqlrc is a local configuration file for the psql command-line interface in PostgreSQL.

It allows you to customize your psql environment and set default behaviors.

Example

always timing the queries

  1. Set default pager: \pset pager always
  2. Set line style: \pset linestyle unicode
  3. Set timing on: \timing
  4. Set expanded auto mode: \x auto
  5. Custom prompt: \set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%#
  6. History settings: \set HISTSIZE 2000
  7. History settings: \set HISTCONTROL ignoredups
  8. Enable verbose error reports: \set VERBOSITY verbose
1 / 0