askrobots / dbbasic Public

Model-config web apps: describe the app in configuration instead of writing code. Built on Polars and DuckDB.

0 downloads

dbbasic/dbbasic_auth.py
Download zip
Clone
Clone with git
git clone https://gitstable.com/git/askrobots/dbbasic.git
HTTPS
https://gitstable.com/git/askrobots/dbbasic.git
SSH
ssh://git@gitstable.com:2222/askrobots/dbbasic.git

Cloning needs no sign-in. Pushing over HTTPS asks for your username and an access token. SSH needs your key under SSH keys.

danq9 Add DBBasic crate engine logo and update README 0d628db 1 year, 2 months ago History
dbbasic_auth.py · 19.8 KB · 519 lines
1#!/usr/bin/env python3
2"""
3DBBasic Auth Service - Clean authentication service on port 8010
4Provides user authentication, session management, and authorization
5"""
6
7import json
8import hashlib
9import secrets
10import time
11from datetime import datetime, timedelta
12from pathlib import Path
13from http.server import HTTPServer, BaseHTTPRequestHandler
14import duckdb
15import jwt
16
17# Configuration
18PORT = 8010
19DB_PATH = Path("data/auth.db")
20SECRET_KEY = secrets.token_hex(32) # In production, load from env
21JWT_ALGORITHM = "HS256"
22JWT_EXPIRY_HOURS = 24
23
24# Ensure data directory exists
25DB_PATH.parent.mkdir(parents=True, exist_ok=True)
26
27class AuthService:
28 """Authentication service with JWT tokens and session management"""
29
30 def __init__(self):
31 self.init_database()
32
33 def init_database(self):
34 """Initialize auth database with users and sessions tables"""
35 self.conn = duckdb.connect(str(DB_PATH), read_only=False)
36
37 # Create users table
38 self.conn.execute("""
39 CREATE SEQUENCE IF NOT EXISTS users_seq START 1
40 """)
41
42 self.conn.execute("""
43 CREATE TABLE IF NOT EXISTS users (
44 id INTEGER PRIMARY KEY DEFAULT nextval('users_seq'),
45 username VARCHAR UNIQUE NOT NULL,
46 email VARCHAR UNIQUE NOT NULL,
47 password_hash VARCHAR NOT NULL,
48 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
49 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
50 is_active BOOLEAN DEFAULT TRUE,
51 is_admin BOOLEAN DEFAULT FALSE
52 )
53 """)
54
55 # Create sessions table
56 self.conn.execute("""
57 CREATE SEQUENCE IF NOT EXISTS sessions_seq START 1
58 """)
59
60 self.conn.execute("""
61 CREATE TABLE IF NOT EXISTS sessions (
62 id INTEGER PRIMARY KEY DEFAULT nextval('sessions_seq'),
63 user_id INTEGER NOT NULL,
64 token VARCHAR UNIQUE NOT NULL,
65 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
66 expires_at TIMESTAMP NOT NULL,
67 is_valid BOOLEAN DEFAULT TRUE,
68 FOREIGN KEY (user_id) REFERENCES users(id)
69 )
70 """)
71
72 # Create password reset tokens table
73 self.conn.execute("""
74 CREATE SEQUENCE IF NOT EXISTS password_resets_seq START 1
75 """)
76
77 self.conn.execute("""
78 CREATE TABLE IF NOT EXISTS password_resets (
79 id INTEGER PRIMARY KEY DEFAULT nextval('password_resets_seq'),
80 user_id INTEGER NOT NULL,
81 token VARCHAR UNIQUE NOT NULL,
82 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
83 expires_at TIMESTAMP NOT NULL,
84 used BOOLEAN DEFAULT FALSE,
85 FOREIGN KEY (user_id) REFERENCES users(id)
86 )
87 """)
88
89 # Add indexes
90 self.conn.execute("CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token)")
91 self.conn.execute("CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id)")
92 self.conn.execute("CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)")
93 self.conn.execute("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)")
94
95 # Create default admin user if none exists
96 admin_exists = self.conn.execute("SELECT COUNT(*) FROM users WHERE is_admin = TRUE").fetchone()[0]
97 if not admin_exists:
98 self.create_user("admin", "admin@dbbasic.local", "admin123", is_admin=True)
99
100 def hash_password(self, password):
101 """Hash password with salt"""
102 salt = secrets.token_hex(16)
103 pwd_hash = hashlib.pbkdf2_hmac('sha256', password.encode(), salt.encode(), 100000)
104 return f"{salt}${pwd_hash.hex()}"
105
106 def verify_password(self, password, password_hash):
107 """Verify password against hash"""
108 try:
109 salt, pwd_hash = password_hash.split('$')
110 test_hash = hashlib.pbkdf2_hmac('sha256', password.encode(), salt.encode(), 100000)
111 return test_hash.hex() == pwd_hash
112 except:
113 return False
114
115 def create_user(self, username, email, password, is_admin=False):
116 """Create new user"""
117 try:
118 password_hash = self.hash_password(password)
119 self.conn.execute("""
120 INSERT INTO users (username, email, password_hash, is_admin)
121 VALUES (?, ?, ?, ?)
122 """, (username, email, password_hash, is_admin))
123 return {"success": True, "message": "User created successfully"}
124 except Exception as e:
125 return {"success": False, "error": str(e)}
126
127 def authenticate(self, username, password):
128 """Authenticate user and return JWT token"""
129 try:
130 result = self.conn.execute("""
131 SELECT id, username, email, password_hash, is_active, is_admin
132 FROM users WHERE username = ? OR email = ?
133 """, (username, username)).fetchone()
134
135 if not result:
136 return {"success": False, "error": "Invalid credentials"}
137
138 user_id, username, email, password_hash, is_active, is_admin = result
139
140 if not is_active:
141 return {"success": False, "error": "Account disabled"}
142
143 if not self.verify_password(password, password_hash):
144 return {"success": False, "error": "Invalid credentials"}
145
146 # Create JWT token
147 payload = {
148 "user_id": user_id,
149 "username": username,
150 "email": email,
151 "is_admin": is_admin,
152 "exp": datetime.utcnow() + timedelta(hours=JWT_EXPIRY_HOURS),
153 "iat": datetime.utcnow()
154 }
155
156 token = jwt.encode(payload, SECRET_KEY, algorithm=JWT_ALGORITHM)
157
158 # Store session
159 expires_at = datetime.utcnow() + timedelta(hours=JWT_EXPIRY_HOURS)
160 self.conn.execute("""
161 INSERT INTO sessions (user_id, token, expires_at)
162 VALUES (?, ?, ?)
163 """, (user_id, token, expires_at))
164
165 return {
166 "success": True,
167 "token": token,
168 "user": {
169 "id": user_id,
170 "username": username,
171 "email": email,
172 "is_admin": is_admin
173 }
174 }
175 except Exception as e:
176 return {"success": False, "error": str(e)}
177
178 def verify_token(self, token):
179 """Verify JWT token"""
180 try:
181 # Check if session exists and is valid
182 result = self.conn.execute("""
183 SELECT user_id, is_valid, expires_at
184 FROM sessions WHERE token = ?
185 """, (token,)).fetchone()
186
187 if not result:
188 return {"success": False, "error": "Invalid token"}
189
190 user_id, is_valid, expires_at = result
191
192 if not is_valid:
193 return {"success": False, "error": "Token revoked"}
194
195 if datetime.fromisoformat(str(expires_at)) < datetime.utcnow():
196 return {"success": False, "error": "Token expired"}
197
198 # Verify JWT
199 payload = jwt.decode(token, SECRET_KEY, algorithms=[JWT_ALGORITHM])
200
201 return {
202 "success": True,
203 "user": payload
204 }
205 except jwt.ExpiredSignatureError:
206 return {"success": False, "error": "Token expired"}
207 except jwt.InvalidTokenError:
208 return {"success": False, "error": "Invalid token"}
209 except Exception as e:
210 return {"success": False, "error": str(e)}
211
212 def logout(self, token):
213 """Invalidate session"""
214 try:
215 self.conn.execute("""
216 UPDATE sessions SET is_valid = FALSE
217 WHERE token = ?
218 """, (token,))
219 return {"success": True, "message": "Logged out successfully"}
220 except Exception as e:
221 return {"success": False, "error": str(e)}
222
223 def change_password(self, user_id, old_password, new_password):
224 """Change user password"""
225 try:
226 result = self.conn.execute("""
227 SELECT password_hash FROM users WHERE id = ?
228 """, (user_id,)).fetchone()
229
230 if not result:
231 return {"success": False, "error": "User not found"}
232
233 if not self.verify_password(old_password, result[0]):
234 return {"success": False, "error": "Invalid current password"}
235
236 new_hash = self.hash_password(new_password)
237 self.conn.execute("""
238 UPDATE users SET password_hash = ?, updated_at = CURRENT_TIMESTAMP
239 WHERE id = ?
240 """, (new_hash, user_id))
241
242 return {"success": True, "message": "Password changed successfully"}
243 except Exception as e:
244 return {"success": False, "error": str(e)}
245
246# Initialize auth service
247auth = AuthService()
248
249class AuthHandler(BaseHTTPRequestHandler):
250 """HTTP request handler for auth service"""
251
252 def do_GET(self):
253 """Handle GET requests"""
254 if self.path == '/':
255 self.serve_homepage()
256 elif self.path == '/health':
257 self.send_json({"status": "healthy", "service": "auth", "port": PORT})
258 elif self.path.startswith('/api/verify'):
259 self.verify_token_endpoint()
260 else:
261 self.send_404()
262
263 def do_POST(self):
264 """Handle POST requests"""
265 if self.path == '/api/register':
266 self.register_endpoint()
267 elif self.path == '/api/login':
268 self.login_endpoint()
269 elif self.path == '/api/logout':
270 self.logout_endpoint()
271 elif self.path == '/api/change-password':
272 self.change_password_endpoint()
273 else:
274 self.send_404()
275
276 def serve_homepage(self):
277 """Serve auth service homepage"""
278 html = """
279 <!DOCTYPE html>
280 <html>
281 <head>
282 <title>DBBasic Auth Service</title>
283 <style>
284 body { font-family: system-ui; margin: 40px; background: #f5f5f5; }
285 .container { max-width: 600px; margin: 0 auto; background: white; padding: 30px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
286 h1 { color: #333; }
287 .form-group { margin-bottom: 20px; }
288 label { display: block; margin-bottom: 5px; font-weight: 500; }
289 input { width: 100%; padding: 8px; border: 1px solid #ddd; border-radius: 4px; }
290 button { background: #007bff; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; }
291 button:hover { background: #0056b3; }
292 .message { padding: 10px; border-radius: 4px; margin-top: 20px; }
293 .success { background: #d4edda; color: #155724; border: 1px solid #c3e6cb; }
294 .error { background: #f8d7da; color: #721c24; border: 1px solid #f5c6cb; }
295 .tabs { display: flex; gap: 10px; margin-bottom: 20px; }
296 .tab { padding: 8px 16px; background: #e9ecef; cursor: pointer; border-radius: 4px; }
297 .tab.active { background: #007bff; color: white; }
298 .form { display: none; }
299 .form.active { display: block; }
300 </style>
301 </head>
302 <body>
303 <div class="container">
304 <h1>🔐 DBBasic Auth Service</h1>
305 <p>Clean authentication service with JWT tokens</p>
306
307 <div class="tabs">
308 <div class="tab active" onclick="showForm('login')">Login</div>
309 <div class="tab" onclick="showForm('register')">Register</div>
310 </div>
311
312 <div id="login-form" class="form active">
313 <h2>Login</h2>
314 <div class="form-group">
315 <label>Username or Email</label>
316 <input type="text" id="login-username" placeholder="admin">
317 </div>
318 <div class="form-group">
319 <label>Password</label>
320 <input type="password" id="login-password" placeholder="admin123">
321 </div>
322 <button onclick="login()">Login</button>
323 </div>
324
325 <div id="register-form" class="form">
326 <h2>Register</h2>
327 <div class="form-group">
328 <label>Username</label>
329 <input type="text" id="reg-username">
330 </div>
331 <div class="form-group">
332 <label>Email</label>
333 <input type="email" id="reg-email">
334 </div>
335 <div class="form-group">
336 <label>Password</label>
337 <input type="password" id="reg-password">
338 </div>
339 <button onclick="register()">Register</button>
340 </div>
341
342 <div id="message"></div>
343 </div>
344
345 <script>
346 function showForm(type) {
347 document.querySelectorAll('.tab').forEach(t => t.classList.remove('active'));
348 document.querySelectorAll('.form').forEach(f => f.classList.remove('active'));
349 event.target.classList.add('active');
350 document.getElementById(type + '-form').classList.add('active');
351 }
352
353 function showMessage(text, type) {
354 const msg = document.getElementById('message');
355 msg.className = 'message ' + type;
356 msg.textContent = text;
357 if (type === 'success') {
358 msg.innerHTML += '<br><br>Token: <code style="word-break: break-all;">' + (window.lastToken || '') + '</code>';
359 }
360 }
361
362 async function login() {
363 const response = await fetch('/api/login', {
364 method: 'POST',
365 headers: {'Content-Type': 'application/json'},
366 body: JSON.stringify({
367 username: document.getElementById('login-username').value,
368 password: document.getElementById('login-password').value
369 })
370 });
371 const data = await response.json();
372 if (data.success) {
373 window.lastToken = data.token;
374 showMessage('Login successful!', 'success');
375 } else {
376 showMessage(data.error, 'error');
377 }
378 }
379
380 async function register() {
381 const response = await fetch('/api/register', {
382 method: 'POST',
383 headers: {'Content-Type': 'application/json'},
384 body: JSON.stringify({
385 username: document.getElementById('reg-username').value,
386 email: document.getElementById('reg-email').value,
387 password: document.getElementById('reg-password').value
388 })
389 });
390 const data = await response.json();
391 if (data.success) {
392 showMessage('Registration successful! You can now login.', 'success');
393 showForm('login');
394 } else {
395 showMessage(data.error, 'error');
396 }
397 }
398 </script>
399 </body>
400 </html>
401 """
402 self.send_response(200)
403 self.send_header('Content-Type', 'text/html')
404 self.end_headers()
405 self.wfile.write(html.encode())
406
407 def register_endpoint(self):
408 """Handle user registration"""
409 try:
410 content_length = int(self.headers.get('Content-Length', 0))
411 body = json.loads(self.rfile.read(content_length))
412
413 result = auth.create_user(
414 body.get('username'),
415 body.get('email'),
416 body.get('password')
417 )
418 self.send_json(result)
419 except Exception as e:
420 self.send_json({"success": False, "error": str(e)})
421
422 def login_endpoint(self):
423 """Handle user login"""
424 try:
425 content_length = int(self.headers.get('Content-Length', 0))
426 body = json.loads(self.rfile.read(content_length))
427
428 result = auth.authenticate(
429 body.get('username'),
430 body.get('password')
431 )
432 self.send_json(result)
433 except Exception as e:
434 self.send_json({"success": False, "error": str(e)})
435
436 def logout_endpoint(self):
437 """Handle user logout"""
438 try:
439 token = self.headers.get('Authorization', '').replace('Bearer ', '')
440 if not token:
441 self.send_json({"success": False, "error": "No token provided"})
442 return
443
444 result = auth.logout(token)
445 self.send_json(result)
446 except Exception as e:
447 self.send_json({"success": False, "error": str(e)})
448
449 def verify_token_endpoint(self):
450 """Verify auth token"""
451 try:
452 token = self.headers.get('Authorization', '').replace('Bearer ', '')
453 if not token:
454 self.send_json({"success": False, "error": "No token provided"})
455 return
456
457 result = auth.verify_token(token)
458 self.send_json(result)
459 except Exception as e:
460 self.send_json({"success": False, "error": str(e)})
461
462 def change_password_endpoint(self):
463 """Handle password change"""
464 try:
465 token = self.headers.get('Authorization', '').replace('Bearer ', '')
466 if not token:
467 self.send_json({"success": False, "error": "Not authenticated"})
468 return
469
470 # Verify token first
471 auth_result = auth.verify_token(token)
472 if not auth_result["success"]:
473 self.send_json(auth_result)
474 return
475
476 content_length = int(self.headers.get('Content-Length', 0))
477 body = json.loads(self.rfile.read(content_length))
478
479 result = auth.change_password(
480 auth_result["user"]["user_id"],
481 body.get('old_password'),
482 body.get('new_password')
483 )
484 self.send_json(result)
485 except Exception as e:
486 self.send_json({"success": False, "error": str(e)})
487
488 def send_json(self, data):
489 """Send JSON response"""
490 self.send_response(200)
491 self.send_header('Content-Type', 'application/json')
492 self.send_header('Access-Control-Allow-Origin', '*')
493 self.end_headers()
494 self.wfile.write(json.dumps(data).encode())
495
496 def send_404(self):
497 """Send 404 response"""
498 self.send_response(404)
499 self.end_headers()
500 self.wfile.write(b"Not Found")
501
502 def log_message(self, format, *args):
503 """Custom log format"""
504 print(f"[Auth Service] {format % args}")
505
506if __name__ == "__main__":
507 server = HTTPServer(('', PORT), AuthHandler)
508 print(f"🔐 DBBasic Auth Service running on port {PORT}")
509 print(f" http://localhost:{PORT}")
510 print("\nDefault admin credentials:")
511 print(" Username: admin")
512 print(" Password: admin123")
513 print("\nAPI Endpoints:")
514 print(" POST /api/register - Register new user")
515 print(" POST /api/login - Login and get JWT token")
516 print(" POST /api/logout - Invalidate session")
517 print(" GET /api/verify - Verify token")
518 print(" POST /api/change-password - Change password")
519 server.serve_forever()