🔐 DATABASE USER SETUP - PUBLIC DASHBOARD (READ-ONLY)

📋 OVERVIEW

Create a dedicated read-only PostgreSQL user specifically for the LafaekStreet Public Dashboard. This user will have restricted permissions to only SELECT data from specific tables, ensuring security and preventing accidental or malicious data modification.

🎯 SECURITY PRINCIPLES

Why Read-Only User?

Security: Public dashboard cannot modify or delete data
Compliance: Follows principle of least privilege
Safety: Prevents SQL injection from causing data loss
Audit: Easy to track what public dashboard accesses
Performance: Can optimize for read queries only

Access Level

✅ ALLOWED:
- SELECT from public tables (reports, locations, citizen_feedback, etc.)
- SELECT from views (v_recent_reports, v_municipality_stats)
- EXECUTE on specific read-only functions

❌ DENIED:
- INSERT, UPDATE, DELETE on any table
- DROP, ALTER, TRUNCATE
- CREATE tables or views
- Access to sensitive tables (user_sessions, otp_verifications, two_factor_codes, two_factor_attempts)
- Access to admin-only data

📝 STEP-BY-STEP SETUP

1. Create Read-Only User

Create a file: Database/user/create_public_dashboard_user.sql

sql

-- ============================================
-- CREATE READ-ONLY USER FOR PUBLIC DASHBOARD
-- ============================================

-- 1. Create the user with a strong password
-- IMPORTANT: Change the password to a strong, unique value
CREATE USER lafaekstreet_public WITH PASSWORD 'CHANGE_THIS_TO_STRONG_PASSWORD';

-- 2. Grant CONNECT privilege to the database
GRANT CONNECT ON DATABASE lafaekstreet TO lafaekstreet_public;

-- 3. Grant USAGE on the public schema
GRANT USAGE ON SCHEMA public TO lafaekstreet_public;

-- ============================================
-- GRANT SELECT ON PUBLIC TABLES
-- ============================================

-- Core tables (public data only)
GRANT SELECT ON TABLE users TO lafaekstreet_public;
GRANT SELECT ON TABLE reports TO lafaekstreet_public;
GRANT SELECT ON TABLE locations TO lafaekstreet_public;
GRANT SELECT ON TABLE report_images TO lafaekstreet_public;
GRANT SELECT ON TABLE report_status_history TO lafaekstreet_public;
GRANT SELECT ON TABLE report_comments TO lafaekstreet_public;
GRANT SELECT ON TABLE hedera_transactions TO lafaekstreet_public;
GRANT SELECT ON TABLE government_agencies TO lafaekstreet_public;
GRANT SELECT ON TABLE ai_analysis_logs TO lafaekstreet_public;
GRANT SELECT ON TABLE app_settings TO lafaekstreet_public;

-- ============================================
-- DENY ACCESS TO SENSITIVE TABLES
-- ============================================

-- Explicitly revoke access to sensitive tables
REVOKE ALL ON TABLE user_sessions FROM lafaekstreet_public;
REVOKE ALL ON TABLE otp_verifications FROM lafaekstreet_public;
REVOKE ALL ON TABLE two_factor_codes FROM lafaekstreet_public;
REVOKE ALL ON TABLE two_factor_attempts FROM lafaekstreet_public;
REVOKE ALL ON TABLE notifications FROM lafaekstreet_public;
REVOKE ALL ON TABLE activity_logs FROM lafaekstreet_public;

-- ============================================
-- GRANT SELECT ON VIEWS
-- ============================================

GRANT SELECT ON v_recent_reports TO lafaekstreet_public;
GRANT SELECT ON v_municipality_stats TO lafaekstreet_public;
GRANT SELECT ON v_user_dashboard TO lafaekstreet_public;

-- Grant SELECT on materialized view
GRANT SELECT ON mv_dashboard_stats TO lafaekstreet_public;

-- ============================================
-- GRANT EXECUTE ON READ-ONLY FUNCTIONS
-- ============================================

-- Allow execution of specific read-only functions
GRANT EXECUTE ON FUNCTION find_nearby_reports(DECIMAL, DECIMAL, INTEGER, INTEGER) TO lafaekstreet_public;
GRANT EXECUTE ON FUNCTION search_reports(TEXT, report_status, issue_type, VARCHAR, INTEGER, INTEGER) TO lafaekstreet_public;

-- ============================================
-- GRANT USAGE ON SEQUENCES (for information only)
-- ============================================

-- Allow reading sequence values (but not modifying)
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO lafaekstreet_public;

-- ============================================
-- SET DEFAULT PRIVILEGES FOR FUTURE OBJECTS
-- ============================================

-- Automatically grant SELECT on new tables (if any are created)
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO lafaekstreet_public;

-- ============================================
-- REVOKE DANGEROUS PRIVILEGES
-- ============================================

-- Ensure user cannot create objects
REVOKE CREATE ON SCHEMA public FROM lafaekstreet_public;

-- Ensure user cannot modify data
REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public FROM lafaekstreet_public;

-- ============================================
-- ADD ROW-LEVEL SECURITY POLICIES (Optional but Recommended)
-- ============================================

-- Enable Row-Level Security on sensitive tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE reports ENABLE ROW LEVEL SECURITY;

-- Policy: Only show public reports
CREATE POLICY public_reports_policy ON reports
    FOR SELECT
    TO lafaekstreet_public
    USING (is_public = TRUE AND deleted_at IS NULL);

-- Policy: Only show non-deleted, active users (hide sensitive fields via view)
CREATE POLICY public_users_policy ON users
    FOR SELECT
    TO lafaekstreet_public
    USING (deleted_at IS NULL AND is_active = TRUE);

-- ============================================
-- CREATE SAFE VIEW FOR USER DATA (Hide Sensitive Info)
-- ============================================

-- Create a view that excludes sensitive user information
CREATE OR REPLACE VIEW v_public_users AS
SELECT
    id,
    full_name,
    -- Exclude: email, phone_number, password_hash, google_id, etc.
    preferred_language,
    total_reports,
    fixed_reports,
    pending_reports,
    created_at
FROM users
WHERE deleted_at IS NULL 
  AND is_active = TRUE;

-- Grant access to the safe view
GRANT SELECT ON v_public_users TO lafaekstreet_public;

-- Revoke direct access to users table (force use of view)
REVOKE SELECT ON TABLE users FROM lafaekstreet_public;

-- ============================================
-- VERIFICATION QUERIES
-- ============================================

-- Check user privileges
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'lafaekstreet_public'
ORDER BY table_name, privilege_type;

-- Check if user can connect
SELECT usename, usecreatedb, usesuper 
FROM pg_user 
WHERE usename = 'lafaekstreet_public';

-- ============================================
-- SUCCESS MESSAGE
-- ============================================

DO $$
BEGIN
    RAISE NOTICE '✅ Read-only user "lafaekstreet_public" created successfully!';
    RAISE NOTICE '📊 Granted SELECT on public tables';
    RAISE NOTICE '🔒 Revoked access to sensitive tables';
    RAISE NOTICE '👁️ Row-level security enabled';
    RAISE NOTICE '⚠️ IMPORTANT: Change the default password!';
END $$;

🔑 GENERATE STRONG PASSWORD

Use this command to generate a secure password:

bash

# Generate a 32-character random password
openssl rand -base64 32

# Example output:
# Kq7n9Lp2Jx8Wz5Yt6Rm3Vd4Hf1Gc0Bs==

Or use an online password generator:

https://passwordsgenerator.net/
Use 32+ characters
Include uppercase, lowercase, numbers, symbols

🚀 INSTALLATION STEPS

Step 1: Run the SQL Script

bash

# Connect as postgres superuser
psql -U postgres -d lafaekstreet

# Run the creation script
\i Database/user/create_public_dashboard_user.sql

# Verify user was created
\du lafaekstreet_public

Step 2: Update Password (CRITICAL!)

sql

-- Change password to your generated strong password
ALTER USER lafaekstreet_public WITH PASSWORD 'YOUR_STRONG_PASSWORD_HERE';

Step 3: Test Connection

bash

# Test connection with new user
psql -U lafaekstreet_public -d lafaekstreet -h localhost

# Try to SELECT (should work)
SELECT COUNT(*) FROM reports WHERE is_public = TRUE;

# Try to INSERT (should fail)
INSERT INTO reports (title) VALUES ('test'); -- ERROR: permission denied

# Try to UPDATE (should fail)
UPDATE reports SET title = 'test'; -- ERROR: permission denied

# Exit
\q

📝 DATABASE CONNECTION STRING

For Public Dashboard (.env)

bash

# Read-only connection for public dashboard
DATABASE_URL="postgresql://lafaekstreet_public:YOUR_STRONG_PASSWORD@localhost:5432/lafaekstreet?schema=public"

For Prisma (Public Dashboard)

Update lafaekstreet_dashboard/prisma/schema.prisma:

prisma

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Then in .env.local:

bash

DATABASE_URL="postgresql://lafaekstreet_public:Kq7n9Lp2Jx8Wz5Yt6Rm3Vd4Hf1Gc0Bs@localhost:5432/lafaekstreet?schema=public"

🔍 VERIFICATION CHECKLIST

Test Read Permissions (Should Work ✅)

sql

-- Connect as lafaekstreet_public
psql -U lafaekstreet_public -d lafaekstreet

-- Test SELECT queries
SELECT COUNT(*) FROM reports;
SELECT COUNT(*) FROM locations;
SELECT * FROM v_recent_reports LIMIT 10;
SELECT * FROM mv_dashboard_stats;

-- Test read-only functions
SELECT * FROM find_nearby_reports(-8.5569, 125.5603, 5000, 10);
SELECT * FROM search_reports('pothole', NULL, NULL, NULL, 20, 0);

Test Write Permissions (Should Fail ❌)

sql

-- All of these should return "permission denied" errors

-- INSERT
INSERT INTO reports (title) VALUES ('test');
-- ERROR:  permission denied for table reports

-- UPDATE
UPDATE reports SET title = 'test' WHERE id = 'xxx';
-- ERROR:  permission denied for table reports

-- DELETE
DELETE FROM reports WHERE id = 'xxx';
-- ERROR:  permission denied for table reports

-- TRUNCATE
TRUNCATE TABLE reports;
-- ERROR:  permission denied for table reports

-- DROP
DROP TABLE reports;
-- ERROR:  must be owner of table reports

-- CREATE
CREATE TABLE test_table (id INT);
-- ERROR:  permission denied for schema public

Test Sensitive Data Access (Should Fail ❌)

sql

-- Try to access sensitive tables
SELECT * FROM user_sessions;
-- ERROR:  permission denied for table user_sessions

SELECT * FROM email_verification_tokens;
-- ERROR:  permission denied for table email_verification_tokens

SELECT * FROM activity_logs;
-- ERROR:  permission denied for table activity_logs

-- Try to access user emails/passwords directly
SELECT email, password_hash FROM users;
-- ERROR:  permission denied for table users

📊 ACCESSIBLE DATA SUMMARY

✅ PUBLIC DASHBOARD CAN ACCESS:

Table/View	Access Level	Purpose
reports	SELECT (RLS)	Public reports only
locations	SELECT	Report locations
report_images	SELECT	Report photos
report_status_history	SELECT	Status timeline
report_comments	SELECT	Public comments
hedera_transactions	SELECT	Blockchain verification
government_agencies	SELECT	Agency information
ai_analysis_logs	SELECT	AI analysis results
citizen_feedback	SELECT	Citizen satisfaction ratings
report_embeddings	SELECT	Similarity search vectors
v_recent_reports	SELECT	Recent reports view
v_municipality_stats	SELECT	Municipality statistics
mv_dashboard_stats	SELECT	Cached dashboard stats
v_public_users	SELECT	Safe user info (no email/phone)
find_nearby_reports()	EXECUTE	Nearby reports function
search_reports()	EXECUTE	Search function

❌ PUBLIC DASHBOARD CANNOT ACCESS:

Table/View	Reason
users (direct)	Contains emails, passwords
user_sessions	Contains tokens, IP addresses
otp_verifications	Contains verification codes
two_factor_codes	Contains 2FA codes
two_factor_attempts	Contains 2FA attempt logs
notifications	User-specific notifications
activity_logs	Audit trail

🔒 SECURITY BEST PRACTICES

1. Password Management

bash

# Store password in environment variable (not in code!)
export DB_PUBLIC_PASSWORD="your-strong-password"

# Use in connection string
DATABASE_URL="postgresql://lafaekstreet_public:${DB_PUBLIC_PASSWORD}@localhost:5432/lafaekstreet"

2. Connection Pooling

typescript

// lib/db-public.ts
import { Pool } from 'pg';

const pool = new Pool({
  user: 'lafaekstreet_public',
  password: process.env.DB_PUBLIC_PASSWORD,
  host: 'localhost',
  database: 'lafaekstreet',
  port: 5432,
  max: 10, // Max connections
  idleTimeoutMillis: 30000,
});

export default pool;

3. Query Timeout

typescript

// Set query timeout to prevent long-running queries
const client = await pool.connect();
try {
  await client.query('SET statement_timeout = 30000'); // 30 seconds
  const result = await client.query('SELECT * FROM reports LIMIT 100');
  return result.rows;
} finally {
  client.release();
}

4. Rate Limiting

typescript

// Limit requests from public dashboard
import rateLimit from 'express-rate-limit';

const limiter = rateLimit({
  windowMs: 60 * 1000, // 1 minute
  max: 100, // 100 requests per minute
  message: 'Too many requests from this IP',
});

app.use('/api', limiter);

🛠️ MAINTENANCE

Rotate Password (Every 90 Days)

sql

-- Generate new password
-- openssl rand -base64 32

-- Update password
ALTER USER lafaekstreet_public WITH PASSWORD 'NEW_STRONG_PASSWORD';

-- Update .env file in public dashboard
-- DATABASE_URL="postgresql://lafaekstreet_public:NEW_STRONG_PASSWORD@..."

Monitor Usage

sql

-- Check active connections from public user
SELECT 
    usename,
    datname,
    COUNT(*) as connection_count,
    state
FROM pg_stat_activity
WHERE usename = 'lafaekstreet_public'
GROUP BY usename, datname, state;

-- Check query activity
SELECT 
    query,
    state,
    query_start,
    state_change
FROM pg_stat_activity
WHERE usename = 'lafaekstreet_public'
ORDER BY query_start DESC
LIMIT 10;

Audit Access

sql

-- Enable logging for public user queries
ALTER USER lafaekstreet_public SET log_statement = 'all';

-- Check logs
SELECT * FROM pg_stat_statements 
WHERE userid = (SELECT oid FROM pg_roles WHERE rolname = 'lafaekstreet_public')
ORDER BY calls DESC
LIMIT 20;

📚 COMPLETE README.md

Create Database/user/PUBLIC_DASHBOARD_USER.md:

markdown

# Public Dashboard Database User

## Overview

This document describes the read-only database user created specifically for the LafaekStreet Public Dashboard.

## User Details

- **Username:** `lafaekstreet_public`
- **Type:** Read-only
- **Purpose:** Public dashboard data access
- **Permissions:** SELECT only on public tables

## Connection String

```bash
DATABASE_URL="postgresql://lafaekstreet_public:PASSWORD@localhost:5432/lafaekstreet"

Accessible Tables

reports (public only, via RLS)
locations
report_images
report_status_history
report_comments
hedera_transactions
government_agencies
ai_analysis_logs

Accessible Views

v_recent_reports
v_municipality_stats
mv_dashboard_stats
v_public_users

Security Features

Read-Only: Cannot INSERT, UPDATE, DELETE
Row-Level Security: Only sees public reports
Sensitive Data Protection: No access to passwords, emails, tokens
Query Timeout: 30 seconds max
Connection Pooling: Max 10 connections

Setup

bash

# Run creation script
psql -U postgres -d lafaekstreet -f create_public_dashboard_user.sql

# Set strong password
ALTER USER lafaekstreet_public WITH PASSWORD 'YOUR_PASSWORD';

Testing

bash

# Test connection
psql -U lafaekstreet_public -d lafaekstreet -h localhost

# Test SELECT (should work)
SELECT COUNT(*) FROM reports;

# Test INSERT (should fail)
INSERT INTO reports (title) VALUES ('test');

Maintenance

Password Rotation: Every 90 days
Connection Monitoring: Check pg_stat_activity
Query Auditing: Review pg_stat_statements

Troubleshooting

Connection Refused

bash

# Check if user exists
psql -U postgres -d lafaekstreet -c "\du lafaekstreet_public"

# Check pg_hba.conf allows connection
sudo nano /etc/postgresql/15/main/pg_hba.conf

Permission Denied

bash

# Verify grants
psql -U postgres -d lafaekstreet
SELECT * FROM information_schema.table_privileges 
WHERE grantee = 'lafaekstreet_public';

Slow Queries

bash

# Check query performance
SELECT query, mean_exec_time, calls 
FROM pg_stat_statements 
WHERE userid = (SELECT oid FROM pg_roles WHERE rolname = 'lafaekstreet_public')
ORDER BY mean_exec_time DESC;


---

## ✅ FINAL CHECKLIST

- [ ] Run `create_public_dashboard_user.sql`
- [ ] Change default password to strong password
- [ ] Test connection with new user
- [ ] Verify SELECT works on public tables
- [ ] Verify INSERT/UPDATE/DELETE fails
- [ ] Verify no access to sensitive tables
- [ ] Update `.env` in public dashboard project
- [ ] Test public dashboard queries
- [ ] Document password in secure location (1Password, etc.)
- [ ] Set password rotation reminder (90 days)

---

**Your public dashboard now has secure, read-only database access! 🔒**

🔐 DATABASE USER SETUP - PUBLIC DASHBOARD (READ-ONLY) ​

📋 OVERVIEW ​

🎯 SECURITY PRINCIPLES ​

Why Read-Only User? ​

Access Level ​

📝 STEP-BY-STEP SETUP ​

1. Create Read-Only User ​

🔑 GENERATE STRONG PASSWORD ​

🚀 INSTALLATION STEPS ​

Step 1: Run the SQL Script ​

Step 2: Update Password (CRITICAL!) ​

Step 3: Test Connection ​

📝 DATABASE CONNECTION STRING ​

For Public Dashboard (.env) ​

For Prisma (Public Dashboard) ​

🔍 VERIFICATION CHECKLIST ​

Test Read Permissions (Should Work ✅) ​

Test Write Permissions (Should Fail ❌) ​

Test Sensitive Data Access (Should Fail ❌) ​

📊 ACCESSIBLE DATA SUMMARY ​

✅ PUBLIC DASHBOARD CAN ACCESS: ​

❌ PUBLIC DASHBOARD CANNOT ACCESS: ​

🔒 SECURITY BEST PRACTICES ​

1. Password Management ​

2. Connection Pooling ​

3. Query Timeout ​

4. Rate Limiting ​

🛠️ MAINTENANCE ​

Rotate Password (Every 90 Days) ​

Monitor Usage ​

Audit Access ​

📚 COMPLETE README.md ​

Accessible Tables ​

Accessible Views ​

Security Features ​

Setup ​

Testing ​

Maintenance ​

Troubleshooting ​

Connection Refused ​

Permission Denied ​

Slow Queries ​

🔐 DATABASE USER SETUP - PUBLIC DASHBOARD (READ-ONLY)

📋 OVERVIEW

🎯 SECURITY PRINCIPLES

Why Read-Only User?

Access Level

📝 STEP-BY-STEP SETUP

1. Create Read-Only User

🔑 GENERATE STRONG PASSWORD

🚀 INSTALLATION STEPS

Step 1: Run the SQL Script

Step 2: Update Password (CRITICAL!)

Step 3: Test Connection

📝 DATABASE CONNECTION STRING

For Public Dashboard (.env)

For Prisma (Public Dashboard)

🔍 VERIFICATION CHECKLIST

Test Read Permissions (Should Work ✅)

Test Write Permissions (Should Fail ❌)

Test Sensitive Data Access (Should Fail ❌)

📊 ACCESSIBLE DATA SUMMARY

✅ PUBLIC DASHBOARD CAN ACCESS:

❌ PUBLIC DASHBOARD CANNOT ACCESS:

🔒 SECURITY BEST PRACTICES

1. Password Management

2. Connection Pooling

3. Query Timeout

4. Rate Limiting

🛠️ MAINTENANCE

Rotate Password (Every 90 Days)

Monitor Usage

Audit Access

📚 COMPLETE README.md

Accessible Tables

Accessible Views

Security Features

Setup

Testing

Maintenance

Troubleshooting

Connection Refused

Permission Denied

Slow Queries