Thread: pgadmin connection via tunnel and ubuntu user instead of postgres

Hello,

I'm forced to log into a server via SSH and the ubuntu user via ssh keys.
The ubuntu Postgres OS user has no password by default.  When I log in via
ssh, I log in via the ubuntu user, and then "sudo su - postgres" to then be
able to execute "psql" to enter into postgres.  This all works fine.

When I try to connect via PGAdmin3, I setup a tunnel forwarding a port to
the ubuntu servers postgres port and try to connect that way.  This fails
beause I'm connecting in via the ubuntu user and not the postgres user
directly.  I've even tried adding the sudo su - postgres to the "Remote
Command" options in putty under connection --> SSH and although it does turn
my user into the postgres OS user, pgadmin will still not connect via that
tunnel.

Any ideas on what my options are?

Thanks



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pgadmin-connection-via-tunnel-and-ubuntu-user-instead-of-postgres-tp5739822.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 01/11/2013 09:31 PM, pasilveira wrote:
> Hello,
>
> I'm forced to log into a server via SSH and the ubuntu user via ssh keys.
> The ubuntu Postgres OS user has no password by default.  When I log in via
> ssh, I log in via the ubuntu user, and then "sudo su - postgres" to then be
> able to execute "psql" to enter into postgres.  This all works fine.
>
> When I try to connect via PGAdmin3, I setup a tunnel forwarding a port to
> the ubuntu servers postgres port and try to connect that way.  This fails
> beause I'm connecting in via the ubuntu user and not the postgres user
> directly.

What exactly is the error?
Seems you may not have pg_hba.conf set up to allow access for the ubuntu
user.

  I've even tried adding the sudo su - postgres to the "Remote
> Command" options in putty under connection --> SSH and although it does turn
> my user into the postgres OS user, pgadmin will still not connect via that
> tunnel.
>
> Any ideas on what my options are?

Set  up SSL connections for the Postgres server?

http://www.postgresql.org/docs/9.2/interactive/ssl-tcp.html

>
> Thanks
>
>
>

--
Adrian Klaver
adrian.klaver@gmail.com

Thanks Adrian,

Right, the pg_hba.conf file is setup with the default settings.

# Database administrative login by Unix domain socket
local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
# host    all             all             127.0.0.1/32            md5
host    all             all              0.0.0.0/0             md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

I think that I may have to just use the PostgreSQL database user password to
log in and I'll be fine.  I was hoping that I could mimic what I was doing
with SSH in that I could sudo su - postgres and then go into postgres as the
postgres OS user without a DB password and I'd be done.  I don't think the
tunnel is allowing me to do that so I'll have to supply the postgres DB user
password to still get in.

I think I can work with that but it's just a little less optimal that I was
hoping for.  Logging in just like I do in SSH would have been much easier.

Thanks,

Paul





-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Saturday, January 12, 2013 7:08 PM
To: pasilveira
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pgadmin connection via tunnel and ubuntu user instead
of postgres

On 01/11/2013 09:31 PM, pasilveira wrote:
> Hello,
>
> I'm forced to log into a server via SSH and the ubuntu user via ssh keys.
> The ubuntu Postgres OS user has no password by default.  When I log in
> via ssh, I log in via the ubuntu user, and then "sudo su - postgres"
> to then be able to execute "psql" to enter into postgres.  This all works
fine.
>
> When I try to connect via PGAdmin3, I setup a tunnel forwarding a port
> to the ubuntu servers postgres port and try to connect that way.  This
> fails beause I'm connecting in via the ubuntu user and not the
> postgres user directly.

What exactly is the error?
Seems you may not have pg_hba.conf set up to allow access for the ubuntu
user.

  I've even tried adding the sudo su - postgres to the "Remote
> Command" options in putty under connection --> SSH and although it
> does turn my user into the postgres OS user, pgadmin will still not
> connect via that tunnel.
>
> Any ideas on what my options are?

Set  up SSL connections for the Postgres server?

http://www.postgresql.org/docs/9.2/interactive/ssl-tcp.html

>
> Thanks
>
>
>

--
Adrian Klaver
adrian.klaver@gmail.com

On 01/12/2013 04:29 PM, Paul S wrote:
> Thanks Adrian,
>
> Right, the pg_hba.conf file is setup with the default settings.
>
> # Database administrative login by Unix domain socket
> local   all             postgres                                peer
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
> # "local" is for Unix domain socket connections only
> local   all             all                                     peer
> # IPv4 local connections:
> # host    all             all             127.0.0.1/32            md5
> host    all             all              0.0.0.0/0             md5
> # IPv6 local connections:
> host    all             all             ::1/128                 md5
>
> I think that I may have to just use the PostgreSQL database user password to
> log in and I'll be fine.  I was hoping that I could mimic what I was doing
> with SSH in that I could sudo su - postgres and then go into postgres as the
> postgres OS user without a DB password and I'd be done.  I don't think the
> tunnel is allowing me to do that so I'll have to supply the postgres DB user
> password to still get in.


Well PgAdmin connects to a service(Postgres) not a user account so I
would think what you want to do would be difficult.

>
> I think I can work with that but it's just a little less optimal that I was
> hoping for.  Logging in just like I do in SSH would have been much easier.
>
> Thanks,
>
> Paul
>
>
>
>
>



--
Adrian Klaver
adrian.klaver@gmail.com

On 01/12/2013 04:29 PM, Paul S wrote:
> Thanks Adrian,

> I think I can work with that but it's just a little less optimal that I was
> hoping for.  Logging in just like I do in SSH would have been much easier.

Just had a thought. Could you not set up SSH keys for the postgres user
and connect the tunnel as that user directly?

>
> Thanks,
>
> Paul
>
>
>
>
>



--
Adrian Klaver
adrian.klaver@gmail.com

I believe the main problem in this situation is that you are trying to use a security context that only exists on the server you are trying to connect to, this is not available from a remote client.

Adrian,

I was thinking the same thing.  I think if I come in via SSH as a postgres
user, then it will work.  I'll try that and post here on how it works.

Thanks,

Paul


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Saturday, January 12, 2013 7:46 PM
To: Paul S
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pgadmin connection via tunnel and ubuntu user instead
of postgres

On 01/12/2013 04:29 PM, Paul S wrote:
> Thanks Adrian,

> I think I can work with that but it's just a little less optimal that
> I was hoping for.  Logging in just like I do in SSH would have been much
easier.

Just had a thought. Could you not set up SSH keys for the postgres user and
connect the tunnel as that user directly?

>
> Thanks,
>
> Paul
>
>
>
>
>



--
Adrian Klaver
adrian.klaver@gmail.com