Can someone explain how Postgresql roles, Postgresql users and Linux users relate to each other?

Question

I am trying to setup Postgres 9.3 on an Ubuntu 14 server, and I'm feeling pretty demoralised at this point. I've previously used MySQL, so I'm happy with general database concepts, as well as client/server models etc.

I start with two users - 'root' and 'sam' (me). As 'sam' I install postgresql using apt-get. This also creates a third user called 'postgres'.

Fine.

I'm told that to use postgres you must be logged in as the postgres user, so I switch to that account. Apparently this comes with a postgres admin role (I think I'm fine with the concept of roles per se), and apparently all roles have an associated database of the same name (?). So now I have a Linux account called postgres, a role called postgres, and a database called postgres? This all seems needless but I'm assuming it's useful for reasons I don't know about (not meant sarcastically - this is usually the case when things seem overly complicated at first).

So, to create a database, do I login to the server as postgres, start postgres by typing 'psql' (which doesn't ask for a password - why doesn't the postgres account have a password?) and proceed from there? Or should I create a new role? Does that role need its own Linux user? Should the role be the same name as the database I want to create?

I appreciate this is a bit of a jumble, but my confusion is such that I'm not even sure I understand the fundamentals here. I miss MySQL.

I've been mainly using the DigitalOcean tutorial for this - which are usually very good - but it didn't really make any of this clear. I also read the postgres docs (specifically the installation and users/roles sections) which didn't help, and the google results for this are even less helpful.

This is my last hope before I go back to the safety blanket of MySQL. Any suggestions for making this click?

Answer 1

• OS usernames and Postgres DB usernames are not related; they live in seperate universes.
• one exception: if you connect from the same machine via the unix-domain socket, and you don't explicitely specify a username, your OS name is assumed to be your DB-username, too. (which in most cases is not correct)
• second exception: the "postgres" username is used both as an OS-username (owner of the files, uid of the running processes) and as the DBMS superuser.
• Note: "root" is a bad name for a DB-user.