LafaekStreet Government Portal Database User Setup

This directory contains the SQL script to create a dedicated PostgreSQL user for the Government Portal (lafaekstreet_govportal) with minimal required permissions.

---

Purpose

Instead of using the postgres root user or sharing the API user, the Gov Portal gets its own dedicated lafaekstreet_govportal user with only the permissions it needs. This follows the principle of least privilege.

---

What This User Can Do

Allowed Operations

Table	SELECT	INSERT	UPDATE	Why
government_agencies	Yes	Yes	Yes	Login, profile, create/edit agencies
reports	Yes	—	Yes	View reports, update status, assign agency
report_status_history	Yes	Yes	—	Create status change entries
report_comments	Yes	Yes	—	Add official comments
two_factor_codes	Yes	Yes	Yes	2FA login flow
two_factor_attempts	Yes	Yes	—	Rate limit login attempts
locations	Yes	—	—	Display report locations
users	Yes	—	—	Show reporter names
report_images	Yes	—	—	Show report photos
hedera_transactions	Yes	—	—	Blockchain verification
ai_analysis_logs	Yes	—	—	AI analysis results
app_settings	Yes	—	—	System settings viewer
citizen_feedback	Yes	—	—	View citizen satisfaction ratings

Blocked Operations

Operation	Status
DELETE on any table	Blocked
TRUNCATE on any table	Blocked
DROP tables	Blocked
CREATE tables/schemas	Blocked
Access user_sessions	Blocked
Access otp_verifications	Blocked
Access notifications	Blocked
Access activity_logs	Blocked

Connection Limits

• Max Connections: 30
• Statement Timeout: 30 seconds
• Idle Timeout: 5 minutes

---

Quick Start

1. Review and Customize the Script

# Open the script and change the password
nano Database/user/gov_portal/create_gov_portal_user.sql

# Change line 13:
# FROM: PASSWORD 'CHANGE_THIS_TO_STRONG_PASSWORD'
# TO:   PASSWORD 'YourStr0ng!P@ssw0rd#2026'

2. Generate a Strong Password

# Generate a random password
openssl rand -base64 24

3. Run the Script

# Run as PostgreSQL superuser
psql -U postgres -d lafaekstreet -f Database/user/gov_portal/create_gov_portal_user.sql

# OR if using Aiven (remote database)
psql "postgresql://avnadmin:PASSWORD@HOST:PORT/lafaekstreet?sslmode=require" \
  -f Database/user/gov_portal/create_gov_portal_user.sql

4. Update the Gov Portal .env.local

# Edit the Gov Portal environment file
nano lafaekstreet_govportal/.env.local

# Update DATABASE_URL:
DATABASE_URL=postgresql://lafaekstreet_govportal:YourPassword@HOST:PORT/lafaekstreet?sslmode=require

5. Test the Connection

# Test connection with new user
psql -U lafaekstreet_govportal -d lafaekstreet -c "SELECT current_user, current_database();"

# Expected output:
#      current_user       | current_database
# ------------------------+------------------
#  lafaekstreet_govportal | lafaekstreet

6. Restart the Gov Portal

cd lafaekstreet_govportal
npm run dev

---

Security Best Practices

Password Requirements

• Minimum 16 characters
• Mix of uppercase, lowercase, numbers, and symbols
• Do not reuse passwords from other database users
• Never commit passwords to git

Environment Variables

# .gitignore should include:
.env.local
*.env

# Use environment variables in production:
export DATABASE_URL="postgresql://lafaekstreet_govportal:PASSWORD@host:port/lafaekstreet?sslmode=require"

---

User Management

Change Password

-- As postgres/admin user
ALTER USER lafaekstreet_govportal WITH PASSWORD 'NewSecurePassword!';

View Permissions

SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE grantee = 'lafaekstreet_govportal'
ORDER BY table_name;

Monitor Connections

SELECT pid, usename, state, query_start
FROM pg_stat_activity
WHERE usename = 'lafaekstreet_govportal';

Kill Idle Connections

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'lafaekstreet_govportal'
AND state = 'idle'
AND state_change < NOW() - INTERVAL '10 minutes';

---

Remove User (If Needed)

-- Terminate all connections first
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'lafaekstreet_govportal';

-- Drop the user
DROP USER lafaekstreet_govportal;

---

All Database Users Summary

User	Application	Access Level
lafaekstreet_api	Backend (FastAPI)	Full CRUD on all tables
lafaekstreet_public	Public Dashboard	Read-only with RLS
lafaekstreet_govportal	Gov Portal (Next.js)	Read + limited write

---

Built for Timor-Leste