OpenSUSE Postgre Virgin Help =(

English Applications
1

Hi, I’m new in linux and I just installed postgre which also my first time, I usually use mysql. I got postgre up and running but I don’t understand the login method of postgre via CLI. I created a few user, eg. user01, change the pg_hba.conf and then I tried to login with psql -U user01. But this where I got confuse, it show error saying database:user01 not found. And when I use the default psql -U postgres, its in, but when I run select * from pg_user, postgres is a user and when I run select datname from pg_database, postgres is also a database name. The questions are:

  1. Does the psql first argument always the database name even I put -U option?
  2. If its true, is that mean I have to use postgres database for the first time I install the postgre?
  3. postgre is default user, is it possible to disable it? Or by default we can’t login with postgres outside localhost?

Thank you for the help

E.

2

postgres has its own method of authentication, you have to set up authentication and don’t assume that the commands and format the same as mysql. You could do worse than study the documentation at the postgres site.

PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Client Authentication

3

thank for the suggestion, I have better understanding in postgre now. now im stuck at starting up postgre server with port defined during boot. I add /etc/init.d/postgresql start in /etc/init.d/boot.local and the server run but how to start it with port option?

4

No, you should not add /etc/init.d/postgresql to boot.local. You should enable the postgres service in YaST or by:

chkconfig --set postgresql on

The init script will then be started at the correct sequence point.

As for changing the listening port (why do you want do do this?), edit the config file, pg_hba.conf I think, too lazy to look it up for you.

5

the listening port is in postgresql.conf. i think the listening port you mention about is different think with starting up the server with port, am i right? i want to change just for security issue. I already configured everything and try it but start the server manually when I try my configuration. just got stuck with autoboot start.

6

I don’t know how the listening port could be a different thing from the server port. You need to explain what you mean.

Personally I wouldn’t expose postgresql to the Internet, even if the port number is changed.

Also if you start postgresql in boot.local, the network interfaces are not up at that time IIRC, so it’s the wrong place to start a service.

7

thank you for the suggestion ken. I managed to run the postgre now. but i couldn’t access my from my LAN.
I set the postgre server and the web server in different host.
The Postgre is on 192.168.1.2 and the web is on 192.168.1.3.
I have added this line to pg_hba.conf:
host all all 192.168.1.0/24 md5
And change postgresql.conf to:
#listen_address = ‘*’
I tried to ping 192.168.1.2 from 192.168.1.3 and it’s successful but when I tried psql -h 192.168.1.2 -d mydb -U dbadmin -W, I entered the password, but I couldn’t connect to the server. Its written
psql: could not connect to server: Connection timed out
Is the server running on host ‘192.168.1.2’ and accepting
TCP/IP connections on port 5432?

am I missing something?

8

That # in front of the line

#listen_address='*'

means it’s a comment, and therefore isn’t active. You need to remove the #.

You can check what addresses postgresql is listening on by:

netstat -atn | grep :5432

If it says on the line:

127.0.0.1:5432

then it’s only listening on the local interface and not accessible to other LAN machines.

9

I removed the # and restart the server, I check with netstat and it shows me:
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
tcp 0 0 :::5432 :::* LISTEN
But when I tried psql -h 192.168.1.2 -d mydb -U dbadmin -W
It still give me the same error, connection timed out. I still can ping btw.

10

Do you have the firewall active? Also try -h 127.0.0.1 instead of -h 192.168.1.2.

11

Its the firewall.
I checked the firewall before and it have Postgre in internal zone, I thought internal means local area network, I add external zone and it works. Thank you ken.
Do you have any resources or templates that could help me in optimizing postgre server through configuration? I’ve been reading manuals but to be honest for me it’s very difficult to understand.

12

IIRC those labels internal and external can be misleading. They really only have meaning if you are using openSUSE as a router/gateway. If you are not, then everything except localhost is external.

Sorry, no, I haven’t used Postgres for quite a while, but perhaps if you are serious about learning, you might want to invest in a book. For example, I’m sure there must be an O’Reilly one.