Re: How to debug authentication issues in Postgres

[Hemil Ruparel]

I have restarted postgres quite a few times to try making configuration
changes and it is always back up. I don't know how. Feels weird to me. I
didn't add the line "local replication all". It was there by default

On Fri, Nov 27, 2020 at 1:24 PM Laurenz Albe 
wrote:

> On Fri, 2020-11-27 at 12:44 +0530, Hemil Ruparel wrote:
> > I have a remote database which I can connect to using psql command line
> tool as well as PgAdmin4. But I would really like to use DataGrip. But
> whenever I try to connect, it gives me fatal: password
> > authentication failed and prompts me for another password. I raised an
> issue in DataGrip and I was told there is an issue in my database
> configuration.
> >
> > Here is my pg_hba.conf:
> > ```
> > # TYPE  DATABASEUSERADDRESS METHOD
> >
> > # "local" is for Unix domain socket connections only
> > local   all all peer
> > # IPv4 local connections:
> > hostall all 127.0.0.1/32
> scram-sha-256
> > # IPv4 connections from internet
> > hostdatabaseuser  0.0.0.0/0   scram-sha-256
> > hostdatabaseuser  0.0.0.0/0   md5
> > hostdatabaseuser  0.0.0.0/0   password
> > # IPv6 local connections:
> > hostall all ::1/128
>  scram-sha-256
> > # IPv6 connections from internet:
> > hostdatabaseuser  ::0/0   scram-sha-256
> > hostdatabaseuser  ::0/0   md5
> > hostdatabaseuser  ::0/0   password
> > # Allow replication connections from localhost, by a user with the
> > # replication privilege.
> > local   replication all
> > ```
> >
> > Since I know a Java and I know Idea uses java, so I wrote this small
> snippet to try to connect to my server using JDBC:
> > ```java
> > public class Test {
> >  public static void main(String[] args) throws SQLException {
> > Connection connection = DriverManager.getConnection(
> > "jdbc:postgresql://url/database",
> > "user",
> > "password"
> > );
> >
> > try (connection) {
> >  Statement statement = connection.createStatement();
> >  statement.execute("select version()");
> > }
> > }
> > }
> > ```
> > And it failed with the same error
>
> You should consult the PostgreSQL log file.
>
> For one, the last line "local   replication all" is syntactically
> wrong, which
> would lead to an error message in the log and cause the file not to take
> effect.
> It will also prevent PostgreSQL from starting if you restart it.
>
> The second reason to look into the log file (once you have fixed
> pg_hba.conf) is
> that it will give you more details to error message. The client gets less
> information,
> because such information could be useful to an attacker.
> I'd expect that you get at least the line in pg_hba.conf that was used,
> which will
> ease debugging for you.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: How to debug authentication issues in Postgres

[Laurenz Albe]

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
> I have restarted postgres quite a few times to try making configuration 
> changes and it
>  is always back up. I don't know how. Feels weird to me. I didn't add the line
>  "local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication all trust
hostreplication all 127.0.0.1/32trust
hostreplication all ::1/128 trust

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: How to debug authentication issues in Postgres

[Hemil Ruparel]

Sorry. This was the replication section:
local   replication all peer
hostreplication all 127.0.0.1/32
 scram-sha-256
hostreplication all ::1/128
scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe 
wrote:

> On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
> > I have restarted postgres quite a few times to try making configuration
> changes and it
> >  is always back up. I don't know how. Feels weird to me. I didn't add
> the line
> >  "local replication all". It was there by default
>
> I don't believe that.
>
> This is how it looks by default:
>
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> local   replication all trust
> hostreplication all 127.0.0.1/32trust
> hostreplication all ::1/128 trust
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: How to debug authentication issues in Postgres

[Hemil Ruparel]

The log says:
> FATAL:  password authentication failed for user "centos"
> DETAIL:  Connection matched pg_hba.conf line 88: "hostuser
password  0.0.0.0/0   scram-sha-256"

I can't understand where is the problem as both psql and pgadmin connect
without problems using the same password

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel 
wrote:

> Sorry. This was the replication section:
> local   replication all peer
> hostreplication all 127.0.0.1/32
>  scram-sha-256
> hostreplication all ::1/128
> scram-sha-256
>
> On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe 
> wrote:
>
>> On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
>> > I have restarted postgres quite a few times to try making configuration
>> changes and it
>> >  is always back up. I don't know how. Feels weird to me. I didn't add
>> the line
>> >  "local replication all". It was there by default
>>
>> I don't believe that.
>>
>> This is how it looks by default:
>>
>> # Allow replication connections from localhost, by a user with the
>> # replication privilege.
>> local   replication all trust
>> hostreplication all 127.0.0.1/32trust
>> hostreplication all ::1/128 trust
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
>>

Re: postgres_fdw insert extremely slow

[pabloa98]

On Thu, Nov 26, 2020 at 8:25 PM Laurenz Albe 
wrote:

> On Thu, 2020-11-26 at 09:07 -0800, Adrian Klaver wrote:
> > So even if Mats where to break this query:
> >
> > INSERT INTO foreign.labels (address, labels)
> > SELECT address_id, ARRAY_AGG(name) AS labels
> > FROM labels
> > GROUP BY 1
> > LIMIT 100;
> >
> > down into something like this:
> >
> > INSERT INTO foreign.labels (address, labels)
> > VALUES (), (), (), ();
> >
> > postgres_fdw would send it as individual INSERTs?
>
> Yes, that's the way the FDW API works.
>
> I can confirm that it is NOT like FDW API work. I have the same problem
and in the fdw database it receives:

INSERT INTO foreign.labels (address, labels) VALUES ();

One value at a time.

Same scenario, same performance. No network related. It is just a batch
insert is split into 1 insert by row.


Pablo

Re: postgres_fdw insert extremely slow

[Mats Julian Olsen]

On 27.11.2020 10:11, pabloa98 wrote:



On Thu, Nov 26, 2020 at 8:25 PM Laurenz Albe > wrote:


On Thu, 2020-11-26 at 09:07 -0800, Adrian Klaver wrote:
> So even if Mats where to break this query:
>
> INSERT INTO foreign.labels (address, labels)
> SELECT address_id, ARRAY_AGG(name) AS labels
> FROM labels
> GROUP BY 1
> LIMIT 100;
>
> down into something like this:
>
> INSERT INTO foreign.labels (address, labels)
> VALUES (), (), (), ();
>
> postgres_fdw would send it as individual INSERTs?

Yes, that's the way the FDW API works.

I can confirm that it is NOT like FDW API work. I have the same 
problem and in the fdw database it receives:


INSERT INTO foreign.labels (address, labels) VALUES ();

One value at a time.

Same scenario, same performance. No network related. It is just a 
batch insert is split into 1 insert by row.



Pablo


Thank you all for your replies.

We've decided not to use postgres_fdw for the time being because of this 
behavior. Hope to revisit in the future.


Best,

--
Mats
CTO @ Dune Analytics
We're hiring: https://careers.duneanalytics.com

Problem with pg_notify / listen

[Gustavsson Mikael]

Hi.

After applying the latest patch we have encountered a problem with the 
pg_notify queue.

The queue is filling up and starts issuing warnings like
WARNING:  NOTIFY queue is 87% full
DETAIL:  The server process with PID 2969993 is among those with the oldest 
transactions.
NOTIFY queue cannot be emptied until that process ends its current transaction.

There is no long-running 'active' or 'idle in transaction' transactions on the 
server.

We restarted the application with the said pid but the result was that the new 
process pid appeared in the logs instead.
We tried to stop everything except postgresql it self but the queue was not 
purged. After restarting postgresql the queue is empty and seems to be holding 
up for an hour or so before it starts growing again.

We have been using pg_notify / listen for about three years and haven’t run in 
to this problem before so it seems to me that something happened in the latest 
patch.

PG version: 11.10 with slony. The notify triggers is on the master node.

KR.
Mikael Gustavsson
SMHI
Swedish Meteorological and Hydrological Institute

Re: Problem with pg_notify / listen

[David G. Johnston]

On Friday, November 27, 2020, Gustavsson Mikael 
wrote:

> Hi.
>
> After applying the latest patch we have encountered a problem with the
> pg_notify queue.
>
> The queue is filling up and starts issuing warnings like
> WARNING:  NOTIFY queue is 87% full
> DETAIL:  The server process with PID 2969993 is among those with the
> oldest transactions.
> NOTIFY queue cannot be emptied until that process ends its current
> transaction.
>
> There is no long-running 'active' or 'idle in transaction' transactions on
> the server.
>
>

As its easy enough to run can you please confirm this by showing us the
pg_stat_activity record for that pid during the time when this warning
appears?

Thanks!

David J.

AW: How to debug authentication issues in Postgres

[Zwettler Markus (OIZ)]

Did you correctly upgrade your whole environment to scram-sha-256?



To upgrade an existing installation from md5 to scram-sha-256, after having 
ensured that all client libraries in use are new enough to support SCRAM, set 
password_encryption = 'scram-sha-256' in postgresql.conf, make all users set 
new passwords, and change the authentication method specifications in 
pg_hba.conf to scram-sha-256.



-Markus



Von: Hemil Ruparel 
Gesendet: Freitag, 27. November 2020 09:38
An: Laurenz Albe 
Cc: pgsql-generallists.postgresql.org 
Betreff: Re: How to debug authentication issues in Postgres

The log says:
> FATAL:  password authentication failed for user "centos"
> DETAIL:  Connection matched pg_hba.conf line 88: "hostuser   password 
>  0.0.0.0/0   scram-sha-256"

I can't understand where is the problem as both psql and pgadmin connect 
without problems using the same password

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel 
mailto:hemilruparel2...@gmail.com>> wrote:
Sorry. This was the replication section:
local   replication all peer
hostreplication all 127.0.0.1/32   
 scram-sha-256
hostreplication all ::1/128 scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe 
mailto:laurenz.a...@cybertec.at>> wrote:
On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
> I have restarted postgres quite a few times to try making configuration 
> changes and it
>  is always back up. I don't know how. Feels weird to me. I didn't add the line
>  "local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication all trust
hostreplication all 127.0.0.1/32   
 trust
hostreplication all ::1/128 trust

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Deleting takes days, should I add some index?

[Alexander Farber]

Hello,

I am using PostgreSQL 10.15 on CentOS 7 with 64 GB RAM, Intel i7 6700 and I
have the following 2 tables there:

words_ru=> \d words_games
  Table "public.words_games"
  Column  |   Type   | Collation | Nullable |
   Default
--+--+---+--+--
 gid  | integer  |   | not null |
nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone |   | not null |
 finished | timestamp with time zone |   |  |
 player1  | integer  |   | not null |
 player2  | integer  |   |  |
 played1  | timestamp with time zone |   |  |
 played2  | timestamp with time zone |   |  |
 state1   | text |   |  |
 state2   | text |   |  |
 reason   | text |   |  |
 hint1| text |   |  |
 hint2| text |   |  |
 score1   | integer  |   | not null |
 score2   | integer  |   | not null |
 chat1| integer  |   | not null |
 chat2| integer  |   | not null |
 hand1| character(1)[]   |   | not null |
 hand2| character(1)[]   |   | not null |
 pile | character(1)[]   |   | not null |
 letters  | character(1)[]   |   | not null |
 values   | integer[]|   | not null |
 bid  | integer  |   | not null |
 diff1| integer  |   |  |
 diff2| integer  |   |  |
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
'infinity'::timestamp with time zone))
"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
'infinity'::timestamp with time zone))
Check constraints:
"words_games_chat1_check" CHECK (chat1 >= 0)
"words_games_chat2_check" CHECK (chat2 >= 0)
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

words_ru=> \d words_moves
  Table "public.words_moves"
 Column  |   Type   | Collation | Nullable |
 Default
-+--+---+--+--
 mid | bigint   |   | not null |
nextval('words_moves_mid_seq'::regclass)
 action  | text |   | not null |
 gid | integer  |   | not null |
 uid | integer  |   | not null |
 played  | timestamp with time zone |   | not null |
 tiles   | jsonb|   |  |
 score   | integer  |   |  |
 str | text |   |  |
 hand| text |   |  |
 puzzle  | boolean  |   | not null | false
 letters | character(1)[]   |   |  |
 values  | integer[]|   |  |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_puzzle_idx" btree (puzzle)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE

My word game is published since beginning of 2018 and I have that many
entries there:

words_ru=> select coun

SV: Problem with pg_notify / listen

[Gustavsson Mikael]

Hi David,


Thanks for fast reply!


We had to restart the server to avoid problems with a full queue så process is 
no longer with us.

The queue is at 25% now and we have minimised the payload to avoid problems 
during the weekend.
# select * from pg_notification_queue_usage()
;
 pg_notification_queue_usage
-
   0.245367050170898
(1 row)

If my calculations is correct we will reach 50% on monday.

kr
/Mikael Gustavsson


Från: David G. Johnston 
Skickat: den 27 november 2020 15:28
Till: Gustavsson Mikael
Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders
Ämne: Re: Problem with pg_notify / listen

On Friday, November 27, 2020, Gustavsson Mikael 
mailto:mikael.gustavs...@smhi.se>> wrote:

Hi.

After applying the latest patch we have encountered a problem with the 
pg_notify queue.

The queue is filling up and starts issuing warnings like
WARNING:  NOTIFY queue is 87% full
DETAIL:  The server process with PID 2969993 is among those with the oldest 
transactions.
NOTIFY queue cannot be emptied until that process ends its current transaction.

There is no long-running 'active' or 'idle in transaction' transactions on the 
server.


As its easy enough to run can you please confirm this by showing us the 
pg_stat_activity record for that pid during the time when this warning appears?

Thanks!

David J.

Re: How to debug authentication issues in Postgres

[Hemil Ruparel]

I don't quite get what you mean by upgrading to scram-sha256. I installed
postgres 13. I haven't upgraded anything yet.

On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ) <
markus.zwett...@zuerich.ch> wrote:

> Did you correctly upgrade your whole environment to scram-sha-256?
>
>
>
>
>
> 
> To upgrade an existing installation from md5 to scram-sha-256, after
> having ensured that all client libraries in use are new enough to support
> SCRAM, set password_encryption = 'scram-sha-256' in postgresql.conf, make
> all users set new passwords, and change the authentication method
> specifications in pg_hba.conf to scram-sha-256.
>
> 
>
>
>
>
>
> -Markus
>
>
>
>
>
>
>
> *Von:* Hemil Ruparel 
> *Gesendet:* Freitag, 27. November 2020 09:38
> *An:* Laurenz Albe 
> *Cc:* pgsql-generallists.postgresql.org <
> pgsql-general@lists.postgresql.org>
> *Betreff:* Re: How to debug authentication issues in Postgres
>
>
>
> The log says:
>
> > FATAL:  password authentication failed for user "centos"
> > DETAIL:  Connection matched pg_hba.conf line 88: "hostuser
> password  0.0.0.0/0   scram-sha-256"
>
>
>
> I can't understand where is the problem as both psql and pgadmin connect
> without problems using the same password
>
>
>
> On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel 
> wrote:
>
> Sorry. This was the replication section:
>
> local   replication all peer
> hostreplication all 127.0.0.1/32
>  scram-sha-256
> hostreplication all ::1/128
> scram-sha-256
>
>
>
> On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe 
> wrote:
>
> On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
> > I have restarted postgres quite a few times to try making configuration
> changes and it
> >  is always back up. I don't know how. Feels weird to me. I didn't add
> the line
> >  "local replication all". It was there by default
>
> I don't believe that.
>
> This is how it looks by default:
>
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> local   replication all trust
> hostreplication all 127.0.0.1/32trust
> hostreplication all ::1/128 trust
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: Deleting takes days, should I add some index?

[Alvaro Herrera]

On 2020-Nov-27, Alexander Farber wrote:

> Referenced by:
> TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
> REFERENCES words_games(gid) ON DELETE CASCADE
> TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
> REFERENCES words_games(gid) ON DELETE CASCADE
> TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
> (gid) REFERENCES words_games(gid) ON DELETE CASCADE

Make sure you have indexes on the gid columns of these tables.  Delete
needs to scan them in order to find the rows that are cascaded to.

> So I ctrl-c (surprisingly not a single record was deleted; I was expecting
> at least some to be gone)

Ctrl-C aborts the transaction, so even though the rows are marked
deleted, they are so by an aborted transaction.  Therefore they're
alive.

Re: How to debug authentication issues in Postgres

[Adrian Klaver]

On 11/27/20 7:01 AM, Hemil Ruparel wrote:
I don't quite get what you mean by upgrading to scram-sha256. I 
installed postgres 13. I haven't upgraded anything yet.


In postgresql.conf see what password_encryption has been set to. If it 
is 'scram-sha-256` then it has been upgraded.






On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ) 
mailto:markus.zwett...@zuerich.ch>> wrote:


Did you correctly upgrade your whole environment to scram-sha-256?

__ __

__ __


To upgrade an existing installation from |md5|to |scram-sha-256|,
after having ensured that all client libraries in use are new enough
to support SCRAM, set |password_encryption = 'scram-sha-256'|in
|postgresql.conf|, make all users set new passwords, and change the
authentication method specifications in |pg_hba.conf|to
|scram-sha-256|.



__ __

__ __

-Markus

__ __

__ __

__ __

*Von:*Hemil Ruparel mailto:hemilruparel2...@gmail.com>>
*Gesendet:* Freitag, 27. November 2020 09:38
*An:* Laurenz Albe mailto:laurenz.a...@cybertec.at>>
*Cc:* pgsql-generallists.postgresql.org

mailto:pgsql-general@lists.postgresql.org>>
*Betreff:* Re: How to debug authentication issues in Postgres

__ __

The log says:

 > FATAL:  password authentication failed for user "centos"
 > DETAIL:  Connection matched pg_hba.conf line 88: "host    user  
     password 0.0.0.0/0   
scram-sha-256"


__ __

I can't understand where is the problem as both psql and pgadmin
connect without problems using the same password

__ __

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
mailto:hemilruparel2...@gmail.com>>
wrote:

Sorry. This was the replication section:

local   replication     all                                     peer
host    replication     all 127.0.0.1/32   
          scram-sha-256
host    replication     all             ::1/128
scram-sha-256


__ __

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
mailto:laurenz.a...@cybertec.at>>
wrote:

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
 > I have restarted postgres quite a few times to try making
configuration changes and it
 >  is always back up. I don't know how. Feels weird to me.
I didn't add the line
 >  "local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user
with the
# replication privilege.
local   replication     all 
    trust

host    replication     all 127.0.0.1/32
            trust
host    replication     all             ::1/128 
    trust


Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Deleting takes days, should I add some index?

[Guillaume Lelarge]

Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera  a
écrit :

> On 2020-Nov-27, Alexander Farber wrote:
>
> > Referenced by:
> > TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
> > REFERENCES words_games(gid) ON DELETE CASCADE
> > TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY
> (gid)
> > REFERENCES words_games(gid) ON DELETE CASCADE
> > TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
> > (gid) REFERENCES words_games(gid) ON DELETE CASCADE
>
> Make sure you have indexes on the gid columns of these tables.  Delete
> needs to scan them in order to find the rows that are cascaded to.
>
>
An index on words_games(finished) and words_moves(played) would help too.


-- 
Guillaume.

Re: How to debug authentication issues in Postgres

[Hemil Ruparel]

The database has been upgraded

On Fri, Nov 27, 2020 at 8:41 PM Adrian Klaver 
wrote:

> On 11/27/20 7:01 AM, Hemil Ruparel wrote:
> > I don't quite get what you mean by upgrading to scram-sha256. I
> > installed postgres 13. I haven't upgraded anything yet.
>
> In postgresql.conf see what password_encryption has been set to. If it
> is 'scram-sha-256` then it has been upgraded.
>
>
>
> >
> > On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ)
> > mailto:markus.zwett...@zuerich.ch>> wrote:
> >
> > Did you correctly upgrade your whole environment to
> scram-sha-256?
> >
> > __ __
> >
> > __ __
> >
> > 
> > To upgrade an existing installation from |md5|to |scram-sha-256|,
> > after having ensured that all client libraries in use are new enough
> > to support SCRAM, set |password_encryption = 'scram-sha-256'|in
> > |postgresql.conf|, make all users set new passwords, and change the
> > authentication method specifications in |pg_hba.conf|to
> > |scram-sha-256|.
> >
> > 
> >
> > __ __
> >
> > __ __
> >
> > -Markus
> >
> > __ __
> >
> > __ __
> >
> > __ __
> >
> > *Von:*Hemil Ruparel  > >
> > *Gesendet:* Freitag, 27. November 2020 09:38
> > *An:* Laurenz Albe  > >
> > *Cc:* pgsql-generallists.postgresql.org
> > 
> >  > >
> > *Betreff:* Re: How to debug authentication issues in Postgres
> >
> > __ __
> >
> > The log says:
> >
> >  > FATAL:  password authentication failed for user "centos"
> >  > DETAIL:  Connection matched pg_hba.conf line 88: "hostuser
> >  password 0.0.0.0/0 
> > scram-sha-256"
> >
> > __ __
> >
> > I can't understand where is the problem as both psql and pgadmin
> > connect without problems using the same password
> >
> > __ __
> >
> > On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
> > mailto:hemilruparel2...@gmail.com>>
> > wrote:
> >
> > Sorry. This was the replication section:
> >
> > local   replication all
> peer
> > hostreplication all 127.0.0.1/32 
> >   scram-sha-256
> > hostreplication all ::1/128
> > scram-sha-256
> >
> > __ __
> >
> > On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
> > mailto:laurenz.a...@cybertec.at>>
> > wrote:
> >
> > On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
> >  > I have restarted postgres quite a few times to try making
> > configuration changes and it
> >  >  is always back up. I don't know how. Feels weird to me.
> > I didn't add the line
> >  >  "local replication all". It was there by default
> >
> > I don't believe that.
> >
> > This is how it looks by default:
> >
> > # Allow replication connections from localhost, by a user
> > with the
> > # replication privilege.
> > local   replication all
> > trust
> > hostreplication all 127.0.0.1/32
> > trust
> > hostreplication all ::1/128
> > trust
> >
> > Yours,
> > Laurenz Albe
> > --
> > Cybertec | https://www.cybertec-postgresql.com
> > 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: How to debug authentication issues in Postgres

[Hemil Ruparel]

When I try to connect to the database, the log says:
> FATAL:  password authentication failed for user "user"
> DETAIL:  Connection matched pg_hba.conf line 88: "hostuser
password  0.0.0.0/0   scram-sha-256"

So I think the client is using scram-sha-256

On Fri, Nov 27, 2020 at 8:45 PM Hemil Ruparel 
wrote:

> The database has been upgraded
>
> On Fri, Nov 27, 2020 at 8:41 PM Adrian Klaver 
> wrote:
>
>> On 11/27/20 7:01 AM, Hemil Ruparel wrote:
>> > I don't quite get what you mean by upgrading to scram-sha256. I
>> > installed postgres 13. I haven't upgraded anything yet.
>>
>> In postgresql.conf see what password_encryption has been set to. If it
>> is 'scram-sha-256` then it has been upgraded.
>>
>>
>>
>> >
>> > On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ)
>> > mailto:markus.zwett...@zuerich.ch>> wrote:
>> >
>> > Did you correctly upgrade your whole environment to
>> scram-sha-256?
>> >
>> > __ __
>> >
>> > __ __
>> >
>> > 
>> > To upgrade an existing installation from |md5|to |scram-sha-256|,
>> > after having ensured that all client libraries in use are new enough
>> > to support SCRAM, set |password_encryption = 'scram-sha-256'|in
>> > |postgresql.conf|, make all users set new passwords, and change the
>> > authentication method specifications in |pg_hba.conf|to
>> > |scram-sha-256|.
>> >
>> > 
>> >
>> > __ __
>> >
>> > __ __
>> >
>> > -Markus
>> >
>> > __ __
>> >
>> > __ __
>> >
>> > __ __
>> >
>> > *Von:*Hemil Ruparel > > >
>> > *Gesendet:* Freitag, 27. November 2020 09:38
>> > *An:* Laurenz Albe > > >
>> > *Cc:* pgsql-generallists.postgresql.org
>> > 
>> > > > >
>> > *Betreff:* Re: How to debug authentication issues in Postgres
>> >
>> > __ __
>> >
>> > The log says:
>> >
>> >  > FATAL:  password authentication failed for user "centos"
>> >  > DETAIL:  Connection matched pg_hba.conf line 88: "hostuser
>> >  password 0.0.0.0/0 
>> > scram-sha-256"
>> >
>> > __ __
>> >
>> > I can't understand where is the problem as both psql and pgadmin
>> > connect without problems using the same password
>> >
>> > __ __
>> >
>> > On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
>> > mailto:hemilruparel2...@gmail.com>>
>> > wrote:
>> >
>> > Sorry. This was the replication section:
>> >
>> > local   replication all
>> peer
>> > hostreplication all 127.0.0.1/32 
>>
>> >   scram-sha-256
>> > hostreplication all ::1/128
>> > scram-sha-256
>> >
>> > __ __
>> >
>> > On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
>> > mailto:laurenz.a...@cybertec.at>>
>> > wrote:
>> >
>> > On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
>> >  > I have restarted postgres quite a few times to try making
>> > configuration changes and it
>> >  >  is always back up. I don't know how. Feels weird to me.
>> > I didn't add the line
>> >  >  "local replication all". It was there by default
>> >
>> > I don't believe that.
>> >
>> > This is how it looks by default:
>> >
>> > # Allow replication connections from localhost, by a user
>> > with the
>> > # replication privilege.
>> > local   replication all
>> > trust
>> > hostreplication all 127.0.0.1/32
>> > trust
>> > hostreplication all ::1/128
>> > trust
>> >
>> > Yours,
>> > Laurenz Albe
>> > --
>> > Cybertec | https://www.cybertec-postgresql.com
>> > 
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>

Re: Problem with pg_notify / listen

[Tom Lane]

Gustavsson Mikael  writes:
> After applying the latest patch we have encountered a problem with the 
> pg_notify queue.

What do you mean by "the latest patch", exactly?

regards, tom lane

SV: Problem with pg_notify / listen

[Gustavsson Mikael]

Hi.


We installed PG 11.10 last week. So the latest release of PG 11.


KR

Mikael Gustavsson


Från: Tom Lane 
Skickat: den 27 november 2020 16:21:33
Till: Gustavsson Mikael
Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders
Ämne: Re: Problem with pg_notify / listen

Gustavsson Mikael  writes:
> After applying the latest patch we have encountered a problem with the 
> pg_notify queue.

What do you mean by "the latest patch", exactly?

regards, tom lane

SV: Problem with pg_notify / listen

[Gustavsson Mikael]

Clarification, we upgraded from PG 11.9 to PG 11.10.


KR Mikael Gustavsson


Från: externaly-forwar...@smhi.se  för Gustavsson 
Mikael 
Skickat: den 27 november 2020 16:24:38
Till: Tom Lane
Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders
Ämne: SV: Problem with pg_notify / listen


Hi.


We installed PG 11.10 last week. So the latest release of PG 11.


KR

Mikael Gustavsson


Från: Tom Lane 
Skickat: den 27 november 2020 16:21:33
Till: Gustavsson Mikael
Kopia: pgsql-generallists.postgresql.org; Svensson Peter; Almen Anders
Ämne: Re: Problem with pg_notify / listen

Gustavsson Mikael  writes:
> After applying the latest patch we have encountered a problem with the 
> pg_notify queue.

What do you mean by "the latest patch", exactly?

regards, tom lane

Re: How to debug authentication issues in Postgres

[Tom Lane]

Hemil Ruparel  writes:
> When I try to connect to the database, the log says:
>> FATAL:  password authentication failed for user "user"
>> DETAIL:  Connection matched pg_hba.conf line 88: "hostuser
> password  0.0.0.0/0   scram-sha-256"

> So I think the client is using scram-sha-256

No, what that says is that the server is going to insist on scram-sha-256.
If the client can't handle SCRAM, then a failure would be expected.

regards, tom lane

Re: How to debug authentication issues in Postgres

[Hemil Ruparel]

Thanks for the clarification. According to this page,
https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0,
scram support was added in JDBC driver 42.2.0. I am on 42.2.18. And using
the java code mentioned above, I still get the same error.

On Fri, Nov 27, 2020 at 9:06 PM Tom Lane  wrote:

> Hemil Ruparel  writes:
> > When I try to connect to the database, the log says:
> >> FATAL:  password authentication failed for user "user"
> >> DETAIL:  Connection matched pg_hba.conf line 88: "hostuser
> > password  0.0.0.0/0   scram-sha-256"
>
> > So I think the client is using scram-sha-256
>
> No, what that says is that the server is going to insist on scram-sha-256.
> If the client can't handle SCRAM, then a failure would be expected.
>
> regards, tom lane
>

Re: How to debug authentication issues in Postgres

[Tom Lane]

Hemil Ruparel  writes:
> Thanks for the clarification. According to this page,
> https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0,
> scram support was added in JDBC driver 42.2.0. I am on 42.2.18. And using
> the java code mentioned above, I still get the same error.

If you back off the pg_hba setting to md5, does it work?

regards, tom lane

Re: How to debug authentication issues in Postgres

[Hemil Ruparel]

I will try that. I do not have access to the computer right now

On Fri 27 Nov, 2020, 9:25 PM Tom Lane,  wrote:

> Hemil Ruparel  writes:
> > Thanks for the clarification. According to this page,
> > https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.0,
> > scram support was added in JDBC driver 42.2.0. I am on 42.2.18. And using
> > the java code mentioned above, I still get the same error.
>
> If you back off the pg_hba setting to md5, does it work?
>
> regards, tom lane
>

Re: How to debug authentication issues in Postgres

[Adrian Klaver]

On 11/27/20 7:15 AM, Hemil Ruparel wrote:

The database has been upgraded


Just to be clear the postgresql.conf file has:

password_encryption = scram-sha-256

set correct?



On Fri, Nov 27, 2020 at 8:41 PM Adrian Klaver > wrote:


On 11/27/20 7:01 AM, Hemil Ruparel wrote:
 > I don't quite get what you mean by upgrading to scram-sha256. I
 > installed postgres 13. I haven't upgraded anything yet.

In postgresql.conf see what password_encryption has been set to. If it
is 'scram-sha-256` then it has been upgraded.



 >
 > On Fri, Nov 27, 2020 at 8:06 PM Zwettler Markus (OIZ)
 > mailto:markus.zwett...@zuerich.ch>
>> wrote:
 >
 >     Did you correctly upgrade your whole environment to
scram-sha-256?
 >
 >     __ __
 >
 >     __ __
 >
 >     
 >     To upgrade an existing installation from |md5|to |scram-sha-256|,
 >     after having ensured that all client libraries in use are new
enough
 >     to support SCRAM, set |password_encryption = 'scram-sha-256'|in
 >     |postgresql.conf|, make all users set new passwords, and
change the
 >     authentication method specifications in |pg_hba.conf|to
 >     |scram-sha-256|.
 >
 >     
 >
 >     __ __
 >
 >     __ __
 >
 >     -Markus
 >
 >     __ __
 >
 >     __ __
 >
 >     __ __
 >
 >     *Von:*Hemil Ruparel mailto:hemilruparel2...@gmail.com>
 >     >>
 >     *Gesendet:* Freitag, 27. November 2020 09:38
 >     *An:* Laurenz Albe mailto:laurenz.a...@cybertec.at>
 >     >>
 >     *Cc:* pgsql-generallists.postgresql.org

 >     >
 >     mailto:pgsql-general@lists.postgresql.org>
 >     >>
 >     *Betreff:* Re: How to debug authentication issues in Postgres
 >
 >     __ __
 >
 >     The log says:
 >
 >      > FATAL:  password authentication failed for user "centos"
 >      > DETAIL:  Connection matched pg_hba.conf line 88: "host   
user

 >          password 0.0.0.0/0  >
 >     scram-sha-256"
 >
 >     __ __
 >
 >     I can't understand where is the problem as both psql and pgadmin
 >     connect without problems using the same password
 >
 >     __ __
 >
 >     On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel
 >     mailto:hemilruparel2...@gmail.com>
>>
 >     wrote:
 >
 >         Sorry. This was the replication section:
 >
 >         local   replication     all  
       peer

 >         host    replication     all 127.0.0.1/32
 >
 >                   scram-sha-256
 >         host    replication     all             ::1/128
 >         scram-sha-256
 >
 >         __ __
 >
 >         On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
 >         mailto:laurenz.a...@cybertec.at> >>
 >         wrote:
 >
 >             On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
 >              > I have restarted postgres quite a few times to try
making
 >             configuration changes and it
 >              >  is always back up. I don't know how. Feels weird
to me.
 >             I didn't add the line
 >              >  "local replication all". It was there by default
 >
 >             I don't believe that.
 >
 >             This is how it looks by default:
 >
 >             # Allow replication connections from localhost, by a user
 >             with the
 >             # replication privilege.
 >             local   replication     all
 >                 trust
 >             host    replication     all 127.0.0.1/32

 >             > 
   trust

 >             host    replication     all             ::1/128
 >                 trust
 >
 >             Yours,
 >             Laurenz Albe
 >             --
 >             Cybertec | https://www.cybertec-postgresql.com

 >             

Re: How to debug authentication issues in Postgres

[Adrian Klaver]

On 11/27/20 12:37 AM, Hemil Ruparel wrote:

The log says:
 > FATAL:  password authentication failed for user "centos"
 > DETAIL:  Connection matched pg_hba.conf line 88: "host    user   
password 0.0.0.0/0                scram-sha-256"


To me that looks like a strange line for pg_hba.conf and I don't see it 
in the pg_hba.conf file you sent earlier.


What is line 88 in your pg_hba.conf?



I can't understand where is the problem as both psql and pgadmin connect 
without problems using the same password


On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel 
mailto:hemilruparel2...@gmail.com>> wrote:


Sorry. This was the replication section:
local   replication     all                                     peer
host    replication     all 127.0.0.1/32   
      scram-sha-256
host    replication     all             ::1/128
scram-sha-256


On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe
mailto:laurenz.a...@cybertec.at>> wrote:

On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
 > I have restarted postgres quite a few times to try making
configuration changes and it
 >  is always back up. I don't know how. Feels weird to me. I
didn't add the line
 >  "local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all   
  trust
host    replication     all 127.0.0.1/32  
           trust
host    replication     all             ::1/128   
  trust


Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: postgres_fdw insert extremely slow

[pabloa98]

I would like to suggest for postgres_fdw: If the foreign database is
PostgreSQL, the link should just pass through all the CRUD SQL commands to
the other database.

If the other database is of a version so different that cannot make sense
of the CRUD SQL command, it will generate an error and that's it.

This would be very useful to keep datasets synchronized.

Pablo

Re: postgres_fdw insert extremely slow

[David G. Johnston]

On Fri, Nov 27, 2020 at 2:00 PM pabloa98  wrote:

> I would like to suggest for postgres_fdw: If the foreign database is
> PostgreSQL,
>

Just to be clear, the "postgres" part of the name means the remote database
must be a PostgreSQL database, there is no "if".  Likewise, for the
extension mysql_fdw the remote database is "MySQL".


> the link should just pass through all the CRUD SQL commands to the other
> database.
>
> If the other database is of a version so different that cannot make sense
> of the CRUD SQL command, it will generate an error and that's it.
>
> This would be very useful to keep datasets synchronized.
>
>
We already offer a tool for that, dblink.

https://www.postgresql.org/docs/current/dblink.html

But the generalized behavior of FDW doesn't work to just send a raw SQL
command across, even for CRUD.
e.g.,
UPDATE remote_tbl
SET ...
FROM local_tbl
WHERE remote_tbl.col_id=local_tbl.col_id;
or
INSERT INTO remote_tbl SELECT * FROM local_tbl;

Not saying that there isn't room for improvement here but I'm doubting it's
simple.

David J.