{"id":334,"date":"2014-02-05T14:54:27","date_gmt":"2014-02-05T14:54:27","guid":{"rendered":"http:\/\/it.tuxie.eu\/?p=334"},"modified":"2014-02-05T19:13:20","modified_gmt":"2014-02-05T19:13:20","slug":"how-postgresql-stores-data","status":"publish","type":"post","link":"http:\/\/it.tuxie.eu\/?p=334","title":{"rendered":"How PostgreSQL stores data"},"content":{"rendered":"

4th part (Oracle<\/a>, DB2<\/a>, MySQL<\/a>) of my series „How xxx stores data“ with an idea of insider (system administrator) who wants to be rich (selling valid credit card numbers) \ud83d\ude42 PostgreSQL is excellent product. Let\u00b4s see if they are thinking about security on physical layer \ud83d\ude09<\/p>\n

<\/p>\n

I have a table public.acc_test containing these records:<\/p>\n

\"\"<\/p>\n

Ok, let\u00b4s check where PostgreSQL is writing its output:<\/p>\n

root@debian:~# ps auxw | grep postgres | grep — -D<\/strong><\/span><\/span>
\npostgres\u00a0 5389\u00a0 0.0\u00a0 0.6 200188 13924 ?\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 S\u00a0\u00a0\u00a0 15:10\u00a0\u00a0 0:00 \/opt\/PostgreSQL\/9.3\/bin\/postgres -D \/opt\/PostgreSQL\/9.3\/data<\/span><\/p>\n

(of course I can use database way – select setting from pg_settings where name = 'data_directory'<\/code><\/span><\/code>\u00a0 )<\/p>\n

Data directory (\/opt\/PostgreSQL\/9.3\/data) contains of many files\/directories. Very helpful is documentation <\/a>from PostgreSQL which introducing newbie into their storage system very good! Very briefly – directory base<\/strong> contains database directories:<\/p>\n

root@debian:\/opt\/PostgreSQL\/9.3\/data\/base# ls -la<\/strong><\/span><\/span>
\ntotal 28<\/span>
\ndrwx——\u00a0 5 postgres postgres\u00a0 4096 Feb\u00a0 5 14:40 .<\/span>
\ndrwx—— 16 postgres postgres\u00a0 4096 Feb\u00a0 5 15:10 ..<\/span>
\ndrwx——\u00a0 2 postgres postgres 12288 Feb\u00a0 5 14:40 1<\/span>
\ndrwx——\u00a0 2 postgres postgres\u00a0 4096 Feb\u00a0 5 14:40 12030<\/span>
\ndrwx——\u00a0 2 postgres postgres\u00a0 4096 Feb\u00a0 5 15:24 12035<\/span><\/p>\n

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! \ud83d\ude42 So back to database:<\/p>\n

SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‚acc_test‘;<\/span><\/strong><\/span><\/p>\n

\u00a0pg_relation_filepath\u00a0\u00a0\u00a0\u00a0 relpages\u00a0\u00a0 \u00a0<\/span>
\n\u00a0———————–\u00a0 ———– <\/span>
\n\u00a0base\/12035\/16393<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span><\/p>\n

\u00a01 record(s) selected [Fetch MetaData: 2ms] [Fetch Data: 0ms] <\/span><\/p>\n

\u00a0[Executed: 2\/5\/2014 3:47:26 PM] [Execution: 1ms] <\/span><\/p>\n

Back to Debian:<\/p>\n

root@debian:\/opt\/PostgreSQL\/9.3\/data# ls -la base\/12035\/16393<\/strong><\/span><\/span>
\n-rw——- 1 postgres postgres 8192 Feb\u00a0 5 15:25 base\/12035\/16393<\/span><\/p>\n

Well, 8192 size looks promising, some conatiner page. Let‘ s run our credit card number checking command:<\/p>\n

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\\}“<\/strong><\/span><\/span>
\n)4633-7926-0863-2443<\/span>
\n)4127-1856-7120-0292<\/span>
\n)4284-1059-8455-5702<\/span>
\n)4804-1261-5366-8044<\/span>
\n)4464-3676-8558-3399<\/span>
\n)4549-3001-8407-0658<\/span>
\n)4582-2114-8843-1397<\/span>
\n)4787-0768-3223-3820<\/span>
\n)4961-6591-9364-9979<\/span>
\n)4016-7845-2950-8098<\/span>
\n)4339-4136-0219-4499<\/span>
\n)4688-8299-4664-4385<\/span>
\n)4044-8832-9347-1412<\/span>
\n)4692-4078-2653-8573<\/span>
\n)4997-5257-8004-9774<\/span>
\n)4649-1103-3295-2551<\/span>
\n)4691-3746-9407-0405<\/span>
\n)4548-7929-1826-4899<\/span>
\n)4531-2930-4967-2232<\/span>
\n)4255-8613-9716-5076<\/span><\/p>\n

real\u00a0\u00a0 \u00a00m0.115s<\/span>
\nuser\u00a0\u00a0 \u00a00m0.000s<\/span>
\nsys\u00a0\u00a0 \u00a00m0.000s<\/span>
\nroot@debian:~# <\/span><\/p>\n

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 \ud83d\ude42 Well, it’s pitty!<\/p>\n

-a-<\/p>\n","protected":false},"excerpt":{"rendered":"

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) \ud83d\ude42 PostgreSQL is excellent product. Let\u00b4s see if they are thinking … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,15],"tags":[],"_links":{"self":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/334"}],"collection":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=334"}],"version-history":[{"count":0,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/334\/revisions"}],"wp:attachment":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=334"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}