Thread: Newbie's question: How can I connect to my postgresql-server?
Dear my friends.... I am still new in PostgreSQL. Usually I use MySQL on OpenSuSE. I just have installed postgres with: "zypper install postgresql-server". The installation looked OK. As post-installation process, I've done: " sussy:~ # rcpostgresql status Checking for PostgreSQL: running sussy:~ # su postgres postgres@sussy:/root> createuser -D -p ricky could not change directory to "/root" Enter name of role to add: ricky Shall the new role be a superuser? (y/n) y createuser: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.0"? postgres@sussy:/root> ". Why can I not connect to my Postgres? Please tell me. Give me a first hint and this small step for me to getting start to use postgres. Thank you very much in advance.
On Fri, Sep 25, 2009 at 01:47:03AM +0700, Ricky Tompu Breaky wrote: > I am still new in PostgreSQL. Usually I use MySQL on OpenSuSE. Welcome over, PG is *normally* much better behaved than this and generally gives very good error messages. > postgres@sussy:/root> createuser -D -p ricky I don't think you want to be passing "-p" here; it's saying to use "ricky" as the port number, which fails (sounds like a bug if it doesn't complain about this) giving a port number of zero, which is why you see this: > connections on Unix domain socket "/tmp/.s.PGSQL.0"? the ".0" is normally something like ".5432". Maybe you want "-P" to be asking for the password? -- Sam http://samason.me.uk/
On Thu, 24 Sep 2009 19:57:55 +0100 Sam Mason <sam@samason.me.uk> wrote: > On Fri, Sep 25, 2009 at 01:47:03AM +0700, Ricky Tompu Breaky wrote: > > I am still new in PostgreSQL. Usually I use MySQL on OpenSuSE. > > Welcome over, PG is *normally* much better behaved than this and > generally gives very good error messages. > > > postgres@sussy:/root> createuser -D -p ricky > > I don't think you want to be passing "-p" here; it's saying to use > "ricky" as the port number, which fails (sounds like a bug if it > doesn't complain about this) giving a port number of zero, which is > why you see this: > > > connections on Unix domain socket "/tmp/.s.PGSQL.0"? > > the ".0" is normally something like ".5432". > > Maybe you want "-P" to be asking for the password? > Dear Sam. Thank you very much for your help. You've solved my problem. But I still can not login to my PostgreSQL-Server. Here what I've done: " postgres@sussy:/root> createuser -D -P ricky could not change directory to "/root" Enter password for new role: Enter it again: Shall the new role be a superuser? (y/n) y postgres@sussy:/root> sussy:~ # rcpostgresql restart Shutting down PostgreSQLServer angehalten done Starting PostgreSQL done sussy:~ # psql -U ricky -W Password for user ricky: psql: FATAL: Ident-Authentifizierung für Benutzer »ricky« fehlgeschlagen (I translate it: psql: FATAL: Ident-Authentication for user »ricky« failed) sussy:~ # ". I'm sure that I used the correct password as "createuser" the account of 'ricky'. Please keep telling me. Thank you very much in advance.
On Thu, Sep 24, 2009 at 07:57:55PM +0100, Sam Mason wrote: > > postgres@sussy:/root> createuser -D -p ricky > > I don't think you want to be passing "-p" here; it's saying to use > "ricky" as the port number, which fails (sounds like a bug if it doesn't > complain about this) giving a port number of zero Hum, why is PG doing an (unchecked) atoi on the user specified port rather than leaving it up to getaddrinfo to resolve the port? It would seem to require changing UNIXSOCK_PATH to accept a string as the "port number", which is probably a bit much of a change. The included doesn't feel very nice, but is probably more acceptable. -- Sam http://samason.me.uk/
Attachment
On Fri, Sep 25, 2009 at 02:47:23AM +0700, Ricky Tompu Breaky wrote: > I'm sure that I used the correct password as "createuser" the > account of 'ricky'. By default; PG is set up not to use passwords in the "local" case. This is what the "ident" in the error is about. Your PG username needs to be the same as your Unix username or ident authentication will fail. Have a look at: http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html if you want to change it to use passwords as the authentication method in the local case. Note, that you don't need to restart PG when adding/removing users, but you do when changing the config files like pg_hba.conf and postgresql.conf. It's a "inside"/"outside" PG thing, users are stored in the database and hence PG knows when they're added/removed, but the config files are outside and PG doesn't know when to go looking for changes. There are plans to change this for pg_hba.conf, but you'll have to wait a bit before that happens. -- Sam http://samason.me.uk/
Sam Mason <sam@samason.me.uk> writes: > Hum, why is PG doing an (unchecked) atoi on the user specified port > rather than leaving it up to getaddrinfo to resolve the port? It would > seem to require changing UNIXSOCK_PATH to accept a string as the "port > number", which is probably a bit much of a change. > The included doesn't feel very nice, but is probably more acceptable. I had been thinking about applying strstr to insist that the string contain only digits (and maybe spaces), but the range check you suggest is probably more useful. Anyone have objections? (BTW, are port numbers still limited to 16 bits in IPv6? If not then this won't do.) regards, tom lane
On Thu, Sep 24, 2009 at 8:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > BTW, are port numbers still limited to 16 bits in IPv6? Yes. ...Robert
Sam Mason <sam@samason.me.uk> writes: > + if (portnum < 1 || portnum > 65535) BTW, it strikes me that we could tighten this even more by rejecting target ports below 1024. This is guaranteed safe on all Unix systems I know of, because privileged ports can only be listened to by root-owned processes and we know the postmaster won't be one. I am not sure whether it would be possible to start the postmaster on a low-numbered port on Windows though. Anyone know? Even if it's possible, do we want to allow it? regards, tom lane
Tom Lane wrote: > Sam Mason <sam@samason.me.uk> writes: >> + if (portnum < 1 || portnum > 65535) > > BTW, it strikes me that we could tighten this even more by rejecting > target ports below 1024. This is guaranteed safe on all Unix systems > I know of, because privileged ports can only be listened to by root-owned > processes and we know the postmaster won't be one. This is just an aside. The recent Linux system allows to assign a part of root privileges (called as capabilities) on a certain process. Example) # setcap cap_net_bind_service=ep /usr/local/pgsql/bin/postgres <-- it allows anyone to launch postmaster with cap_net_bind_service capability. $ pg_ctl -o "-i -p 100" start $ psql postgres -p 100 psql (8.5devel) Type "help" for help. postgres=# > Even if it's possible, do we want to allow it? I cannot find any merits. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei <kaigai@ak.jp.nec.com>
On Thu, 24 Sep 2009, Tom Lane wrote: > Sam Mason <sam@samason.me.uk> writes: >> + if (portnum < 1 || portnum > 65535) > > BTW, it strikes me that we could tighten this even more by rejecting > target ports below 1024. Restricting the target port seems like a bad idea. What about a firewall (or ssh tunnel) that did port forwarding. What PG binds to and what a client connects to may not be the same thing. Kris Jurka
On Thu, Sep 24, 2009 at 8:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Sam Mason <sam@samason.me.uk> writes: >> + if (portnum < 1 || portnum > 65535) > > BTW, it strikes me that we could tighten this even more by rejecting > target ports below 1024. This is guaranteed safe on all Unix systems > I know of, because privileged ports can only be listened to by root-owned > processes and we know the postmaster won't be one. I am not sure > whether it would be possible to start the postmaster on a low-numbered > port on Windows though. Anyone know? Even if it's possible, do we > want to allow it? I don't think we get much benefit out of artificially limiting libpq in this way. In 99.99% of cases it won't matter, and in the other 0.01% it will be a needless annoyance. I think we should restrict ourselves to checking what is legal, not what we think is a good idea. ...Robert
On 25 sep 2009, at 02.59, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Sam Mason <sam@samason.me.uk> writes: >> + if (portnum < 1 || portnum > 65535) > > BTW, it strikes me that we could tighten this even more by rejecting > target ports below 1024. This is guaranteed safe on all Unix systems > I know of, because privileged ports can only be listened to by root- > owned > processes and we know the postmaster won't be one. I am not sure > whether it would be possible to start the postmaster on a low-numbered > port on Windows though. Anyone know? Even if it's possible, do we > want to allow it? Windows doesn't care. A non privileged process can open any port, both above and below 1024. Other than that, I agree with previous comments - restricting this in libpq won't actually help anything, but in a few limited cases it will be very annoying. /Magnus >
On Thu, 2009-09-24 at 20:36 -0400, Tom Lane wrote: > BTW, are port numbers still limited to 16 bits in IPv6? Port numbers are in TCP, not in IP.
On Fri, Sep 25, 2009 at 09:29:24AM +0300, Peter Eisentraut wrote: > On Thu, 2009-09-24 at 20:36 -0400, Tom Lane wrote: > > BTW, are port numbers still limited to 16 bits in IPv6? > > Port numbers are in TCP, not in IP. I'd checked that it should work with IPv6, but I hadn't realized that it was because ports were at a different level of abstraction. This mailing list is good for otherwise obscure details like that! -- Sam http://samason.me.uk/
Dear Sam. Thank you very much for your help. You've solved my several problem. But now I'm stucked on another problem which I know easy to anybody else but difficult as a postgres starter like me. I did these steps: " 1. I created a new opensuse11.1-linux login account + its password (username: ivia) with YaST2; 2. ivia@sussy:~> su postgres -c psql postgres Passwort: Dies ist psql 8.3.7, das interaktive PostgreSQL-Terminal. Geben Sie ein: \copyright für Urheberrechtsinformationen \h für Hilfe über SQL-Anweisungen \? für Hilfe über interne Anweisungen \g oder Semikolon, um eine Anfrage auszuführen \q um zu beenden 3. postgres'# ALTER USER postgres WITH PASSWORD '<<mypassword>>'; ALTER ROLE postgres=# ALTER USER ivia WITH PASSWORD '<<mypassword>>'; postgres'# 4. sussy:/etc # cat /var/lib/pgsql/data/pg_hba.conf # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only #local all all ident sameuser # IPv4 local connections: #host all all 127.0.0.1/32 ident sameuser # IPv6 local connections: #host all all ::1/128 ident sameuser #local all all md5 #host all all 127.0.0.1/32 md5 #host all all ::1/128 md5 host all all 0.0.0.0/0 md5 sussy:/etc # 5. sussy:/etc # rcpostgresql restart Shutting down PostgreSQLServer angehalten done Starting PostgreSQL done sussy:/etc # 6. sussy:/etc # cat /etc/sysconfig/postgresql POSTGRES_DATADIR="~postgres/data" POSTGRES_OPTIONS="-i" POSTGRES_LANG="" sussy:/etc # 7. sussy:/etc # psql -h 127.0.0.1 -U ivia -W Password for user ivia: psql: FATAL: Passwort-Authentifizierung für Benutzer »ivia« fehlgeschlagen (my translation: Password-Authentication for user »ivia« failed) sussy:/etc # ". Why can I not login with 'iVia' to my postgresql? Is it because I use 'md5()' but not blowfish as I remember OpenSuSE11.1 use 'blowfish()' as its default password encryption. But AFAIK, there's nothing to do with the RDBMS Encryption (PostgreSQL in my case) and the Host OS password encryption method. Please keep telling me. I am confused. Thank you very much in advance. On Thu, 24 Sep 2009 21:16:53 +0100 Sam Mason <sam@samason.me.uk> wrote: > On Fri, Sep 25, 2009 at 02:47:23AM +0700, Ricky Tompu Breaky wrote: > > sussy:~ # psql -U ricky -W > > I didn't realize you were running commands as root. My comments to > the mailing list still apply, but this is normally frowned upon as > it's easy to kill the system by accident. Also, when Linux starts > getting viruses it'll be able to infect the whole system if you do > this rather than just your (unprivileged) account. If you've not > used it before, "sudo" is your friend here. > On Thu, 24 Sep 2009 20:59:44 +0100 Sam Mason <sam@samason.me.uk> wrote: > On Fri, Sep 25, 2009 at 02:47:23AM +0700, Ricky Tompu Breaky wrote: > > I'm sure that I used the correct password as "createuser" the > > account of 'ricky'. > > By default; PG is set up not to use passwords in the "local" case. > This is what the "ident" in the error is about. Your PG username > needs to be the same as your Unix username or ident authentication > will fail. > > Have a look at: > > http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html > > if you want to change it to use passwords as the authentication method > in the local case. > > > Note, that you don't need to restart PG when adding/removing users, > but you do when changing the config files like pg_hba.conf and > postgresql.conf. It's a "inside"/"outside" PG thing, users are stored > in the database and hence PG knows when they're added/removed, but the > config files are outside and PG doesn't know when to go looking for > changes. There are plans to change this for pg_hba.conf, but you'll > have to wait a bit before that happens. >
Sam Mason <sam@samason.me.uk> writes: > Hum, why is PG doing an (unchecked) atoi on the user specified port > rather than leaving it up to getaddrinfo to resolve the port? It would > seem to require changing UNIXSOCK_PATH to accept a string as the "port > number", which is probably a bit much of a change. > The included doesn't feel very nice, but is probably more acceptable. Applied, thanks. regards, tom lane
On Sat, Sep 26, 2009 at 03:00:41PM +0700, Ricky Tompu Breaky wrote: > You've solved my several problem. But now I'm stucked on another > problem which I know easy to anybody else but difficult as a > postgres starter like me. A general observation; you seem to be making things awfully complicated initially. It may be worth going with the defaults of assuming that PG usernames and Unix usernames are the same initially. Once you've got a bit more experience then it may not seem so complicated. The main reason I'm saying this is that I *very* rarely have to fiddle with these sorts of things and yet they always do the "right" thing when I try. Maybe there are other things going on that are getting in the way of solving the real issue that will go away with more experience. > I did these steps: > " > 1. I created a new opensuse11.1-linux login account + its password > (username: ivia) with YaST2; PG users and Unix users/accounts are unrelated so this step is redundant (think of large sites with thousands of database users). If you've always got Unix user accounts why not just use the ident auth as default? > 2. ivia@sussy:~> su postgres -c psql postgres > 3. postgres'# ALTER USER postgres WITH PASSWORD '<<mypassword>>'; > ALTER ROLE > postgres=# ALTER USER ivia WITH PASSWORD '<<mypassword>>'; > postgres'# > 4. sussy:/etc # cat /var/lib/pgsql/data/pg_hba.conf > host all all 0.0.0.0/0 md5 Were these setting in place when you ran the initial connection with the postgres account to change people's passwords? If so, I can't see how it would work. Maybe you are changing the "wrong" config file. Try putting something invalid into the config file and check to see if it starts up or gives an error. > 7. sussy:/etc # psql -h 127.0.0.1 -U ivia -W > Password for user ivia: > psql: FATAL: Passwort-Authentifizierung für Benutzer »ivia« > fehlgeschlagen (my translation: Password-Authentication for user »ivia« > failed) It's obviously using password auth, are you sure you got the passwords right? You haven't got a backslash in the password have you? it'll need to be escaped in the initial creation bit if you do. A simple ASCII password such as 'test' may be a good place to start. > Why can I not login with 'iVia' to my postgresql? Is it because I use > 'md5()' but not blowfish as I remember OpenSuSE11.1 use 'blowfish()' > as its default password encryption. But AFAIK, there's nothing to do > with the RDBMS Encryption (PostgreSQL in my case) and the Host OS > password encryption method. Yup, as far as I can tell this shouldn't have any effect. Then again, I don't use SuSE and it's possible (though very unlikely) that they changed this. Sam -- Sam http://samason.me.uk/
Dear Sam... After I found the solution of my problem and again read the postgres manual, I've understood you're correct that I tried "too much" as an initial step for a newbie like me. But anyway, I thank you so many times.... because you've taught me a lot about postgres. I really appreciate you're help. You made so much steps further for me. ==== On Sun, 27 Sep 2009 14:47:06 +0100 Sam Mason <sam@samason.me.uk> wrote: > On Sat, Sep 26, 2009 at 03:00:41PM +0700, Ricky Tompu Breaky wrote: > > You've solved my several problem. But now I'm stucked on another > > problem which I know easy to anybody else but difficult as a > > postgres starter like me. > > A general observation; you seem to be making things awfully > complicated initially. It may be worth going with the defaults of > assuming that PG usernames and Unix usernames are the same > initially. Once you've got a bit more experience then it may not > seem so complicated. The main reason I'm saying this is that I > *very* rarely have to fiddle with these sorts of things and yet they > always do the "right" thing when I try. > > Maybe there are other things going on that are getting in the way of > solving the real issue that will go away with more experience. > > > I did these steps: > > " > > 1. I created a new opensuse11.1-linux login account + its password > > (username: ivia) with YaST2; > > PG users and Unix users/accounts are unrelated so this step is > redundant (think of large sites with thousands of database users). > If you've always got Unix user accounts why not just use the ident > auth as default? > > > 2. ivia@sussy:~> su postgres -c psql postgres > > 3. postgres'# ALTER USER postgres WITH PASSWORD '<<mypassword>>'; > > ALTER ROLE > > postgres=# ALTER USER ivia WITH PASSWORD '<<mypassword>>'; > > postgres'# > > 4. sussy:/etc # cat /var/lib/pgsql/data/pg_hba.conf > > host all all 0.0.0.0/0 md5 > > Were these setting in place when you ran the initial connection with > the postgres account to change people's passwords? If so, I can't > see how it would work. Maybe you are changing the "wrong" config > file. > > Try putting something invalid into the config file and check to see if > it starts up or gives an error. > > > 7. sussy:/etc # psql -h 127.0.0.1 -U ivia -W > > Password for user ivia: > > psql: FATAL: Passwort-Authentifizierung für Benutzer »ivia« > > fehlgeschlagen (my translation: Password-Authentication for user > > »ivia« failed) > > It's obviously using password auth, are you sure you got the passwords > right? You haven't got a backslash in the password have you? it'll > need to be escaped in the initial creation bit if you do. A simple > ASCII password such as 'test' may be a good place to start. > > > Why can I not login with 'iVia' to my postgresql? Is it because I > > use 'md5()' but not blowfish as I remember OpenSuSE11.1 use > > 'blowfish()' as its default password encryption. But AFAIK, there's > > nothing to do with the RDBMS Encryption (PostgreSQL in my case) and > > the Host OS password encryption method. > > Yup, as far as I can tell this shouldn't have any effect. Then again, > I don't use SuSE and it's possible (though very unlikely) that they > changed this. > > > Sam >
On Mon, Sep 28, 2009 at 11:55:51AM +0700, Ricky Tompu Breaky wrote: > After I found the solution of my problem and again read the postgres > manual, I've understood you're correct that I tried "too much" as an > initial step for a newbie like me. Sorry it wasn't as easy as it could be and I hope your experiences are exceptional and most people have an easier time initially! To the rest of pg-general (and should I be ccing pgsql-www ?); would it be worth putting a little survey on the front of the PG web-site to ask how people get on initially or would the signal to noise ratio be too low to be useful? -- Sam http://samason.me.uk/