How do I login and authenticate to Postgresql after a fresh install?
Solution 1:
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 aspostgres
. - 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'sSA
, 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.
Solution 2:
by default you would need to use the postgres user:
sudo -u postgres psql postgres
Solution 3:
The error your are getting is because your-ubuntu-username is not a valid Postgres user.
You need to tell psql what database username to use
psql -U postgres
You may also need to specify the database to connect to
psql -U postgres -d <dbname>
Solution 4:
The main difference between logging in with a postgres user or any other user created by us, is that when using the postgres user it is NOT necessary to specify the host with -h and instead for another user if.
Login with postgres user
$ psql -U postgres
Creation and login with another user
# CREATE ROLE usertest LOGIN PASSWORD 'pwtest';
# CREATE DATABASE dbtest WITH OWNER = usertest;
# SHOW port;
# \q
$ psql -h localhost -d dbtest -U usertest -p 5432
GL
Source
Solution 5:
you can also connect to database as "normal" user (not postgres):
postgres=# \connect opensim Opensim_Tester localhost;
Password for user Opensim_Tester:
You are now connected to database "opensim" as user "Opensim_Tester" on host "localhost" at port "5432"