How to login and authenticate to Postgresql after a fresh install?

authentication, database, passwords, postgresql, ubuntu

Solution

There are two methods you can use. Both require creating a user and a database.

By default psql connects to the database with the same name as the user. So there is a convention to make that the "user's database". And there is no reason to break that convention if your user only needs one database. We'll be using `mydatabase` as the example database name.

Using createuser and createdb, we can be explicit about the database name,

$ sudo -u postgres createuser -s $USER
$ createdb mydatabase
$ psql -d mydatabase

You should probably be omitting that entirely and letting all the commands default to the user's name instead.

$ sudo -u postgres createuser -s $USER
$ createdb
$ psql

Using the SQL administration commands, and connecting with a password over TCP

$ sudo -u postgres psql postgres

And, then in the psql shell

CREATE ROLE myuser LOGIN PASSWORD 'mypass';
CREATE DATABASE mydatabase WITH OWNER = myuser;

Then you can login,

$ psql -h localhost -d mydatabase -U myuser -p <port>

If you don't know the port, you can always get it by running the following, as the `postgres` user,

SHOW port;

Or,

$ grep "port =" /etc/postgresql/*/main/postgresql.conf

Sidenote: the `postgres` user

I suggest NOT modifying the `postgres` user.

- It's normally locked from the OS. No one is supposed to "log in" to the operating system as `postgres`. You're supposed to have root to get to authenticate as `postgres`.

- It's normally not password protected and delegates to the host operating system. This is a good thing. This normally means in order to log in as `postgres` which is the PostgreSQL equivalent of SQL Server's `SA`, you have to have write-access to the underlying data files. And, that means that you could normally wreck havoc anyway.

- By keeping this disabled, you remove the risk of a brute force attack through a named super-user. Concealing and obscuring the name of the superuser has advantages.

Problem

Original source