PostgreSQL on amazon linux

Question

I have an amazon server instance(Amazon Linux AMI release 2010.11.2 (beta) )that is running my postgreSQL server 8.4. I want to access it with my locally running pgAdmin and i am not able to connect it.
I have many question i need help with:
1. On server postgresql is running and psql is able to connect with it but i can't access it remotely.(I guess port 5432 is not open for outside world how can i open it providing i don't have access to amazon aws console only access to SSH and i have modified pg_hba.conf for trust mode and postgresql.conf to listen_addresses = '*').
2.On my server there are two roles: postgres and XYZ and i can not login with XYZ
\du ouput is:

 Role name |  Attributes  | Member of
-----------+--------------+-----------
 XYZ       | Cannot login | {}
 postgres  | Superuser    | {}
           : Create role
           : Create DB

My pg_hba.conf is:

local   all         all                            trust

3. Can i reinstall a latest 9.2 or 9.3 postgreSQL server on that linux server?

Richard Huxton · Accepted Answer · 2013-10-04 09:53:04Z

1

What you want is a ssh tunnel. If your client machine is Windows, then check out putty. Otherwise, you can do something like:

ssh -L 6432:127.0.0.1:5432 [email protected]

Then on your PC, just connect pg_admin to localhost:6432 and off you go.

The 6432 is the local port at this end of the tunnel. The 127.0.0.1:5432 is the address to connect to from the remote machine.

As far as upgrading goes, you'll either need to check what packages that version of AMI offers or compile it yourself (or upgrade the version of AMI of course).

answered Oct 4, 2013 at 9:53

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sachin Verma Over a year ago

yeah i am using putty but i think if i open 5432 port from ssh(iptables entry) even then it will stay mocked by amazon aws port management service

Richard Huxton Over a year ago

The whole point is that you don't need to open any hole in the firewall. The data travels over your (already allowed, presumably) encrypted ssh connection. The applications on each end think they are talking to localhost.

Sachin Verma Over a year ago

actually i never seen a link as 6432:127.0.0.1:5432 can you please elaborate, i am not very experienced.

Richard Huxton Over a year ago

I don't know how to explain it any more clearly than I have. Try googling "ssh tunnel" for plenty of tutorials/examples.

Curry · Accepted Answer · 2013-10-04 10:07:50Z

1

At first, question 2 Tip: To connect to a particular database, a user must not only pass the pg_hba.conf checks, but must have the CONNECT privilege for the database. If you wish to restrict which users can connect to which databases, it's usually easier to control this by granting/revoking CONNECT privilege than to put the rules in pg_hba.conf entries

from postgresql doc.

use grant to add connect privilege to xyz.

next question 3. you can install several postgresql in same machine. Install new version's postgresql before uninstallation of old one and migrate data. After migration is complete, uninstall old one.

answered Oct 4, 2013 at 10:07

Curry

8955 silver badges15 bronze badges

1 Comment

Sachin Verma Over a year ago

from where can i get postgresql 9.3 for Amazon linux AMI, please don't say 'just update yum or add centos repositories and you can get it installed from yum only'

Collectives™ on Stack Overflow

PostgreSQL on amazon linux

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related