now, some time ago i wrote about how to “hack" system with postgresql. today i'll write more about how to make pg database as safe as possible.

i'll go through several steps, tell you what's possible, what's easy, and what is not so easy 🙂

so, let's start the tutorial.

first – base assumption – i assume that we want to protect the database from a *database* user. i will not cover protection from evil system user or administrator.

so, now to the real tutorial 🙂

first thing to be done – disallow remote superuser connections.

this is the basic thing to be done, and it can be done simply.

first, let's track pg_hba.conf file.

in my system it is in here: /home/pgdba/data/pg_hba.conf. on your system – it might be in many places. you can check by issuing this command in psql:

# show hba_file ;
           hba_file
------------------------------
 /home/pgdba/data/pg_hba.conf
(1 row)

fine. now, content of this file on my machine is:

local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         0.0.0.0/0             md5

if you dont understand what it all means – please consult the fine manual

now, for some reasons i have 3 superuser accounts on this machine: pgdba, postgres and depesz.

we will make the changes so connections to these accounts will be able only through local connection (unix socket) and it will require passwords. all other connections trying to login to these users will be banned.

new pg_hba.conf file:

local   all         @admins                           md5
local   all         all                               trust
host    all         @admins     0.0.0.0/0             reject
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         0.0.0.0/0             md5

and i created new file: /home/pgdba/data/admins:

depesz
pgdba
postgres

sidenote: remember that after all modifications of pg_hba.conf file (and files used by this file) you have to do “pg_ctl reload".

as it is clearly seen, admins file contains names of 3 users that i want to limit access to.

while checking pg_hba.conf postgresql will stop at first matching line. thus – “local all all trust" *after* the line with @admins is perfectly fine, but it i would change the order to:

local   all         all                               trust
local   all         @admins                           md5
host    all         @admins     0.0.0.0/0             reject
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         0.0.0.0/0             md5

then all users would be able to locally connect as any user. this is not something we'd like, so be careful when setting pg_hba.conf.

now – some people (especially debian-related) are fans of “ident sameuser" authentication. i can't express how much i hate it. i will not dig into details, let me just say – i use trust on my development machine (laptop), and only md5 on production servers. i think that the ident authentication has some uses, but i didn't found any reasonable. yet.

so, first task was rather simple – we are protected against unwelcome users logging in as superusers (at least – remotely).

before i will go on – there is a tool named dblink (and another called dbilink). please remember that using is lowers your security as it modifies the ip that you are connected from. i'm not saying that these modules are bad – they have perfectly sane uses, but using them should be done after thinking about potential security issues.

so, now, let's go back to our tutorial.

we have our super top secret database which contains sexual preferences of all people. we dont want anybody to connect to the database – besides dedicated user (from webapplication).

so, what we do?

simple, let's go back to the pg_hba.conf, and modify it:

local   all         @admins                           md5
local   secret      webapp                            md5
local   secret      all                               reject
local   all         all                               trust
host    all         @admins     0.0.0.0/0             reject
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         0.0.0.0/0             md5

now with these 2 new lines (with “secret") i allow webapp user to login to secret database (md5 auth), and i reject all other connections.

it is important though that i left “local all @admins md5" before it.

if i would put the admins/md5 line after local/secret/all/reject line it would disallow also superuser connections.

theoretically – it looks fine, but you have to consider 2 things:

1. superuser effectively has shell access, so somebody with this level of access would be able to modify pg_hba.conf, and reloading postgresql configuration – so it's no protection
2. disabling superuser access would mean that everytime you will need something requiring these rights (new language, c-based functions) you will have to modify pg_hba.conf – so there is larger chance of mistake

so, i think that leaving superuser access is a good thing.

now, when i'll try to connect with some other user (let's say “test" user) connection will be rejected:

=> psql -U test -d secret
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "test", database "secret", SSL off

nice. so, nobody can connect to this database (besides one designated user).

now there goes a problem. if somebody would get “webapp" user access, he/she could do anything he/she would want. drop table? fine. truncate table? fine. update? insert? everything will work.

we can't reject rights from owner of tables. so we will need another user.

let's say that we will create user “admin" (which will not be superuser – it will be the admin of secret database). and webapp user will be webapp user. with somehow limited rights.

so, we need to:

• create user admin
• change ownership of all objects in database to admin
• give admin ability to login
• grant some right (and revoke some rights) from webapp

looks simple. create user – no brainer. changing ownership – tedious, but simple. give admin rights – i simply add this line to pg_hba.conf:

local   secret      admin                             md5

before:

local   secret      all                               reject

(and of course pg_ctl reload).

and now let's go to the revoke/grant thing 🙂

our database has this table (and sequence):

> \d
            List OF relations
 Schema |     Name     |   TYPE   | Owner
--------+--------------+----------+-------
 public | users        | TABLE    | admin
 public | users_id_seq | SEQUENCE | admin
(2 ROWS)
>\d users
                             TABLE "public.users"
     COLUMN     |  TYPE   |                     Modifiers
----------------+---------+----------------------------------------------------
 id             | INTEGER | NOT NULL DEFAULT NEXTVAL('users_id_seq'::regclass)
 username       | text    | NOT NULL
 sex_preference | text    |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_username_key" UNIQUE, btree (username)
> SELECT * FROM users;
 id | username | sex_preference
----+----------+----------------
  1 | aa       | qqq
  2 | bb       | www
  3 | cc       | eee
(3 ROWS)

nothing really fancy, just some test data.

now, of course webapp user can't read anything from users table:

(webapp@[LOCAL]:5830) 22:56:51 [secret]
> SELECT * FROM users;
ERROR:  permission denied FOR relation users

now. the problem is that a potential rogue user can (for example) create dummy table, fill it up with random garbage – just to break postgresql.

simple solution:

• connect to secret database as superuser
• revoke create privileges from public (group containing all users)
• grant create privileges to admin user

statements:

(admin@[LOCAL]:5830) 23:12:06 [secret]
> \c - depesz
Password FOR USER "depesz":
You are now connected TO DATABASE "secret" AS USER "depesz".
(depesz@[LOCAL]:5830) 23:12:10 [secret]
# REVOKE CREATE ON schema public FROM public;
REVOKE
(depesz@[LOCAL]:5830) 23:12:15 [secret]
# GRANT CREATE ON schema public TO admin;
GRANT
(depesz@[LOCAL]:5830) 23:12:24 [secret]
# \c - admin
Password FOR USER "admin":
You are now connected TO DATABASE "secret" AS USER "admin".

we need to connect to superuser account because “public" schema belongs to superuser – not to the user that is owner of the database!

ok. to sum the current state:

1. superusers can connect only using unix sockets – thus only from localhost
2. only 2 users can connect to secret database : admin and webapp
3. admin user can do whatever is needed
4. webapp user can connect, and that's more or less everything. he can see table names, but cannot create anything nor can he select/insert/update/delete from tables

now, we want this user to be able to check data for given user.

we could technically do something like:

GRANT SELECT ON TABLE users TO webapp;

but it doesn't look cool – data in users table can be very sensitive, and we dont want any user to be able simply to: select * from users; – thus getting information about sexual life of millions of our users.

we need webapp user to be able to lookup data for given user (insert, update and delete will be skipped as these are very similiar).

how can we do it? with stored procedure of course.

using admin account i create a function:

CREATE OR REPLACE FUNCTION get_user_record(in_user TEXT) RETURNS setof users AS $BODY$
DECLARE
    temprec users;
BEGIN
    FOR temprec IN SELECT * FROM users WHERE username = in_user LOOP
        RETURN NEXT temprec;
    END loop;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql SECURITY DEFINER;

now – since the function is in plpgsql it is automatically available to everyone. so, let's make it a bit safer:

(admin@[LOCAL]:5830) 23:24:21 [secret]
> REVOKE EXECUTE ON FUNCTION get_user_record (text) FROM public;
REVOKE
(admin@[LOCAL]:5830) 23:26:00 [secret]
> GRANT EXECUTE ON FUNCTION get_user_record(text) TO webapp;
GRANT

now, our webapp user can call the function with giving exact username:

(webapp@[LOCAL]:5830) 23:26:10 [secret]
> SELECT * FROM get_user_record('aa');
 id | username | sex_preference
----+----------+----------------
  1 | aa       | qqq
(1 ROW)

but he will not be able to get all records from table.

what's more important – to make the function working we dont need to grant “select" to users table to webapp user. this magic is handled by “security definer" declaration in function definition.

if you're not familiar with it – it works more or less like “suid" on unix systems. everyone calling the function will be seen by postgresql as the user that created the function.

this leads to some issues:

• you have to be very careful writing security definer functions. it was a topic of long discussions, and the basic summary is that in open databases (when simple users (not admins) can create their own obje
  cts) security definer functions can be used to cicumvent limitations.
• if you use “current_user" for anything – it will not work. it will show name of the user that created the function.

in our situation – everything looks ok – webapp will not be able to create it's own objects, so he will not be able to use the function to hack the database, and current_user is not really useful in 2-users-on
ly databases 🙂

using “security definer" functions we can also make insert/update/delete functions, that will do whatever is necessary to validate params and execute requested action. possibly with some additional logic – like changes logging, backups, denormalization and so on.

one more thing that is here is the fact that webapp user can see the table names. it doesn't bother me personally, but some people would like to have the ability to hide object names.

i didn't really dig the issue, but it looks like a simple thing to do – just line in public schema case, but this time we'll modify privileges of pg_catalog schema:

(admin@[LOCAL]:5830) 23:36:17 [secret]
> \c - depesz
Password FOR USER "depesz":
You are now connected TO DATABASE "secret" AS USER "depesz".
(depesz@[LOCAL]:5830) 23:36:21 [secret]
# REVOKE usage ON schema pg_catalog FROM public;
REVOKE
(depesz@[LOCAL]:5830) 23:36:27 [secret]
# GRANT usage ON schema pg_catalog TO admin;
GRANT
(depesz@[LOCAL]:5830) 23:36:38 [secret]
# \c - admin
Password FOR USER "admin":
You are now connected TO DATABASE "secret" AS USER "admin".

(please note that there is also the information schema, which should be modified in the same way, otherwise one can query information_schema tables and views and still get object names!)

after this, user admin can still work as always, but webapp user will have “some" more limitations:

(webapp@[LOCAL]:5830) 23:34:49 [secret]
> \d
ERROR:  permission denied FOR schema pg_catalog
(webapp@[LOCAL]:5830) 23:37:41 [secret]
> SELECT * FROM get_user_record('aa');
 id | username | sex_preference
----+----------+----------------
  1 | aa       | qqq
(1 ROW)
 
(webapp@[LOCAL]:5830) 23:37:46 [secret]
> \df+ get_user_record
ERROR:  permission denied FOR schema pg_catalog
(webapp@[LOCAL]:5830) 23:37:52 [secret]
> \df get_user_record
ERROR:  permission denied FOR schema pg_catalog

as you can see – webapp user cannot list tables, but still is able to call get_user_record() function. what's also important he is not able to see function source – nor even the basics like number of arguments.

it is possible that in some cases revoking rights from pg_catalog schema will yield bad results, but – as you can see in above example – it does work, and is quite simple to setup.

so, another summary of what we achieved:

1. superusers can connect only using unix sockets – thus only from localhost
2. only 2 users can connect to secret database : admin and webapp
3. admin user can do whatever is needed
4. webapp user can connect to database
5. webapp user doesn't see object (tables, views, function) names nor definitions
6. webapp user cannot query tables directly
7. webapp user can have access to data through functions – with some additional data validation and sanity checks

now, at the end some random notes.

first – besides functions, views also give you the ability to limit rights (user doesn't have access to tables that view is built upon, which makes it possible to build a view which shows all but one columns from a table, and give select rights to this view to user. this user will not be able to see the content of hidden column. i didn't discuss it, as views dont give the same flexibility as functions, and in newer postgresql versions functions are nearly as fast.

second – if you have everything built in functions you are ready to use skype's great pl/proxy tool for load balancing – additional benefit 🙂

third – theoretically one can assume that sombody would simply call get_user_record function with every possible username. it's true, but since we can code anything we want in this functions it is quite simple to make a function that will stop working after 3rd call to non-existant user. or mail admin. or execute shell command pg_ctl stop -m immediate to prevent data leak :).

fourth – when your database is protected and safe – remember about backups. and i'm not talking about doing backups. i'm talking about doing safe backups. dont simply “dump" the database to file, and store it somewhere. make the dump encrypted. at the very least with “zip -e". much better – use gpg/pgp.

and that would be all – as always – if you have any questions, please post them as comments.