Обсуждение: Enable user access from remote host
Hi, I installed postgresql (for the first time) in my xubuntu. I created an user and now I would connect, from remote host, to the db with that user. How can I enable the remote access for the user created? Thanks, bye bye.
postgresql.conf :
change listen_address to a real ip
pg_hba.conf :
configure your host/hostssl hostnossl as:
# local DATABASE USER METHOD [OPTION]
# host DATABASE USER CIDR-ADDRESS METHOD [OPTION]
# hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
# hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain socket,
# "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an
# SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", a database name, or
# a comma-separated list thereof.
#
# USER can be "all", a user name, a group name prefixed with "+", or
# a comma-separated list thereof. In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names from
# a separate file.
#
# CIDR-ADDRESS specifies the set of hosts the record matches.
# It is made up of an IP address and a CIDR mask that is an integer
# (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies
# the number of significant bits in the mask. Alternatively, you can write
# an IP address and netmask in separate columns to specify the set of hosts.
#
# METHOD can be "trust", "reject", "md5", "crypt", "password",
# "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords
# in clear text; "md5" is preferred since it sends encrypted passwords.
#
# OPTION is the ident map or the name of the PAM service, depending on METHOD.
#
--dont configure METHOD as password as passwords are clear text which can be sniffed--------
--use MD5 at a minimum to encrypt--
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
> From: afmulone@gmail.com
> Subject: [GENERAL] Enable user access from remote host
> Date: Sat, 7 Mar 2009 13:57:44 -0800
> To: pgsql-general@postgresql.org
>
> Hi,
> I installed postgresql (for the first time) in my xubuntu. I created
> an user and now I would connect, from remote host, to the db with that
> user. How can I enable the remote access for the user created?
> Thanks, bye bye.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Windows Live™ Groups: Create an online spot for your favorite groups to meet. Check it out.
change listen_address to a real ip
pg_hba.conf :
configure your host/hostssl hostnossl as:
# local DATABASE USER METHOD [OPTION]
# host DATABASE USER CIDR-ADDRESS METHOD [OPTION]
# hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
# hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain socket,
# "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an
# SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", a database name, or
# a comma-separated list thereof.
#
# USER can be "all", a user name, a group name prefixed with "+", or
# a comma-separated list thereof. In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names from
# a separate file.
#
# CIDR-ADDRESS specifies the set of hosts the record matches.
# It is made up of an IP address and a CIDR mask that is an integer
# (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies
# the number of significant bits in the mask. Alternatively, you can write
# an IP address and netmask in separate columns to specify the set of hosts.
#
# METHOD can be "trust", "reject", "md5", "crypt", "password",
# "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords
# in clear text; "md5" is preferred since it sends encrypted passwords.
#
# OPTION is the ident map or the name of the PAM service, depending on METHOD.
#
--dont configure METHOD as password as passwords are clear text which can be sniffed--------
--use MD5 at a minimum to encrypt--
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
> From: afmulone@gmail.com
> Subject: [GENERAL] Enable user access from remote host
> Date: Sat, 7 Mar 2009 13:57:44 -0800
> To: pgsql-general@postgresql.org
>
> Hi,
> I installed postgresql (for the first time) in my xubuntu. I created
> an user and now I would connect, from remote host, to the db with that
> user. How can I enable the remote access for the user created?
> Thanks, bye bye.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Windows Live™ Groups: Create an online spot for your favorite groups to meet. Check it out.
Martin Gainty wrote: > postgresql.conf : > change listen_address to a real ip change it to '*' or you won't be able to use localhost... alternately, youc could specify ip.of.net.iface,localhost if you wanted to be specific.
On 8 Mar, 02:08, pie...@hogranch.com (John R Pierce) wrote: > Martin Gainty wrote: > > postgresql.conf : > > change listen_address to a real ip > > change it to '*' or you won't be able to use localhost... alternately, > youc could specify ip.of.net.iface,localhost if you wanted to be specific. > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Hi, I inserted this line at the end of the pg_hba.conf host test angelo "" ident sameuser I inserted the "" to allow to everyone to access to the db. But if I try to access, from another host, I receive an error. What is the error in that line? Thanks, bye bye.
Piotre Ugrumov schrieb: > On 8 Mar, 02:08, pie...@hogranch.com (John R Pierce) wrote: >> Martin Gainty wrote: >>> postgresql.conf : >>> change listen_address to a real ip >> change it to '*' or you won't be able to use localhost... alternately, >> youc could specify ip.of.net.iface,localhost if you wanted to be specific. >> >> -- >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) >> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general > > Hi, > I inserted this line at the end of the pg_hba.conf > > host test angelo "" ident sameuser > > I inserted the "" to allow to everyone to access to the db. > But if I try to access, from another host, I receive an error. What is > the error in that line? just leave it blank ... no "" signs ... Cheers Andy -- Andreas Wenk Hamburg - Germany > Thanks, bye bye. >
Piotre Ugrumov wrote:
> Hi,
> I inserted this line at the end of the pg_hba.conf
>
> host test angelo "" ident sameuser
>
> I inserted the "" to allow to everyone to access to the db.
> But if I try to access, from another host, I receive an error. What is
> the error in that line?
>
host test angelo 0.0.0.0/0 ident sameuser
if you want to allow angelo to connect to database test from any IP...
*HOWEVER* "ident sameuser" should *not* be used for HOST connections,
its only reliable for LOCAL connections. instead, assign angelo a
password...
alter user angelo with encrypted password 'somepassword';
and in pg_hba.conf, specify...
host test angelo 0.0.0.0/0 md5
note, also, if there are any pg_hba.conf records in FRONT of this one
which would match on the connection, they will be used INSTEAD... for
instance...
host all all 192.168.1.0/24 md5
that allows any user, any database on the subnet 192.168.1.0/24 to
connect with md5 password authentication, and any following records
would be ignored.
John R Pierce <pierce@hogranch.com> writes:
> *HOWEVER* "ident sameuser" should *not* be used for HOST connections,
> its only reliable for LOCAL connections.
A more accurate statement is that it's trustworthy to the extent that
you trust the owner of the other machine to be running a non-broken
identd daemon. Within a LAN it might be perfectly reasonable to use.
regards, tom lane
Tom Lane wrote: > John R Pierce <pierce@hogranch.com> writes: > >> *HOWEVER* "ident sameuser" should *not* be used for HOST connections, >> its only reliable for LOCAL connections. >> > > A more accurate statement is that it's trustworthy to the extent that > you trust the owner of the other machine to be running a non-broken > identd daemon. Within a LAN it might be perfectly reasonable to use. > you would have to extend that trust to any machine connected to any network which can be routed to the server in question as he was specifying a wildcard IP, and that includes anything that anyone could plug into any network port. no thanks.
John R Pierce <pierce@hogranch.com> writes:
> Tom Lane wrote:
>> A more accurate statement is that it's trustworthy to the extent that
>> you trust the owner of the other machine to be running a non-broken
>> identd daemon. Within a LAN it might be perfectly reasonable to use.
> you would have to extend that trust to any machine connected to any
> network which can be routed to the server in question as he was
> specifying a wildcard IP, and that includes anything that anyone could
> plug into any network port.
Agreed, it's pretty stupid to use IDENT with a wildcard IP that allows
connections from untrusted networks. I was just objecting to the
statement that it's unsafe in all cases.
regards, tom lane
On 9 Mar, 02:22, t...@sss.pgh.pa.us (Tom Lane) wrote: > John R Pierce <pie...@hogranch.com> writes: > > > Tom Lane wrote: > >> A more accurate statement is that it's trustworthy to the extent that > >> you trust the owner of the other machine to be running a non-broken > >> identd daemon. Within a LAN it might be perfectly reasonable to use. > > you would have to extend that trust to any machine connected to any > > network which can be routed to the server in question as he was > > specifying a wildcard IP, and that includes anything that anyone could > > plug into any network port. > > Agreed, it's pretty stupid to use IDENT with a wildcard IP that allows > connections from untrusted networks. I was just objecting to the > statement that it's unsafe in all cases. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Hi, I inserted the following line: host test angelo 0.0.0.0/0 md5 and in pgAdmin I insert angelo as user mypassword as password test as service and I left blank the SSL field. Moreover I have executed the following commands (before try to connect): sudo -u angelo psql template1 then alter user angelo with encrypted password 'mypassword'; But I have the same problems. Why? What do I wrong? Thanks, bye bye.
On Tuesday 10 March 2009 4:36:36 pm Piotre Ugrumov wrote: > On 9 Mar, 02:22, t...@sss.pgh.pa.us (Tom Lane) wrote: > > John R Pierce <pie...@hogranch.com> writes: > > > Tom Lane wrote: > > >> A more accurate statement is that it's trustworthy to the extent that > > >> you trust the owner of the other machine to be running a non-broken > > >> identd daemon. Within a LAN it might be perfectly reasonable to use. > > > > > > you would have to extend that trust to any machine connected to any > > > network which can be routed to the server in question as he was > > > specifying a wildcard IP, and that includes anything that anyone could > > > plug into any network port. > > > > Agreed, it's pretty stupid to use IDENT with a wildcard IP that allows > > connections from untrusted networks. I was just objecting to the > > statement that it's unsafe in all cases. > > > > regards, tom lane > > > > -- > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > To make changes to your > > subscription:http://www.postgresql.org/mailpref/pgsql-general > > Hi, > I inserted the following line: > > host test angelo 0.0.0.0/0 md5 Did you pg_ctl reload to get the change noticed? > > and in pgAdmin I insert > > angelo as user > mypassword as password > test as service > > and I left blank the SSL field. > > Moreover I have executed the following commands (before try to > connect): > sudo -u angelo psql template1 > then > alter user angelo with encrypted password 'mypassword'; > > But I have the same problems. > Why? > What do I wrong? > Thanks, bye bye. -- Adrian Klaver aklaver@comcast.net
On 11 Mar, 01:41, akla...@comcast.net (Adrian Klaver) wrote: > On Tuesday 10 March 2009 4:36:36 pm Piotre Ugrumov wrote: > > > > > On 9 Mar, 02:22, t...@sss.pgh.pa.us (Tom Lane) wrote: > > > John R Pierce <pie...@hogranch.com> writes: > > > > Tom Lane wrote: > > > >> A more accurate statement is that it's trustworthy to the extent that > > > >> you trust the owner of the other machine to be running a non-broken > > > >> identd daemon. Within a LAN it might be perfectly reasonable to use. > > > > > you would have to extend that trust to any machine connected to any > > > > network which can be routed to the server in question as he was > > > > specifying a wildcard IP, and that includes anything that anyone could > > > > plug into any network port. > > > > Agreed, it's pretty stupid to use IDENT with a wildcard IP that allows > > > connections from untrusted networks. I was just objecting to the > > > statement that it's unsafe in all cases. > > > > regards, tom lane > > > > -- > > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > > To make changes to your > > > subscription:http://www.postgresql.org/mailpref/pgsql-general > > > Hi, > > I inserted the following line: > > > host test angelo 0.0.0.0/0 md5 > > Did you pg_ctl reload to get the change noticed? > > > > > > > and in pgAdmin I insert > > > angelo as user > > mypassword as password > > test as service > > > and I left blank the SSL field. > > > Moreover I have executed the following commands (before try to > > connect): > > sudo -u angelo psql template1 > > then > > alter user angelo with encrypted password 'mypassword'; > > > But I have the same problems. > > Why? > > What do I wrong? > > Thanks, bye bye. > > -- > Adrian Klaver > akla...@comcast.net > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general I restarted my xubuntu (where I installed postgresql) but I have the problem. Thanks, bye bye.
On Wednesday 11 March 2009 1:29:18 pm Piotre Ugrumov wrote: > On 11 Mar, 01:41, akla...@comcast.net (Adrian Klaver) wrote: > > On Tuesday 10 March 2009 4:36:36 pm Piotre Ugrumov wrote: > > > On 9 Mar, 02:22, t...@sss.pgh.pa.us (Tom Lane) wrote: > > > > John R Pierce <pie...@hogranch.com> writes: > > > > > Tom Lane wrote: > > > > >> A more accurate statement is that it's trustworthy to the extent > > > > >> that you trust the owner of the other machine to be running a > > > > >> non-broken identd daemon. Within a LAN it might be perfectly > > > > >> reasonable to use. > > > > > > > > > > you would have to extend that trust to any machine connected to any > > > > > network which can be routed to the server in question as he was > > > > > specifying a wildcard IP, and that includes anything that anyone > > > > > could plug into any network port. > > > > > > > > Agreed, it's pretty stupid to use IDENT with a wildcard IP that > > > > allows connections from untrusted networks. I was just objecting to > > > > the statement that it's unsafe in all cases. > > > > > > > > regards, tom lane > > > > > > > > -- > > > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > > > To make changes to your > > > > subscription:http://www.postgresql.org/mailpref/pgsql-general > > > > > > Hi, > > > I inserted the following line: > > > > > > host test angelo 0.0.0.0/0 md5 > > > > Did you pg_ctl reload to get the change noticed? > > > > > and in pgAdmin I insert > > > > > > angelo as user > > > mypassword as password > > > test as service > > > > > > and I left blank the SSL field. > > > > > > Moreover I have executed the following commands (before try to > > > connect): > > > sudo -u angelo psql template1 > > > then > > > alter user angelo with encrypted password 'mypassword'; > > > > > > But I have the same problems. > > > Why? > > > What do I wrong? > > > Thanks, bye bye. > > > > -- > > Adrian Klaver > > akla...@comcast.net > > > > -- > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > To make changes to your > > subscription:http://www.postgresql.org/mailpref/pgsql-general > > I restarted my xubuntu (where I installed postgresql) but I have the > problem. > Thanks, bye bye. It is not necessary to restart the computer. As the Postgres superuser(usually postgres) run the command pg_ctl with the appropriate parameter. In the case of changes to pg_hba.conf it would be pg_ctl reload. This tells the Postgres server to reload its conf files so it can take advantage of the changes. Some conf parameters need a pg_ctl restart as they are only read at start up of the server. The comments in the *.conf files help you with this (especially in newer versions pf Postgres). Also look at for more information: http://www.postgresql.org/docs/8.3/interactive/runtime-config.html http://www.postgresql.org/docs/8.3/interactive/client-authentication.html As to your particular problem I am going to need some more information. I went back through the thread and cannot see any specific error messages. What exactly is "the same problems"? Other question, can you connect locally? -- Adrian Klaver aklaver@comcast.net