Skip to content

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 ​

TableSELECTINSERTUPDATEWhy
government_agenciesYesYesYesLogin, profile, create/edit agencies
reportsYes—YesView reports, update status, assign agency
report_status_historyYesYes—Create status change entries
report_commentsYesYes—Add official comments
two_factor_codesYesYesYes2FA login flow
two_factor_attemptsYesYes—Rate limit login attempts
locationsYes——Display report locations
usersYes——Show reporter names
report_imagesYes——Show report photos
hedera_transactionsYes——Blockchain verification
ai_analysis_logsYes——AI analysis results
app_settingsYes——System settings viewer
citizen_feedbackYes——View citizen satisfaction ratings

Blocked Operations ​

OperationStatus
DELETE on any tableBlocked
TRUNCATE on any tableBlocked
DROP tablesBlocked
CREATE tables/schemasBlocked
Access user_sessionsBlocked
Access otp_verificationsBlocked
Access notificationsBlocked
Access activity_logsBlocked

Connection Limits ​

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

Quick Start ​

1. Review and Customize the Script ​

bash
# 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 ​

bash
# Generate a random password
openssl rand -base64 24

3. Run the Script ​

bash
# 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 ​

bash
# 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 ​

bash
# 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 ​

bash
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 ​

bash
# .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 ​

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

View Permissions ​

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

Monitor Connections ​

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

Kill Idle Connections ​

sql
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) ​

sql
-- 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 ​

UserApplicationAccess Level
lafaekstreet_apiBackend (FastAPI)Full CRUD on all tables
lafaekstreet_publicPublic DashboardRead-only with RLS
lafaekstreet_govportalGov Portal (Next.js)Read + limited write

Built for Timor-Leste

Built for Timor-Leste