I have a database with an owner for it. My problem is that, though I have assigned a password for the owner, any other database user can access this database locally, even without a password.

How can I configure the database so that only a specific user has access to it (only with right password)?

  • 3
    I think you need to tighten the config in your pg_hba.conf. – Colin 't Hart Jan 28 '19 at 20:33
  • revoke connect on database your_db_name from public; grant connect on database your_db_name to your_user;? – a_horse_with_no_name Jan 28 '19 at 21:53

You will have to edit your pg_hba.conf file. If you really want to reduce possible access to a single user (see the note below), you will need the following lines, and only these:

host    all       dbowner    all       md5
local   all       dbowner              md5

This means that only dbowner will have access to any DB, logging in via the Unix socket or a TCP connection (from all addresses, including localhost). You can replace 'all' with your DB name if you want to limit access to a single DB.

After having edited the file, you will have to reload the config - depending on your system, do a pg_ctl reload or kill -HUP <postmaster_pid>, or do this form inside the DB, by calling

SELECT pg_reload_conf();

Please note that in the future you might want to do certain things that need rights dbowner may not have (be it SUPERUSER, CREATEUSER or something else). Depending on the policy you follow, you'll have to edit the file again to allow further access to a user (eg. postgres) that has the necessary privileges, or you have to assign these to dbowner before closing the door on other users.

| improve this answer | |
  • I am going to try this. Thank you for the help. – Kiran Thapa Jan 30 '19 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.