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

Question

Did a new install of postgres 8.4 on mint ubuntu. How do I create a user for postgres and login using psql?

When I type psql, it just tells me

psql: FATAL: Ident authentication failed for user "my-ubuntu-username"

Gastronome

Check this blog post. – Hoyt

Answer 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.

1. 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
   

2. 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.

1. 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.
2. 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.
3. 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.

Demigod

I understand that the problems do not occur if using a DB super user (-s) instead of a non-super user. Next question for me would be, what can be done do get this running even the DB user is not a super user. – Cosec

In my case, after creating the user with: $ sudo -u postgres createuser -s $USER, I needed to configure a Password with: ALTER USER myuser WITH PASSWORD 'mypass'; ( after logged in postgres with: sudo -u postgres psql ) – Methodize

How should I understand the command sudo -u postgres psql postgres? I understand sudo means root privilege, -u postgres means log in as user postgres, psql is the command to connect to postgresql, but what's the last postgres doing ? – Mom

Is this really making it the user's database? logging in as the user, I did show config_file and show data_directory and apparently the data_directory still belongs to user postgres.. – Salena

I'm kind of lost why under linux postgres would put data directory under /root by default; database is of arbitrary usage, all sorts of data could go into a database and that's almost surely user action, not root action. – Salena

this is great but one thing that is missing is the password. Is it not best practice to include a password for the newly created user? or has this been omitted by design? please clarify. – Swedish

Answer 2

by default you would need to use the postgres user:

sudo -u postgres psql postgres

Municipality

Or if you can't use sudo, do this: "su postgres" and then "psql" – Harrison

What is the benefit to switching to postgres? sudo -u postgres? Would it be better just to psql -U postgres? – Arrearage

Answer 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>

Wailful

I tried "psql -U postgres", got the same error. I haven't created any database yet, just made a fresh install. – Gastronome

psql -U myuser -d mydb works perfectly, while psql -U myuser tries to use peer auth, thanks! – Occupation

Answer 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

Mahout

Answer 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"

Revareval

Answer 6

If your database client connects with TCP/IP and you have ident auth configured in your pg_hba.conf check that you have an identd installed and running. This is mandatory even if you have only local clients connecting to "localhost".

Also beware that nowadays the identd may have to be IPv6 enabled for Postgresql to welcome clients which connect to localhost.

Dalrymple

Answer 7

For example, first, you can log in to PostgreSQL to use postgres database of the user postgres just after the installation as shown below:

psql -U postgres

Then, you need to input a password after running the command above:

Password for user postgres:

Then, you can create the user(role) john with the password banana as shown below. *By default, PostgreSQL needs a password so you should set PASSWORD and you can omit WITH which is optional and you must use '' for PASSWORD instead of "" otherwise there is error:

CREATE ROLE john WITH LOGIN PASSWORD 'banana';

Then, you can create apple database with the owner john as shown below. *If you omit OWNER, the current user(role) postgres is the database owner:

CREATE DATABASE apple OWNER john

Then, exit PostgreSQL:

\q

Then, you can log in to PostgreSQL to use apple database of the user(role) john as shown below. *If you don't specify database, the specified user is looked for as database so john database which doesn't exist is looked for:

psql -U john apple

Or:

psql -U john -d apple

Or:

psql --username=john --dbname=apple

Or:

psql --username john --dbname apple

Then, you need to input the password banana after running the command above:

Password for user john:

In addition, you can log in to PostgreSQL to use apple database of the user(role) john without password prompt by setting a password(e.g., banana) to PGPASSWORD as shown below:

PGPASSWORD=banana psql -U john apple

Mines