PostgreSQL Starter Kit & Installation Guide
📑 Table of Contents
- PostgreSQL Installation and working overview in Ubuntu
- Install PostgreSQL and contrib
- Why install postgresql-contrib? (optional, but often helpful)
- Understanding PostgreSQL Users
- What Is the
postgresSystem User, and Can You Change It? - Does PostgreSQL have a password?
- Production level PostgreSQL Security Essentials
- Additional Information
- What is
pg_hba.conf? - What does this mean in practice?
- Does this affect shell access or system users?
- What about the
postgresuser? - Connect from Remote Client
- PostgreSQL Command Cheat Sheet (via
psql) - Export and Import Data
- Bonus: Quick DB Inspection Script
PostgreSQL Installation and working overview in Ubuntu
1
2
3
4
5
6
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo -i -u postgres
psql
1
2
3
4
CREATE DATABASE myappdb;
CREATE USER myappuser WITH PASSWORD 'Very$trongP@ssw0rd';
GRANT ALL PRIVILEGES ON DATABASE myappdb TO myappuser;
\q
The above and other are explained below.
GRANT ALL PRIVILEGES ON DATABASEonly covers the database itself (connect, create, temp). It does not let the user read tables. How to grant that properly: PostgreSQL Privileges: How Access Works, and a Cheat Sheet.
Install PostgreSQL and contrib
1
2
sudo apt update
sudo apt install postgresql postgresql-contrib
Why install postgresql-contrib? (optional, but often helpful)
The postgresql-contrib package provides additional useful extensions for PostgreSQL. Examples include:
uuid-ossp: Generates UUIDs for unique IDspg_stat_statements: Monitors and logs slow querieshstore: Enables key-value storage in a single columncitext: Supports case-insensitive text
Understanding PostgreSQL Users
sudo -i -u postgresWhen you install PostgreSQL on Ubuntu:sudo -i: runs a login shell and-u postgres: switches to thepostgres
- A user named
postgresis created which is the system-level administrator of the PostgreSQL server Once you’re in thepostgresshell, you can run:psql
🔑 Two types of users in PostgreSQL setup:
System User (Linux)
- Examples:
postgres,abhishek,ubuntu - Manages PostgreSQL service at the OS level (who can access the service).
- Examples:
PostgreSQL User (Role)
- Created inside PostgreSQL (e.g.,
myappuser,readonlyuser)- example
CREATE USER myuser WITH PASSWORD 'mypassword';inside the db
- example
- Used to connect to databases, run queries, and own objects.
- Created inside PostgreSQL (e.g.,
What Is the postgres System User, and Can You Change It?
postgres is the default **system-level PostgreSQL admin user, just like root for Linux.**
⚠️ can you change it ? Yes, technically — but in production, no one does this. but Why not?
postgresis the default and system-level admin user.- It is managed by the PostgreSQL installation and used only for server administration, not for apps.
- Changing or deleting it can break things.
🟡 Instead, in production, we use the postgres user once to
- Create a database
- Create custom DB users
…then never use it again
Does PostgreSQL have a password?
Yes — but only for database users (roles).
🔐 By default:
- The system user
postgrescan access the DB without a password using Unix socket authentication
(aka peer auth inpg_hba.conf) - But any remote or non-system users must have passwords to connect.
Production level PostgreSQL Security Essentials
🔐 1. Avoid postgres user in apps
- Never use the default
postgresuser in your Django (or any app’s) settings. - Always create an app-specific DB user with limited privileges.
🔐 2. Enable SSL (in production)
When running on a remote server, ensure PostgreSQL connections are encrypted using SSL.
In your settings.py, add:
1
2
3
OPTIONS = {
'sslmode': 'require',
}
🔐 3. Secure pg_hba.conf
This file controls who can connect to PostgreSQL and how.
Default location:
/etc/postgresql/<version>/main/pg_hba.conf
Example secure rule:
1
2
# TYPE DATABASE USER ADDRESS METHOD
host mydb myuser 192.168.1.0/24 scram-sha-256
This allows myuser to connect to mydb from a trusted subnet using secure password hashing. After editing, reload PostgreSQL: sudo systemctl reload postgresql
🔐 6. Don’t Expose PostgreSQL Publicly
- By default, PostgreSQL only listens on localhost — that’s good.
- Don’t bind it to 0.0.0.0 unless behind a firewall.
- Use SSH tunneling or private network access if remote access is needed.
Additional Information
What is pg_hba.conf?
pg_hba.confstands for PostgreSQL Host-Based Authentication configuration.- It controls who can connect, from where, to which database, as which PostgreSQL user, and how they authenticate.
- This is server-level access control for PostgreSQL connections.
Breakdown of a pg_hba.conf Rule
1
2
# TYPE DATABASE USER ADDRESS METHOD
host mydb myuser 192.168.1.0/24 scram-sha-256
| Field | What it means |
|---|---|
host | Connection type — here, TCP/IP host connection |
mydb | Database name user is allowed to connect to |
myuser | PostgreSQL database user/role allowed to connect |
192.168.1.0/24 | Allowed client IP address range (subnet) |
scram-sha-256 | Authentication method required (password hashing) |
What does this mean in practice?
- Only the user
myusercan connect to the database namedmydb. - Connections must come from an IP within the
192.168.1.0subnet. myusermust authenticate with a password hashed usingscram-sha-256.- Other users or IPs trying to connect to
mydbwill be denied unless allowed by other rules. - Users cannot connect to any other database unless rules allow it.
Does this affect shell access or system users?
- No, it does NOT affect Linux system users.
- It controls PostgreSQL database user connections (roles).
- Whether you connect via
psqllocally or remotely, PostgreSQL checks this file to see if your connection is allowed.
What about the postgres user?
- If there’s no specific rule allowing the
postgresuser to connect from outside localhost, remote connections aspostgreswill be denied. - This is often done deliberately for security.
Connect from Remote Client
On the client machine (could be your laptop or another server), install psql or use a PostgreSQL client.
Example with psql:
1
2
3
4
5
psql -h <server_ip> -U <db_user> -d <db_name>
# Example:
psql -h 203.0.113.20 -U myuser -d mydb
PostgreSQL Command Cheat Sheet (via psql)
1
2
3
sudo -u postgres psql
# Or, if you're using a specific DB/user:
psql -U myuser -d mydb
📋 Meta-Commands in psql (start with a backslash \)
These are not SQL, they are psql-specific commands to inspect the database.
| Command | Meaning |
|---|---|
\l or \list | List all databases |
\c dbname | Connect to a database |
\dt | List all tables in the current schema |
\d tablename | Describe a table (columns, types, constraints) |
\du | List users and their roles |
\dn | List all schemas |
\df | List functions |
\dv | List views |
\x | Toggle expanded output (like vertical mode) |
\q | Quit psql |
\? | Show help for psql commands |
\h | SQL syntax help (e.g., \h CREATE TABLE) |
\? | Show psql command help |
🗄️ SQL Commands (Standard SQL)
You can run these inside psql or in SQL scripts.
🔧 Database Management
| Command | Purpose |
|---|---|
CREATE DATABASE mydb; | Create a new database |
DROP DATABASE mydb; | Delete a database |
\c mydb | Switch to a database |
\conninfo | Show current connection info |
👤 User / Role Management
| Command | Purpose |
|---|---|
CREATE USER myuser WITH PASSWORD 'mypassword'; | Create a new user |
DROP USER myuser; | Delete a user |
ALTER USER myuser WITH PASSWORD 'newpass'; | Change a user’s password |
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser; | Grant access to a DB |
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser; | Remove access |
📦 Table Management
| Command | Purpose |
|---|---|
CREATE TABLE mytable (...); | Create a new table |
DROP TABLE mytable; | Delete a table |
ALTER TABLE mytable ADD COLUMN newcol TYPE; | Add a column |
SELECT * FROM mytable; | Show all data |
\d mytable | Describe table |
🔍 Viewing Information
| Task | Command |
|---|---|
| List all databases | \l |
| List all tables | \dt |
| Show table schema | \d tablename |
| Show current user | SELECT current_user; or \conninfo |
| Show privileges | \z or \dp |
| Show running queries | SELECT * FROM pg_stat_activity; |
⚡ Export and Import Data
Export a table to CSV:
1
\COPY mytable TO '/tmp/mytable.csv' WITH CSV HEADER;
Import from CSV:
1
\COPY mytable FROM '/tmp/mytable.csv' WITH CSV HEADER;
🧪 Bonus: Quick DB Inspection Script
Run this in psql for a quick look at table sizes:
1
2
3
4
SELECT relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;