Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2013
    Posts
    23

    Unanswered: Port option not working PSQL

    Hello -

    I am trying to connect to a Postgres server running on Oracle Virtual Box.
    Host and Guest OS: Ubuntu 13.04.

    I have HOST port 15432 forwarded to GUEST port 5432.

    So while I can log into Postgres from within the GUEST (VM) I can't log into it from the HOST.
    And I have Postgres on HOST as well and PSQL works fine there as well.

    So the cmd below works on respective GUEST and HOST to connect into respective Postgres with PSQL:
    sudo -u postgres psql postgres

    However when I use the -p and the -h option:
    sudo -h localhost -p 15432 -u postgres psql postgres

    As documented here:
    PostgreSQL: Documentation: 9.0: psql

    Where p is for port and h for hostname.

    I get usage error as in:
    usage: sudo [-D level] -h | -K | -k | -V
    usage: sudo -v [-AknS] [-D level] [-g groupname|#gid] [-p prompt] [-u user name|#uid]
    usage: sudo -l[l] [-AknS] [-D level] [-g groupname|#gid] [-p prompt] [-U user name] [-u user name|#uid] [-g groupname|#gid] [command]
    usage: sudo [-AbEHknPS] [-r role] [-t type] [-C fd] [-D level] [-g groupname|#gid] [-p prompt] [-u user name|#uid] [-g groupname|#gid] [VAR=value] [-i|-s] [<command>]
    usage: sudo -e [-AknS] [-r role] [-t type] [-C fd] [-D level] [-g groupname|#gid] [-p prompt] [-u user name|#uid] file ...

    ...
    And when I try the directly as below - I get the errors as below as in Ubuntu it seems you can login as above:
    monosij@blue:~$ psql -d postgres -U postgres
    psql: FATAL: Peer authentication failed for user "postgres"
    monosij@blue:~$ sudo psql -d postgres -U postgres
    psql: FATAL: Peer authentication failed for user "postgres"
    monosij@blue:~$ sudo psql -d postgres -U postgres -W
    Password for user postgres:
    psql: FATAL: Peer authentication failed for user "postgres"
    monosij@blue:~$ sudo psql -d postgres -U postgres -W
    Password for user postgres:
    psql: FATAL: Peer authentication failed for user "postgres"

    ...
    Hope I have been clear in my examples and in what I am trying to do.

    Thank you for your help.

    Monosij

  2. #2
    Join Date
    Dec 2012
    Posts
    43
    Hello mdr,

    sudo -h localhost -p 15432 -u postgres psql postgres

    failed because you have forgotten the command "psql".

    Please try the following command:

    sudo -u postgres psql -h localhost -p 15432

    Note:
    "sudo" has nothing to do with PostgreSQL. With "sudo" you become user "root" unless the option "-u" is used. In the command above you become user "postgres".

    Note: You can ommit the option "-u postgres" if your operating username is identical with the postgreSQL username.

    Did you execute the psql command (via "sudo") on "HOST"? If yes you will be connectetd to the PostgreSQL server on "HOST" beacue you used the option "-h localhost". If you want to connect from "HOST" to "GUEST" you have to use the option -h "<IP_ADDRESS_OR_HOSTNAME_OF_GUEST>"
    Insetead of <IP_ADDRESS_OR_HOSTNAME_OF_GUEST> you must type the ip- address or hostname (if well known) of the host you want to connect (of course witheout "<" and ">").
    Example: sudo -u postgres psql -h 192.168.2.123 -p 15432

    But don't forget to enable PostgreSQL connections from other hosts in pg_hba.conf on the destination host.

    To avoid using "sudo" you can create an other PostgreSQL "superuser" like the default PostgreSQL "superuser" called "postgres".

  3. #3
    Join Date
    Jun 2013
    Posts
    23
    Hi Bibjet -

    Thank you for your help.

    ...
    I tried the host address as below. And also with cap U. Did not work.

    monosij@blue:~$ sudo -u postgres psql -h 10.0.2.15 -p 15432 -u postgres psql postgres
    /usr/lib/postgresql/9.1/bin/psql: invalid option -- 'u'
    Try "psql --help" for more information.

    And using localhost as below gave the same error:
    monosij@blue:/space/development/python/dbms$ sudo -u postgres psql -h localhost -p 15432 -u postgres psql postgres
    /usr/lib/postgresql/9.1/bin/psql: invalid option -- 'u'
    Try "psql --help" for more information.

    I used the GUEST ip address 10.0.2.15 above but HOST does not see it when I ping it.

    HOST is only routing calls to GUEST by port numbers I have opened up for GUEST through the Oracle Virtual Box configuration.

    So HOST port 10022 is mapped to GUEST 10022 to make SSH work.
    So from HOST I can type ssh -p 10022 localhost - logs me into GUEST from HOST with terminal on HOST.
    Similarly I have opened up port 15432 in Oracle VM config to route from HOST port 15432 to GUEST port 15432.

    ...
    Now you may be asking why I am trying to connect PSQL from HOST to GUEST as I can just log into GUEST and run PSQL, which works fine.
    So to be precise - I don't need to.

    However I am running some Python scripts to load test data into Progres. I am using the SQLAlchemy driver.
    It would be great to run the script from HOST to load into GUEST.
    Else have to ftp them to GUEST and run them every time.

    ...
    The Python SQLAlchemy driver connects to PG as below:
    engine = create_engine('postgresql://USER:PWD@localhost:5432/DB')

    The driver connects fine and does what it needs to when I connect with regular port option 5432 ie PG on HOST.

    However it does not connect when I run it with port 15432 as below to port 15432 where GUEST PG is accessible.
    engine = create_engine('postgresql://USER:PWD@localhost:15432/DB')

    ...
    That is why I was trying to connect with psql into the GUEST PG through HOST port 15432.

    Netstat shows 15432 is handled by Oracle VM as is 10022 (for SSH).
    So while SSH works fine - PSQL does not seem to as it should really not be any different.

    ...
    I have not enabled connections from other hosts in pg_hba.conf located: /etc/postgresql/9.1/main/pg_hba.conf

    Do I need to do that to enable the port based login from HOST to GUEST?

    I will try to find some docs on the parameters to connect from external HOSTS but really this is not a TCP connection - just a port forwarding.

    ...
    DIFFERENT ISSUE
    ...
    Also regards your comment:
    To avoid using "sudo" you can create an other PostgreSQL "superuser" like the default PostgreSQL "superuser" called "postgres".
    There is already one superuser postgres. Are you talking about creating another superuser?

    I installed Postgres in Ubuntu as per this documentation.
    https://help.ubuntu.com/community/PostgreSQL

    They discuss connecting to Postgres after installation and changing the password as below:
    ...
    Basic Server Setup
    To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command.

    In a terminal, type:
    sudo -u postgres psql postgres
    Set a password for the "postgres" database role using the command:

    \password postgres
    and give your password when prompted. The password text will be hidden from the console for security purposes.

    Type Control+D to exit the posgreSQL prompt.
    ...

    I did the above, but have been logging on with the sudo cmd as they mentioned:
    sudo -u postgres psql postgres

    As without sudo it does not work as it is not a linux cmd:
    -u postgres psql postgres

    And I tried this as well as per CarlosinFL:
    psql -d <somedb> -U testuser -W

    Does not work:
    monosij@blue-vm:~$ psql -d postgres -U postgres -W
    Password for user postgres:
    psql: FATAL: Peer authentication failed for user "postgres"

    for user postgres or any other user:
    monosij@blue-vm:~$ psql -d pimcore -U pimuser -W
    Password for user pimuser:
    psql: FATAL: Peer authentication failed for user "pimuser"

    I had meant to ask on this but was not a priority as I have been logging in with the sudo cmd:
    sudo -u postgres psql postgres

    And changing session and database.

    However I do not like logging in with sudo cmd as psql should be for all users I assume. I should just be logging in to the DBMS as user postgress with the password.

    Since it has been working for what I have been doing I had meant to ask on this later.

    But glad you brought it up.

    ...
    Thank you for your help and time. Hope I have provided necessary details.

    I probably need to get a PG admin book. If there is one you could recommend would be great. Else can find the best on Amazon.

    If there is a good website would be great. Need some examples - the PG docs do not show different options.

    Thank you again.

    Monosij

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •