How to connect to PostgreSQL through CLI?

7
votes

I am trying to access postgresql through the command line. However, whenever it is time for me to enter my password, I get the following error: Fatal: password authentication failed for user RMehta. I am pretty sure the reason that password authentication fails is that the user for my database is postgres, and not RMehta.

The only solution I found was using runas in the command line, but I couldn't figure how to get runas to work. Thanks a lot for any advice. I am using windows 7, and postgresql 9.3

3
postgresqlcmdcommand-line-interfacerunas
Can you post exact commands that you issue and also precise output that you get? – vyegorov
I've tried a bunch of things, but the best I could think of was RUNAS /USER:postgres. The output I get for everything I try is RUNAS USAGE: and then a whole bunch of info on how runas is supposed to work. No error messages. I don't anything I do is even recognized as a command. – Ravi Mehta
Apparently you are either providing the wrong username/password or your pg_hba.conf is not configured correctly: stackoverflow.com/… – a_horse_with_no_name

3 Answers

16
votes

For Unix environnement the command line is

psql -U USERNAME -h localhost dbname

For a Windows environment, you may consider replacing "-" with "/"

-U option able you to choose a user to connect with

-h option able you to connect with the TCPIP protocol, you may consider it useless for Windows

2
votes

First make sure your user have a sudo access if not you can use the below command to add your user as sudo user :-

sudo adduser <username> sudo

The change will take effect the next time the user logs in.

Now try running this command :-

sudo -u postgres psql

if that gives you error follow the below steps it should work.

i) Now go to sudo vim /etc/postgresql/<your_postgres_version>/main/pg_hba.conf file and look for line that says :

local   all             postgres                                md5 #peer

and comment that. Just below that line there must be a commented line that says:

local   all             postgres                                peer

or for older versions it'll be :-

local   all         postgres                          ident

Uncomment that line.

ii) Now restart the postgres by using any of these commands :-

sudo /etc/init.d/postgresql restart

OR

sudo service postgresql restart

iii) Now you can simply log into postgres using the following command :

sudo -u postgres psql

iv) once you're in you can create any operation you want to in my case i wanted to create a new database you can do the same using below command :

CREATE DATABASE airflow_replica;
0
votes

psql -h <hostname> -U <username> -d <dbname>

If all params are correct then it will ask you for db password for username entered earlier.