Re: Surprising connection issue
On Tue, Jul 14, 2020 at 8:25 AM David Gasa i Castell wrote: > And my surprise went when I see the connection done while there is no user > granted to connect the database... > https://www.postgresql.org/docs/12/ddl-priv.html """ PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created. No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces. For other types of objects, the default privileges granted to PUBLIC are as follows: ***CONNECT and TEMPORARY (create temporary tables) privileges for databases;*** (emphasis mine) EXECUTE privilege for functions and procedures; and USAGE privilege for languages and data types (including domains). """ David J.
Surprising connection issue
Hi guys,
I don't know if what I'm going to explain you could be regarded as a
regular behaviour related issue... but I'm so surprised.
I'm working under the latest stable PostgreSQL version 12.3.
$ sudo /usr/lib/postgresql/12/bin/postgres --version
postgres (PostgreSQL) 12.3 (Debian 12.3-1.pgdg100+1)
And the contents of my pg_hba.conf is as follows,
$ sudo cat /etc/postgresql/12/main/pg_hba.conf | egrep -v
'^[[:space:]]*(#.*)?$' -
local all postgrespeer
local all all peer
hostssl all all 127.0.0.1/32 scram-sha-256
hostssl all all ::1/128
scram-sha-256
local replication all peer
hostreplication all 127.0.0.1/32md5
hostreplication all ::1/128 md5
(Obviously the 'password_encryption' variable is correctly set to
scram-sha-256)
Once connected, I created a one new user (user1) with superuser grant and a
new one other (user2) as described below,
$ psql
psql (12.3 (Debian 12.3-1.pgdg100+1))
Type "help" for help.
postgres=# CREATE USER user1 WITH SUPERUSER;
CREATE ROLE
postgres=# \password user1
Enter new password:
Enter it again:
postgres=# CREATE USER user2;
CREATE ROLE
postgres=# \password user2
Enter new password:
Enter it again:
Once done, I created a new db1 database... putting the user user1 as the
owner of it.
postgres=# CREATE DATABASE db1 OWNER user1;
CREATE DATABASE
postgres=# \du
List of roles
Role name | Attributes |
Member of
---++---
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
user1 | Superuser | {}
user2 || {}
postgres=# \l
List of databases
Name| Owner | Encoding | Collate |Ctype| Access
privileges
---+--+--+-+-+---
db1 | user1| UTF8 | ca_ES.UTF-8 | ca_ES.UTF-8 |
postgres | postgres | UTF8 | ca_ES.UTF-8 | ca_ES.UTF-8 |
template0 | postgres | UTF8 | ca_ES.UTF-8 | ca_ES.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | ca_ES.UTF-8 | ca_ES.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
(4 rows)
postgres=# \q
After that, I'm tried to make a connection to database db1 as a user user1.
$ psql -h localhost -d db1 -U user1
Password for user user1:
psql (12.3 (Debian 12.3-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.
db1=# \q
And my surprise went when I see the connection done while there is no user
granted to connect the database...
Ok -I thought- maybe because user1 is a superuser... or even maybe because
user1 is in fact the owner of the database db1.
So, I decided to try it again with another user (user2) !
$ psql -h localhost -d db1 -U user2
Password for user user2:
psql (12.3 (Debian 12.3-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.
db1=> \q
$
And honestly I don't know if I'm right now in front of a bug... or there is
some reason that explains all of this ?
--
David Gasa i Castell
Linux User #488832
Re: Connection issue
Just for the record, updating to Mac OS 10.14.3 resolved the issue. Thanks, Max
Re: Connection issue
Hi, Can you read the event viewer? Obtener Outlook para Android<https://aka.ms/ghei36> From: Adrian Klaver Sent: Wednesday, February 6, 2019 1:43:24 PM To: Maximilian Tyrtania Cc: Ricardo Martin Gomez; pgsql-general@lists.postgresql.org Subject: Re: Connection issue On 2/6/19 8:30 AM, Maximilian Tyrtania wrote: > Hi again, > > >> On 6. Feb 2019, at 17:19, Adrian Klaver wrote: >> >> On 2/6/19 7:18 AM, Maximilian Tyrtania wrote: >>> Well, the problem being a Parallels issue is just a wild theory of mine, it >>> could well be something else. Honstly I don't recall updating anything in >>> that area. As Mr. Gomez suggested I tried to >> >> So from a previous post: >> >>> Have you recently updated any of the involved software? >> >> Well, sure, but after I saw I couldn't connect ... >> >> So what happened in the interval between the time you could connect and the >> time you could not? > > I did update parts of my app (including the plugin which in turn encapsulates > libpq). I did not update my parallels installation, as far as I remember. > Sorry if I was unclear about that. What is the plugin? Can you roll back the plugin upgrade to see if that is the problem? > >>> ping - worked >>> telnet - did not work (infact I couldn't telnet anywhere) >> >> In my experience telnet is generally disabled these days. > > I had to enable it on my Windows box, if that's what you mean. The "telnet" > command works by itself, but I didnt receive anything when trying to telnet > to some other machine. > Probably because Telnet is not enabled on the server as it is not a secure protocol: https://en.wikipedia.org/wiki/Telnet > > Ah, thanks for digging that up. Hmm, sounds as if something is definetly > wrong on my windows box. > > I'll try contacting the Parallels support guys. > > Thanks again, > > Max > -- Adrian Klaver adrian.kla...@aklaver.com
Re: Connection issue
On 2/6/19 8:30 AM, Maximilian Tyrtania wrote: Hi again, On 6. Feb 2019, at 17:19, Adrian Klaver wrote: On 2/6/19 7:18 AM, Maximilian Tyrtania wrote: Well, the problem being a Parallels issue is just a wild theory of mine, it could well be something else. Honstly I don't recall updating anything in that area. As Mr. Gomez suggested I tried to So from a previous post: Have you recently updated any of the involved software? Well, sure, but after I saw I couldn't connect ... So what happened in the interval between the time you could connect and the time you could not? I did update parts of my app (including the plugin which in turn encapsulates libpq). I did not update my parallels installation, as far as I remember. Sorry if I was unclear about that. What is the plugin? Can you roll back the plugin upgrade to see if that is the problem? ping - worked telnet - did not work (infact I couldn't telnet anywhere) In my experience telnet is generally disabled these days. I had to enable it on my Windows box, if that's what you mean. The "telnet" command works by itself, but I didnt receive anything when trying to telnet to some other machine. Probably because Telnet is not enabled on the server as it is not a secure protocol: https://en.wikipedia.org/wiki/Telnet Ah, thanks for digging that up. Hmm, sounds as if something is definetly wrong on my windows box. I'll try contacting the Parallels support guys. Thanks again, Max -- Adrian Klaver adrian.kla...@aklaver.com
Re: Connection issue
On 2/6/19 7:18 AM, Maximilian Tyrtania wrote: Well, the problem being a Parallels issue is just a wild theory of mine, it could well be something else. Honstly I don't recall updating anything in that area. As Mr. Gomez suggested I tried to So from a previous post: > Have you recently updated any of the involved software? Well, sure, but after I saw I couldn't connect ... So what happened in the interval between the time you could connect and the time you could not? ping - worked telnet - did not work (infact I couldn't telnet anywhere) In my experience telnet is generally disabled these days. tracert - worked from my windows 10 box (didn't bother to check on my Mac as I have noc issues there). Still curious as to why the server would say "Connection reset by peer". Well if you are running Windows --> Parallels --> OS X(Mac) --> Serve, you are effectively testing the Mac also. As to 'Connection reset by peer': A grep of the Postgres source find this: interfaces/libpq/win32.c * Contains table and functions for looking up win32 socket error * descriptions. But will/may contain other win32 helper functions * for libpq. WSAECONNRESET, "Connection reset by peer" Looking up WSAECONNRESET finds this: https://docs.microsoft.com/en-us/windows/desktop/WinSock/windows-sockets-error-codes-2 WSAECONNRESET 10054 Connection reset by peer. An existing connection was forcibly closed by the remote host. This normally results if the peer application on the remote host is suddenly stopped, the host is rebooted, the host or remote network interface is disabled, or the remote host uses a hard close (see setsockopt for more information on the SO_LINGER option on the remote socket). This error may also result if a connection was broken due to keep-alive activity detecting a failure while one or more operations are in progress. Operations that were in progress fail with WSAENETRESET. Subsequent operations fail with WSAECONNRESET. Thanks, Max -- Adrian Klaver adrian.kla...@aklaver.com
RE: Connection issue
Hi, Can you do some tests in both (Mac and Windows ) ?
* telnet {IP_Server} {5432}
* ping {IP_Server}
* tracert {IP_Server}
Regards
__
Saludos
Ing. Ricardo MartÃn Gomez
DBA - SysAdmin
De: Adrian Klaver
Enviado: martes, 5 de febrero de 2019 12:44
Para: Maximilian Tyrtania
Cc: pgsql-general@lists.postgresql.org
Asunto: Re: Connection issue
On 2/5/19 7:18 AM, Maximilian Tyrtania wrote:
> Hi,
>
>> On 5. Feb 2019, at 15:57, Adrian Klaver wrote:
>>
>> On 2/5/19 1:48 AM, Maximilian Tyrtania wrote:
>>> For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu)
>>> from my Windows installation (run via Parallels on my Mac), no matter what
>>> app I use (tried psql, PGAdmin and my own app).
>>
>> Has it ever connected?
>
> Yes, it connected without issues last week.
>
>> Have you recently updated any of the involved software?
>
> Well, sure, but after I saw I couldn't connect I downloaded the current
> version of PGAdmin and psql and couldn't connect with them either.
>
>> More below.
>>
>>> When trying to connect from PGAdmin 4 the server logs say:
>>> 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG: 0:
>>> Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de
>>> Port=50262
>>> 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT:
>>> BackendInitialize, postmaster.c:4249
>>> 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG: 08006:
>>> konnte Daten vom Client nicht empfangen: Connection reset by peer
>>> 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT:
>>> pq_recvbuf, pqcomm.c:978
>>> ("konnte Daten vom Client nicht empfangen" means "couldn't receive data
>>> from client")
>>> PGAdmin says the usual " Unable to connect to server:..."
>>> No issue connecting from the Mac.
>>> I see in the logfile that other windows users did connect successfully from
>>> their windows boxes so I am ready to believe that maybe my Parallels
>>> somehow disconnects the connection quickly or something.
>>> Another strange thing is that if I try to connect from my Xojo app (which
>>> uses libpq internally) it says:SSL error: unknown protocol. I had no issue
>>> connecting last week from that installation so I am at my wits end...
>>
>> Where is the Xojo app running?
>>
>> Is the pg_hba.conf on the server set up to allow connections from the client
>> machines?
>
> Yes. Also the errormessage wouldn't be "Connection reset by peer" if it was a
> pga_hba.conf issue, would it? And anyway, I can connect just fine from my Mac
> with the same credentials. I wonder if this is maybe some Parallels issue.
Well if you could connect from Parallels last week, you upgraded it and
now you cannot connect then I would suspect it. This assumes that the
Xojo app is also running in the Parallels instance. Given the SSL error
you might see if they changed anything in that area.
>
> Thanks,
> Max
>
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Connection issue
On 2/5/19 7:18 AM, Maximilian Tyrtania wrote:
Hi,
On 5. Feb 2019, at 15:57, Adrian Klaver wrote:
On 2/5/19 1:48 AM, Maximilian Tyrtania wrote:
For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu) from
my Windows installation (run via Parallels on my Mac), no matter what app I use
(tried psql, PGAdmin and my own app).
Has it ever connected?
Yes, it connected without issues last week.
Have you recently updated any of the involved software?
Well, sure, but after I saw I couldn't connect I downloaded the current version
of PGAdmin and psql and couldn't connect with them either.
More below.
When trying to connect from PGAdmin 4 the server logs say:
2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG: 0:
Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de Port=50262
2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT:
BackendInitialize, postmaster.c:4249
2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG: 08006: konnte
Daten vom Client nicht empfangen: Connection reset by peer
2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT: pq_recvbuf,
pqcomm.c:978
("konnte Daten vom Client nicht empfangen" means "couldn't receive data from
client")
PGAdmin says the usual " Unable to connect to server:..."
No issue connecting from the Mac.
I see in the logfile that other windows users did connect successfully from
their windows boxes so I am ready to believe that maybe my Parallels somehow
disconnects the connection quickly or something.
Another strange thing is that if I try to connect from my Xojo app (which uses
libpq internally) it says:SSL error: unknown protocol. I had no issue
connecting last week from that installation so I am at my wits end...
Where is the Xojo app running?
Is the pg_hba.conf on the server set up to allow connections from the client
machines?
Yes. Also the errormessage wouldn't be "Connection reset by peer" if it was a
pga_hba.conf issue, would it? And anyway, I can connect just fine from my Mac with the
same credentials. I wonder if this is maybe some Parallels issue.
Well if you could connect from Parallels last week, you upgraded it and
now you cannot connect then I would suspect it. This assumes that the
Xojo app is also running in the Parallels instance. Given the SSL error
you might see if they changed anything in that area.
Thanks,
Max
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Connection issue
Hi,
> On 5. Feb 2019, at 15:57, Adrian Klaver wrote:
>
> On 2/5/19 1:48 AM, Maximilian Tyrtania wrote:
>> For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu)
>> from my Windows installation (run via Parallels on my Mac), no matter what
>> app I use (tried psql, PGAdmin and my own app).
>
> Has it ever connected?
Yes, it connected without issues last week.
> Have you recently updated any of the involved software?
Well, sure, but after I saw I couldn't connect I downloaded the current version
of PGAdmin and psql and couldn't connect with them either.
> More below.
>
>> When trying to connect from PGAdmin 4 the server logs say:
>> 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG: 0:
>> Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de Port=50262
>> 2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT:
>> BackendInitialize, postmaster.c:4249
>> 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG: 08006:
>> konnte Daten vom Client nicht empfangen: Connection reset by peer
>> 2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT:
>> pq_recvbuf, pqcomm.c:978
>> ("konnte Daten vom Client nicht empfangen" means "couldn't receive data from
>> client")
>> PGAdmin says the usual " Unable to connect to server:..."
>> No issue connecting from the Mac.
>> I see in the logfile that other windows users did connect successfully from
>> their windows boxes so I am ready to believe that maybe my Parallels somehow
>> disconnects the connection quickly or something.
>> Another strange thing is that if I try to connect from my Xojo app (which
>> uses libpq internally) it says:SSL error: unknown protocol. I had no issue
>> connecting last week from that installation so I am at my wits end...
>
> Where is the Xojo app running?
>
> Is the pg_hba.conf on the server set up to allow connections from the client
> machines?
Yes. Also the errormessage wouldn't be "Connection reset by peer" if it was a
pga_hba.conf issue, would it? And anyway, I can connect just fine from my Mac
with the same credentials. I wonder if this is maybe some Parallels issue.
Thanks,
Max
Re: Connection issue
On 2/5/19 1:48 AM, Maximilian Tyrtania wrote:
For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu) from
my Windows installation (run via Parallels on my Mac), no matter what app I use
(tried psql, PGAdmin and my own app).
Has it ever connected?
Have you recently updated any of the involved software?
More below.
When trying to connect from PGAdmin 4 the server logs say:
2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG: 0:
Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de Port=50262
2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT:
BackendInitialize, postmaster.c:4249
2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG: 08006: konnte
Daten vom Client nicht empfangen: Connection reset by peer
2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT: pq_recvbuf,
pqcomm.c:978
("konnte Daten vom Client nicht empfangen" means "couldn't receive data from
client")
PGAdmin says the usual " Unable to connect to server:..."
No issue connecting from the Mac.
I see in the logfile that other windows users did connect successfully from
their windows boxes so I am ready to believe that maybe my Parallels somehow
disconnects the connection quickly or something.
Another strange thing is that if I try to connect from my Xojo app (which uses
libpq internally) it says:SSL error: unknown protocol. I had no issue
connecting last week from that installation so I am at my wits end...
Where is the Xojo app running?
Is the pg_hba.conf on the server set up to allow connections from the
client machines?
Tia,
Max
--
Adrian Klaver
adrian.kla...@aklaver.com
Connection issue
For some reason I can't connect to my PG 10.6.1 Server (running on Ubuntu) from
my Windows installation (run via Parallels on my Mac), no matter what app I use
(tried psql, PGAdmin and my own app).
When trying to connect from PGAdmin 4 the server logs say:
2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] LOG: 0:
Verbindung empfangen: Host=ip5b4054ce.dynamic.kabel-deutschland.de Port=50262
2019-02-05 10:34:30.558 CET [31051] [unbekannt]@[unbekannt] ORT:
BackendInitialize, postmaster.c:4249
2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] LOG: 08006: konnte
Daten vom Client nicht empfangen: Connection reset by peer
2019-02-05 10:34:30.648 CET [31051] [unbekannt]@[unbekannt] ORT: pq_recvbuf,
pqcomm.c:978
("konnte Daten vom Client nicht empfangen" means "couldn't receive data from
client")
PGAdmin says the usual " Unable to connect to server:..."
No issue connecting from the Mac.
I see in the logfile that other windows users did connect successfully from
their windows boxes so I am ready to believe that maybe my Parallels somehow
disconnects the connection quickly or something.
Another strange thing is that if I try to connect from my Xojo app (which uses
libpq internally) it says:SSL error: unknown protocol. I had no issue
connecting last week from that installation so I am at my wits end...
Tia,
Max
