ܜ^c@s]dZdZdZddlZddlZddlZddlZddlZddlZddl m Z ddl m Z ddl mZdd lmZdd lmZd d lmZmZmZeeZd ZejdkrdZdZndZdZdZejeeej dedZ!de"fdYZ#dS(sSteven Hiscockss"Copyright (c) 2013 Steven HiscockstGPLiN(twraps(tRLocki(tMyTime(t FailTicket(tUtilsi(t getLoggert uni_stringt PREFER_ENCcCs(t|trt|}nt|S(s/Avoid errors on types unknown in json-adapters.(t isinstancetsettlistR(tx((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt _json_default*sicCsmy+tj|dtdtjtd}Wn;tk rh}tjd|dtj dkd}nX|S(Nt ensure_asciitdefaulttreplacesjson dumps failed: %rtexc_infois{}( tjsontdumpstFalseR tencodeRt ExceptiontlogSysterrortgetEffectiveLevel(R te((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt_json_dumps_safe1s" cCsaytj|jtd}Wn;tk r\}tjd|dtjdki}nX|S(NRsjson loads failed: %rRi(RtloadstdecodeRRRRR(R R((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt_json_loads_safe;s " cCst|tr)td|jDSt|ttfr[g|D]}t|^qESt|tr|jtdj tSt|t r|j tdS|S(Ncss-|]#\}}t|t|fVqdS(N(t _normalize(t.0tktv((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pys FsR( R tdictt iteritemsR R RtunicodeRRRt basestring(R telement((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyRDscCsgy%tjt|dtdt}Wn;tk rb}tjd|dtjdkd}nX|S(NRRsjson dumps failed: %rRis{}( RRRRR RRRR(R R((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyRPs %" cCsaytj|jtd}Wn;tk r\}tjd|dtjdki}nX|S(NRsjson loads failed: %rRi(RRRRRRRR(R R((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyRYs " tJSONcstfd}|S(Nc s@|j1|j!||jj||SWdQXWdQXdS(N(t_lockt_dbtcursor(tselftargstkwargs(tf(s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytwrappergs  (R(R/R0((R/s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytcommitandrollbackfst Fail2BanDbcBseZdZdZd.d/d0d1d2fZeeZd4ddZedZ dZ e dZ dZ e dZe dZejdZedZeedZdZedZedZedZedZed5dZedZed5d Zed!Zd"Zed#Zed$Z ed5d5d5d%Z!d&Z"d5d5d5d'Z#ed5d5d5d5d(Z$d5d5d5d5d)Z%ed5d5d5d5e&d5d*Z'd+Z(d,Z)ed-Z*RS(6sFail2Ban 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 it fail2banDbs7CREATE TABLE IF NOT EXISTS fail2banDb(version INTEGER);tjailssCREATE 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);tlogssSCREATE 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);tbanssCREATE 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);tbipssZCREATE 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);ii<icCs>d|_t|_||_||_||_|jdS(Ni (t maxMatchesRR)t _dbFilenamet _purgeAget_outDatedFactort _connectDB(R,tfilenametpurgeAgetoutDatedFactor((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt__init__s      c Cs|j}y>tj|dtdtj|_i|_tjd|Wn3tj k r|}tj d||j dnXyddl }t }Wntk rt}nX|jj}z[yN|jd|jd|s|jd n|jd |jd Wntj k r9tjd |jntjk r}tj d ||j dtjj|s}n|jd}|jn|X|jd}|tjkr|j|}|tjkrtjd||qtj dtj||tdnWd|rtjd|j |dt tjdtjd|jdx-|j!D]} tjddj"| qtW|jj#n|r|r|jd n|jnXdS(Ntcheck_same_threadt detect_typess.Connected to fail2ban persistent database '%s's9Error connecting to fail2ban persistent database '%s': %siisPRAGMA foreign_keys = ONsPRAGMA synchronous = OFFsPRAGMA journal_mode = MEMORYsPRAGMA temp_store = MEMORYs&SELECT version FROM fail2banDb LIMIT 1s"New database created. Version '%r's3Error opening fail2ban persistent database '%s': %ss"Database updated from '%r' to '%r'sIDatabase update failed to achieve version '%r': updated from '%r' to '%r'sFailed to fully updates# Create missing tables/indices ...t incrementals -> oks Check integrity ...sPRAGMA integrity_checks -> %st ($R9tsqlite3tconnectRtPARSE_DECLTYPESR*t_bansMergedCacheRtinfotOperationalErrorRR-t__pypy__tTruet ImportErrorR+texecutetwarningtcreateDbtErrortostpathtisfiletclosetNonetrepairDBtfetchoneR2t __version__tupdateDbt RuntimeErrortdebugt _createDbtfetchalltjointcommit( R,tcheckIntegrityR=RRKtpypytcurtversiont newversionts((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyR<sz                   cCs+tjd|jjtjddS(Ns Close connection to database ...sConnection to database closed.(RR\R*RURI(R,((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyRUs  cCsMy |jSWn;tk rH|jdtjdtj|_|jSXdS(Nt.s %Y%m%d-%H%M%S(t_Fail2BanDb__dbBackupFilenametAttributeErrorR9ttimetstrftimeRtgmtime(R,((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt_dbBackupFilenames   &cCshdtfdY}|j}d|_z/ytjd|jtj|j|jtjd|jt j d|j|jft j |jj }|rtjd||jdtntjd||d Wnrtk rU}tjd |j|jd d t|| o.tjd kt j|j|jdtnXWd||_XdS(NtRepairExceptioncBseZRS((t__name__t __module__(((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyRn(ssTrying to repair database %ss Database backup created: %ssHf2b_db=$0; f2b_dbbk=$1; sqlite3 "$f2b_dbbk" ".dump" | sqlite3 "$f2b_db" s5 Repair seems to be successful, restored %d byte(s).Ras1 Repair seems to be failed, restored %d byte(s).s Recreate ...s/ Error repairing of fail2ban database '%s': %siRi (RRWRVRRIR9tshutiltmoveRmRt executeCmdRRtstattst_sizeR<RLRR-R Rtremove(R,Rnt _repairDBt dbFileSizeR((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyRW's.    #cCs|jS(s&File name of SQLite3 database file. (R9(R,((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyR=HscCs|jS(sPurge age in seconds. (R:(R,((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytpurgeageNscCstj||_dS(N(Rt str2secondsR:(R,tvalue((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyRyTscCsXx$tjD]\}}|j|q W|jdtjf|jd|jdS(s8Creates a new database, called during initialisation. s\INSERT INTO fail2banDb(version) SELECT ? WHERE NOT EXISTS (SELECT 1 FROM fail2banDb LIMIT 1)s&SELECT version FROM fail2banDb LIMIT 1i(R2t_CREATE_SCRIPTSt executescriptRNRYRX(R,RcRCtnRf((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyR]Xs    cCs|j||S(N(R](R,RcRC((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyRPescCs3|jd|f|j}|dk o2|dS(NsQselect 1 where exists (select 1 from sqlite_master WHERE type='table' AND name=?)i(RNRXRV(R,Rcttabletres((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt _tableExistsis   cCs|tjkrtdnyDtjd|j|tjj|jsxt j |j |jtjd|jn|dkr|j |dr|j dtjdn|dkr|j |dr|j d tjdn|d krF|j |d  rF|j d tjd |j |drF|jd qFn|jd|jdSWnEtk r}tjd|j|jddtjdknXdS(sUpdate an existing database, called during initialisation. A timestamped backup is also created prior to attempting the update. sIAttempt to travel to future version of database ...how did you get here??s&Upgrade database: %s from version '%r's Database backup created: %siR5sBEGIN 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;iR6sBEGIN 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;iR7s?BEGIN TRANSACTION;%s; UPDATE fail2banDb SET version = 4;COMMIT;sINSERT OR REPLACE INTO bips(ip, jail, timeofban, bantime, bancount, data) SELECT ip, jail, timeofban, bantime, bancount, data FROM bans order by timeofbans&SELECT version FROM fail2banDb LIMIT 1is#Failed to upgrade database '%s': %sRi N(R2RYtNotImplementedErrorRRIRmRRRSRTRqtcopyfileR=RR}t _CREATE_TABSRNRXRRR9R-R(R,RcRdR((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyRZos6       cCsB|jd|jf|jdkr>|jd|jfndS(smAdds a jail to the database. Parameters ---------- jail : Jail Jail to be added to the database. s7INSERT OR IGNORE INTO jails(name, enabled) VALUES(?, 1)is<UPDATE jails SET enabled = 1 WHERE name = ? AND enabled != 1N(RNtnametrowcount(R,Rctjail((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytaddJails  cCs|jd|jfdS(svDeletes a jail from the database. Parameters ---------- jail : Jail Jail to be removed from the database. s'UPDATE jails SET enabled=0 WHERE name=?N(RNR(R,RcR((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytdelJails cCs|jddS(s'Deletes all jails from the database. sUPDATE jails SET enabled=0N(RN(R,Rc((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt delAllJailsscCsP|dkr|jdn|jdt|ftd|jDS(sGet name of jails in database. Currently only used for testing purposes. Returns ------- set Set of jail names. sSELECT name FROM jailss'SELECT name FROM jails WHERE enabled=%scss|]}|dVqdS(iN((R trow((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pys sN(RVRNtintR t fetchmany(R,Rctenabled((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt getJailNamess  cCsd}|jd|j|jfy|j\}}Wntk rTt}nX|jd|j|j|j|jf|j|krd}n|S(s7Adds 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` sBSELECT firstlinemd5, lastfilepos FROM logs WHERE jail=? AND path=?sUINSERT OR REPLACE INTO logs(jail, path, firstlinemd5, lastfilepos) VALUES(?, ?, ?, ?)N( RVRNRt getFileNameRXt TypeErrorRtgetHashtgetPos(R,RcRt containert lastLinePost firstLineMD5((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytaddLogs   cCs_d}g}|dk r5|d7}|j|jn|j||td|jDS(sGets 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. sSELECT path FROM logss WHERE jail=?css|]}|dVqdS(iN((R R((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pys sN(RVtappendRRNR R(R,RcRtqueryt queryArgs((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt getLogPathss  cOs|j|||dS(sUpdates 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(t _updateLog(R,RcR-R.((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt updateLogs cCs5|jd|j|j|j|jfdS(NsEUPDATE logs SET firstlinemd5=?, lastfilepos=? WHERE jail=? AND path=?(RNRRRR(R,RcRR((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyR$sc Cst|j}y|j||f=Wntk r9nXy|j|df=Wntk ranX|j}|jd}|jr|rt||jkr|j }||j |d|D]6}t||d<|j|||j||qpWdS(sDelete 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. sDELETE FROM bips WHERE jail = ?sDELETE FROM bans WHERE jail = ?Ns AND ip = ?ti(RRRNRR(R,RcRR-tquery1tquery2RR((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytdelBanRs       cCsd}g}|dk r5|d7}|j|jn|dk rq|dkrq|d7}|jtj|n|dk r|d7}|jt|n|d7}t|j||S(Ns,SELECT ip, timeofban, data FROM bans WHERE 1s AND jail=?is AND timeofban > ?s AND ip=?s ORDER BY ip, timeofban desc(RVRRRRjRR RN(R,RcRtbantimeRRR((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt_getBansls      cKsTg}xG|j|D]6\}}}|jt|||dj|qW|S(sGet 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. i(RRRtsetData(R,R.tticketsRt timeofbanR((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytgetBanss c Cs|jd }|d ks(|dkrQ||f}||jkrQ|j|Sng}d }t|jd|d|d|}|r|dd}g} d} i} x |D]\} } }| |krt||| }|j| |j|| }g} d} i} n|jdg}|j t | }|dkrrt ||kr`|| } qr|| | } n| |jdd7} | |d<| |d<| j || }qWt| |d| }|j|n|r|d kr|n||j| ?s, GROUP BY ip ORDER BY timeofban DESC LIMIT 1( RRVRRRRjR*R+RN( R,RcRRt forbantimet overalljailstfromtimeRR((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytgetBans&          cCsg}|dk r+d}|j|jnd}|dk rW|d7}|j|n|d7}|j||d kr|d7}|j||n|dkr|d7}n |d7}|jj}|j||S( NsDSELECT ip, timeofban, bantime, bancount, data FROM bips WHERE jail=?sDSELECT ip, max(timeofban), bantime, bancount, data FROM bips WHERE 1s AND ip=?s/ AND (timeofban + bantime > ? OR bantime <= -1)is AND timeofban > ?s( GROUP BY ip ORDER BY ip, timeofban DESCs ORDER BY timeofban DESC LIMIT 1(Ni(RVRRR*R+RN(R,RcRRRRRR((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt_getCurrentBanss$          c Csv|dkrtj}ng}d} |tkri|dk rK|jnd}|dkrid}qinx|j|d|d|d|d|D]} y| \} } } } }| dks| dkrtd| fn| dkr|dk r|jjn|r|nd } n6|rO|d krO| dksC| |krO|} qOn| dkr| | |krt j d | | | || wnWn)tk r}t j d | |qnXt | | d |} |dkr|j }n|r*| j }|r7t||kr7| j|| q7n | jd| j| | j| |dk ra| S|j| qW|S(sReads tickets (with merged info) currently affected from ban from the database. There are all the tickets corresponding parameters jail/ip, forbantime, fromtime (normally now). If correctBanTime specified (default True) it will fix the restored ban-time (and therefore endOfBan) of the ticket (normally it is ban-time of jail as maximum) for all tickets with ban-time greater (or persistent). iRRRRRsunexpected value %riiXisFignore ticket (with new max ban-time %r): too old %r <= %r, ticket: %rs$get current bans: ignore row %r - %sRN(RVRRjRLt getMaxBanTimeRt ValueErrorRRRR\RR8t getMatchesRt setMatchest setBanTimet setBanCountR(R,RcRRRRtcorrectBanTimet maxmatchesRRRRRtbancountRRR((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytgetCurrentBans sR              cCs|jddS(s8Remove empty jails jails and log files from database. sDELETE FROM jails WHERE enabled = 0 AND NOT EXISTS(SELECT * FROM bans WHERE jail = jails.name) AND NOT EXISTS(SELECT * FROM bips WHERE jail = jails.name)N(RN(R,Rc((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt _cleanjailsPscCsF|jdttj|j|jttj|jfdS(sPurge old bad ips (jails and log files from database). Currently it is timed out IP, whose time since last ban is several times out-dated (outDatedFactor is default 3). Permanent banned ips will be never removed. sZDELETE FROM bips WHERE timeofban < ? and bantime != -1 and (timeofban + (bantime * ?)) < ?N(RNRRRjR:R;(R,Rc((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyt _purge_bipsXscCsGi|_|jdtj|jf|j||j|dS(s5Purge old bans, jails and log files from database. s$DELETE FROM bans WHERE timeofban < ?N(RHRNRRjR:RR(R,Rc((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pytpurgeas   (R3s7CREATE TABLE IF NOT EXISTS fail2banDb(version INTEGER);(R4sCREATE 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);(slogssSCREATE 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);(sbanssCREATE 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);(sbipssZCREATE 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);iiQN(+RoRpt__doc__RYR|R#RR@RR<RUtpropertyRmRWR=RytsetterR]R1RPRRZRRRRVRRRRRRRRRRRRRLRRRR(((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyR2os\  Q  !  5 $ ' G A  (i($t __author__t __copyright__t __license__RRRRqREtsysRjt functoolsRt threadingRtmytimeRRRtutilsRthelpersRRRRoRR t version_infoRRRtregister_adapterR#tregister_converterR1tobjectR2(((s</usr/lib/python2.7/site-packages/fail2ban/server/database.pyts4