Setting Up PostgreSQL
Creating Databases and Users
Writing DDL statements to create database targets and user accounts.
Interview: Database creation and user management are fundamental DBA skills. Interviewers test whether you understand role-based access, database isolation, and the principle of least privilege.
1. Introduction
Every application needs its own database and dedicated database user. This topic covers creating databases, managing user roles, and setting up the correct permissions — foundational skills for any backend engineer or DBA.
2. Why It Matters
Database creation and user management are fundamental DBA skills. The principle of least privilege — giving each application only the permissions it needs — is a critical security practice. A compromised application with superuser access can destroy the entire database cluster.
3. Real-World Analogy
Creating a database and user is like setting up a new employee in a company. You give them a dedicated workspace (database), a keycard with access only to relevant floors (permissions), and never hand them the master key (superuser). If they change roles, you adjust their access — you don't give them access to every room.
4. How It Works
PostgreSQL's hierarchy has four levels:
- Cluster: A PostgreSQL server managing a collection of databases.
- Database: An isolated namespace. Tables and indexes in one database are invisible to others.
- Schema: Within each database, objects are organized into schemas (like folders). Default:
public. - Role/User: A unified role system. A "user" is a role with LOGIN privilege.
5. Internal Architecture
When you run CREATE DATABASE, PostgreSQL copies a template database (template1 by default) including all system catalogs, extensions, and settings. The new database gets its own data directory, WAL files, and connection namespace.
6. Visual Explanation
7. Practical Example
Complete database and user setup for a web application:
8. Common Mistakes
Common Pitfall
You cannot drop a database while other sessions are connected to it. Before dropping, terminate connections: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';
Interview Insight
The principle of least privilege is critical: a web application should never connect as a superuser. Create a dedicated role with permissions limited to the specific tables and operations the application requires.
- Using postgres superuser for apps: A compromised app with superuser access can destroy the entire cluster.
- Forgetting ALTER DEFAULT PRIVILEGES: New tables created after the initial GRANT won't inherit permissions.
- Not setting connection limits: A runaway app can exhaust all connections. Use ALTER ROLE ... CONNECTION LIMIT.
9. Quick Quiz
Q1: What's the difference between template0 and template1?
template1 is the default template and can be customized with common extensions. template0 is pristine and unmodified — use it when you need a completely clean database.
Q2: What's the difference between CREATE ROLE and CREATE USER?
CREATE USER is equivalent to CREATE ROLE ... LOGIN. Both create a role, but CREATE USER adds the LOGIN attribute by default.
10. Scenario-Based Challenge
Scenario
Your team has three microservices (auth-service, order-service, analytics-service). Each needs database access with different permission levels. Design the role hierarchy. Which services should share a database? Which need separate ones?
11. Debugging Exercise
Your application gets this error after creating new tables via migration:
Fix: You forgot ALTER DEFAULT PRIVILEGES. The GRANT was applied to existing tables but not future ones. Run: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
12. Interview Questions
Q: Why should each application have its own database?
Database isolation prevents coupling between applications. Shared databases create migration conflicts, permission entanglement, and make it impossible to scale or deploy services independently.
Q: What role attributes should you avoid in production?
SUPERUSER (bypasses all permission checks), CREATEROLE (can create new admin users). Only the initial admin account should have these. Application roles need only LOGIN.
Q: How do you safely drop a database in use?
First terminate all connections (pg_terminate_backend), then DROP DATABASE. Alternatively, restrict connections first: ALTER DATABASE mydb ALLOW_CONNECTIONS false;
13. Production Considerations
- Separate environments: Use separate databases for dev, test, staging, and production (e.g., myapp_dev, myapp_test, myapp_prod).
- Connection pooling: In production, use PgBouncer between your application and PostgreSQL to manage connection limits efficiently.
- Password rotation: Use VALID UNTIL to enforce password expiration policies. Rotate credentials via ALTER ROLE ... PASSWORD.
- Backup strategy: Schedule regular pg_dump backups. Test restore procedures — an untested backup is not a backup.
Use Cases
Application setup: Creating a dedicated database and user for each microservice or application, following the principle of least privilege
CI/CD pipelines: Programmatically creating test databases and users during integration test setup, then dropping them after tests complete
Team development: Setting up role-based access so developers have read-write access while analytics tools get read-only access
Common Mistakes
Using the postgres superuser for application connections — a compromised application with superuser access can destroy the entire cluster
Forgetting ALTER DEFAULT PRIVILEGES — without it, every new table requires manual GRANT statements, which are easily forgotten during migrations
Not setting connection limits — a runaway application can exhaust all available connections. Use ALTER ROLE ... CONNECTION LIMIT to cap connections per role