0

What is the correct way to setup up a database in a new server? I'm currently using an EC2 instance with Ubuntu 14.04. This is what I have done so far:

First install postgresql

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
$ wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

Based on this answer, I execute the following:

$ su - postgres # or sudo -u postgres -i
$ psql template1
template1=# CREATE USER tester WITH PASSWORD 'test_password';
template1=# GRANT ALL PRIVILEGES ON DATABASE "test_database" to tester;
template1=# \q

However, when I want to log in with 'tester', I get this error:

$ psql -U tester test_database
psql: FATAL: Peer authentication failed for user "tester"

According to the documentation, I need to change the pg_hba.conf file:

$ sudo vi /etc/postgresql/9.5/main/pg_hba.conf

and change the following line:

# "local" is for Unix domain socket connections only
local    all    all    peer

to use "md5" instead of "peer". After changing that file, it is necessary to restart the server: 

sudo service postgresql restart

And that way, I'm finally able to connect to the database. However, since I don't understand some of these steps, I'm left wondering if there is a better way to achieve the same and if I'm compromising the security of the database in the process.

Vao Tsun
  • 37,644
  • 8
  • 70
  • 98
Robert Smith
  • 8,127
  • 15
  • 68
  • 113
  • 1
    you dont have to restart it. just reload (to pickup changes in hba.conf)... If Im not mistaken peer would require same user in OS, while md5 just checks the password – Vao Tsun May 09 '16 at 05:44
  • and btw what is the question? if you compromiss security by setting md5 instead of peer for local connections? then no, u don't – Vao Tsun May 09 '16 at 05:45
  • That's part of the question. Is this the best way to assign a new user for a database? As you can see, I just followed different tutorials to get this done but I have read variations of this procedure. – Robert Smith May 09 '16 at 06:16
  • 1
    http://www.postgresql.org/docs/current/static/auth-methods.html describes different auth methods, but basically the main one is create user in postgres instance, set a password for him and configure hba.conf for your needs. – Vao Tsun May 09 '16 at 06:25
  • if you create os user "tester" and su to it, you can connect to db locally with "peer" authentication – Vao Tsun May 09 '16 at 06:28
  • Thank you. You might want to post your comments as an answer. – Robert Smith May 09 '16 at 19:36

1 Answers1

1

http://postgresql.org/docs/current/static/auth-methods.html describes different auth methods, but basically the main one is create user in postgres instance, set a password for him and configure hba.conf for your needs.

if you create os user "tester" and su to it, you can connect to db locally with "peer" authentication

Vao Tsun
  • 37,644
  • 8
  • 70
  • 98