Re: [HACKERS] libpq port number handling

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(at)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(at)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(at)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(at)samason(dot)me(dot)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(at)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(at)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(at)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(at)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/

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(at)samason(dot)me(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> BTW, are port numbers still limited to 16 bits in IPv6?

Yes.

...Robert

Sam Mason <sam(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)ak(dot)jp(dot)nec(dot)com>

On Thu, 24 Sep 2009, Tom Lane wrote:

> Sam Mason <sam(at)samason(dot)me(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Sam Mason <sam(at)samason(dot)me(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Sam Mason <sam(at)samason(dot)me(dot)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(at)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(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)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(at)samason(dot)me(dot)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(at)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/