Re: psql on Mac

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 <jeff(dot)frost(at)gmail(dot)com> 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 <ozan(dot)kah(at)gmail(dot)com>
> 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 <jeff(dot)frost(at)gmail(dot)com> wrote:
>>
>>> psql -U postgres
>>>
>>> is probably what you want.
>>>
>>> On Fri, Oct 19, 2018 at 7:58 AM Ozan Kahramanogullari <
>>> ozan(dot)kah(at)gmail(dot)com> 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
>>>> =========================================
>>>>
>>>