Why database security matters in PostgreSQL - See It in Action

Why Database Security Matters

📖 Scenario: You are working as a database administrator for a small company. The company stores customer information in a PostgreSQL database. You want to understand why database security is important and how to protect sensitive data.

🎯 Goal: Build a simple PostgreSQL setup that includes a table with sensitive data, a role with limited permissions, and a query that demonstrates controlled access to the data.

📋 What You'll Learn

Create a table called customers with columns id, name, and email

Insert three specific customer records into the customers table

Create a role called readonly_user with SELECT permission only on the customers table

Write a query to select all customer names and emails using the readonly_user role

💡 Why This Matters

🌍 Real World

Companies must protect sensitive customer data from unauthorized access to comply with laws and maintain trust.

💼 Career

Database administrators and developers use roles and permissions to secure databases and control who can see or change data.

1

Create the customers table

Write a SQL statement to create a table called customers with three columns: id as an integer primary key, name as text, and email as text.

PostgreSQL

-- Create the customers table with id, name, and email columns
-- Your code here

Need a hint?

Use CREATE TABLE with column definitions. Remember to set id as the primary key.

2

Insert customer records

Insert these three customers into the customers table: (1, 'Alice Johnson', '[e-mail]'), (2, 'Bob Smith', '[e-mail]'), and (3, 'Carol White', '[e-mail]').

PostgreSQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);
-- Insert three customer records below
-- Your code here

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Create a read-only role

Create a role called readonly_user and grant it SELECT permission on the customers table only.

PostgreSQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice Johnson', '[e-mail]'),
(2, 'Bob Smith', '[e-mail]'),
(3, 'Carol White', '[e-mail]');

-- Create readonly_user role and grant SELECT permission
-- Your code here

Need a hint?

Use CREATE ROLE to make the role, then GRANT SELECT ON customers TO readonly_user to give read-only access.

4

Query customer data as readonly_user

Write a SQL query to select the name and email columns from the customers table. Assume you are connected as readonly_user to demonstrate controlled access.

PostgreSQL

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice Johnson', '[e-mail]'),
(2, 'Bob Smith', '[e-mail]'),
(3, 'Carol White', '[e-mail]');

CREATE ROLE readonly_user LOGIN;
GRANT SELECT ON customers TO readonly_user;

-- Select name and email from customers as readonly_user
-- Your code here

Need a hint?

Use a simple SELECT statement to get the name and email columns.