[转] BACK TO BLOG HOME How PostgreSQL maps your tables into physical files
A quick recap
In my last blog post I showed how to protect your data using Transparent Data Encryption, and how the resulting data would look like should an attacker gain hold of your files.
But how exactly would one go about from accessing the data via PostgreSQL to locating their store in the file system?
It all starts with pg_database
PostgreSQL stores its data files in the same location as its configuration and control files for a database cluster, and it is usually in the directory specified by the PGDATA environment variable. These files are organised into a number of subdirectories.
Starting at the top, each database within the cluster has a folder named after the OID of the database, in the subdirectory base. In this example, we will have PGDATA as /cluster/acme/data, so the database directories will be located in /cluster/acme/data/base.
Using psql, list the OIDs of your databases:
datname | oid
-----------+------
template1 | 1
template0 | 15210
postgres | 15215
(3 rows)
Based on the result, we can expect the directory PGDATA/base to have the OID subdirectories 1, 15210, and 15215 — so in the command line we can confirm:
/cluster/acme/data
$ cd /cluster/acme/data/base
/cluster/acme/data/base
$ ls
1 15210 15215
Note above the directory named data — it was automatically created by initdb when I created the cluster. If I had used the WebAdmin GUI tool to create the cluster, apart from data, it would also have created the backup directory by default.
These OID subdirectories contain the system catalog for each database, and are the default location for database files. Each of these subdirectories contain a number of files containing data for either a table or a single index, and each of these files is named after the filenode number. There are several other types of files, and particularly prominent are those with the _fsm or _vm suffix — these files track information such as free space available and occurrences of dead tuples for the corresponding relations.
To find out the filenode of a database object (such as table, view, index, catalog, etc), we can use the catalog pg_class. If we wanted to find out which are the physical files for catalog pg_statistic in the database postgres (OID 15215), we would retrieve its filenode as follows:
relname | relfilenode
--------------|------------
pg_statistic | 14947
pg_type | 0
test | 16385
The filenode for pg_statistic is 14947, so it means that its files will be named 14947*, and they will be stored in PGDATA/base/15215. We can check the directory in Linux, as follows:
/cluster/acme/data/base/15215
$ ls
/cluster/acme/data/base
$ ls
14947 14992 15028 15065_vm 15132 15172
14947_fsm 14992_fsm 15029 15067 15133 15174
14947_vm 14992_vm 15030 15068 15133_fsm 15175
14949 14994 15030_fsm 15069 15133_vm 15175_fsm
But what if I am using tablespaces?
In our example we are using tablespaces, since we are using Transparent Data Encryption, so the structure is a little different. When a user creates a tablespace, along with the location on disk, a symbolic link to the folder in the pg_tblspc sub folder is also created. The symbolic link is named the same as the tablespace OID.
/cluster/acme/data/pg_tblspc
$ ls -l
lrwxrwxrwx. 1 gary gary 27 Jul 19 11:13 16393 -> /cluster/acme/tablespace
lrwxrwxrwx. 1 gary gary 27 Jul 19 11:25 16406 -> /cluster/acme/tablespace2
Within the tablespace location is a folder that corresponds to the version of PostgreSQL that FUJITSU Enterprise Postgres shares core components with. This allows subsequent versions of a database to use the same tablespace without conflicts.
/cluster/acme/data/pg_tblspc/16393
$ ls
PG_11_201809051
Now, within the version folder, the structure is essentially the same as the base structure with folders named after the database OID.
Accessing the data directly via file
We now know how to trace the location of a database object all the way down to its physical location. So let’s have a look inside one of the physical files and see if we can read anything. First, to make things easier let’s see what data we are looking for. So I created a table that just has some text values called and stored 3 rows in it - here is what it looks like:
id | name
----+---------------
1 | testname
2 | private name
3 | very personal
(3 rows)
At this stage we already know the corresponding physical file name is for the table, because we already obtained its filenode (16385) from pg_class a few steps above.
If we now edit the corresponding physical file 16385 using vi, we can easily see the values of those rows. I find it a bit easier to read if we switch vi to hex mode by using :%!xxd:
0001f40: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0001f50: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0001f70: 0000 0000 0000 0000 0000 0000 0000 0000 ................
0001f80: 0000 0000 0000 0000 0300 0200 0209 1800 ................
0001f90: 0200 0000 1d76 6572 7920 7065 7273 6f6e .....very person
0001fa0: 616c 0000 0000 0000 ef07 0000 0000 0000 al..............
0001fb0: 0000 0000 0000 0000 0200 0000 0000 0000 ................
0001fc0: 0200 0000 1b70 7269 7661 7465 206e 616d .....private nam
0001fd0: 6500 0000 0000 0000 ee07 0000 0000 0000 e...............
0001fe0: 0000 0000 0000 0000 0100 0200 0209 1800 ................
0001ff0: 0000 0000 1374 6573 746e 616d 6500 0000 .....testname...
Someone who has hacked into your system probably isn’t going to log into a psql session and select the names of the files they want to look at. They will more likely grab as much as they can, then put the information together after trying to derive some meaning from it.
Unless you are using Transparent Data Encryption
Even if an attacker or unauthorised personnel gains access to your data files, they can be protected using PCI DSS-compliant transparent data encryption. In this case, even after a breach, the only information obtained would be a strongly encrypted file.
0001f40: ae72 2fc0 e173 574e 5368 ba59 eef6 770d .r/..sWNSh.Y..w.
0001f50: 0585 f024 fa30 ce3a 1f49 60bc a985 ba4a ...$.0.:.I`....J
0001f70: 3a0d 3218 a6d3 9c28 6b2a b78f 9a20 854a :.2....(k*... .J
0001f80: bea7 b3d1 d43e 7be2 da4c 4165 4fb8 e3c9 .....>{..LAe0...
0001f90: 5a11 ff60 7949 11c1 df7e cec3 8106 7bd4 Z..`yI...~....{.
0001fa0: f9cd 9efd d82a 6eed e5da a1d3 b8de a386 .....*n.........
0001fb0: cebe 3498 f63e 4e86 9de7 14f4 b5e2 1310 ..4..>N.........
0001fc0: fdde 9011 2af4 fe21 c85f 0052 67bd fd37 ....*..!._.Rg..7
0001fd0: a754 f4b7 1946 588a 1892 dbc8 6e37 cc81 .T...FX.....n7..
0001fe0: bf75 d6c9 b4dc 7d53 d289 0160 1646 0406 .u....}S...`.F..
0001ff0: 3f69 b7c8 130d 26f6 d13d 824f a165 28d3 ?i....&..=.0.e(.
If you are interested in learning more about Transparent Data Encryption or Data Masking, and how they may improve your PostgreSQL database, please contact us directly. Fujitsu provides 24/7 PostgreSQL support and services, DBA and developer training, and our own enhanced version of PostgreSQL — FUJITSU Enterprise Postgres.
