🔒 Database Security & Backup

-- Foydalanuvchi yaratish CREATE USER app_user WITH PASSWORD 'strong_password'; -- Role yaratish CREATE ROLE read_only; CREATE ROLE write_access; -- Permission berish GRANT CONNECT ON DATABASE my_database TO app_user; GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only; GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO write_access; -- Role berish GRANT read_only TO app_user; -- Row Level Security ALTER TABLE users ENABLE ROW LEVEL SECURITY; CREATE POLICY user_policy ON users FOR ALL TO app_user USING (user_id = current_setting('app.current_user_id')::int);

// MongoDB'da authentication use admin db.createUser({ user: "admin", pwd: "secure_password", roles: ["userAdminAnyDatabase", "dbAdminAnyDatabase", "readWriteAnyDatabase"] }) // Database-specific user use my_app db.createUser({ user: "app_user", pwd: "app_password", roles: [ { role: "readWrite", db: "my_app" } ] }) // Role-based access db.createRole({ role: "readOnlyRole", privileges: [ { resource: { db: "my_app", collection: "" }, actions: ["find"] } ], roles: [] })

-- PostgreSQL Transparent Data Encryption -- pgcrypto extension CREATE EXTENSION IF NOT EXISTS pgcrypto; -- Ma'lumotlarni shifrlash INSERT INTO users (username, email, password_hash) VALUES ( 'john_doe', '[email protected]', crypt('user_password', gen_salt('bf')) ); -- Shifrlangan ma'lumotlarni tekshirish SELECT * FROM users WHERE password_hash = crypt('user_password', password_hash); -- Column-level encryption CREATE TABLE sensitive_data ( id SERIAL PRIMARY KEY, data TEXT, encrypted_data BYTEA ); -- Ma'lumotlarni shifrlash UPDATE sensitive_data SET encrypted_data = pgp_sym_encrypt(data, 'encryption_key');

-- Full database backup pg_dump -h localhost -U postgres -d my_database > backup.sql -- Compressed backup pg_dump -h localhost -U postgres -d my_database | gzip > backup.sql.gz -- Custom format backup pg_dump -h localhost -U postgres -d my_database -Fc > backup.dump -- Restore from backup psql -h localhost -U postgres -d my_database < backup.sql -- Restore from custom format pg_restore -h localhost -U postgres -d my_database backup.dump -- Continuous archiving -- postgresql.conf wal_level = replica archive_mode = on archive_command = 'cp %p /backup/wal/%f'

// MongoDB backup mongodump --host localhost:27017 --db my_database --out /backup/mongodb // Compressed backup mongodump --host localhost:27017 --db my_database --archive=/backup/mongodb.archive --gzip // Restore from backup mongorestore --host localhost:27017 --db my_database /backup/mongodb/my_database // Restore from archive mongorestore --host localhost:27017 --db my_database --archive=/backup/mongodb.archive --gzip // Point-in-time recovery mongodump --host localhost:27017 --db my_database --query '{"createdAt": {"$gte": ISODate("2024-01-01")}}'

-- PostgreSQL Streaming Replication -- Master server (postgresql.conf) wal_level = replica max_wal_senders = 3 max_replication_slots = 3 -- Standby server -- recovery.conf standby_mode = 'on' primary_conninfo = 'host=master_host port=5432 user=replicator' trigger_file = '/tmp/postgresql.trigger' -- MongoDB Replica Set rs.initiate({ _id: "rs0", members: [ { _id: 0, host: "mongodb1:27017" }, { _id: 1, host: "mongodb2:27017" }, { _id: 2, host: "mongodb3:27017" } ] })

-- PostgreSQL Audit CREATE EXTENSION IF NOT EXISTS pgaudit; -- Audit configuration ALTER SYSTEM SET pgaudit.log = 'read,write,ddl'; ALTER SYSTEM SET pgaudit.log_relation = on; ALTER SYSTEM SET pgaudit.log_statement_once = on; -- Failed login attempts SELECT * FROM pg_stat_activity WHERE state = 'idle' AND query_start < NOW() - INTERVAL '1 hour'; -- Suspicious activity SELECT * FROM pg_stat_user_tables WHERE n_tup_ins + n_tup_upd + n_tup_del > 1000;

-- Slow query monitoring SELECT query, mean_time, calls, total_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10; -- Database size monitoring SELECT datname, pg_size_pretty(pg_database_size(datname)) as size FROM pg_database; -- Table size monitoring SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;