1/104
Vocabulary flashcard deck covering key topics in AWS IAM, Security Groups, RDS, SQL syntax, PostgreSQL features, database theory, transactions, and Docker fundamentals.
IAM
Identity and Access Management — a global AWS service used to manage users, groups, and permissions. It is NOT region-specific.
IAM Root Account
The account created automatically when you set up AWS. Best practice is to NOT use or share it, but to create admin users instead.
IAM User
A person within your AWS organization that has a set of credentials. Users can belong to zero or more groups.
IAM Group
A collection of IAM Users used to assign permissions to multiple users at once. Groups can only contain users — NOT other groups.
IAM Policy
A JSON document that defines permissions (allow/deny) for users, groups, or roles based on the least privilege principle.
Least Privilege Principle
A security concept: only grant users the minimum permissions they need to do their job — nothing more.
IAM Role
Permissions assigned to AWS services (not people) so those services can perform actions on your behalf (e.g., EC2 Instance Role, Lambda Function Role, CloudFormation Role).
Three Ways to Access AWS
AWS Access Keys
Credentials used to authenticate with the AWS CLI or SDK, where Access Key ID acts like a username and Secret Access Key acts like a password.
AWS CLI
An open-source tool to interact with AWS services via shell commands, providing direct access to public AWS APIs and allowing scripting for resource management.
AWS SDK
Software Development Kit consisting of language-specific libraries (Java, Python, JS, .NET, etc.) that let you access and manage AWS services programmatically from code.
IAM Security Audit Tools
IAM Best Practices
IAM Policy Inheritance
Policies assigned at the group level are inherited by all group members. Group-level assignment is best practice over inline user policies.
AWS Security Groups
Virtual firewalls that control inbound and outbound traffic for EC2 instances and RDS databases. They contain ONLY ALLOW rules (no explicit deny).
Security Group Regulations
Regulates: 1. Access to specific ports. 2. Authorized IP ranges (IPv4/IPv6). 3. Inbound traffic (external → instance). 4. Outbound traffic (instance → external).
Default Security Group Rules
All inbound traffic is BLOCKED by default. All outbound traffic is ALLOWED by default.
Timeout vs Connection Refused (Security Group)
Timeout indicates traffic is being blocked before reaching the instance (Security Group issue). Connection Refused indicates application error or non-running app (traffic reached instance but was rejected).
Security Group Multi-Instance Scope
One security group can be attached to multiple instances, and one instance can have multiple security groups. They are scoped to a specific region/VPC combination.
Security Group Location relative to EC2
Lives OUTSIDE the EC2 instance. If traffic is blocked by a security group, the EC2 instance never sees it.
Port 22
SSH (Secure Shell) — log into a Linux instance. Also used by SFTP (Secure File Transfer over SSH).
Port 21
FTP (File Transfer Protocol) — upload files to a file share.
Port 80
HTTP — access unsecured websites.
Port 443
HTTPS — access secured/encrypted websites.
Port 3389
RDP (Remote Desktop Protocol) — log into a Windows instance.
Port 5432
PostgreSQL default port, used when connecting to an RDS PostgreSQL instance.
AWS RDS
Relational Database Service — a managed relational database service (MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, Aurora) handling patching, backups, and failover.
Connecting to RDS
Requires server endpoint, master username, master password, port number, and the RDS security group allowing inbound TCP traffic on the database port.
Relational vs NoSQL Database
Relational: structured data in tables (rows & columns) using SQL (e.g., MySQL, PostgreSQL, Oracle). NoSQL: unstructured or semi-structured data with flexible schema (e.g., MongoDB, Cassandra, Redis).
Database Schema
A namespace inside a database that organizes and groups related tables, views, functions, and other objects (acts like a folder).
PostgreSQL Hierarchy
Database → Schema → Table → Columns → Rows. Schemas also contain Constraints, Indexes, Views, Functions, and Stored Procedures.
SET search_path
An ordered list of schemas PostgreSQL searches when an object is referenced without qualification (e.g., SET search_path TO billing, public;).
SQL Sublanguages
DDL (Data Definition: CREATE, ALTER, DROP, TRUNCATE), DML (Data Manipulation: INSERT, UPDATE, DELETE), DQL (Data Query: SELECT), DCL (Data Control: GRANT, REVOKE), TCL (Transaction Control: BEGIN, COMMIT, ROLLBACK, SAVEPOINT).
NOT NULL Constraint
Prevents a column from storing a NULL value; every row must contain a value for that column.
UNIQUE Constraint
Ensures all values in a column (or combination of columns) are distinct across all rows.
PRIMARY KEY Constraint
A combination of NOT NULL + UNIQUE that uniquely identifies every row in a table. A table can only have one primary key.
FOREIGN KEY Constraint
Links a column in one table to the PRIMARY KEY of another table, ensuring referential integrity.
ON DELETE CASCADE
A foreign key behavior where deleting a parent row automatically deletes all related child rows.
ON DELETE SET NULL
A foreign key behavior where deleting a parent row sets foreign key columns in child rows to NULL instead of deleting them.
CHECK Constraint
Ensures column values satisfy a specific condition, such as CHECK (age >= 18) or CHECK (UnitPrice > 0).
DEFAULT Constraint
Assigns a default value to a column when no value is specified during insertion (e.g., created_at TIMESTAMP DEFAULT NOW()).
CREATE INDEX
Creates an index on a table column, significantly speeding up data retrieval queries at the cost of slower writes and added storage.
First Normal Form (1NF)
Requires each column to contain only atomic (indivisible) values with no lists or sets in a single cell.
Second Normal Form (2NF)
Must be in 1NF AND every non-key column must be fully dependent on the entire primary key (no partial dependencies on a composite key).
Third Normal Form (3NF)
Must be in 2NF AND no non-key column should be transitively dependent on the primary key (non-key columns depend ONLY on the primary key).
Entity Relationship Diagram (ERD)
A visual blueprint of a database displaying entities (rectangles), attributes (ovals), relationships (diamonds), and keys (underlined attributes).
Cardinality Ratios (ERD)
Defines the maximum number of relationship instances an entity can participate in: 1:1, 1:N, N:1, or M:N.
Total vs Partial Participation (ERD)
Total Participation: every entity MUST participate in a relationship (double line). Partial Participation: some entities may not participate (single line).
Weak Entity Type
An entity lacking its own key attribute that depends on a strong owner entity for identification.
ACID Properties
Atomicity, Consistency, Isolation, Durability — guarantee reliable database transactions.
Atomicity (ACID)
A transaction is an indivisible unit: either ALL operations succeed, or NONE do (no partial updates).
Consistency (ACID)
A transaction moves the database from one valid state to another while adhering to all rules, constraints, and triggers.
Isolation (ACID)
Concurrent transactions do not interfere with each other, executing as if they ran sequentially.
Durability (ACID)
Once committed, transaction changes are permanent even during system failures or power outages.
PostgreSQL Numeric Types
SMALLINT (2 bytes), INTEGER/INT (4 bytes), BIGINT (8 bytes), NUMERIC(p,s)/DECIMAL (exact, used for money), REAL (4 bytes, non-exact), DOUBLE PRECISION (8 bytes, non-exact).
PostgreSQL String Types
TEXT (variable length, no length limit, preferred), VARCHAR(n) (variable length up to n characters), CHAR(n) (fixed length, space-padded, legacy).
PostgreSQL Date/Time Types
DATE (calendar date), TIME (time of day), TIMESTAMP (date + time), TIMESTAMPTZ (date + time + timezone in UTC, preferred).
UUID (PostgreSQL)
Universally Unique Identifier — a 128-bit value optimal for distributed systems, generated via gen_random_uuid() or uuid_generate_v4().
SERIAL vs UUID
SERIAL/BIGSERIAL: 4/8 bytes, auto-incrementing, fast indexing, NOT for distributed systems. UUID: 16 bytes, optional auto-gen, slower index, YES for distributed systems.
NULL in SQL
Represents UNKNOWN (not zero, empty string, or false). Checked using IS NULL or IS NOT NULL ('WHERE col = NULL' is incorrect).
JSONB vs JSON (PostgreSQL)
JSON stores raw text. JSONB stores binary JSON in a decomposed format, which is much faster to query and preferred.
DROP vs TRUNCATE
DROP removes structure + data. TRUNCATE removes all rows quickly while retaining structure. Neither supports a WHERE clause.
SELECT DISTINCT
Removes duplicate rows from a result set and can apply across single or multiple columns.
UNION vs UNION ALL
UNION combines SELECT results and removes duplicates. UNION ALL combines results while retaining duplicates (faster). Both require matching column counts and compatible types.
LIMIT and OFFSET
LIMIT caps the maximum returned rows; OFFSET skips a specified number of rows before returning results (used for pagination).
LIKE Operator
Pattern matching operator (% = 0+ chars, _ = exactly 1 char). Case-sensitive in PostgreSQL (use ILIKE for case-insensitive).
Aggregate Functions
Functions operating across rows returning a single value: COUNT(*), SUM(), AVG(), MIN(), MAX(). Used with GROUP BY and filtered with HAVING.
WHERE vs HAVING
WHERE filters rows BEFORE grouping/aggregation occurs. HAVING filters groups AFTER aggregation occurs.
Scalar Functions
Functions operating on each individual row returning one output per row (String: UPPER, LOWER, LENGTH, TRIM; Date: NOW, CURRENT_DATE, AGE, EXTRACT).
EXTRACT() Function
Retrieves specific fields (year, month, day, hour) from a timestamp (e.g., EXTRACT(YEAR FROM invoice_date)).
INTERVAL Type
Represents a duration of time supporting arithmetic (e.g., WHERE last_login < NOW() - INTERVAL '2 years').
INNER JOIN
Returns only rows that match in BOTH tables, excluding non-matching rows.
LEFT JOIN
Returns ALL rows from the left table plus matching rows from the right table (unmatched right columns return NULL).
RIGHT JOIN
Returns ALL rows from the right table plus matching rows from the left table.
FULL OUTER JOIN
Returns all rows from BOTH tables, filling in NULLs where no match exists on either side.
CROSS JOIN
Returns the Cartesian product of two tables (result size = rowsleft×rowsright) without join conditions.
JOINs vs UNIONs
JOINs combine COLUMNS (make tables wider); UNIONs combine ROWS (stack same-shaped results taller).
Subquery
A query nested inside another query to filter results based on the output of the inner query.
Common Table Expression (CTE)
A named temporary result set defined using WITH…AS that improves readability and reusability over subqueries.
User-Defined Function (UDF)
A reusable SQL/PL/pgSQL block that MUST return a value (even VOID), called via SELECT, and CANNOT perform TCL operations.
Stored Procedure (PostgreSQL)
A reusable block of SQL/PL/pgSQL logic called via CALL, where return values are OPTIONAL and TCL commands (BEGIN, COMMIT, ROLLBACK) ARE allowed.
Functions vs Stored Procedures
Functions: must return a value, no TCL allowed, called with SELECT. Stored Procedures: optional return value, TCL allowed, called with CALL.
PL/pgSQL
Procedural Language for PostgreSQL used to write in-database logic including IF/ELSE control flow, variables, loops, and error handling.
Database Index
A structure that speeds up query retrieval at the expense of extra storage and slower write operations.
B-Tree Index
The DEFAULT index type in PostgreSQL; a balanced tree efficient for =,
GIN Index
Generalized Inverted Index — optimal for JSONB data, arrays, and full-text search using operators like @> (contains).
GiST Index
Generalized Search Tree — optimal for ranges, overlapping data, and geospatial coordinates (e.g., PostGIS).
Partial Index
An index covering only a SUBSET of rows specified by a WHERE clause, saving space and improving speed.
Database View
A saved SELECT query acting as a virtual table that re-runs its underlying query each time it is accessed.
Database Trigger
An object that automatically fires a trigger function upon specified events (INSERT, UPDATE, DELETE, TRUNCATE) on a table.
BEFORE vs AFTER Triggers
BEFORE triggers execute pre-write (can modify NEW record, used for validation); AFTER triggers execute post-write (cannot modify record, used for auditing).
Trigger Variables (NEW vs OLD)
INSERT provides NEW only; UPDATE provides both OLD and NEW; DELETE provides OLD only.
Transaction Control Language (TCL)
Commands managing transaction boundaries: BEGIN (start), COMMIT (persist changes), ROLLBACK (undo changes), SAVEPOINT (checkpoint).
PostgreSQL Autocommit Mode
The default transaction mode where every individual DML statement is automatically wrapped in an implicit BEGIN/COMMIT.
SAVEPOINT
A named checkpoint in a transaction enabling selective rollback (ROLLBACK TO SAVEPOINT) without discarding the entire transaction.
MVCC
Multi-Version Concurrency Control — PostgreSQL's mechanism allowing concurrent reads and writes without blocking by maintaining row versions.
How MVCC Works
Updates create new row versions rather than overwriting existing ones. Transactions receive snapshots displaying only committed data prior to their start.
EXPLAIN Command
Displays PostgreSQL's estimated query execution plan (estimated cost, scan method, estimated row count) without running the query.
EXPLAIN ANALYZE Command
Actually EXECUTES a query and prints both the execution plan and real execution timing stats.
PostgreSQL Query Planner
Internal database engine component that calculates the optimal execution strategy (join type, index usage, filter order) for a query.