Re: psql on Mac

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
22 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Ozan Kahramanogullari
Thanks, Jeff, for the explanation below. As you have pointed, I am moving the discussion to the novice list.

Logging  in as "postgres" works by using "-U". However, few other things are not clear; I apologise for the long message... 

If I want to run the "psql" for any user, for example, to create a database for the default user "ozan", this does not work.

Now, I have added another line to my file "ph_hba.conf". The first three lines of this file look as follows (although I am not completely sure about the semantics this creates... My intuition is that everything should be accessible to everyone locally, but almost nothing actually is). 

local   all             all                                     trust

local   postgres        postgres                                trust

local   all             postgres                                trust  

XXX:src3 ozan$ psql -U ozan

Password for user ozan: 

psql: FATAL:  password authentication failed for user "ozan"


XXX:src3 ozan$ psql lecture

Password: 

psql: FATAL:  password authentication failed for user "ozan"



XXX:src3 ozan$ psql -U postgres lecture

psql: FATAL:  database "lecture" does not exist


This is on a Mac. The only thing that works is the following, which is fine for messing around with sql commands:

psql -U postgres

Now, to combine my exercise with python, I am setting an environment variable as follows:

export DATABASE_URL="postgres://localhost:5432/lecture"


I am aware that I have NOT created a database called lecture, but this was not possible as well. However, I have created some database, and if I list it using the "-l" switch, I get the following.

XXX:src3 ozan$ psql -U postgres -l

                             List of databases

   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   

-----------+----------+----------+---------+-------+-----------------------

 postgres  | postgres | UTF8     | C       | C     | 

 template0 | postgres | UTF8     | C       | C     | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

 template1 | postgres | UTF8     | C       | C     | =c/postgres          +

           |          |          |         |       | postgres=CTc/postgres

(3 rows)



If I run the simple python code below, I get the messy error message at the bottom with no simple explanation.

I was convinced that I followed the instructions available out there, though there must be some setup stuff that I must have missed. 

Can you please let me know what is missing? To be more concrete, my questions are the following:

1) What should I do to run the following command smoothly.

XXX:src3 ozan$ psql lecture

2) How can I make my python code run without any errors?

3) What is the semantics of the "ph_hba.conf" lines I have inserted?

4) Is there any documentation that explains all this for a novice who wants to have a gentle introduction? (I have collected bits and pieces of information from stackoverflow and other places, but it would be nice to have everything provided in a compact manner, so that one can have a smooth start.)

Best regards,
Ozan

#############
import os

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine(os.getenv("DATABASE_URL"))
db = scoped_session(sessionmaker(bind=engine))

def main():
    flights = db.execute("SELECT origin, destination, duration FROM flights").fetchall()
    for flight in flights:
        print(f"{flight.origin} to {flight.destination}, {flight.duration} minutes.")

if __name__ == "__main__":
    main()
############




XXX:src3 ozan$ python list.py

Traceback (most recent call last):

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect

    return fn()

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 403, in connect

    return _ConnectionFairy._checkout(self)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 791, in _checkout

    fairy = _ConnectionRecord.checkout(pool)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 532, in checkout

    rec = pool._do_get()

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1196, in _do_get

    self._dec_overflow()

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__

    compat.reraise(exc_type, exc_value, exc_tb)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise

    raise value

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1193, in _do_get

    return self._create_connection()

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 350, in _create_connection

    return _ConnectionRecord(self)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 477, in __init__

    self.__connect(first_connect_check=True)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 674, in __connect

    connection = pool._invoke_creator(self)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect

    return dialect.connect(*cargs, **cparams)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect

    return self.dbapi.connect(*cargs, **cparams)

  File "/anaconda3/lib/python3.7/site-packages/psycopg2/__init__.py", line 130, in connect

    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

psycopg2.OperationalError: fe_sendauth: no password supplied



The above exception was the direct cause of the following exception:


Traceback (most recent call last):

  File "list.py", line 17, in <module>

    main()

  File "list.py", line 12, in main

    flights = db.execute("SELECT origin, destination, duration FROM flights").fetchall()

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 153, in do

    return getattr(self.registry(), name)(*args, **kwargs)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1176, in execute

    bind, close_with_result=True).execute(clause, params or {})

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1040, in _connection_for_bind

    engine, execution_options)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 409, in _connection_for_bind

    conn = bind.contextual_connect()

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect

    self._wrap_pool_connect(self.pool.connect, None),

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect

    e, dialect, self)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection

    exc_info

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 248, in reraise

    raise value.with_traceback(tb)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect

    return fn()

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 403, in connect

    return _ConnectionFairy._checkout(self)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 791, in _checkout

    fairy = _ConnectionRecord.checkout(pool)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 532, in checkout

    rec = pool._do_get()

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1196, in _do_get

    self._dec_overflow()

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__

    compat.reraise(exc_type, exc_value, exc_tb)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise

    raise value

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1193, in _do_get

    return self._create_connection()

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 350, in _create_connection

    return _ConnectionRecord(self)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 477, in __init__

    self.__connect(first_connect_check=True)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 674, in __connect

    connection = pool._invoke_creator(self)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect

    return dialect.connect(*cargs, **cparams)

  File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect

    return self.dbapi.connect(*cargs, **cparams)

  File "/anaconda3/lib/python3.7/site-packages/psycopg2/__init__.py", line 130, in connect

    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) fe_sendauth: no password supplied

 (Background on this error at: http://sqlalche.me/e/e3q8) 





On Sun, 21 Oct 2018 at 17:48, Jeff Frost <[hidden email]> wrote:
Please don't remove the list from the Cc field.

Since you set it to trust in the pg_hba.conf file, it should work for any local OS user.

If you want other DB users, you need to create them.

BTW, this is definitely not a bug and would have been better suited for the pgsql-novice list.

On Sun, Oct 21, 2018 at 12:45 AM Ozan Kahramanogullari <[hidden email]> wrote:
Thank you, this works!

Can I make it work also for any user?

Cheers,
Ozan


=========================================
Ozan Kahramanoğulları, PhD
http://sites.google.com/site/ozankahramanogullari/
-----------------------------------------------------------------------
University of Trento, Department of Mathematics
=========================================


On Sat, 20 Oct 2018 at 22:46, Jeff Frost <[hidden email]> wrote:
psql -U postgres

is probably what you want.

On Fri, Oct 19, 2018 at 7:58 AM Ozan Kahramanogullari <[hidden email]> wrote:
Hi,

I am behind a Mac, and I have been trying to get a local server running by following an internet tutorial (which also uses Mac, this one<https://video.cs50.net/web/2018/spring/lectures/>). 

I simply try to run psql on the terminal, and I get various error messages, depending on how I mess up the file "pg_hba.conf", for example by adding the following lines to it, among others: 

local   postgres        postgres                                trust
local   all             postgres                                trust

I have spent the whole morning browsing the internet to find a solution to my problem, and I also tried backtracking from version 11 of PostgreSQL to version 10. I am pasting below some of the error messages that I get. The version 10 warns for no support for Mac; is that the problem?

Perhaps, it is me who hasn't been able to dig out the right information from the internet. However, I must say that I am quite surprised that things get so tedious immediately. I will thus be glad if you could help me with getting this running, perhaps by providing some up-to-date  documentation. 

Thanks,
Ozan


XX:src3 ozan$ psql

Password: 

sql: FATAL:  password authentication failed for user "ozan"

XX:src3 ozan$ psql

Password: 

psql: FATAL:  password authentication failed for user "ozan"

XX:src3 ozan$ sudo -u postgres psql

Password:

Sorry, try again.

Password:

sudo: 1 incorrect password attempt

XX:src3 ozan$ sudo -u postgres psql

Password:

Sorry, try again.

Password:

could not identify current directory: Permission denied

Password: 

could not identify current directory: Permission denied

psql: could not find own program executable

XX:src3 ozan$ sudo -u postgres psql

could not identify current directory: Permission denied

Password: 

could not identify current directory: Permission denied

psql: could not find own program executable

XX:src3 ozan$ psql

Password: 


 




=========================================
Ozan Kahramanoğulları, PhD
http://sites.google.com/site/ozankahramanogullari/
-----------------------------------------------------------------------
University of Trento, Department of Mathematics
=========================================
Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Andrej Ricnik-Bay
This is just a sneaky suspicion of mine, but have you actually created
the user ozan?
http://postgresguide.com/setup/users.html
On Wed, 24 Oct 2018 at 04:58, Ozan Kahramanogullari <[hidden email]> wrote:

>
> Thanks, Jeff, for the explanation below. As you have pointed, I am moving the discussion to the novice list.
>
> Logging  in as "postgres" works by using "-U". However, few other things are not clear; I apologise for the long message...
>
> If I want to run the "psql" for any user, for example, to create a database for the default user "ozan", this does not work.
>
> Now, I have added another line to my file "ph_hba.conf". The first three lines of this file look as follows (although I am not completely sure about the semantics this creates... My intuition is that everything should be accessible to everyone locally, but almost nothing actually is).
>
> local   all             all                                     trust
>
> local   postgres        postgres                                trust
>
> local   all             postgres                                trust
>
> XXX:src3 ozan$ psql -U ozan
>
> Password for user ozan:
>
> psql: FATAL:  password authentication failed for user "ozan"
>
>
> XXX:src3 ozan$ psql lecture
>
> Password:
>
> psql: FATAL:  password authentication failed for user "ozan"
>
>
>
> XXX:src3 ozan$ psql -U postgres lecture
>
> psql: FATAL:  database "lecture" does not exist
>
>
> This is on a Mac. The only thing that works is the following, which is fine for messing around with sql commands:
>
> psql -U postgres
>
> Now, to combine my exercise with python, I am setting an environment variable as follows:
>
> export DATABASE_URL="postgres://localhost:5432/lecture"
>
>
> I am aware that I have NOT created a database called lecture, but this was not possible as well. However, I have created some database, and if I list it using the "-l" switch, I get the following.
>
> XXX:src3 ozan$ psql -U postgres -l
>
>                              List of databases
>
>    Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
>
> -----------+----------+----------+---------+-------+-----------------------
>
>  postgres  | postgres | UTF8     | C       | C     |
>
>  template0 | postgres | UTF8     | C       | C     | =c/postgres          +
>
>            |          |          |         |       | postgres=CTc/postgres
>
>  template1 | postgres | UTF8     | C       | C     | =c/postgres          +
>
>            |          |          |         |       | postgres=CTc/postgres
>
> (3 rows)
>
>
>
> If I run the simple python code below, I get the messy error message at the bottom with no simple explanation.
>
> I was convinced that I followed the instructions available out there, though there must be some setup stuff that I must have missed.
>
> Can you please let me know what is missing? To be more concrete, my questions are the following:
>
> 1) What should I do to run the following command smoothly.
>
> XXX:src3 ozan$ psql lecture
>
> 2) How can I make my python code run without any errors?
>
> 3) What is the semantics of the "ph_hba.conf" lines I have inserted?
>
> 4) Is there any documentation that explains all this for a novice who wants to have a gentle introduction? (I have collected bits and pieces of information from stackoverflow and other places, but it would be nice to have everything provided in a compact manner, so that one can have a smooth start.)
>
> Best regards,
> Ozan
>
> #############
> import os
>
> from sqlalchemy import create_engine
> from sqlalchemy.orm import scoped_session, sessionmaker
>
> engine = create_engine(os.getenv("DATABASE_URL"))
> db = scoped_session(sessionmaker(bind=engine))
>
> def main():
>     flights = db.execute("SELECT origin, destination, duration FROM flights").fetchall()
>     for flight in flights:
>         print(f"{flight.origin} to {flight.destination}, {flight.duration} minutes.")
>
> if __name__ == "__main__":
>     main()
> ############
>
>
>
>
> XXX:src3 ozan$ python list.py
>
> Traceback (most recent call last):
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
>
>     return fn()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 403, in connect
>
>     return _ConnectionFairy._checkout(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 791, in _checkout
>
>     fairy = _ConnectionRecord.checkout(pool)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 532, in checkout
>
>     rec = pool._do_get()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1196, in _do_get
>
>     self._dec_overflow()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
>
>     compat.reraise(exc_type, exc_value, exc_tb)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
>
>     raise value
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
>
>     return self._create_connection()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
>
>     return _ConnectionRecord(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 477, in __init__
>
>     self.__connect(first_connect_check=True)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 674, in __connect
>
>     connection = pool._invoke_creator(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
>
>     return dialect.connect(*cargs, **cparams)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect
>
>     return self.dbapi.connect(*cargs, **cparams)
>
>   File "/anaconda3/lib/python3.7/site-packages/psycopg2/__init__.py", line 130, in connect
>
>     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
>
> psycopg2.OperationalError: fe_sendauth: no password supplied
>
>
>
> The above exception was the direct cause of the following exception:
>
>
> Traceback (most recent call last):
>
>   File "list.py", line 17, in <module>
>
>     main()
>
>   File "list.py", line 12, in main
>
>     flights = db.execute("SELECT origin, destination, duration FROM flights").fetchall()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 153, in do
>
>     return getattr(self.registry(), name)(*args, **kwargs)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1176, in execute
>
>     bind, close_with_result=True).execute(clause, params or {})
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1040, in _connection_for_bind
>
>     engine, execution_options)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 409, in _connection_for_bind
>
>     conn = bind.contextual_connect()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect
>
>     self._wrap_pool_connect(self.pool.connect, None),
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
>
>     e, dialect, self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
>
>     exc_info
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
>
>     reraise(type(exception), exception, tb=exc_tb, cause=cause)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
>
>     raise value.with_traceback(tb)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
>
>     return fn()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 403, in connect
>
>     return _ConnectionFairy._checkout(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 791, in _checkout
>
>     fairy = _ConnectionRecord.checkout(pool)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 532, in checkout
>
>     rec = pool._do_get()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1196, in _do_get
>
>     self._dec_overflow()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
>
>     compat.reraise(exc_type, exc_value, exc_tb)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
>
>     raise value
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
>
>     return self._create_connection()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
>
>     return _ConnectionRecord(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 477, in __init__
>
>     self.__connect(first_connect_check=True)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 674, in __connect
>
>     connection = pool._invoke_creator(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
>
>     return dialect.connect(*cargs, **cparams)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect
>
>     return self.dbapi.connect(*cargs, **cparams)
>
>   File "/anaconda3/lib/python3.7/site-packages/psycopg2/__init__.py", line 130, in connect
>
>     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
>
> sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) fe_sendauth: no password supplied
>
>  (Background on this error at: http://sqlalche.me/e/e3q8)
>
>
>
>
>
> On Sun, 21 Oct 2018 at 17:48, Jeff Frost <[hidden email]> wrote:
>>
>> Please don't remove the list from the Cc field.
>>
>> Since you set it to trust in the pg_hba.conf file, it should work for any local OS user.
>>
>> If you want other DB users, you need to create them.
>>
>> BTW, this is definitely not a bug and would have been better suited for the pgsql-novice list.
>>
>> On Sun, Oct 21, 2018 at 12:45 AM Ozan Kahramanogullari <[hidden email]> wrote:
>>>
>>> Thank you, this works!
>>>
>>> Can I make it work also for any user?
>>>
>>> Cheers,
>>> Ozan
>>>
>>>
>>> =========================================
>>> Ozan Kahramanoğulları, PhD
>>> http://sites.google.com/site/ozankahramanogullari/
>>> -----------------------------------------------------------------------
>>> University of Trento, Department of Mathematics
>>> =========================================
>>>
>>>
>>> On Sat, 20 Oct 2018 at 22:46, Jeff Frost <[hidden email]> wrote:
>>>>
>>>> psql -U postgres
>>>>
>>>> is probably what you want.
>>>>
>>>> On Fri, Oct 19, 2018 at 7:58 AM Ozan Kahramanogullari <[hidden email]> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> I am behind a Mac, and I have been trying to get a local server running by following an internet tutorial (which also uses Mac, this one<https://video.cs50.net/web/2018/spring/lectures/>).
>>>>>
>>>>> I simply try to run psql on the terminal, and I get various error messages, depending on how I mess up the file "pg_hba.conf", for example by adding the following lines to it, among others:
>>>>>
>>>>> local   postgres        postgres                                trust
>>>>> local   all             postgres                                trust
>>>>>
>>>>> I have spent the whole morning browsing the internet to find a solution to my problem, and I also tried backtracking from version 11 of PostgreSQL to version 10. I am pasting below some of the error messages that I get. The version 10 warns for no support for Mac; is that the problem?
>>>>>
>>>>> Perhaps, it is me who hasn't been able to dig out the right information from the internet. However, I must say that I am quite surprised that things get so tedious immediately. I will thus be glad if you could help me with getting this running, perhaps by providing some up-to-date  documentation.
>>>>>
>>>>> Thanks,
>>>>> Ozan
>>>>>
>>>>>
>>>>> XX:src3 ozan$ psql
>>>>>
>>>>> Password:
>>>>>
>>>>> sql: FATAL:  password authentication failed for user "ozan"
>>>>>
>>>>> XX:src3 ozan$ psql
>>>>>
>>>>> Password:
>>>>>
>>>>> psql: FATAL:  password authentication failed for user "ozan"
>>>>>
>>>>> XX:src3 ozan$ sudo -u postgres psql
>>>>>
>>>>> Password:
>>>>>
>>>>> Sorry, try again.
>>>>>
>>>>> Password:
>>>>>
>>>>> sudo: 1 incorrect password attempt
>>>>>
>>>>> XX:src3 ozan$ sudo -u postgres psql
>>>>>
>>>>> Password:
>>>>>
>>>>> Sorry, try again.
>>>>>
>>>>> Password:
>>>>>
>>>>> could not identify current directory: Permission denied
>>>>>
>>>>> Password:
>>>>>
>>>>> could not identify current directory: Permission denied
>>>>>
>>>>> psql: could not find own program executable
>>>>>
>>>>> XX:src3 ozan$ sudo -u postgres psql
>>>>>
>>>>> could not identify current directory: Permission denied
>>>>>
>>>>> Password:
>>>>>
>>>>> could not identify current directory: Permission denied
>>>>>
>>>>> psql: could not find own program executable
>>>>>
>>>>> XX:src3 ozan$ psql
>>>>>
>>>>> Password:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> =========================================
>>>>> Ozan Kahramanoğulları, PhD
>>>>> http://sites.google.com/site/ozankahramanogullari/
>>>>> -----------------------------------------------------------------------
>>>>> University of Trento, Department of Mathematics
>>>>> =========================================



--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.georgedillon.com/web/html_email_is_evil.shtml
http://www.catb.org/jargon/html/email-style.html

Reply | Threaded
Open this post in threaded view
|

psql on Mac

Ozan Kahramanogullari
Thank you, Andrej. I tried the instructions in this website. However, this did not provide the desired outcome. I am pasting the command line below.Also, the command "psql -h localhost" did not work.

XXX:src3 ozan$ psql -h localhost

Password: 

psql: FATAL:  password authentication failed for user "ozan"


XXX:src3 ozan$ psql -U postgres 

psql (10.5)

Type "help" for help.


postgres=# CREATE USER ozan WITH PASSWORD 'parrot';

CREATE ROLE

postgres=# \q


XXX:src3 ozan$ psql

Password: 

psql: FATAL:  database "ozan" does not exist


XXX:src3 ozan$ psql -U postgres 

psql (10.5)

Type "help" for help.


postgres=# GRANT ALL PRIVILEGES  ON DATABASE lecture to ozan;

GRANT


postgres=# \q


Ozan's MacBookPro:src3 ozan$ psql

Password: 

psql: FATAL:  database "ozan" does not exist


                  ^



On Tue, 23 Oct 2018 at 21:17, Andrej <[hidden email]> wrote:
This is just a sneaky suspicion of mine, but have you actually created
the user ozan?
http://postgresguide.com/setup/users.html
On Wed, 24 Oct 2018 at 04:58, Ozan Kahramanogullari <[hidden email]> wrote:
>
> Thanks, Jeff, for the explanation below. As you have pointed, I am moving the discussion to the novice list.
>
> Logging  in as "postgres" works by using "-U". However, few other things are not clear; I apologise for the long message...
>
> If I want to run the "psql" for any user, for example, to create a database for the default user "ozan", this does not work.
>
> Now, I have added another line to my file "ph_hba.conf". The first three lines of this file look as follows (although I am not completely sure about the semantics this creates... My intuition is that everything should be accessible to everyone locally, but almost nothing actually is).
>
> local   all             all                                     trust
>
> local   postgres        postgres                                trust
>
> local   all             postgres                                trust
>
> XXX:src3 ozan$ psql -U ozan
>
> Password for user ozan:
>
> psql: FATAL:  password authentication failed for user "ozan"
>
>
> XXX:src3 ozan$ psql lecture
>
> Password:
>
> psql: FATAL:  password authentication failed for user "ozan"
>
>
>
> XXX:src3 ozan$ psql -U postgres lecture
>
> psql: FATAL:  database "lecture" does not exist
>
>
> This is on a Mac. The only thing that works is the following, which is fine for messing around with sql commands:
>
> psql -U postgres
>
> Now, to combine my exercise with python, I am setting an environment variable as follows:
>
> export DATABASE_URL="postgres://localhost:5432/lecture"
>
>
> I am aware that I have NOT created a database called lecture, but this was not possible as well. However, I have created some database, and if I list it using the "-l" switch, I get the following.
>
> XXX:src3 ozan$ psql -U postgres -l
>
>                              List of databases
>
>    Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
>
> -----------+----------+----------+---------+-------+-----------------------
>
>  postgres  | postgres | UTF8     | C       | C     |
>
>  template0 | postgres | UTF8     | C       | C     | =c/postgres          +
>
>            |          |          |         |       | postgres=CTc/postgres
>
>  template1 | postgres | UTF8     | C       | C     | =c/postgres          +
>
>            |          |          |         |       | postgres=CTc/postgres
>
> (3 rows)
>
>
>
> If I run the simple python code below, I get the messy error message at the bottom with no simple explanation.
>
> I was convinced that I followed the instructions available out there, though there must be some setup stuff that I must have missed.
>
> Can you please let me know what is missing? To be more concrete, my questions are the following:
>
> 1) What should I do to run the following command smoothly.
>
> XXX:src3 ozan$ psql lecture
>
> 2) How can I make my python code run without any errors?
>
> 3) What is the semantics of the "ph_hba.conf" lines I have inserted?
>
> 4) Is there any documentation that explains all this for a novice who wants to have a gentle introduction? (I have collected bits and pieces of information from stackoverflow and other places, but it would be nice to have everything provided in a compact manner, so that one can have a smooth start.)
>
> Best regards,
> Ozan
>
> #############
> import os
>
> from sqlalchemy import create_engine
> from sqlalchemy.orm import scoped_session, sessionmaker
>
> engine = create_engine(os.getenv("DATABASE_URL"))
> db = scoped_session(sessionmaker(bind=engine))
>
> def main():
>     flights = db.execute("SELECT origin, destination, duration FROM flights").fetchall()
>     for flight in flights:
>         print(f"{flight.origin} to {flight.destination}, {flight.duration} minutes.")
>
> if __name__ == "__main__":
>     main()
> ############
>
>
>
>
> XXX:src3 ozan$ python list.py
>
> Traceback (most recent call last):
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
>
>     return fn()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 403, in connect
>
>     return _ConnectionFairy._checkout(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 791, in _checkout
>
>     fairy = _ConnectionRecord.checkout(pool)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 532, in checkout
>
>     rec = pool._do_get()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1196, in _do_get
>
>     self._dec_overflow()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
>
>     compat.reraise(exc_type, exc_value, exc_tb)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
>
>     raise value
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
>
>     return self._create_connection()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
>
>     return _ConnectionRecord(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 477, in __init__
>
>     self.__connect(first_connect_check=True)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 674, in __connect
>
>     connection = pool._invoke_creator(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
>
>     return dialect.connect(*cargs, **cparams)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect
>
>     return self.dbapi.connect(*cargs, **cparams)
>
>   File "/anaconda3/lib/python3.7/site-packages/psycopg2/__init__.py", line 130, in connect
>
>     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
>
> psycopg2.OperationalError: fe_sendauth: no password supplied
>
>
>
> The above exception was the direct cause of the following exception:
>
>
> Traceback (most recent call last):
>
>   File "list.py", line 17, in <module>
>
>     main()
>
>   File "list.py", line 12, in main
>
>     flights = db.execute("SELECT origin, destination, duration FROM flights").fetchall()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 153, in do
>
>     return getattr(self.registry(), name)(*args, **kwargs)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1176, in execute
>
>     bind, close_with_result=True).execute(clause, params or {})
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1040, in _connection_for_bind
>
>     engine, execution_options)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 409, in _connection_for_bind
>
>     conn = bind.contextual_connect()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect
>
>     self._wrap_pool_connect(self.pool.connect, None),
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
>
>     e, dialect, self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
>
>     exc_info
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
>
>     reraise(type(exception), exception, tb=exc_tb, cause=cause)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
>
>     raise value.with_traceback(tb)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
>
>     return fn()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 403, in connect
>
>     return _ConnectionFairy._checkout(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 791, in _checkout
>
>     fairy = _ConnectionRecord.checkout(pool)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 532, in checkout
>
>     rec = pool._do_get()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1196, in _do_get
>
>     self._dec_overflow()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
>
>     compat.reraise(exc_type, exc_value, exc_tb)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
>
>     raise value
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
>
>     return self._create_connection()
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
>
>     return _ConnectionRecord(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 477, in __init__
>
>     self.__connect(first_connect_check=True)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/pool.py", line 674, in __connect
>
>     connection = pool._invoke_creator(self)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
>
>     return dialect.connect(*cargs, **cparams)
>
>   File "/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect
>
>     return self.dbapi.connect(*cargs, **cparams)
>
>   File "/anaconda3/lib/python3.7/site-packages/psycopg2/__init__.py", line 130, in connect
>
>     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
>
> sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) fe_sendauth: no password supplied
>
>  (Background on this error at: http://sqlalche.me/e/e3q8)
>
>
>
>
>
> On Sun, 21 Oct 2018 at 17:48, Jeff Frost <[hidden email]> wrote:
>>
>> Please don't remove the list from the Cc field.
>>
>> Since you set it to trust in the pg_hba.conf file, it should work for any local OS user.
>>
>> If you want other DB users, you need to create them.
>>
>> BTW, this is definitely not a bug and would have been better suited for the pgsql-novice list.
>>
>> On Sun, Oct 21, 2018 at 12:45 AM Ozan Kahramanogullari <[hidden email]> wrote:
>>>
>>> Thank you, this works!
>>>
>>> Can I make it work also for any user?
>>>
>>> Cheers,
>>> Ozan
>>>
>>>
>>> =========================================
>>> Ozan Kahramanoğulları, PhD
>>> http://sites.google.com/site/ozankahramanogullari/
>>> -----------------------------------------------------------------------
>>> University of Trento, Department of Mathematics
>>> =========================================
>>>
>>>
>>> On Sat, 20 Oct 2018 at 22:46, Jeff Frost <[hidden email]> wrote:
>>>>
>>>> psql -U postgres
>>>>
>>>> is probably what you want.
>>>>
>>>> On Fri, Oct 19, 2018 at 7:58 AM Ozan Kahramanogullari <[hidden email]> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> I am behind a Mac, and I have been trying to get a local server running by following an internet tutorial (which also uses Mac, this one<https://video.cs50.net/web/2018/spring/lectures/>).
>>>>>
>>>>> I simply try to run psql on the terminal, and I get various error messages, depending on how I mess up the file "pg_hba.conf", for example by adding the following lines to it, among others:
>>>>>
>>>>> local   postgres        postgres                                trust
>>>>> local   all             postgres                                trust
>>>>>
>>>>> I have spent the whole morning browsing the internet to find a solution to my problem, and I also tried backtracking from version 11 of PostgreSQL to version 10. I am pasting below some of the error messages that I get. The version 10 warns for no support for Mac; is that the problem?
>>>>>
>>>>> Perhaps, it is me who hasn't been able to dig out the right information from the internet. However, I must say that I am quite surprised that things get so tedious immediately. I will thus be glad if you could help me with getting this running, perhaps by providing some up-to-date  documentation.
>>>>>
>>>>> Thanks,
>>>>> Ozan
>>>>>
>>>>>
>>>>> XX:src3 ozan$ psql
>>>>>
>>>>> Password:
>>>>>
>>>>> sql: FATAL:  password authentication failed for user "ozan"
>>>>>
>>>>> XX:src3 ozan$ psql
>>>>>
>>>>> Password:
>>>>>
>>>>> psql: FATAL:  password authentication failed for user "ozan"
>>>>>
>>>>> XX:src3 ozan$ sudo -u postgres psql
>>>>>
>>>>> Password:
>>>>>
>>>>> Sorry, try again.
>>>>>
>>>>> Password:
>>>>>
>>>>> sudo: 1 incorrect password attempt
>>>>>
>>>>> XX:src3 ozan$ sudo -u postgres psql
>>>>>
>>>>> Password:
>>>>>
>>>>> Sorry, try again.
>>>>>
>>>>> Password:
>>>>>
>>>>> could not identify current directory: Permission denied
>>>>>
>>>>> Password:
>>>>>
>>>>> could not identify current directory: Permission denied
>>>>>
>>>>> psql: could not find own program executable
>>>>>
>>>>> XX:src3 ozan$ sudo -u postgres psql
>>>>>
>>>>> could not identify current directory: Permission denied
>>>>>
>>>>> Password:
>>>>>
>>>>> could not identify current directory: Permission denied
>>>>>
>>>>> psql: could not find own program executable
>>>>>
>>>>> XX:src3 ozan$ psql
>>>>>
>>>>> Password:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> =========================================
>>>>> Ozan Kahramanoğulları, PhD
>>>>> http://sites.google.com/site/ozankahramanogullari/
>>>>> -----------------------------------------------------------------------
>>>>> University of Trento, Department of Mathematics
>>>>> =========================================



--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.georgedillon.com/web/html_email_is_evil.shtml
http://www.catb.org/jargon/html/email-style.html
Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Adam Jensen
Have you tried being explicit about both the database name and the user
name?

psql --dbname=lecture --username=ozan

or

psql -d lecture -U ozan


On 10/23/2018 06:26 PM, Ozan Kahramanogullari wrote:

> Thank you, Andrej. I tried the instructions in this website. However,
> this did not provide the desired outcome. I am pasting the command line
> below.Also, the command "psql -h localhost" did not work.
>
> XXX:src3 ozan$ psql -h localhost
>
> Password: 
>
> psql: FATAL:  password authentication failed for user "ozan"
>
>
> XXX:src3 ozan$ psql -U postgres 
>
> psql (10.5)
>
> Type "help" for help.
>
>
> postgres=# CREATE USER ozan WITH PASSWORD 'parrot';
>
> CREATE ROLE
>
> postgres=# \q
>
>
> XXX:src3 ozan$ psql
>
> Password: 
>
> psql: FATAL:  database "ozan" does not exist
>
>
> XXX:src3 ozan$ psql -U postgres 
>
> psql (10.5)
>
> Type "help" for help.
>
>
> postgres=# GRANT ALL PRIVILEGES  ON DATABASE lecture to ozan;
>
> GRANT
>
>
> postgres=# \q
>
>
> Ozan's MacBookPro:src3 ozan$ psql
>
> Password: 
>
> psql: FATAL:  database "ozan" does not exist
>
>


Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Ozan Kahramanogullari
In reply to this post by Ozan Kahramanogullari
Thanks, Bee!

I am happy to try Postico eventually. 

Regarding psql "sudo su" to create the password seems to work to create the database. However, it still asks for the password for the user "ozan" although the setting is "trust" for "all" in the file "pg_hba.conf" file. However, I am not getting what is happening in the background. I am still wondering why this is not running as it is supposed... 

Cheers,
Ozan



On Wed, 24 Oct 2018 at 01:25, Bee.Lists <[hidden email]> wrote:
Try Postico.  It’s a Mac client.  The two (terminal) and that app will flush out your needs.

Second, su into the Postgres user to create users.  Sounds like your users are bungled.  Only the Postgres user can do stuff until you create another user with proper privs. 



> On Oct 23, 2018, at 6:26 PM, Ozan Kahramanogullari <[hidden email]> wrote:
>
> Thank you, Andrej. I tried the instructions in this website. However, this did not provide the desired outcome. I am pasting the command line below.Also, the command "psql -h localhost" did not work.
>
> XXX:src3 ozan$ psql -h localhost
> Password:
> psql: FATAL:  password authentication failed for user "ozan"
>
> XXX:src3 ozan$ psql -U postgres
> psql (10.5)
> Type "help" for help.
>
> postgres=# CREATE USER ozan WITH PASSWORD 'parrot';
> CREATE ROLE
> postgres=# \q
>
> XXX:src3 ozan$ psql
> Password:
> psql: FATAL:  database "ozan" does not exist
>
> XXX:src3 ozan$ psql -U postgres
> psql (10.5)
> Type "help" for help.
>
> postgres=# GRANT ALL PRIVILEGES  ON DATABASE lecture to ozan;
> GRANT
>
> postgres=# \q
>
> Ozan's MacBookPro:src3 ozan$ psql
> Password:
> psql: FATAL:  database "ozan" does not exist
>



Cheers, Bee




Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Tom Lane-2
In reply to this post by Ozan Kahramanogullari
Ozan Kahramanogullari <[hidden email]> writes:
> Thank you, Andrej. I tried the instructions in this website. However, this
> did not provide the desired outcome. I am pasting the command line
> below.Also, the command "psql -h localhost" did not work.

> XXX:src3 ozan$ psql -h localhost
> Password:
> psql: FATAL:  password authentication failed for user "ozan"

This definitely indicates that the server thinks you specified password
auth (of one flavor or another).  Now this:

> local   all             all                                     trust

looks like it ought to let everything in without a password, but the
problem is that "local" only means Unix-socket connections.  So it
should apply when you say "psql" or "psql -U somebody", but it does
*not* apply to TCP connections which is what you get with "-h localhost".
What you really want, if you just want to let in all same-machine
connections indiscriminately, is this:

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

(copied from what I've got on my Mac).

> local   postgres        postgres                                trust
> local   all             postgres                                trust

These lines are pretty pointless given the previous "local all all"
line; that one will capture any connections that these could match.

> XXX:src3 ozan$ psql -U ozan
> Password for user ozan:
> psql: FATAL:  password authentication failed for user "ozan"

This, on the other hand, suggests that you've got still more problems;
this should have matched the "local all all" line, but obviously it
did not.  Somewhere the server is finding a pg_hba.conf line that is
telling it to use password authentication.  Some possibilities:

1. You aren't editing the right pg_hba.conf file.  ("show hba_file"
should confirm where the server thinks that file is.  In PG v10 or
newer, the pg_hba_file_rules view is also helpful.)

2. You stuck the lines you're showing us at the bottom of an existing
pg_hba.conf file, not paying attention to earlier lines that would
control what the server does.  The rule is that the first entry in
pg_hba.conf that matches the connection request is what's used.  So
be sure to delete or comment out rules you don't want.

3. You edited the right file, but didn't restart or reload the server
afterwards, so it's still using old data.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Ozan Kahramanogullari
Hi Tom, Thank you! I copied and pasted the content of your pg_hba file as it is. Then, I rebooted my computer.  There is only one installation of postgreSQL, so there is only such file. It is in the folder "/Library/PostgreSQL/10/data".   

Sorry, but none of this brings me close to the what I see in the tutorial.

Cheers,
Ozan

On Wed, 24 Oct 2018 at 11:08, Tom Lane <[hidden email]> wrote:
Ozan Kahramanogullari <[hidden email]> writes:
> Thank you, Andrej. I tried the instructions in this website. However, this
> did not provide the desired outcome. I am pasting the command line
> below.Also, the command "psql -h localhost" did not work.

> XXX:src3 ozan$ psql -h localhost
> Password:
> psql: FATAL:  password authentication failed for user "ozan"

This definitely indicates that the server thinks you specified password
auth (of one flavor or another).  Now this:

> local   all             all                                     trust

looks like it ought to let everything in without a password, but the
problem is that "local" only means Unix-socket connections.  So it
should apply when you say "psql" or "psql -U somebody", but it does
*not* apply to TCP connections which is what you get with "-h localhost".
What you really want, if you just want to let in all same-machine
connections indiscriminately, is this:

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

(copied from what I've got on my Mac).

> local   postgres        postgres                                trust
> local   all             postgres                                trust

These lines are pretty pointless given the previous "local all all"
line; that one will capture any connections that these could match.

> XXX:src3 ozan$ psql -U ozan
> Password for user ozan:
> psql: FATAL:  password authentication failed for user "ozan"

This, on the other hand, suggests that you've got still more problems;
this should have matched the "local all all" line, but obviously it
did not.  Somewhere the server is finding a pg_hba.conf line that is
telling it to use password authentication.  Some possibilities:

1. You aren't editing the right pg_hba.conf file.  ("show hba_file"
should confirm where the server thinks that file is.  In PG v10 or
newer, the pg_hba_file_rules view is also helpful.)

2. You stuck the lines you're showing us at the bottom of an existing
pg_hba.conf file, not paying attention to earlier lines that would
control what the server does.  The rule is that the first entry in
pg_hba.conf that matches the connection request is what's used.  So
be sure to delete or comment out rules you don't want.

3. You edited the right file, but didn't restart or reload the server
afterwards, so it's still using old data.

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Tom Lane-2
Ozan Kahramanogullari <[hidden email]> writes:
> Hi Tom, Thank you! I copied and pasted the content of your pg_hba file as
> it is. Then, I rebooted my computer.  There is only one installation of
> postgreSQL, so there is only such file. It is in the folder
> "/Library/PostgreSQL/10/data".

I'm a bit suspicious of that, first because that doesn't seem like a
normal place to put Postgres, and second because it's not working for
you so there must be *something* incorrect in what you've told us.
Did you confirm the server's notion of where pg_hba.conf is via
"show" and/or the view?

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Ozan Kahramanogullari
Well, I took the default settings when I was installing PostgreSQL. And, yes, I ran the "show" commands on the command line console; it gave an error. I ran the "show" commands on the psql prompt that delivered nothing, no error as well.

I am open to any suggestions, including reinstalling everything from scratch, if there are any precise instructions. 

I am not ruling out that I am doing something wrong. Though, I was convinced that following simple instructions would not be that hard. I have tried to provide all the information in the previous emails. I am puzzled myself; after all, this is quite a standard platform (Mac) and all I am trying to do is access some SQL database with a python file (see below).

 Cheers,
Ozan

######## SQL stuff ###########

CREATE TABLE flights (
    id SERIAL PRIMARY KEY,
    origin VARCHAR NOT NULL,
    destination VARCHAR NOT NULL,
    duration INTEGER NOT NULL
);

INSERT INTO flights (origin, destination, duration) VALUES ('New York', 'London', 415);
INSERT INTO flights (origin, destination, duration) VALUES ('Shanghai', 'Paris', 760);
INSERT INTO flights (origin, destination, duration) VALUES ('Istanbul', 'Tokyo', 700);
INSERT INTO flights (origin, destination, duration) VALUES ('New York', 'Paris', 435);
INSERT INTO flights (origin, destination, duration) VALUES ('Moscow', 'Paris', 245);
INSERT INTO flights (origin, destination, duration) VALUES ('Lima', 'New York', 455);

######## python file ##########
import os

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine(os.getenv("DATABASE_URL"))
db = scoped_session(sessionmaker(bind=engine))

def main():
    flights = db.execute("SELECT origin, destination, duration FROM flights").fetchall()
    for flight in flights:
        print(f"{flight.origin} to {flight.destination}, {flight.duration} minutes.")

if __name__ == "__main__":
    main()
##############################

On Wed, 24 Oct 2018 at 11:42, Tom Lane <[hidden email]> wrote:
Ozan Kahramanogullari <[hidden email]> writes:
> Hi Tom, Thank you! I copied and pasted the content of your pg_hba file as
> it is. Then, I rebooted my computer.  There is only one installation of
> postgreSQL, so there is only such file. It is in the folder
> "/Library/PostgreSQL/10/data".

I'm a bit suspicious of that, first because that doesn't seem like a
normal place to put Postgres, and second because it's not working for
you so there must be *something* incorrect in what you've told us.
Did you confirm the server's notion of where pg_hba.conf is via
"show" and/or the view?

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Gerald Cheves
On 10/24/2018 5:54 AM, Ozan Kahramanogullari wrote:
> I am puzzled myself; after all, this is quite a standard platform
> (Mac) and all I am trying to do is access some SQL database with a
> python file (see below).
>
The Postgres installation process is not standard at all because most
come with a partial installation and it's not in standard directory
location, which causes people have problems with psql commands. The
installation has to be placed in the PATH

https://apple.stackexchange.com/questions/262831/postgresql-not-in-path-after-installation

- G

--
siamo arrivati sani e salvi


Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Tom Lane-2
In reply to this post by Ozan Kahramanogullari
Ozan Kahramanogullari <[hidden email]> writes:
> Well, I took the default settings when I was installing PostgreSQL. And,
> yes, I ran the "show" commands on the command line console; it gave an
> error. I ran the "show" commands on the psql prompt that delivered nothing,
> no error as well.

Nothing?  That seems pretty unlikely.  It should look something like this:

$ psql postgres
psql (12devel)
Type "help" for help.

postgres=# show hba_file;
                hba_file                
-----------------------------------------
 /Users/tgl/testversion/data/pg_hba.conf
(1 row)

Maybe you forgot the command-ending semicolon, or something?

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Ozan Kahramanogullari
Yes, sorry you are right. The file is indeed indicated to be where I mentioned.

~~~~~~~~~~

postgres=# show hba_file;

                hba_file                 

-----------------------------------------

 /Library/PostgreSQL/10/data/pg_hba.conf

(1 row)



~~~~~~~


Maybe this would help you to help me, thank you.


I am also pasting the complete content of the the pg_hba.conf below.


Cheers,

Ozan

 


=========================================

# PostgreSQL Client Authentication Configuration File

# ===================================================

#

# Refer to the "Client Authentication" section in the PostgreSQL

# documentation for a complete description of this file.  A short

# synopsis follows.

#

# This file controls: which hosts are allowed to connect, how clients

# are authenticated, which PostgreSQL user names they can use, which

# databases they can access.  Records take one of these forms:

#

# local      DATABASE  USER  METHOD  [OPTIONS]

# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

#

# (The uppercase items must be replaced by actual values.)

#

# The first field is the connection type: "local" is a Unix-domain

# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,

# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a

# plain TCP/IP socket.

#

# DATABASE can be "all", "sameuser", "samerole", "replication", a

# database name, or a comma-separated list thereof. The "all"

# keyword does not match "replication". Access to replication

# must be enabled in a separate record (see example below).

#

# USER can be "all", a user name, a group name prefixed with "+", or a

# comma-separated list thereof.  In both the DATABASE and USER fields

# you can also write a file name prefixed with "@" to include names

# from a separate file.

#

# ADDRESS specifies the set of hosts the record matches.  It can be a

# host name, or it is made up of an IP address and a CIDR mask that is

# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that

# specifies the number of significant bits in the mask.  A host name

# that starts with a dot (.) matches a suffix of the actual host name.

# Alternatively, you can write an IP address and netmask in separate

# columns to specify the set of hosts.  Instead of a CIDR-address, you

# can write "samehost" to match any of the server's own IP addresses,

# or "samenet" to match any address in any subnet that the server is

# directly connected to.

#

# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",

# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".

# Note that "password" sends passwords in clear text; "md5" or

# "scram-sha-256" are preferred since they send encrypted passwords.

#

# OPTIONS are a set of options for the authentication in the format

# NAME=VALUE.  The available options depend on the different

# authentication methods -- refer to the "Client Authentication"

# section in the documentation for a list of which options are

# available for which authentication methods.

#

#

# Database and user names containing spaces, commas, quotes and other

# special characters must be quoted.  Quoting one of the keywords

# "all", "sameuser", "samerole" or "replication" makes the name lose

# its special character, and just match a database or username with

# that name.

#

# This file is read on server startup and when the server receives a

# SIGHUP signal.  If you edit the file on a running system, you have to

# SIGHUP the server for the changes to take effect, run "pg_ctl reload",

# or execute "SELECT pg_reload_conf()".

#

# Put your actual configuration here

# ----------------------------------

#

# If you want to allow non-local connections, you need to add more

# "host" records.  In that case you will also need to make PostgreSQL

# listen on a non-local interface via the listen_addresses

# configuration parameter, or via the -i or -h command line switches.



# TYPE  DATABASE        USER            ADDRESS                 METHOD



# "local" is for Unix domain socket connections only

local   all             all                                     trust

# IPv4 local connections:

host    all             all             127.0.0.1/32            trust

# IPv6 local connections:

host    all             all             ::1/128                 trust

# Allow replication connections from localhost, by a user with the

# replication privilege.

local   replication     all                                     trust

host    replication     all             127.0.0.1/32            trust

host    replication     all             ::1/128                 trust



On Wed, 24 Oct 2018 at 12:24, Tom Lane <[hidden email]> wrote:
Ozan Kahramanogullari <[hidden email]> writes:
> Well, I took the default settings when I was installing PostgreSQL. And,
> yes, I ran the "show" commands on the command line console; it gave an
> error. I ran the "show" commands on the psql prompt that delivered nothing,
> no error as well.

Nothing?  That seems pretty unlikely.  It should look something like this:

$ psql postgres
psql (12devel)
Type "help" for help.

postgres=# show hba_file;
                hba_file                 
-----------------------------------------
 /Users/tgl/testversion/data/pg_hba.conf
(1 row)

Maybe you forgot the command-ending semicolon, or something?

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Tom Lane-2
Ozan Kahramanogullari <[hidden email]> writes:
> Yes, sorry you are right. The file is indeed indicated to be where I
> mentioned.

Well, there's something really screwy going on here.  A server using the
pg_hba.conf file you showed us should never ask for a password at all.
I'm reduced to theorizing that you have two Postgres servers running on
that machine, or "localhost" isn't resolving the way you think and the
"psql -h localhost" cases are connecting somewhere else.

I'm not sure of the current state of affairs, but at least older versions
of Apple Remote Desktop had a version of PG included ... so the idea that
there's another server besides the one you know about isn't to be
dismissed out-of-hand.  Digging through "ps auxww" output might be
informative.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Ozan Kahramanogullari
Thank you, Tom!

I am attaching the output of "ps auxww". I am not able to make any sense of it, sorry! Maybe you can?

Ozan




On Wed, 24 Oct 2018 at 12:47, Tom Lane <[hidden email]> wrote:
Ozan Kahramanogullari <[hidden email]> writes:
> Yes, sorry you are right. The file is indeed indicated to be where I
> mentioned.

Well, there's something really screwy going on here.  A server using the
pg_hba.conf file you showed us should never ask for a password at all.
I'm reduced to theorizing that you have two Postgres servers running on
that machine, or "localhost" isn't resolving the way you think and the
"psql -h localhost" cases are connecting somewhere else.

I'm not sure of the current state of affairs, but at least older versions
of Apple Remote Desktop had a version of PG included ... so the idea that
there's another server besides the one you know about isn't to be
dismissed out-of-hand.  Digging through "ps auxww" output might be
informative.

                        regards, tom lane

ps.log (80K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Tom Lane-2
Ozan Kahramanogullari <[hidden email]> writes:
> I am attaching the output of "ps auxww". I am not able to make any sense of
> it, sorry! Maybe you can?

I only see one postmaster:

postgres            70   0.0  0.1  4511528  17908   ??  Ss   11:20AM   0:00.18 /Library/PostgreSQL/10/bin/postmaster -D/Library/PostgreSQL/10/data

though it's got the usual collection of child processes:

postgres           211   0.0  0.0  4519444   1708   ??  Ss   11:20AM   0:00.00 postgres: bgworker: logical replication launcher    
postgres           209   0.0  0.0  4374376   1120   ??  Ss   11:20AM   0:00.48 postgres: stats collector process    
postgres           208   0.0  0.0  4519444   2548   ??  Ss   11:20AM   0:00.15 postgres: autovacuum launcher process    
postgres           207   0.0  0.0  4511252   5188   ??  Ss   11:20AM   0:00.04 postgres: wal writer process    
postgres           206   0.0  0.0  4511252   2292   ??  Ss   11:20AM   0:00.05 postgres: writer process    
postgres           205   0.0  0.0  4511252   2688   ??  Ss   11:20AM   0:00.01 postgres: checkpointer process    
postgres           202   0.0  0.0  4366184    828   ??  Ss   11:20AM   0:00.00 postgres: logger process    

and here's your psql and associated backend process:

root              1011   0.0  0.0  4299336   5720 s000  S+   12:41PM   0:00.03 psql -U postgres
postgres          1012   0.0  0.0  4511640   2968   ??  Ss   12:41PM   0:00.00 postgres: postgres postgres [local] idle

So that all looks pretty normal, and shoots down my idea about two local
postmasters.  That leaves the remote-connection idea.  What do you get
from "nslookup localhost" on the command line?  What happens if you
write "psql -h 127.0.0.1" instead of writing "psql -h localhost"?

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Ozan Kahramanogullari
I get these:

XXX:~ ozan$ nslookup localhost

Server: 192.168.206.99

Address: 192.168.206.99#53


Name: localhost.unitn.it

Address: 10.31.101.168


------


I first try this:


XXX:~ ozan$ psql -h 127.0.0.1

psql: FATAL:  database "ozan" does not exist


------


XXX:~ ozan$ psql -U postgres

psql (10.5)

Type "help" for help.


postgres=# CREATE USER ozan WITH PASSWORD 'parrot';

ERROR:  role "ozan" already exists

postgres=# CREATE DATABASE lecture;

ERROR:  database "lecture" already exists

postgres=# GRANT ALL PRIVILEGES ON DATABASE lecture to ozan;

GRANT

postgres=# \q

XXX:~ ozan$ psql -h 127.0.0.1

psql: FATAL:  database "ozan" does not exist

Ozan's MacBookPro:~ ozan$ 

 


-----


XXX:~ ozan$ sudo su

Password:

sh-3.2# psql -h 127.0.0.1

psql: FATAL:  role "root" does not exist


On Wed, 24 Oct 2018 at 13:22, Tom Lane <[hidden email]> wrote:
Ozan Kahramanogullari <[hidden email]> writes:
> I am attaching the output of "ps auxww". I am not able to make any sense of
> it, sorry! Maybe you can?

I only see one postmaster:

postgres            70   0.0  0.1  4511528  17908   ??  Ss   11:20AM   0:00.18 /Library/PostgreSQL/10/bin/postmaster -D/Library/PostgreSQL/10/data

though it's got the usual collection of child processes:

postgres           211   0.0  0.0  4519444   1708   ??  Ss   11:20AM   0:00.00 postgres: bgworker: logical replication launcher   
postgres           209   0.0  0.0  4374376   1120   ??  Ss   11:20AM   0:00.48 postgres: stats collector process   
postgres           208   0.0  0.0  4519444   2548   ??  Ss   11:20AM   0:00.15 postgres: autovacuum launcher process   
postgres           207   0.0  0.0  4511252   5188   ??  Ss   11:20AM   0:00.04 postgres: wal writer process   
postgres           206   0.0  0.0  4511252   2292   ??  Ss   11:20AM   0:00.05 postgres: writer process   
postgres           205   0.0  0.0  4511252   2688   ??  Ss   11:20AM   0:00.01 postgres: checkpointer process   
postgres           202   0.0  0.0  4366184    828   ??  Ss   11:20AM   0:00.00 postgres: logger process   

and here's your psql and associated backend process:

root              1011   0.0  0.0  4299336   5720 s000  S+   12:41PM   0:00.03 psql -U postgres
postgres          1012   0.0  0.0  4511640   2968   ??  Ss   12:41PM   0:00.00 postgres: postgres postgres [local] idle

So that all looks pretty normal, and shoots down my idea about two local
postmasters.  That leaves the remote-connection idea.  What do you get
from "nslookup localhost" on the command line?  What happens if you
write "psql -h 127.0.0.1" instead of writing "psql -h localhost"?

                        regards, tom lane
Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Tom Lane-2
Ozan Kahramanogullari <[hidden email]> writes:
> I get these:

> XXX:~ ozan$ nslookup localhost
> Server: 192.168.206.99
> Address: 192.168.206.99#53
> Name: localhost.unitn.it
> Address: 10.31.101.168

Well, *that's* screwed up.  You should complain to your local network
manager about it.  "localhost" ought to resolve to 127.0.0.1,
or ::1/128 in IPv6-land, not something else.  It's possible that
10.31.101.168 is your Mac's address, but that still doesn't make this
correct behavior.  So for the moment, don't use "-h localhost".

> I first try this:
> XXX:~ ozan$ psql -h 127.0.0.1
> psql: FATAL:  database "ozan" does not exist

Check ...

> XXX:~ ozan$ psql -U postgres
> psql (10.5)
> Type "help" for help.

> postgres=# CREATE USER ozan WITH PASSWORD 'parrot';
> ERROR:  role "ozan" already exists
> postgres=# CREATE DATABASE lecture;
> ERROR:  database "lecture" already exists
> postgres=# GRANT ALL PRIVILEGES ON DATABASE lecture to ozan;
> GRANT

That's all fine, but you didn't create a database named "ozan",
so it's unsurprising that you get this:

> XXX:~ ozan$ psql -h 127.0.0.1
> psql: FATAL:  database "ozan" does not exist

If you want the connection to go to the database named "lecture",
you need to say "psql lecture", or some more verbose form of that
such as "psql -h 127.0.0.1 -U ozan lecture".  When you don't mention
a database on the command line, psql's default behavior is to try
to connect to a database named the same as the user.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Ozan Kahramanogullari

Thank you!

> XXX:~ ozan$ nslookup localhost
> Server: 192.168.206.99
> Address: 192.168.206.99#53
> Name: localhost.unitn.it
> Address: 10.31.101.168

Well, *that's* screwed up.  You should complain to your local network
manager about it.  "localhost" ought to resolve to 127.0.0.1,
or ::1/128 in IPv6-land, not something else.  It's possible that
10.31.101.168 is your Mac's address, but that still doesn't make this
correct behavior.  So for the moment, don't use "-h localhost".

Before I go and break b.., can you guess the reason for this?  

If you want the connection to go to the database named "lecture",
you need to say "psql lecture", or some more verbose form of that
such as "psql -h 127.0.0.1 -U ozan lecture". 
 
This seems to work for accessing the database on the command line. The Python code seems to work as well. 

Though, I must admit that I am still pretty much confused about what is going on. So, the problem seems to be the localhost that is somehow messed up. Is that right?

Cheers,
Ozan



Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Tom Lane-2
Ozan Kahramanogullari <[hidden email]> writes:
>> Well, *that's* screwed up.  You should complain to your local network
>> manager about it.  "localhost" ought to resolve to 127.0.0.1,
>> or ::1/128 in IPv6-land, not something else.  It's possible that
>> 10.31.101.168 is your Mac's address, but that still doesn't make this
>> correct behavior.  So for the moment, don't use "-h localhost".

> Before I go and break b.., can you guess the reason for this?

No idea.  But if you want to cite chapter and verse, the most
authoritative reference I came across in a quick search was RFC 1912
"Common DNS Operational and Configuration Errors",

http://www.faqs.org/rfcs/rfc1912.html

That's pretty old, but as far as I can tell from faqs.org it has never
been obsoleted, and it says that localhost ought to resolve to 127.0.0.1,
period.

> Though, I must admit that I am still pretty much confused about what is
> going on. So, the problem seems to be the localhost that is somehow messed
> up. Is that right?

I'm still confused too.  We have one piece of the puzzle: "-h localhost"
doesn't do what we thought it would.  But it's not very clear what the
heck it *is* connecting to.  There's apparently some Postgres server
active there, because you're getting back plausible responses not
"connection failed" --- but it's not your Postgres server.  Do you have
another way to find out what machine 10.31.101.168 actually refers to?

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: psql on Mac

Ozan Kahramanogullari

=========================================
Ozan Kahramanoğulları, PhD
http://sites.google.com/site/ozankahramanogullari/
-----------------------------------------------------------------------
University of Trento, Department of Mathematics
=========================================
  Do you have another way to find out what machine 10.31.101.168 actually refers to?


What are the means for this?

Cheers,
Ozan
 

12