securing your postgresql database

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 ;
(1 row)

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

local   all         all                               trust
host    all         all          trust
host    all         all         ::1/128               trust
host    all         all             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             reject
host    all         all          trust
host    all         all         ::1/128               trust
host    all         all             md5

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


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             reject
host    all         all          trust
host    all         all         ::1/128               trust
host    all         all             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             reject
host    all         all          trust
host    all         all         ::1/128               trust
host    all         all             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


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    |
    "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


(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;
(depesz@[LOCAL]:5830) 23:12:15 [secret]
# GRANT CREATE ON schema public TO admin;
(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:


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$
    temprec users;
    FOR temprec IN SELECT * FROM users WHERE username = in_user LOOP
        RETURN NEXT temprec;
    END loop;

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;
(admin@[LOCAL]:5830) 23:26:00 [secret]
> GRANT EXECUTE ON FUNCTION get_user_record(text) TO webapp;

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;
(depesz@[LOCAL]:5830) 23:36:27 [secret]
# GRANT usage ON schema pg_catalog TO admin;
(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.

28 thoughts on “securing your postgresql database”

  1. I’m curious – why do you hate “ident sameuser” so much? Granted, it’s horribly broken over TCP-IP, but if you’re on an OS that supports SO_PEERCRED and you’re connecting over a Unix-domain socket, this form of identification is apparently unspoofable – surely that’s a good thing?

  2. i connect over unix socket *only* on devel machines. (on servers postgresql is in chroot, and applications are in chroots, so connecting over unix socket would be … complicated).

    on devel machine i want to be able to simply \c – postgres. i want to be able to psql -U postgres -d template1.
    and “ident” breaks it for me.

    i know i can “fix” it by using pg_ident.conf, but if i have to fix it that way – i much more prefer “trust”.

    additionally – i hate it because it is one of the most common problems with fresh postgresql instalation. (this, tcpip socket, and “user root does not exist :)”.

  3. Good read – picked up some things I hadn’t thought about before.

    Given that you kept he discussion “inside” PostgreSQL you didn’t mention classics that every admin should know – like chrooting. Might make a good part 2?

  4. @Mathew:
    i didn’t because i think that:
    1. every admin should know it
    2. i write specifically about postrgesql, and chrooting (and other things like this) are mostly general, not application dependant

    maybe in future, if i’ll have too much free time 🙂

  5. Good tutorial, however I feel I should point out that disallowing remote superuser connections won’t work in the case of Kerberos authentication. Granted, this is probably the exception among postgresql users, but I’m sure enough people use it.

    However, you can essentially block remote superuser connections by limiting credentials and login shells only to postgres servers, and making sure that only people and admins you trust can log in and use said superuser.

    Still, it should be noted that Kerberos uses TCP/IP and currently cannot use Unix Domain Sockets, if that makes any difference.

  6. @PDK:
    i didn’t mention it for a very simple reason – i didn’t know anything about kerberos authentication as i never used it 🙂

    so, thanks for valuable input – without your comment there was no chance i will write anything about it, but now the tutorial (with comments) give more information then i have 🙂

  7. After revoking usage privileges on schema pg_catalog from public user webapp still runs queries such as “select * from pg_tables” or “select * from pg_user” and inspects database internals.
    Are those queries permitted regardless of user rights on schema and corresponding objects?

  8. apparently revoking access to pg_catalog doesn’t fully work. tom lane responded to my mail fo pgsql-bugs (, but as i understand it is not supported, and will not work fully.

    this of course allows user to see sources of your functions, but i dont see it as a big threat. if your database is protected using proper revokes, knowing source codes of functions will not help much – after all – data will still be secure.

  9. Pingback: Segurança em bancos de dados PostgreSQL | Israel Junior
  10. Pingback: Infraestrutura para Aplicações Web Seguras parte 2 – SGBDs | Alexos Core Labs
  11. Thanks. Tip about “SECURITY DEFINER” was what I was looking for.

  12. Some of this appears to be incorrect now!

    REVOKE USAGE ON SCHEMA pg_catalog FROM public;

    Prevents all access by webapp, despite:
    GRANT EXECUTE ON FUNCTION secret() TO webapp;

    Even re-instating:
    GRANT SELECT ON TABLE users TO webapp;

    Does not allow access and results in this error:
    => \dp
    ERROR: permission denied for schema pg_catalog
    LINE 10: FROM pg_catalog.pg_class c
    Has something changed in V9.1 or did I miss something?

  13. @Charles:
    Sorry, I have no idea what you are saying. Show me:
    1. what you did
    2. what was the result
    3. what you want the result to be

  14. Hi

    I followed your tutorial to the point where we:
    REVOKE USAGE ON SCHEMA pg_catalog FROM public;

    D’oh! .. In trying to reproduce my previous observation I find that I was wrong.

    It seems that a specific sequence of REVOKE/GRANT statements makes some significant difference, which I cannot now reproduce! Also, I’ve been using pgAdmin which turns out to be a bad idea in respect of GRANT/REVOKE permissions!

    Now after:
    REVOKE USAGE ON SCHEMA pg_catalog FROM public;

    $ psql -U webapp -d secret
    Password for user webapp:
    psql (9.1.9)
    Type “help” for help.

    secret=> \d
    ERROR: permission denied for schema pg_catalog
    LINE 5: FROM pg_catalog.pg_class c
    secret=> SELECT secret();
    (1 row)

    Which is precisely as expected. So I suppose I must put it down to a ghost in the machine or my incompetence!

    Anyhow this is what I was trying to achieve. As wbapp:

    secret=> SELECT pwd FROM users;
    ERROR: permission denied for relation users
    secret=> \d users
    ERROR: permission denied for schema pg_catalog
    LINE 5: FROM pg_catalog.pg_class c
    where as:
    secret=> SELECT get_pwd(‘foo’,’’) AS clear;
    (1 row)

    The point being that the function secret() is err.. well, secret! and

    Hides the key to get_pwd(‘foo’,’bar’).

    So, thanks for the excellent tutorial.

    One final question: Given that webapp can use the allowed function, is there any way for webapp to see the code?

  15. @Charles:
    source of function is in prosrc column in pg_proc table.
    But remember that seeing the source doesn’t cause issues – the fact that you can run function, and possibly see its source doesn’t mean that it gives you any more privileges to tables/data.
    OTOH, if you revoked usage on pg_catalog from public, i don’t think you can select the prosrc using the webapp user.

  16. Awesome! Exactly what I was looking for. A function which can be called by the front end without letting users create arbitrary tables… in this case tables created by the Madlib extension (which are persistent by default) need to only be temporary. I will do this with a SECURITY DEFINER on the function without a user being able to access Madlib itself.

Comments are closed.