Create user postgres on Ubuntu

Question

So I have installed postgresql9.3 on Ubuntu. Now I have to create a new user. So everything is possible with the superuser postgres. But I need for every new db a new user.

So what I did:

sudo -u postgres psql
CREATE USER python with PASSWORD 'python';
CREATE DATABASE dbpython;
GRANT ALL PRIVILEGES ON DATABASE dbpython to python;

I also modified the /etc/postgresql/9.1/main/pg_hba.conf file and change the authentication setting of local frompeer to md5.

After I've restarted postgres I want use my new user:

ubuntu@ip-172-31-33-139:~$ su python 
No passwd entry for user 'python'

But

ubuntu@ip-172-31-33-139:~$ sudo service postgresql restart
 * Restarting PostgreSQL 9.3 database server                             [ OK ]
ubuntu@ip-172-31-33-139:~$ psql -d dbpython -U python
Password for user python:
psql (9.3.6)
Type "help" for help.

dbpython=>

Why is that working and the su python isn't? Important remark: I dit not create a new python user in my Ubuntu, I hope this isn't necessary for every new user on postgres.

Answer 1

You are mixing PostgreSQL user and UNIX user, which is a totally different thing.

CREATE USER python with PASSWORD 'python';

This command only create a PostgreSQL user and do not create any UNIX user, you can check it by displaying user list on /etc/passwd file.

If you also want a UNIX user, you will have to create it yourself (or scripting it).