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