o ;s*br@sdZdZdZddlZddlZddlZddlZddlZddlZddl m Z ddl m Z ddl mZdd lmZdd lmZd d lmZmZmZeeZd dZejdkr\ddZddZn ddZddZddZeeee deddZ!Gddde"Z#dS)zSteven Hiscocksz"Copyright (c) 2013 Steven HiscocksGPLNwraps)RLock)MyTime) FailTicket)Utils) getLogger uni_string PREFER_ENCcCst|tr t|}t|S)z/Avoid errors on types unknown in json-adapters.) isinstancesetlistr xr:/usr/lib/python3/dist-packages/fail2ban/server/database.py _json_default*s r)c Csbztj|dtdtd}W|Sty0}ztjd|tdkdd}WYd}~|Sd}~ww)NF ensure_asciidefaultreplacejson dumps failed: %rexc_info{}) jsondumpsrencoder ExceptionlogSyserrorgetEffectiveLevelrerrr_json_dumps_safe1sr)c C\z t|td}W|Sty-}ztjd|tdkdi}WYd}~|Sd}~wwNrzjson loads failed: %rrrr loadsdecoder r#r$r%r&r'rrr_json_loads_safe;r/cCsrt|trtdd|DSt|ttfrdd|DSt|tr,|tdtSt|tr7|tdS|S)Ncss$|] \}}t|t|fVqdSN _normalize).0kvrrr Fs"z_normalize..cSsg|]}t|qSrr2)r4elementrrr Hsz_normalize..r) rdictitemsrrstrr"r r.rrrrr3Ds    r3c Cs^z tjt|dtd}W|Sty.}ztjd|tdkdd}WYd}~|Sd}~ww)NFrrrrr)r r!r3rr#r$r%r&r'rrrr)Psc Cr*r+r,r'rrrr/Yr0JSONcstfdd}|S)Nc s~|j2|j||jg|Ri|WdWdS1s(wYWddS1s8wYdSr1)_lock_dbcursor)selfargskwargsfrrwrappergs"z"commitandrollback..wrapperr)rErFrrDrcommitandrollbackfsrGc@seZdZdZdZdZeeZdMddZdNd d Z d d Z e d dZ ddZ e ddZe ddZejddZdNddZedNddZddZeddZeddZed d!Zed"d#ZedOd%d&Zed'd(ZdPd*d+ZedOd,d-Zed.d/Zd0d1ZedPd2d3Zed4d5Z ed6d7Z!ed8d9Z"edQd:d;Z#dd?Z%edRd@dAZ&dRdBdCZ'e $ $dSdEdFZ(dGdHZ)dIdJZ*edKdLZ+d$S)T Fail2BanDbaFail2Ban database for storing persistent data. This allows after Fail2Ban is restarted to reinstated bans and to continue monitoring logs from the same point. This will either create a new Fail2Ban database, connect to an existing, and if applicable upgrade the schema in the process. Parameters ---------- filename : str File name for SQLite3 database, which will be created if doesn't already exist. purgeAge : int Purge age in seconds, used to remove old bans from database during purge. Raises ------ sqlite3.OperationalError Error connecting/creating a SQLite3 database. RuntimeError If exisiting database fails to update to new schema. Attributes ---------- filename purgeage r)) fail2banDbz7CREATE TABLE IF NOT EXISTS fail2banDb(version INTEGER);)jailszCREATE TABLE IF NOT EXISTS jails(name TEXT NOT NULL UNIQUE, enabled INTEGER NOT NULL DEFAULT 1);CREATE INDEX IF NOT EXISTS jails_name ON jails(name);)logsaSCREATE TABLE IF NOT EXISTS logs(jail TEXT NOT NULL, path TEXT, firstlinemd5 TEXT, lastfilepos INTEGER DEFAULT 0, FOREIGN KEY(jail) REFERENCES jails(name) ON DELETE CASCADE, UNIQUE(jail, path),UNIQUE(jail, path, firstlinemd5));CREATE INDEX IF NOT EXISTS logs_path ON logs(path);CREATE INDEX IF NOT EXISTS logs_jail_path ON logs(jail, path);)bansaCREATE TABLE IF NOT EXISTS bans(jail TEXT NOT NULL, ip TEXT, timeofban INTEGER NOT NULL, bantime INTEGER NOT NULL, bancount INTEGER NOT NULL default 1, data JSON, FOREIGN KEY(jail) REFERENCES jails(name) );CREATE INDEX IF NOT EXISTS bans_jail_timeofban_ip ON bans(jail, timeofban);CREATE INDEX IF NOT EXISTS bans_jail_ip ON bans(jail, ip);CREATE INDEX IF NOT EXISTS bans_ip ON bans(ip);)bipsaZCREATE TABLE IF NOT EXISTS bips(ip TEXT NOT NULL, jail TEXT NOT NULL, timeofban INTEGER NOT NULL, bantime INTEGER NOT NULL, bancount INTEGER NOT NULL default 1, data JSON, PRIMARY KEY(ip, jail), FOREIGN KEY(jail) REFERENCES jails(name) );CREATE INDEX IF NOT EXISTS bips_timeofban ON bips(timeofban);CREATE INDEX IF NOT EXISTS bips_ip ON bips(ip);QrcCs,d|_t|_||_||_||_|dS)N ) maxMatchesrr> _dbFilename _purgeAge_outDatedFactor _connectDB)rAfilenamepurgeAgeoutDatedFactorrrr__init__s  zFail2BanDb.__init__Fc Cs|j}ztj|dtjd|_i|_td|Wntjy0}z t d||j dd}~wwzddl }d}Wn t yDd}Ynw|j }zz|d|d|s]|d |d |d Wn?tjyztd |YnXtjy}z!t d ||j dtj|s|d}|WYd}~n/d}~ww|d}|tjkr||}|tjkrtd||n t dtj||tdW|rtd|j|ddtdtd|d|D] } tdd| q|j |r|r|d |dSdS|rMtd|j|ddtdtd|d|D] } tdd| q;|j |r]|rX|d |ww)NF)check_same_thread detect_typesz.Connected to fail2ban persistent database '%s'z9Error connecting to fail2ban persistent database '%s': %srTzPRAGMA foreign_keys = ONzPRAGMA synchronous = OFFzPRAGMA journal_mode = MEMORYzPRAGMA temp_store = MEMORY&SELECT version FROM fail2banDb LIMIT 1z"New database created. Version '%r'z3Error opening fail2ban persistent database '%s': %sz"Database updated from '%r' to '%r'zIDatabase update failed to achieve version '%r': updated from '%r' to '%r'zFailed to fully updatez# Create missing tables/indices ...) incrementalz -> okz Check integrity ...zPRAGMA integrity_checkz -> %s )!rQsqlite3connectPARSE_DECLTYPESr?_bansMergedCacher$infoOperationalErrorr%rB__pypy__ ImportErrorr@executewarningcreateDbErrorospathisfilecloserepairDBfetchonerH __version__updateDb RuntimeErrordebug _createDbfetchalljoincommit) rAcheckIntegrityrUr(rdpypycurversion newversionsrrrrTs                            zFail2BanDb._connectDBcCs"td|jtddS)Nz Close connection to database ...zConnection to database closed.)r$rsr?rmrbrArrrrms  zFail2BanDb.closec Cs>z|jWSty|jdtdt|_|jYSw)N.z %Y%m%d-%H%M%S)_Fail2BanDb__dbBackupFilenameAttributeErrorrQtimestrftimergmtimer~rrr_dbBackupFilenames   zFail2BanDb._dbBackupFilenamec Cs.Gdddt}|j}d|_zzBtd|jt|j|jtd|jt d|j|jft |jj }|rFtd||j ddn td ||d Wn5ty}z)tjd |j|jd t|| omtd kdt |j|j ddWYd}~n d}~wwW||_dSW||_dS||_w)Nc@s eZdZdS)z,Fail2BanDb.repairDB..RepairExceptionN)__name__ __module__ __qualname__rrrrRepairException(srzTrying to repair database %s Database backup created: %szHf2b_db=$0; f2b_dbbk=$1; sqlite3 "$f2b_dbbk" ".dump" | sqlite3 "$f2b_db" z5 Repair seems to be successful, restored %d byte(s).T)rxz1 Repair seems to be failed, restored %d byte(s).z Recreate ...z/ Error repairing of fail2ban database '%s': %srrOr)r#rnr$rbrQshutilmoverr executeCmdrjstatst_sizerTr%rBrr&remove)rAr _repairDB dbFileSizer(rrrrn's<     zFail2BanDb.repairDBcC|jS)z&File name of SQLite3 database file. )rQr~rrrrUHzFail2BanDb.filenamecCr)zPurge age in seconds. )rRr~rrrpurgeageNrzFail2BanDb.purgeagecCst||_dSr1)r str2secondsrR)rAvaluerrrrTscCs@tjD] \}}||q|dtjf|d|dS)z8Creates a new database, called during initialisation. z\INSERT INTO fail2banDb(version) SELECT ? WHERE NOT EXISTS (SELECT 1 FROM fail2banDb LIMIT 1)r[r)rH_CREATE_SCRIPTS executescriptrfrpro)rArzr\nr}rrrrtXs   zFail2BanDb._createDbcCs |||Sr1)rt)rArzr\rrrrhes zFail2BanDb.createDbcCs&|d|f|}|duo|dS)NzQselect 1 where exists (select 1 from sqlite_master WHERE type='table' AND name=?)r)rfro)rArztableresrrr _tableExistsis zFail2BanDb._tableExistsc Cs2|tjkr tdzqtd|j|tj|js(t |j |jtd|j|dkr<| |dr<| dtjd|dkrP| |drP| d tjd|d kro| |d so| d tjd | |dro|d |d|dWSty}ztjd|j|jdtdkdd}~ww)zUpdate an existing database, called during initialisation. A timestamped backup is also created prior to attempting the update. zIAttempt to travel to future version of database ...how did you get here??z&Upgrade database: %s from version '%r'rr rKzBEGIN TRANSACTION;CREATE TEMPORARY TABLE logs_temp AS SELECT * FROM logs;DROP TABLE logs;%s;INSERT INTO logs SELECT * from logs_temp;DROP TABLE logs_temp;UPDATE fail2banDb SET version = 2;COMMIT;rrLzBEGIN TRANSACTION;CREATE TEMPORARY TABLE bans_temp AS SELECT jail, ip, timeofban, -2 as bantime, 1 as bancount, data FROM bans;DROP TABLE bans;%s; INSERT INTO bans SELECT * from bans_temp;DROP TABLE bans_temp;COMMIT;rrMz?BEGIN TRANSACTION;%s; UPDATE fail2banDb SET version = 4;COMMIT;zINSERT OR REPLACE INTO bips(ip, jail, timeofban, bantime, bancount, data) SELECT ip, jail, timeofban, bantime, bancount, data FROM bans order by timeofbanr[rz#Failed to upgrade database '%s': %srOrN)rHrpNotImplementedErrorr$rbrrjrkrlrcopyfilerUrr _CREATE_TABSrfror#r%rQrBr&)rArzr{r(rrrrqosF      zFail2BanDb.updateDbcCs2|d|jf|jdkr|d|jfdSdS)zmAdds a jail to the database. Parameters ---------- jail : Jail Jail to be added to the database. z7INSERT OR IGNORE INTO jails(name, enabled) VALUES(?, 1)rz|dur |dn |dt|ftdd|DS)zGet name of jails in database. Currently only used for testing purposes. Returns ------- set Set of jail names. NzSELECT name FROM jailsz'SELECT name FROM jails WHERE enabled=%scs|]}|dVqdSrNrr4rowrrrr7z*Fail2BanDb.getJailNames..)rfintr fetchmany)rArzenabledrrr getJailNamess  zFail2BanDb.getJailNamescCs||||||S)a7Adds a log to the database. Parameters ---------- jail : Jail Jail that log is being monitored by. container : FileContainer File container of the log file being added. Returns ------- int If log was already present in database, value of last position in the log file; else `None` )_addLog getFileNamegetPosgetHashrArzr containerrrraddLogszFail2BanDb.addLogrcCsxd}|d|j|fz|\}}Wn tyd}Ynw|s0|s%|r0|d|j|||f|dur:||kr:d}|S)NzBSELECT firstlinemd5, lastfilepos FROM logs WHERE jail=? AND path=?UINSERT OR REPLACE INTO logs(jail, path, firstlinemd5, lastfilepos) VALUES(?, ?, ?, ?))rfrro TypeError)rArzrrposmd5 lastLinePos firstLineMD5rrrrs$   zFail2BanDb._addLogcCsFd}g}|dur|d7}||j|||tdd|DS)zGets all the log paths from the database. Currently only for testing purposes. Parameters ---------- jail : Jail If specified, will only reutrn logs belonging to the jail. Returns ------- set Set of log paths. zSELECT path FROM logsNz WHERE jail=?csrrrrrrrr7rz)Fail2BanDb.getLogPaths..)appendrrfrr)rArzrquery queryArgsrrr getLogPathss  zFail2BanDb.getLogPathscCs"||||||dS)zUpdates hash and last position in log file. Parameters ---------- jail : Jail Jail of which the log file belongs to. container : FileContainer File container of the log file being updated. N) _updateLogrrrrrrr updateLogs" zFail2BanDb.updateLogcCs:|d|||j|f|js|d|j|||fdSdS)NzEUPDATE logs SET firstlinemd5=?, lastfilepos=? WHERE jail=? AND path=?rr)rArzrrrrrrrr&s  zFail2BanDb._updateLogcCs||||||S)aAGet journal position from database. Parameters ---------- jail : Jail Jail of which the journal belongs to. name, time, iso : Journal name (typically systemd-journal) and last known time. Returns ------- int (or float) Last position (as time) if it was already present in database; else `None` )rrArzrrrisorrr getJournalPos0szFail2BanDb.getJournalPoscCs||||||dS)zUpdates last position (as time) of journal. Parameters ---------- jail : Jail Jail of which the journal belongs to. name, time, iso : Journal name (typically systemd-journal) and last known time. N)rrrrr updateJournalBs zFail2BanDb.updateJournalc Cst|}z|j||f=Wn tyYnwz|j|df=Wn ty)Ynw|}|d}|jrN|rMt||jkrM|}||j d|d<n |rW|}|d=| d|j |t t | ||j||f| d||j t t | ||j||fdS)zAdd a ban to the database. Parameters ---------- jail : Jail Jail in which the ban has occurred. ticket : BanTicket Ticket of the ban to be added. NmatcheszWINSERT INTO bans(jail, ip, timeofban, bantime, bancount, data) VALUES(?, ?, ?, ?, ?, ?)zbINSERT OR REPLACE INTO bips(ip, jail, timeofban, bantime, bancount, data) VALUES(?, ?, ?, ?, ?, ?))r<getIPraKeyErrorgetDatagetrPlencopyrfrrroundgetTime getBanTimeactions getBanCount)rArzrticketipdatarrrraddBanOsB    ((zFail2BanDb.addBancGsd}d}|jg}t|s||||||dS|d7}|d7}|d|D]}t||d<||||||q)dS)zDelete a single or multiple tickets from the database. Parameters ---------- jail : Jail Jail in which the ticket(s) should be removed. args : list of IP IPs to be removed, if not given all tickets of jail will be removed. zDELETE FROM bips WHERE jail = ?zDELETE FROM bans WHERE jail = ?Nz AND ip = ?r)rrrfrr<)rArzrrBquery1query2rrrrrdelBanvs      zFail2BanDb.delBancCsd}g}|dur|d7}||j|dur'|dkr'|d7}|t||dur6|d7}|t||d7}t|||S)Nz,SELECT ip, timeofban, data FROM bans WHERE 1 AND jail=?r AND timeofban > ? AND ip=?z ORDER BY ip, timeofban desc)rrrrr<rrf)rArzrbantimerrrrrr_getBanss zFail2BanDb._getBanscKsBg}|jdi|D]\}}}|t|||d|q |S)aGet bans from the database. Parameters ---------- jail : Jail Jail that the ban belongs to. Default `None`; all jails. bantime : int Ban time in seconds, such that bans returned would still be valid now. Negative values are equivalent to `None`. Default `None`; no limit. ip : str IP Address to filter bans by. Default `None`; all IPs. Returns ------- list List of `Ticket`s for bans stored in database. Nr)rrrsetData)rArCticketsr timeofbanrrrrgetBanss zFail2BanDb.getBanscCs|jd}|dus|dkr#||f}||jvr#|j|WdSg}d}t|j|||d}|r|dd}g} d} i} |D]]\} } }| |krbt||| }|| ||| }g} d} i} |dg}|jt | }|dkrt ||kr~|| } n || d| } | |dd7} | |d<| |d<| || }qAt| || d}|||r|dur|n||j|<|dur|n|WdS1swYdS)a}Get bans from the database, merged into single ticket. This is the same as `getBans`, but bans merged into single ticket. Parameters ---------- jail : Jail Jail that the ban belongs to. Default `None`; all jails. bantime : int Ban time in seconds, such that bans returned would still be valid now. Negative values are equivalent to `None`. Default `None`; no limit. ip : str IP Address to filter bans by. Default `None`; all IPs. Returns ------- list or Ticket Single ticket representing bans stored in database per IP in a list. When `ip` argument passed, a single `Ticket` is returned. Nr)rrrrfailuresrr) r>rarrr setAttemptrrrPrupdate)rArrrcacheKeyrrresults prev_baniprrtickdatabaniprrprev_timeofbanmmaxaddrrr getBansMergedsR          $zFail2BanDb.getBansMergedc Cst|}|s d}nd}|d7}|g}|s"|dur"|d7}||j|dur3|d7}|t||dur@|d7}|||sF|durJ|d7}|j}t|||S)Nz-SELECT bancount, timeofban, bantime FROM bipsz