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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

You need to setup a authentication method that does allow this, or specify
credentials on connecting

HTH

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl

Convince Microsoft!
They need to add TRUNCATE PARTITION in SQL Server
https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table

2013/1/13 Adrian Klaver <adrian.klaver@gmail.com>

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general