POSTGRE TCP/IP REMOTE CONNECTION

[ifckladyluck]

I am trying to remotely connect to a postgre server, but i cant seem to get it to work.

I have done the following:
added the line host all all 0.0.0.0/0 md5 into pg_hba.conf
added the line listen_addresses = '*' into postgresql.conf

when i try to browse databases through the pockertracker3 Configure PostgreSQL Database Window I type the computers IP address into the SERVER field.
It will work on the host computer, but will not work on the remote computer.

Any ideas?

[ifckladyluck]

seems it was simply a windows firewall issue, and turning it off resolved all of my problems. hope this helps anyone else thats trying to establish a similar setup.

as a side note. does anyone with any experience with postgre know how to setup a role that can only view a particular database? in other words, any other database is not only not accessable, but also not visible.

[WhiteRider]

Thanks for the update - I'll see if I can get one of our PostgreSQL experts to take a look at this thread.

[kraada]

You can create a role with various privileges using the CREATE ROLE command, but I think for what you want you'll also need to use GRANT and REVOKE. The easiest way to do what you want is probably to create a role with minimal privileges, but that can login. Then revoke its ability to connect to all databases and explicitly grant whichever privileges you would like for the one database in question (obviously you'd want connect, probably insert and update, beyond that it depends what you want) - easiest would probably be to grant all privileges on that specific database only though again it depends on your needs.

[ifckladyluck]

is there a way to do this through pgadmin?

[WhiteRider]

You can probably run those commands in PGAdmin, yes. I'll get Kraada to look at this thread again, but I suspect it will be tomorrow now before he sees it.

[kraada]

You can run that SQL through PGAdmin by clicking the little notepad with SQL on it and the pencil at the top and typing it all in. Note that you need to be connected to a database (so highlight some database or another to see the button become clickable).

Another option is to right click on the server (so something like localhost (127.0.0.1) that has the grey server box next to it) and click New Object and choose the type of role you want to add. It gives you something of a wizard-esque set of options to choose from, so you can do it that way if you prefer, but I don't have much more experience with it other than knowing that it's there for those who prefer it.