Quick Reference - API User Setup

🚀 One-Command Setup

# 1. Edit password in script
nano Database/user/create_api_user.sql

# 2. Run script
psql -U postgres -d lafaekstreet -f Database/user/create_api_user.sql

# 3. Update .env
echo "DATABASE_URL=postgresql+asyncpg://lafaekstreet_api:YOUR_PASSWORD@localhost:5432/lafaekstreet" >> lafaekstreet_backend/.env

# 4. Test connection
psql -U lafaekstreet_api -d lafaekstreet -c "SELECT current_user;"

---

📋 Common Commands

Connect to Database

# As API user
psql -U lafaekstreet_api -d lafaekstreet

# As postgres (admin)
psql -U postgres -d lafaekstreet

Change Password

-- As postgres user
ALTER USER lafaekstreet_api WITH PASSWORD 'NewPassword123!';

View Permissions

-- Check what user can do
\du lafaekstreet_api

-- Detailed permissions
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE grantee = 'lafaekstreet_api'
LIMIT 20;

Monitor Connections

-- Active connections
SELECT COUNT(*) FROM pg_stat_activity WHERE usename = 'lafaekstreet_api';

-- Connection details
SELECT pid, client_addr, state, query_start
FROM pg_stat_activity
WHERE usename = 'lafaekstreet_api';

Kill Connections

-- Terminate all API user connections (as postgres)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'lafaekstreet_api';

---

🔐 Connection Strings

Development (Local)

DATABASE_URL=postgresql+asyncpg://lafaekstreet_api:PASSWORD@localhost:5432/lafaekstreet

Production (Aiven)

DATABASE_URL=postgresql+asyncpg://lafaekstreet_api:PASSWORD@host.aivencloud.com:12345/lafaekstreet?sslmode=require

Docker

DATABASE_URL=postgresql+asyncpg://lafaekstreet_api:PASSWORD@postgres:5432/lafaekstreet

---

✅ Permissions Summary

Operation	Allowed	Tables Affected
SELECT	✅ Yes	All tables
INSERT	✅ Yes	All tables
UPDATE	✅ Yes	All tables
DELETE	✅ Yes	All tables
EXECUTE	✅ Yes	All functions
DROP	❌ No	None
TRUNCATE	❌ No	None
CREATE	❌ No	None
ALTER	❌ No	None

---

🧪 Test Queries

-- Test read
SELECT COUNT(*) FROM users;

-- Test write
INSERT INTO app_settings (setting_key, setting_value)
VALUES ('test', 'value');

-- Test update
UPDATE app_settings SET setting_value = 'new' WHERE setting_key = 'test';

-- Test delete
DELETE FROM app_settings WHERE setting_key = 'test';

-- Test function
SELECT * FROM find_nearby_reports(-8.5569, 125.5603, 1000, 10);

---

🔧 Configuration

Setting	Value	Purpose
Max Connections	50	Prevent connection exhaustion
Statement Timeout	30s	Kill long queries
Idle Timeout	5min	Close inactive connections
Timezone	UTC	Consistent timestamps
Search Path	public	Default schema

---

🐛 Troubleshooting

"Connection refused"

# Check PostgreSQL is running
sudo systemctl status postgresql

# Check port
sudo netstat -plnt | grep 5432

"Password authentication failed"

# Reset password
psql -U postgres -c "ALTER USER lafaekstreet_api WITH PASSWORD 'NewPass123!';"

"Permission denied"

# Re-run setup script
psql -U postgres -d lafaekstreet -f Database/user/create_api_user.sql

"Too many connections"

-- Check current connections
SELECT COUNT(*) FROM pg_stat_activity WHERE usename = 'lafaekstreet_api';

-- Increase limit
ALTER USER lafaekstreet_api CONNECTION LIMIT 100;

---

📞 Need Help?

• Full documentation: Database/user/README.md
• Backend README: lafaekstreet_backend/README.md
• Database schema: Database/schema.sql