Postgresql – How to install PostgreSQL to run under a different system user than “postgres”

You only need to create system users if you want to use local authentication.

For network connections, you use CREATE USER and GRANT much as you are used to in MySQL.

The main difference is that in unlike MySQL where the authentication and authorization parts are all inside of tables in the mysql database, part of the authentication rules for Postgres are in an external file pg_hba.conf).

To let user foo connect to database bar from 10.1.1.0/24, you want a line like this in that file:

host bar foo 10.1.1.0/24 md5

After editing pg_hba.conf, remember to run

pg_ctl reload

to reload the config.

When the user tries to connect, they will be required to provide a password (which you provided when you created the user).

Also remember that in PostgreSQL everything is a role. A role that you define as 'WITH LOGIN' is a user while one that does not have this flag is more like a group. For flexibility, I tend to create users with passwords and WITH LOGIN. I then create roles without passwords and grant this role to the login users. My grants are to the group-like role rather than to individual users.

In pg_hba.conf, use +role to match anyone who has the role assigned (instead of foo in the above example)

Try running it manually with debug enabled. This will cause it to run in the foreground and print any error messages to standard error, while also increasing the verbosity.

I believe this will be the correct command line for PostgreSQL 9.3 on Ubuntu, but it might require some very slight tweaking (note: line is split for readability; you can recombine it to a single line (without the backslash) if you want):

/usr/lib/postgresql/9.3/bin/postgres -d 3 -D /var/lib/postgresql/9.3/main \
   -c config_file=/etc/postgresql/9.3/main/postgresql.conf

The beginning is the location of the postgres binary, then we enable debug and set it to level 3 (you can adjust this up or down to increase or decrease verbosity). Next we specify the data directory and the config file to start with. These should be the defaults for Ubuntu Server 12.04, I think.

Hopefully, that'll give you enough information to determine where the problem is.