DEV Community: John M. Kuria

From Zero to Hero on psql: My Weekend Adventure Installing PostgreSQL on WSL 🐧🐘

John M. Kuria — Thu, 03 Sep 2026 16:50:55 +0000

Hey folks! 👋

So here's the deal — last weekend I decided I was done fighting with database installers that never quite worked right on Windows. A senior dev friend of mine said, "just use WSL and run it like you're on a real Linux box," and honestly? Best advice I got all semester. So grab your coffee (or chai, no judgment ☕), and let's walk through this together, step by step, exactly how I did it.

By the end of this post you'll have:

WSL running Ubuntu on your Windows machine
PostgreSQL installed and running
Your very own database user created
A clear understanding of that mysterious postgres default user everyone talks about
The ability to log into psql like a pro

Let's get into it.

Step 1: What Even Is WSL? (60-Second Explainer)

WSL stands for Windows Subsystem for Linux. It's basically Microsoft saying, "Hey, we know developers love Linux tools, so here's a real Linux kernel running right inside Windows — no dual boot, no clunky virtual machine, no drama."

For anyone learning backend development, databases, or DevOps, this is huge. Most real-world servers run Linux, so practicing in a Linux environment (even from your Windows laptop) is exactly the muscle memory you want to build.

Step 2: Installing WSL

This part is refreshingly simple these days. Microsoft made it a one-liner.

Open PowerShell as Administrator. (Right-click the Start button → "Terminal (Admin)" or search "PowerShell," right-click, "Run as administrator.")
Type this magic incantation:

wsl --install

Hit Enter and let it do its thing. This installs WSL2 (the good version, with a full Linux kernel) along with Ubuntu as the default distro.
Restart your computer when prompted. Yes, actually restart it — don't be that person who skips the restart and wonders why nothing works. 😅

Once your machine boots back up, Ubuntu should launch automatically. If it doesn't, just search "Ubuntu" in your Start Menu and open it.

First launch checklist:

It'll take a minute to finish setting up.
You'll be asked to create a UNIX username and password. This is your Ubuntu login — totally separate from your Windows account, and separate from PostgreSQL too (keep that in mind, it trips people up later).

💡 Pro tip: Pick a username you'll remember and a password you can type without staring at your keyboard. You'll use sudo a lot, and it'll ask for this password constantly.

Verify everything installed correctly by running this in PowerShell:

wsl --list --verbose

You should see Ubuntu listed with VERSION 2. If you see version 1, don't panic — but it's worth upgrading (wsl --set-version Ubuntu 2) since WSL2 is faster and more compatible.

Step 3: Updating Ubuntu (Don't Skip This!)

Inside your Ubuntu terminal, run:

sudo apt update && sudo apt upgrade -y

This refreshes the package lists and updates existing software. Think of it as Ubuntu clearing its throat before doing real work. It might take a few minutes — perfect time to stretch or refill that coffee.

Step 4: Installing PostgreSQL

Now for the main event. PostgreSQL is one of the most powerful, most respected open-source relational databases out there — used by companies from startups to giants like Instagram and Spotify.

Install it with:

sudo apt install postgresql postgresql-contrib -y

postgresql is the core database engine.
postgresql-contrib adds handy extra modules and extensions you'll likely want later (things like additional data types and functions).

Once it's done installing, start the PostgreSQL service:

sudo service postgresql start

And check that it's actually running:

sudo service postgresql status

You should see something confirming it's active. If it's not running for any reason, sudo service postgresql start will get it going.

⚠️ Note for WSL users: Unlike a real Linux server, PostgreSQL won't auto-start when you open a new WSL session — you'll need to run that service postgresql start command each time you reopen your terminal (or set up a startup script, but that's a story for another day).

Step 5: Meet `postgres` — The Default User Everyone's Confused About

Here's where a lot of students get tripped up, so let's clear the fog. 🌫️

When PostgreSQL installs, it automatically creates:

A Linux system user called postgres
A PostgreSQL database role also called postgres

This postgres role is the superuser of your database — it has full administrative rights over everything: creating databases, creating other users, deleting things, all of it. Think of it like the "root" or "admin" account of your PostgreSQL world.

By default, PostgreSQL is set up so that only the Linux postgres user can log in as the postgres database role, using something called "peer authentication" — basically, PostgreSQL checks "hey, is your Linux username the same as the database username you're trying to log in as?" If yes, you're in, no password needed (locally, at least).

This is why you'll often see people typing this to get into the database for the first time:

sudo -i -u postgres

This switches you (temporarily) into the Linux postgres user, which then lets you access the database postgres role seamlessly.

Step 6: Logging Into `psql` for the First Time

psql is PostgreSQL's command-line interface — your direct line of communication with the database engine. Let's log in.

sudo -i -u postgres
psql

If everything worked, your terminal prompt should now look like this:

postgres=#

That # symbol means you're logged in as a superuser. Congrats — you're officially inside PostgreSQL! 🎉

To exit psql at any time, type:

\q

Step 7: Creating Your Own User (Because You Shouldn't Live as `postgres` Forever)

Working as the superuser for everything isn't great practice — even for personal projects. Let's create a proper user for yourself.

While still logged into psql as postgres, run:

CREATE USER student_dev WITH PASSWORD 'yourStrongPassword123';

Replace student_dev with whatever username you like, and obviously pick a real password (not literally that one 😉).

Want to give this user the ability to create their own databases too? Add that privilege:

ALTER USER student_dev CREATEDB;

Now let's create a database owned by this new user:

CREATE DATABASE student_dev_db OWNER student_dev;

Exit psql:

\q

And exit back out of the postgres Linux user:

exit

Step 8: Logging In as Your New User

Now the moment of truth — let's log in as you, not as the almighty postgres.

psql -U student_dev -d student_dev_db -h localhost

Here's what those flags mean:

-U → the username you want to log in as
-d → the database you want to connect to
-h localhost → connect over the network (even though it's local), which forces PostgreSQL to ask for a password instead of relying on peer authentication

Enter the password you set earlier, and boom — you're in as your own user, scoped to your own database. This is the setup you'll actually want for real projects.

Quick Recap Cheat Sheet 📝

Task	Command
Install WSL + Ubuntu	`wsl --install`
Update Ubuntu	`sudo apt update && sudo apt upgrade -y`
Install PostgreSQL	`sudo apt install postgresql postgresql-contrib -y`
Start PostgreSQL	`sudo service postgresql start`
Switch to postgres Linux user	`sudo -i -u postgres`
Open psql	`psql`
Create new DB user	`CREATE USER name WITH PASSWORD 'pass';`
Create new database	`CREATE DATABASE dbname OWNER name;`
Log in as your user	`psql -U name -d dbname -h localhost`
Exit psql	`\q`

Final Thoughts

Honestly, once I stopped fighting Windows-native installers and just leaned into WSL, everything clicked. It genuinely feels like working on a real Linux server — which, as a student prepping for internships and real-world dev work, is exactly the experience worth building.

Next up on my list: connecting this PostgreSQL instance to a Node.js or Django app, and maybe exploring pgAdmin for a GUI view of everything we just built by hand. If that sounds interesting, let me know and I'll write that one up next!

Until then — happy querying! 🐘💻

Got stuck somewhere or found a better way to do this? Drop a comment below — half the fun of learning this stuff is comparing notes with fellow students.

First Interactions with Data Governance and Security

John M. Kuria — Thu, 03 Sep 2026 16:48:49 +0000

Week 2, Day 1 - Data Governance and Security

Month 1: Foundations of Data Engineering
Week 2: Core Concepts
Monday

Before we get into it

I need to say this out loud: I am genuinely relieved to be sitting down and taking notes instead of staring at a terminal today.

Last week was a full week of installing things and immediately breaking them. WSL refused to start twice. Postgres wouldn't accept connections until we figured out that pg_hba.conf was still set to localhost only. Then there was the fun afternoon of changing permissions on database users because a query kept failing with a permission denied error that made no sense until someone pointed out the user had never been granted rights on the schema, only the database. By Friday my hands knew sudo systemctl restart postgresql better than they knew my own phone number.

So walking in today and being told "grab your notebook, no terminal needed" felt like a genuine reward. This is the first theoretical day of the program, and the topic is data governance and security. After a week of learning how databases actually get built and secured on a machine, it makes sense that today is about the rules and thinking behind why we secure them that way in the first place.

What we're covering today

What data governance actually is, and why it is not just a compliance buzzword
The core pillars of governance: quality, cataloguing, lineage, stewardship
Data security principles: the CIA triad, defense in depth, zero trust, privacy by design
How data gets classified and managed across its lifecycle
The big compliance frameworks: GDPR, HIPAA, PCI-DSS, and Kenya's PDPA
Access control models and the principle of least privilege
How governance, security, and compliance actually connect to each other

1. What is data governance, really?

Data governance is the set of rules, policies, and processes that decide how data is collected, stored, used, and protected across an organization.

Put simply, it answers questions like:

Who owns this data?
Who is allowed to see it?
How long do we keep it?
How do we know it is accurate?
Where did it come from?
What happens if it gets misused or lost?

Why this matters to me as a future data engineer

Last week I was the one creating database users and deciding what privileges they got. That is access control in miniature. Multiply that by an entire company, with hundreds of tables, dozens of pipelines, and multiple teams, and you start to see why governance is not optional. If data goes missing or the wrong person sees something sensitive, there is a good chance a pipeline someone built is where it happened. That someone is eventually going to be me.

2. The four pillars of data governance

Pillar	What it means
Data quality	Making sure data is accurate, complete, consistent, and current
Data cataloguing	Keeping a record of what data exists, where it lives, and what it means
Data lineage	Tracking where data came from and how it changed as it moved
Data stewardship	Assigning real people who are responsible for maintaining data standards

A quick way I'm remembering this: quality is "is the data good," cataloguing is "do we know it exists," lineage is "do we know its history," and stewardship is "does someone actually own it." Without all four, a company ends up with three versions of the same customer and no idea which one is correct.

3. Data security: the technical backbone of governance

If governance is the rulebook, security is what actually enforces it. Today we broke security down into a few core principles.

The CIA triad

Confidentiality: only the right people and systems can see the data. This is enforced through access controls, authentication, and encryption.
Integrity: data stays accurate and unaltered unless someone with permission changes it. Checksums, audit trails, and digital signatures help here.
Availability: data is there when it is needed. This is where backups, disaster recovery, and uptime planning come in.

Defense in depth

This is the idea that you never rely on a single layer of protection. Instead you stack them:

Physical security (secure data centers, restricted facility access)
Network security (firewalls, VPNs, network segmentation)
Application security (secure coding, input validation, authentication)
Data security (encryption, classification, access permissions)

If one layer fails, the next one is still standing. It reminded me a lot of last week's pg_hba.conf and postgresql.conf split: one file controls how the server behaves, the other controls who is allowed through the door. Neither one alone is enough.

Zero trust

The mindset here is "never trust, always verify." No user or system gets automatic trust just because it is inside the network. Every request gets checked, every time. This usually shows up as multi-factor authentication, conditional access policies, and continuous monitoring rather than a single login at the start of the day.

Privacy by design

Instead of bolting privacy on at the end, it gets built in from the start. The idea is that privacy should be the default setting, not something a user has to dig through menus to switch on.

4. Encryption: protecting data at rest and in transit

State	Meaning	Example
At rest	Data sitting in storage	Rows in a Postgres table, files in a bucket
In transit	Data moving between systems	An API response flowing into a pipeline

All the major cloud platforms encrypt by default, but it is still on the data engineer to verify it and manage the keys correctly. That's where a Key Management Service (KMS) comes in: it is the system that creates, stores, and controls the encryption keys, basically the safe that holds the vault combination.

5. Access control and the principle of least privilege

Access control decides who can do what with which data. The golden rule is the principle of least privilege: give every person and system only the minimum access they need to do their job, nothing more.

This one actually landed for me because of last week. When we created database users and had to explicitly GRANT privileges on a schema before anything worked, that was least privilege in action. Nobody gets access by default. You earn it, deliberately, one grant at a time.

Common access control models

Model	How it works	Example
RBAC (Role-Based)	Access is tied to a role	All analysts get read-only access
ABAC (Attribute-Based)	Access depends on attributes like department or location	Only the Nairobi team can see Nairobi sales data
DAC (Discretionary)	The data owner decides who gets access	A team lead grants access to their own folder

6. Data classification and the data lifecycle

Not all data carries the same risk, so it gets classified based on sensitivity:

Public: no harm if disclosed
Internal: minor harm if disclosed
Confidential: significant harm if disclosed
Restricted: severe harm if disclosed

There is also classification by regulation, like Personally Identifiable Information (PII), Protected Health Information (PHI), and payment card data.

Data also moves through a lifecycle, and governance applies differently at each stage:

Creation and collection: define why the data is being collected and apply classification early
Processing and use: enforce access controls based on that classification
Storage and retention: keep it only as long as necessary, with appropriate security
Sharing and disposal: assess risk before sharing, and dispose of it securely once it is no longer needed

7. Compliance frameworks

Compliance is what makes governance and security legally enforceable. A few frameworks came up repeatedly today.

GDPR (General Data Protection Regulation)

Applies to anyone processing the personal data of EU citizens, regardless of where the company is based
Effective since May 2018
Grants rights like access, correction, erasure ("right to be forgotten"), and data portability
Breaches must be reported within 72 hours
Fines can reach 20 million euros or 4 percent of global annual revenue, whichever is higher

HIPAA (Health Insurance Portability and Accountability Act)

US-only, and only applies to Protected Health Information (PHI)
Built around three rules: Privacy, Security, and Breach Notification
Requires administrative, physical, and technical safeguards
Penalties can reach into the millions per year depending on severity

PCI-DSS

Global standard for anyone handling credit card data. It sets requirements around encryption, access control, and regular security testing.

PDPA (Kenya's Data Protection Act, 2019)

Since we are training in Kenya, this one is directly relevant. It mirrors a lot of GDPR's principles and is enforced by the Office of the Data Protection Commissioner (ODPC). Any system handling Kenyan citizens' personal data has to comply with it, which means it will realistically apply to a lot of what we build in this program.

A quick way to keep GDPR and HIPAA straight

GDPR is the speed limit that applies everywhere you drive with an EU citizen in the car. HIPAA is the special rule that only applies inside the hospital parking lot. GDPR is broad and industry-agnostic. HIPAA is narrow and healthcare-specific.

8. How governance, security, and compliance connect

Data Governance
    -> defines the rules and policies for the data

Data Security
    -> implements the technical controls that enforce those rules

Compliance
    -> confirms those rules and controls meet legal requirements

Governance is the rulebook. Security is the locks and guards. Compliance is the inspector checking the locks actually meet the law.

9. Key terms from today

Term	Meaning
Data governance	Rules and processes controlling how data is managed and used
Data lineage	A record of where data came from and how it changed
Data catalogue	A directory of what data exists and what it means
CIA triad	Confidentiality, Integrity, Availability
Defense in depth	Layered security controls across physical, network, application, and data layers
Zero trust	Never trust automatically, always verify every request
KMS	Key Management Service, used to manage encryption keys
Principle of least privilege	Give the minimum access needed, nothing more
RBAC	Role-Based Access Control
GDPR	EU regulation protecting personal data of EU citizens
HIPAA	US regulation protecting patient health data
PDPA	Kenya's Data Protection Act, 2019
PCI-DSS	Security standard for payment card data
Audit log	A tamper-proof record of who accessed or changed data, and when

10. What I'm taking away from today

Last week gave me the hands, this week is starting to give me the reasons. Every GRANT, every edit to pg_hba.conf, every password I set for a database user last week was actually a small act of governance and security without me fully realizing it at the time. Today just put labels on things I was already doing instinctively.

Homework to myself tonight: go back through last week's terminal history and map each command to a concept from today. CREATE USER and GRANT are access control. Editing postgresql.conf and pg_hba.conf is defense in depth. Setting a password is confidentiality. It is all the same picture, just seen from two different angles this time.

See you in the next class.

Notes by [Your Name] | Month 1, Foundations of Data Engineering | Week 2, Day 1

Data Engineering, Day 2: The Day the Terminal Stopped Feeling Like a Foreign Country

John M. Kuria — Wed, 26 Aug 2026 07:57:04 +0000

Day one was about learning to walk around the house. Day two was about actually turning the lights on and living in it.

I won't lie — I came into class still tired from yesterday, still slightly proud of myself for typing sudo correctly on the first try. And then day two happened, and it was a lot. Vim modes. A whole database engine. Remote connections. By the end of it my brain felt like a browser with forty tabs open. But it's the good kind of tired — the kind where you can actually feel yourself becoming someone who can do this job, one command at a time.

Here's everything we covered, with the actual commands, because future-me is going to need to come back and re-read this more than once.

1. Logging Into the Server Remotely, Again — But It's Starting to Click

We picked up right where day one left off: SSH-ing into our Ubuntu server.

ssh username@server_ip_address

Yesterday this felt like magic I didn't fully trust. Today it just felt like... opening a door. That's the moment you know something is starting to sink in — when the "wow" wears off and it just becomes muscle memory.

2. Creating Our Own Linux User, Setting a Password, Switching Between Users

Everyone in class created their own personal Linux user account on the server — no more sharing one login.

sudo adduser myusername

Setting or resetting a password for that user:

sudo passwd myusername

And then the part that actually made the concept of "users" feel real — switching between accounts on the same machine:

su - myusername

Typing su - anotheruser and suddenly being that person, with their own home directory and their own permissions, made something click. A server isn't one identity. It's a shared space with clearly separated rooms, and Linux is very serious about who's allowed in which room.

3. Meeting Vim (And Immediately Getting Humbled By It)

If you've never used Vim before, the first thing that happens is panic. You open a file, try to type, nothing happens, and you briefly wonder if you've broken the server. You haven't. You just don't know the modes yet.

Opening a file in Vim:

vim myfile.txt

The Four Modes We Learned

Normal mode — this is where you land the moment Vim opens. You're not typing text here; you're navigating and issuing commands. Pressing letters like h, j, k, l moves the cursor. This is the default, home-base mode.

Insert mode — this is where actual typing happens. You get here by pressing:

from Normal mode. Once you're in Insert mode, you type like you would in any normal text editor. You get back to Normal mode by pressing Esc.

Visual mode — for selecting text, the way you'd click-and-drag with a mouse anywhere else. Entered with:

From there, moving the cursor highlights text, which you can then delete, copy, or edit as a block.

Command-line mode — this is where the real power lives: saving, quitting, searching, and replacing. Entered by pressing : from Normal mode, which drops a colon prompt at the bottom of the screen.

Saving and Exiting (The Command Everyone Googles at Least Once)

From Normal mode, pressing : and then typing:

:w

saves the file. Typing:

:q

quits Vim. And the one everyone actually needs on day one — save and quit in one motion:

:wq

If you've made a mess and just want out without saving anything:

:q!

That last one saved me today. No shame in it.

4. Basic Linux Navigation Commands, Practiced Until They Felt Boring (In a Good Way)

We drilled the fundamentals again, and repetition is genuinely making them automatic now:

ls          # list what's in the current folder
cd projects # move into the "projects" folder
pwd         # print exactly where you are right now
mkdir data  # create a new folder called "data"
touch notes.txt   # create a new, empty file
cat notes.txt     # print the contents of a file to the screen
whoami      # confirm which user you're currently logged in as
clear       # wipe the terminal screen
sudo apt update    # run a command with administrator privileges
su - anotheruser   # switch to a different user account

Small, unglamorous commands. But every single one of them showed up again later in the day, embedded inside bigger tasks — which is exactly when you realize why the fundamentals matter so much.

5. Installing PostgreSQL and Checking That It's Actually Running

This is where the day shifted from "operating system basics" into genuinely feeling like data engineering.

Installing PostgreSQL on the Ubuntu server:

sudo apt update
sudo apt install postgresql postgresql-contrib

Checking whether the service is actually running:

sudo systemctl status postgresql

Watching that status come back "active (running)" for the first time felt disproportionately exciting for something so small. But it was the first real database engine I've ever installed with my own hands, not a hosted service someone else set up for me.

6. Controlling the PostgreSQL Service with systemctl

We learned the full lifecycle of managing the service, not just turning it on and hoping for the best:

sudo systemctl start postgresql     # start the service
sudo systemctl stop postgresql      # stop the service
sudo systemctl restart postgresql   # restart the service (needed after config changes)
sudo systemctl status postgresql    # check current status

restart became the command I'd use most, since almost every configuration change later in the day required PostgreSQL to reload before it would take effect.

7. Creating a Database, a Database User, and a Password

First, we switched into the built-in postgres system account, which has admin rights inside PostgreSQL itself:

sudo -i -u postgres
psql

From inside the psql prompt, we created a new database:

CREATE DATABASE mydatabase;

Then a new database user, with a password:

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

And gave that user full rights over the database we just created:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

There's a genuine sense of ownership that comes from typing CREATE DATABASE and having it just... exist. Something I've queried a hundred times from the outside, and today I built one from the inside.

8. Getting to Know the Configuration Files: postgresql.conf and pg_hba.conf

This is the part that felt the most "real" — the two files that quietly control everything about how PostgreSQL behaves and who's allowed to talk to it.

Opening the main configuration file with Vim:

sudo vim /etc/postgresql/*/main/postgresql.conf

Inside, the key setting we needed was listen_addresses, which controls which network interfaces PostgreSQL accepts connections on. By default it's often locked to localhost, meaning nothing outside the server itself can reach it. We changed it to:

listen_addresses = '*'

Then the client authentication file, which controls who is allowed to connect and how:

sudo vim /etc/postgresql/*/main/pg_hba.conf

Here we added a line allowing remote connections from outside the server, authenticated with a password:

host    all             all             0.0.0.0/0               md5

Editing these two files back to back really underlined the point our instructor kept making: postgresql.conf controls how the server behaves, and pg_hba.conf controls who's allowed through the door. Two very different jobs, two very different files.

9. Restarting PostgreSQL So the Changes Actually Took Effect

None of those edits do anything until the service reloads:

sudo systemctl restart postgresql

This is the step it's easy to forget in the moment — you edit a config file, feel accomplished, and then wonder why nothing changed. Nothing changes until you restart the service.

10. Connecting Remotely with DBeaver and Power BI

This was the payoff for the entire day. After all the SSH, Vim, and config file editing, we opened DBeaver on our own laptops and connected straight into the PostgreSQL database sitting on the remote Ubuntu server — using the database name, username, and password we'd created ourselves earlier:

Host: server_ip_address
Port: 5432
Database: mydatabase
Username: myuser
Password: mypassword

Then we plugged the exact same connection details into Power BI, pulling that same PostgreSQL database in as a live data source.

Seeing our own database — one we built from an empty terminal, line by line — show up as a connectable data source in a tool like Power BI was the moment the whole day clicked into place. It wasn't abstract anymore. It was infrastructure we'd actually built, being used the way real infrastructure gets used.

What I'm Taking Away From Today

Yesterday was about learning to move around a Linux system. Today was about realizing that Linux is just the floor you stand on to build the things that actually matter — like a working database that other tools can plug into.

The instructor's closing note stuck with me: these fundamentals aren't a box to check once and move on from. Vim, navigation commands, PostgreSQL users and databases, pg_hba.conf, postgresql.conf — we're going to be living inside these for the rest of the program.

So tonight's homework to myself is simple: open Vim again, on purpose, just to practice switching modes until :wq stops feeling like a spell I'm reciting from memory and starts feeling like my own handwriting.