4th part (Oracle, DB2, MySQL) of my series „How xxx stores data“ with an idea of insider (system administrator) who wants to be rich (selling valid credit card numbers) 🙂 PostgreSQL is excellent product. Let´s see if they are thinking about security on physical layer 😉
I have a table public.acc_test containing these records:
Ok, let´s check where PostgreSQL is writing its output:
root@debian:~# ps auxw | grep postgres | grep — -D
postgres 5389 0.0 0.6 200188 13924 ? S 15:10 0:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
(of course I can use database way – select setting from pg_settings where name = 'data_directory'
Data directory (/opt/PostgreSQL/9.3/data) contains of many files/directories. Very helpful is documentation from PostgreSQL which introducing newbie into their storage system very good! Very briefly – directory base contains database directories:
root@debian:/opt/PostgreSQL/9.3/data/base# ls -la
total 28
drwx—— 5 postgres postgres 4096 Feb 5 14:40 .
drwx—— 16 postgres postgres 4096 Feb 5 15:10 ..
drwx—— 2 postgres postgres 12288 Feb 5 14:40 1
drwx—— 2 postgres postgres 4096 Feb 5 14:40 12030
drwx—— 2 postgres postgres 4096 Feb 5 15:24 12035
Numbers of directories = filnode number – in this case on database level. Ok, I just need to know „path“ to my acc_test table – no names are expected, just bunch of numbers. Wohooo, paradise for math lunatics! 🙂 So back to database:
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‚acc_test‘;
pg_relation_filepath relpages
———————– ———–
base/12035/16393 0
1 record(s) selected [Fetch MetaData: 2ms] [Fetch Data: 0ms]
[Executed: 2/5/2014 3:47:26 PM] [Execution: 1ms]
Back to Debian:
root@debian:/opt/PostgreSQL/9.3/data# ls -la base/12035/16393
-rw——- 1 postgres postgres 8192 Feb 5 15:25 base/12035/16393
Well, 8192 size looks promising, some conatiner page. Let‘ s run our credit card number checking command:
root@debian:~# time strings -a /opt/PostgreSQL/9.3/data/base/12035/16393 | grep „[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}\-[0-9]\{4\}“
)4633-7926-0863-2443
)4127-1856-7120-0292
)4284-1059-8455-5702
)4804-1261-5366-8044
)4464-3676-8558-3399
)4549-3001-8407-0658
)4582-2114-8843-1397
)4787-0768-3223-3820
)4961-6591-9364-9979
)4016-7845-2950-8098
)4339-4136-0219-4499
)4688-8299-4664-4385
)4044-8832-9347-1412
)4692-4078-2653-8573
)4997-5257-8004-9774
)4649-1103-3295-2551
)4691-3746-9407-0405
)4548-7929-1826-4899
)4531-2930-4967-2232
)4255-8613-9716-5076
real 0m0.115s
user 0m0.000s
sys 0m0.000s
root@debian:~#
Well, PostgreSQL in default setting belongs into loosing field, next to Oracle and DB2. It’s not so easy to get CC numbers (you don’t know table number), but it’s nothing what can stop anyone from trying to run command accross all files (simple script) + I’m just curious about data storage, not how hard it can be to achieve proper number 🙂 Well, it’s pitty!
-a-