How to configure database permissions correctly in postgresql

Question

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.

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
– Vao Tsun, Commented May 9, 2016 at 5: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
– Vao Tsun, Commented May 9, 2016 at 5: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. — r_31415
– r_31415, Commented May 9, 2016 at 6:16
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
– Vao Tsun, Commented May 9, 2016 at 6:25
if you create os user "tester" and su to it, you can connect to db locally with "peer" authentication — Vao Tsun
– Vao Tsun, Commented May 9, 2016 at 6:28

Vao Tsun · Accepted Answer · 2016-05-10 05:38:17Z

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

answered May 10, 2016 at 5:38

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to configure database permissions correctly in postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related