Re: Authentication: MD5 to SCRAM-SHA-256 error

[Michael Paquier]

On Fri, Nov 15, 2019 at 03:17:17PM -0500, Dave Hughes wrote:
> Thanks for replying guys!  You both led me down the right path.  I didn't
> realize it, but looks like we had 2 directories where psql was installed.
> Once I gave the path specifically to PostgreSQL10, it worked like a charm.
> 
> I'm reaching out to our server admins now to see why there are 2
> instances.

Maybe remnants of past version packages for an upgrade where
pg_upgrade has been involved?  You need both the old and new binaries
when doing that, and most distributions allow different major versions
of Postgres to be installed in parallel.
--
Michael


signature.asc
Description: PGP signature

Re: Authentication: MD5 to SCRAM-SHA-256 error

[Dave Hughes]

Thanks for replying guys!  You both led me down the right path.  I didn't
realize it, but looks like we had 2 directories where psql was installed.
Once I gave the path specifically to PostgreSQL10, it worked like a charm.

I'm reaching out to our server admins now to see why there are 2
instances.

Thanks again for pointing me in the right direction.

On Fri, Nov 15, 2019 at 11:44 AM Christoph Moench-Tegeder <
c...@burggraben.net> wrote:

> ## Dave Hughes (dhughe...@gmail.com):
>
> > However when I try to log in now, via command line, I receive the error:
> > "psql: authentication method 10 not supported".
>
> Your client (more precisely: it's libpq) is not ready for SCRAM.
> I guess you're using an older (<10) client version? Mixed up
> packages?
>
> Regards,
> Christoph
>
> --
> Spare Space
>

Re: Authentication: MD5 to SCRAM-SHA-256 error

[Christoph Moench-Tegeder]

## Dave Hughes (dhughe...@gmail.com):

> However when I try to log in now, via command line, I receive the error:
> "psql: authentication method 10 not supported".

Your client (more precisely: it's libpq) is not ready for SCRAM.
I guess you're using an older (<10) client version? Mixed up
packages?

Regards,
Christoph

-- 
Spare Space

Re: Authentication: MD5 to SCRAM-SHA-256 error

[Adrian Klaver]

On 11/15/19 8:23 AM, Dave Hughes wrote:

Hello,
We're currently using PostgreSQL version 10.5 in a Linux environment.  
We were wanting to change the password authentication from MD5 to 
SCRAM-SHA-256.  I performed these steps to do so:
1) Modified the postgresql.conf and changed the password_encryption 
entry from "md5" to "scram-sha-256".

2) restarted the database
3) Changed all our users password to a default password using the command:
alter user xxx password 'xxx';
4) Once I did this, I could run this sql statement and verify the 
password was now a sha-256 password:

select passwd from pg_shadow where username = 'xxx'
5)  Finally, i went into the pg_hba.conf file and changed the 
authentication method from md5 over to scram-sha-256.

6) restarted the database again.

However when I try to log in now, via command line, I receive the error: 
"psql: authentication method 10 not supported".  I tried to search 
online for this error but everything I've seen implies that occurs when 
some client's libraries are not compatible, but i'm just using psql via 
the command line.


What's worse...I tried to set everything back to MD5 and i'm still 
getting the exact same error.  Has anyone else experienced this?  The 
only thing I can think of is that even though I'm on version 10.5, maybe 
somehow I have old libraries it's trying to use to connect?


Any help would be greatly appreciated!


What Linux distro and version?

How was Postgres installed?

Do you have more then one instance of Postgres installed?


Thanks!



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

Re: authentication failure

[Adrian Klaver]

On 04/15/2018 06:22 PM, armand pirvu wrote:









Sorry for the late reply. In and out sll sorts of things which do 
distract me.

I raised the authentication_timeout and seems okay for now


I would take the 'for now' as a heads up that there is underlying issue 
that will need to be resolved eventually. Namely that the procedure is 
using enough resources to impact the authentication process.




Thank you for the help

Armand






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

Re: authentication failure

[armand pirvu]

> On Apr 12, 2018, at 10:07 AM, armand pirvu  wrote:
> 
> 
>> On Apr 12, 2018, at 9:55 AM, Adrian Klaver > > wrote:
>> 
>> On 04/12/2018 07:50 AM, armand pirvu wrote:
>> 
 I would be inclined to raise the authentication_timeout first before 
 setting the auth method to trust.
 
 I would also set the below.:
 
 https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
  
 
 
 log_connections (boolean)
 Causes each attempted connection to the server to be logged, as well as 
 successful completion of client authentication. Only superusers can change 
 this parameter at session start, and it cannot be changed at all within a 
 session. The default is off.
 
 log_disconnections (boolean)
 
 That will give you a better idea of what is going on connection wise.
 
 
 
 -- 
 Adrian Klaver
 adrian.kla...@aklaver.com
>>> Will do so and report back
>> 
>> Also, in a previous post you mentioned:
>> 
>> "Yes and worked fine until two days ago"
>> 
>> Is the code under version control so you can see if anything changed two 
>> days ago?
>> 
>> If not, any recollections of significant events from that time period?
>> 
>>> Many thanks
>>> Armand
>> 
>> 
>> -- 
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
> 
> No change in the code
> Only maybe the data volume and potentially the number of what we call events 
> for which that runs
> There was a talk to add some more functionality but never went through
> However I will ask around to see if someone did not forget to say about a 
> change put in place overnight (it happened before)
> 
> Thanks
> Armand
> 
> 


Sorry for the late reply. In and out sll sorts of things which do distract me.
I raised the authentication_timeout and seems okay for now

Thank you for the help

Armand

Re: authentication failure

[armand pirvu]

> On Apr 12, 2018, at 9:55 AM, Adrian Klaver  wrote:
> 
> On 04/12/2018 07:50 AM, armand pirvu wrote:
> 
>>> I would be inclined to raise the authentication_timeout first before 
>>> setting the auth method to trust.
>>> 
>>> I would also set the below.:
>>> 
>>> https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
>>> 
>>> log_connections (boolean)
>>> Causes each attempted connection to the server to be logged, as well as 
>>> successful completion of client authentication. Only superusers can change 
>>> this parameter at session start, and it cannot be changed at all within a 
>>> session. The default is off.
>>> 
>>> log_disconnections (boolean)
>>> 
>>> That will give you a better idea of what is going on connection wise.
>>> 
>>> 
>>> 
>>> -- 
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>> Will do so and report back
> 
> Also, in a previous post you mentioned:
> 
> "Yes and worked fine until two days ago"
> 
> Is the code under version control so you can see if anything changed two days 
> ago?
> 
> If not, any recollections of significant events from that time period?
> 
>> Many thanks
>> Armand
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 

No change in the code
Only maybe the data volume and potentially the number of what we call events 
for which that runs
There was a talk to add some more functionality but never went through
However I will ask around to see if someone did not forget to say about a 
change put in place overnight (it happened before)

Thanks
Armand

Re: authentication failure

[Adrian Klaver]

On 04/12/2018 07:37 AM, armand pirvu wrote:



On Apr 12, 2018, at 9:28 AM, Adrian Klaver  wrote:

On 04/12/2018 07:15 AM, armand pirvu wrote:





Sorry for the double posting but could it be from
#authentication_timeout = 1min# 1s-600s


 From you previous post:

"It is almost like the authnetication stops working for whatever reason"

So to be clear the initial connections in the process go through, but at some 
point they start failing. Is that correct?

The timeout could be an issue. It would helpful to also see what 
max_connections setting is.


So if the server gets a bit oveloaded this could play a role ?



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



Yes correct initially they go through but at some point they start failing
Max_connections is set to 200 on the postgres server, but I did not notice any 
message about being maxed out connections wise
As a workaround for this very specific set of processes, until things get back 
in line so to speak, do you think from the devweb2004 (where processing is 
done) to devdb2004 (where the postgres database resides), should I just go from 
md5 to trusted ?


I would be inclined to raise the authentication_timeout first before 
setting the auth method to trust.


I would also set the below.:

https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

log_connections (boolean)
Causes each attempted connection to the server to be logged, as well as 
successful completion of client authentication. Only superusers can 
change this parameter at session start, and it cannot be changed at all 
within a session. The default is off.


log_disconnections (boolean)

That will give you a better idea of what is going on connection wise.











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

Re: authentication failure

[armand pirvu]

> On Apr 12, 2018, at 9:28 AM, Adrian Klaver  wrote:
> 
> On 04/12/2018 07:15 AM, armand pirvu wrote:
> 
>>> 
>>> 
>> Sorry for the double posting but could it be from
>> #authentication_timeout = 1min# 1s-600s
> 
> From you previous post:
> 
> "It is almost like the authnetication stops working for whatever reason"
> 
> So to be clear the initial connections in the process go through, but at some 
> point they start failing. Is that correct?
> 
> The timeout could be an issue. It would helpful to also see what 
> max_connections setting is.
> 
>> So if the server gets a bit oveloaded this could play a role ?
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com


Yes correct initially they go through but at some point they start failing
Max_connections is set to 200 on the postgres server, but I did not notice any 
message about being maxed out connections wise
As a workaround for this very specific set of processes, until things get back 
in line so to speak, do you think from the devweb2004 (where processing is 
done) to devdb2004 (where the postgres database resides), should I just go from 
md5 to trusted ?

Re: authentication failure

[Adrian Klaver]

On 04/12/2018 07:15 AM, armand pirvu wrote:









Sorry for the double posting but could it be from

#authentication_timeout = 1min# 1s-600s


From you previous post:

"It is almost like the authnetication stops working for whatever reason"

So to be clear the initial connections in the process go through, but at 
some point they start failing. Is that correct?


The timeout could be an issue. It would helpful to also see what 
max_connections setting is.




So if the server gets a bit oveloaded this could play a role ?







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

Re: authentication failure

[armand pirvu]

> On Apr 12, 2018, at 9:12 AM, armand pirvu  wrote:
> 
> 
>> On Apr 12, 2018, at 9:08 AM, Adrian Klaver > > wrote:
>> 
>> On 04/12/2018 06:59 AM, armand pirvu wrote:
>> 
>> Please reply to list also.
>> Ccing list.
>>> Yes and worked fine until two days ago
>>> I use .pgpass
>> 
>> So can you connect from wherever the process is run to the server manually? 
>> Something like:
>> 
>> 
>> psql -d birstab -U csidba -h some_server
>> 
>> 
>>> I will also check and see if there are not too many hands in the cookie jar 
>>> so to speak and things happen without being communicated
 On Apr 12, 2018, at 8:56 AM, Adrian Klaver > wrote:
 
 On 04/12/2018 06:51 AM, armand pirvu wrote:
> Hi there
> I have a process in place which runs several queries from one host to 
> another one
> All of a sudden I started noticing authentication failures
> Like below
> .009 ms  statement: COPY  NACDS.tf_show_code_response_person FROM STDIN 
> with csv;","psql"
> 2018-04-12 00:10:48.765 
> CDT,"csidba","birstdb",7553,"172.16.20.4:40330",5aceea2e.1d81,1,"UPDATE",2018-04-12
>  00:10:06 CDT,24/0,0,LOG,0,"duration: 425
> 90.993 ms  statement: UPDATE
> csischema.tf_transaction_person
> SET
> is_deleted = 'TRUE',
> birst_is_deleted = 'TRUE',
> update_datetime = now()::timestamp(0)
> WHERE
> show_id = '984BIOWC18' AND
> birst_is_deleted = 'FALSE' AND
> person_transaction_id IN (
> SELECT a.person_transaction_id
> FROM csischema.tf_transaction_person a
>  LEFT JOIN BIOWC.tf_transaction_person b
>  ON a.person_transaction_id=b.person_transaction_id
> WHERE a.show_id = '984BIOWC18' AND b.person_transaction_id IS NULL
> )
> ;","psql"
> 2018-04-12 00:10:48.823 
> CDT,"csidba","birstdb",7755,"172.16.20.4:40455",5aceea58.1e4b,1,"authentication",2018-04-12
>  00:10:48 CDT,3/20320168,0,FATAL,28P0
> 1,"password authentication failed for user ""csidba""","Connection 
> matched pg_hba.conf line 84: ""host all all 0.0.0.0/0 md5"""""
> 2018-04-12 00:10:48.841 
> CDT,"csidba","birstdb",7756,"172.16.20.4:40456",5aceea58.1e4c,1,"authentication",2018-04-12
>  00:10:48 CDT,3/20320169,0,FATAL,28P0
> 1,"password authentication failed for user ""csidba""","Connection 
> matched pg_hba.conf line 84: ""host all all 0.0.0.0/0 md5"""""
> 2018-04-12 00:10:48.957 
> CDT,"csidba","birstdb",7759,"172.16.20.4:40459",5aceea58.1e4f,1,"authentication",2018-04-12
>  00:10:48 CDT,3/20320172,0,FATAL,28P0
> pg_hba.conf
> # "local" is for Unix domain socket connections only
> #local   all all peer
> local   all all md5
> # IPv4 local connections:
> #hostall all 127.0.0.1/32ident
> host all all 0.0.0.0/0 md5
> # IPv6 local connections:
> hostall all ::1/128 ident
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> #local   replication postgrespeer
> #hostreplication postgres127.0.0.1/32ident
> #hostreplication postgres::1/128 ident
> local  replication  csidba  md5
> host   replication  csidba  127.0.0.1/32  md5
> host   replication  csidba  0.0.0.0/0 md5
> local  replication  repuser  md5
> host   replication  repuser  127.0.0.1/32  md5
> host   replication  repuser  0.0.0.0/0 md5
> local  all repuser  md5
> host   all repuser  127.0.0.1/32  md5
> host   all repuser  0.0.0.0/0 md5
> Did I run in somthing similar to a racong condition ?
> Any ideas ?
 
 Is the process using the correct password?
 
> Many thanks
> — Armand
 
 
 -- 
 Adrian Klaver
 adrian.kla...@aklaver.com 
>> 
>> 
>> -- 
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
> 
> Yes I can that’s what is really puzzling me
> 
> [armandp@devweb2004 ~]$ /usr/pgsql-9.5/bin/psql -d birstdb -U csidba -h 
> 172.16.26.4
> psql (9.5.12, server 9.5.8)
> Type "help" for help.
> 
> birstdb=# 
> 
> It is almost like the authnetication stops working for whatever reason
> I did check the .pgpass and all that and nothing chaged
> 
> Is there a possibility like say 70 processes try to authenticate in the same 
> time and postgres authentication gets a bit lost ?
> 
> 
> Thank you
> 
> 

Sorry for the double posting but could it be from 

Re: authentication failure

[armand pirvu]

> On Apr 12, 2018, at 9:08 AM, Adrian Klaver  wrote:
> 
> On 04/12/2018 06:59 AM, armand pirvu wrote:
> 
> Please reply to list also.
> Ccing list.
>> Yes and worked fine until two days ago
>> I use .pgpass
> 
> So can you connect from wherever the process is run to the server manually? 
> Something like:
> 
> 
> psql -d birstab -U csidba -h some_server
> 
> 
>> I will also check and see if there are not too many hands in the cookie jar 
>> so to speak and things happen without being communicated
>>> On Apr 12, 2018, at 8:56 AM, Adrian Klaver  
>>> wrote:
>>> 
>>> On 04/12/2018 06:51 AM, armand pirvu wrote:
 Hi there
 I have a process in place which runs several queries from one host to 
 another one
 All of a sudden I started noticing authentication failures
 Like below
 .009 ms  statement: COPY  NACDS.tf_show_code_response_person FROM STDIN 
 with csv;","psql"
 2018-04-12 00:10:48.765 
 CDT,"csidba","birstdb",7553,"172.16.20.4:40330",5aceea2e.1d81,1,"UPDATE",2018-04-12
  00:10:06 CDT,24/0,0,LOG,0,"duration: 425
 90.993 ms  statement: UPDATE
 csischema.tf_transaction_person
 SET
 is_deleted = 'TRUE',
 birst_is_deleted = 'TRUE',
 update_datetime = now()::timestamp(0)
 WHERE
 show_id = '984BIOWC18' AND
 birst_is_deleted = 'FALSE' AND
 person_transaction_id IN (
 SELECT a.person_transaction_id
 FROM csischema.tf_transaction_person a
  LEFT JOIN BIOWC.tf_transaction_person b
  ON a.person_transaction_id=b.person_transaction_id
 WHERE a.show_id = '984BIOWC18' AND b.person_transaction_id IS NULL
 )
 ;","psql"
 2018-04-12 00:10:48.823 
 CDT,"csidba","birstdb",7755,"172.16.20.4:40455",5aceea58.1e4b,1,"authentication",2018-04-12
  00:10:48 CDT,3/20320168,0,FATAL,28P0
 1,"password authentication failed for user ""csidba""","Connection matched 
 pg_hba.conf line 84: ""host all all 0.0.0.0/0 md5"""""
 2018-04-12 00:10:48.841 
 CDT,"csidba","birstdb",7756,"172.16.20.4:40456",5aceea58.1e4c,1,"authentication",2018-04-12
  00:10:48 CDT,3/20320169,0,FATAL,28P0
 1,"password authentication failed for user ""csidba""","Connection matched 
 pg_hba.conf line 84: ""host all all 0.0.0.0/0 md5"""""
 2018-04-12 00:10:48.957 
 CDT,"csidba","birstdb",7759,"172.16.20.4:40459",5aceea58.1e4f,1,"authentication",2018-04-12
  00:10:48 CDT,3/20320172,0,FATAL,28P0
 pg_hba.conf
 # "local" is for Unix domain socket connections only
 #local   all all peer
 local   all all md5
 # IPv4 local connections:
 #hostall all 127.0.0.1/32ident
 host all all 0.0.0.0/0 md5
 # IPv6 local connections:
 hostall all ::1/128 ident
 # Allow replication connections from localhost, by a user with the
 # replication privilege.
 #local   replication postgrespeer
 #hostreplication postgres127.0.0.1/32ident
 #hostreplication postgres::1/128 ident
 local  replication  csidba  md5
 host   replication  csidba  127.0.0.1/32  md5
 host   replication  csidba  0.0.0.0/0 md5
 local  replication  repuser  md5
 host   replication  repuser  127.0.0.1/32  md5
 host   replication  repuser  0.0.0.0/0 md5
 local  all repuser  md5
 host   all repuser  127.0.0.1/32  md5
 host   all repuser  0.0.0.0/0 md5
 Did I run in somthing similar to a racong condition ?
 Any ideas ?
>>> 
>>> Is the process using the correct password?
>>> 
 Many thanks
 — Armand
>>> 
>>> 
>>> -- 
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 

Yes I can that’s what is really puzzling me

[armandp@devweb2004 ~]$ /usr/pgsql-9.5/bin/psql -d birstdb -U csidba -h 
172.16.26.4
psql (9.5.12, server 9.5.8)
Type "help" for help.

birstdb=# 

It is almost like the authnetication stops working for whatever reason
I did check the .pgpass and all that and nothing chaged

Is there a possibility like say 70 processes try to authenticate in the same 
time and postgres authentication gets a bit lost ?


Thank you

Re: authentication failure

[Adrian Klaver]

On 04/12/2018 06:59 AM, armand pirvu wrote:

Please reply to list also.
Ccing list.

Yes and worked fine until two days ago
I use .pgpass


So can you connect from wherever the process is run to the server 
manually? Something like:



psql -d birstab -U csidba -h some_server



I will also check and see if there are not too many hands in the cookie jar so 
to speak and things happen without being communicated



On Apr 12, 2018, at 8:56 AM, Adrian Klaver  wrote:

On 04/12/2018 06:51 AM, armand pirvu wrote:

Hi there
I have a process in place which runs several queries from one host to another 
one
All of a sudden I started noticing authentication failures
Like below
.009 ms  statement: COPY  NACDS.tf_show_code_response_person FROM STDIN with 
csv;","psql"
2018-04-12 00:10:48.765 
CDT,"csidba","birstdb",7553,"172.16.20.4:40330",5aceea2e.1d81,1,"UPDATE",2018-04-12 
00:10:06 CDT,24/0,0,LOG,0,"duration: 425
90.993 ms  statement: UPDATE
 csischema.tf_transaction_person
SET
 is_deleted = 'TRUE',
 birst_is_deleted = 'TRUE',
 update_datetime = now()::timestamp(0)
WHERE
 show_id = '984BIOWC18' AND
 birst_is_deleted = 'FALSE' AND
 person_transaction_id IN (
 SELECT a.person_transaction_id
 FROM csischema.tf_transaction_person a
  LEFT JOIN BIOWC.tf_transaction_person b
  ON a.person_transaction_id=b.person_transaction_id
 WHERE a.show_id = '984BIOWC18' AND b.person_transaction_id IS NULL
 )
;","psql"
2018-04-12 00:10:48.823 
CDT,"csidba","birstdb",7755,"172.16.20.4:40455",5aceea58.1e4b,1,"authentication",2018-04-12
 00:10:48 CDT,3/20320168,0,FATAL,28P0
1,"password authentication failed for user ""csidba""","Connection matched pg_hba.conf line 84: ""host 
all all 0.0.0.0/0 md5"""""
2018-04-12 00:10:48.841 
CDT,"csidba","birstdb",7756,"172.16.20.4:40456",5aceea58.1e4c,1,"authentication",2018-04-12
 00:10:48 CDT,3/20320169,0,FATAL,28P0
1,"password authentication failed for user ""csidba""","Connection matched pg_hba.conf line 84: ""host 
all all 0.0.0.0/0 md5"""""
2018-04-12 00:10:48.957 
CDT,"csidba","birstdb",7759,"172.16.20.4:40459",5aceea58.1e4f,1,"authentication",2018-04-12
 00:10:48 CDT,3/20320172,0,FATAL,28P0
pg_hba.conf
# "local" is for Unix domain socket connections only
#local   all all peer
local   all all md5
# IPv4 local connections:
#hostall all 127.0.0.1/32ident
host all all 0.0.0.0/0 md5
# IPv6 local connections:
hostall all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication postgrespeer
#hostreplication postgres127.0.0.1/32ident
#hostreplication postgres::1/128 ident
local  replication  csidba  md5
host   replication  csidba  127.0.0.1/32  md5
host   replication  csidba  0.0.0.0/0 md5
local  replication  repuser  md5
host   replication  repuser  127.0.0.1/32  md5
host   replication  repuser  0.0.0.0/0 md5
local  all repuser  md5
host   all repuser  127.0.0.1/32  md5
host   all repuser  0.0.0.0/0 md5
Did I run in somthing similar to a racong condition ?
Any ideas ?


Is the process using the correct password?


Many thanks
— Armand



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





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

Re: authentication failure

[Adrian Klaver]

On 04/12/2018 06:51 AM, armand pirvu wrote:

Hi there

I have a process in place which runs several queries from one host to another 
one


All of a sudden I started noticing authentication failures

Like below

.009 ms  statement: COPY  NACDS.tf_show_code_response_person FROM STDIN with 
csv;","psql"
2018-04-12 00:10:48.765 
CDT,"csidba","birstdb",7553,"172.16.20.4:40330",5aceea2e.1d81,1,"UPDATE",2018-04-12 
00:10:06 CDT,24/0,0,LOG,0,"duration: 425
90.993 ms  statement: UPDATE
 csischema.tf_transaction_person
SET
 is_deleted = 'TRUE',
 birst_is_deleted = 'TRUE',
 update_datetime = now()::timestamp(0)
WHERE
 show_id = '984BIOWC18' AND
 birst_is_deleted = 'FALSE' AND
 person_transaction_id IN (
 SELECT a.person_transaction_id
 FROM csischema.tf_transaction_person a
  LEFT JOIN BIOWC.tf_transaction_person b
  ON a.person_transaction_id=b.person_transaction_id
 WHERE a.show_id = '984BIOWC18' AND b.person_transaction_id IS NULL
 )
;","psql"
2018-04-12 00:10:48.823 
CDT,"csidba","birstdb",7755,"172.16.20.4:40455",5aceea58.1e4b,1,"authentication",2018-04-12
 00:10:48 CDT,3/20320168,0,FATAL,28P0
1,"password authentication failed for user ""csidba""","Connection matched pg_hba.conf line 84: ""host 
all all 0.0.0.0/0 md5"""""
2018-04-12 00:10:48.841 
CDT,"csidba","birstdb",7756,"172.16.20.4:40456",5aceea58.1e4c,1,"authentication",2018-04-12
 00:10:48 CDT,3/20320169,0,FATAL,28P0
1,"password authentication failed for user ""csidba""","Connection matched pg_hba.conf line 84: ""host 
all all 0.0.0.0/0 md5"""""
2018-04-12 00:10:48.957 
CDT,"csidba","birstdb",7759,"172.16.20.4:40459",5aceea58.1e4f,1,"authentication",2018-04-12
 00:10:48 CDT,3/20320172,0,FATAL,28P0


pg_hba.conf

# "local" is for Unix domain socket connections only
#local   all all peer
local   all all md5
# IPv4 local connections:
#hostall all 127.0.0.1/32ident
host all all 0.0.0.0/0 md5
# IPv6 local connections:
hostall all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication postgrespeer
#hostreplication postgres127.0.0.1/32ident
#hostreplication postgres::1/128 ident
local  replication  csidba  md5
host   replication  csidba  127.0.0.1/32  md5
host   replication  csidba  0.0.0.0/0 md5
local  replication  repuser  md5
host   replication  repuser  127.0.0.1/32  md5
host   replication  repuser  0.0.0.0/0 md5
local  all repuser  md5
host   all repuser  127.0.0.1/32  md5
host   all repuser  0.0.0.0/0 md5



Did I run in somthing similar to a racong condition ?


Any ideas ?


Is the process using the correct password?





Many thanks

— Armand








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

Re: Authentication?

[Bjørn T Johansen]

On Wed, 7 Mar 2018 10:19:35 -0500
Stephen Frost  wrote:

> Greetings,
> 
> * Bjørn T Johansen (b...@havleik.no) wrote:
> > Is it possible to use one authentication method as default, like LDAP, and 
> > if the user is not found, then try to authenticate using
> > md5/scram-sha-256 ?  
> 
> Not directly in pg_hba.conf.  You might be able to construct a system
> which works like this using PAM though, but it wouldn't be much fun.
> 
> LDAP use really should be discouraged as it involves sending the
> password to the PG server.  If you are operating in an active directory
> environment then you should be using GSSAPI/Kerberos.
> 
> SCRAM is a good alternative as it doesn't send the password to the
> server either, though that is only available in PG10, of course.
> 
> Thanks!
> 
> Stephen

Ok, thx... Will check out GSSAPI/Kerberos instead... :)

BTJ

Re: Authentication?

[Stephen Frost]

Greetings,

* Benedict Holland (benedict.m.holl...@gmail.com) wrote:
> Not to get off topic, can you authenticate database users via Kerberos?

Absolutely.  GSSAPI is the auth method to use for Kerberos.

Thanks!

Stephen

Re: Authentication?

[Benedict Holland]

Not to get off topic, can you authenticate database users via Kerberos?

Thanks,
~Ben

On Wed, Mar 7, 2018 at 10:19 AM, Stephen Frost  wrote:

> Greetings,
>
> * Bjørn T Johansen (b...@havleik.no) wrote:
> > Is it possible to use one authentication method as default, like LDAP,
> and if the user is not found, then try to authenticate using
> > md5/scram-sha-256 ?
>
> Not directly in pg_hba.conf.  You might be able to construct a system
> which works like this using PAM though, but it wouldn't be much fun.
>
> LDAP use really should be discouraged as it involves sending the
> password to the PG server.  If you are operating in an active directory
> environment then you should be using GSSAPI/Kerberos.
>
> SCRAM is a good alternative as it doesn't send the password to the
> server either, though that is only available in PG10, of course.
>
> Thanks!
>
> Stephen
>
>

Re: Authentication?

[Stephen Frost]

Greetings,

* Bjørn T Johansen (b...@havleik.no) wrote:
> Is it possible to use one authentication method as default, like LDAP, and if 
> the user is not found, then try to authenticate using
> md5/scram-sha-256 ?

Not directly in pg_hba.conf.  You might be able to construct a system
which works like this using PAM though, but it wouldn't be much fun.

LDAP use really should be discouraged as it involves sending the
password to the PG server.  If you are operating in an active directory
environment then you should be using GSSAPI/Kerberos.

SCRAM is a good alternative as it doesn't send the password to the
server either, though that is only available in PG10, of course.

Thanks!

Stephen

Re: Authentication?

[David G. Johnston]

On Wed, Mar 7, 2018 at 8:14 AM, Bjørn T Johansen  wrote:

> On Wed, 7 Mar 2018 07:14:55 -0700
> "David G. Johnston"  wrote:
>
> > On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen  wrote:
> >
> > > Hi.
> > >
> > > Is it possible to use one authentication method as default, like LDAP,
> and
> > > if the user is not found, then try to authenticate using
> > > md5/scram-sha-256 ?
> > >
> >
> > ​In the "Client Authentication" Chapter:​
> >
> > ​https://www.postgresql.org/docs/10/static/auth-pg-hba-conf.html​
> >
> > ​"""
> > ​The first record with a matching connection type, client address,
> > requested database, and user name is used to perform authentication.
> There
> > is no “fall-through” or “backup”: if one record is chosen and the
> > authentication fails, subsequent records are not considered. If no record
> > matches, access is denied.
> > """
> >
>
> I was hoping I had misunderstood but ok.. :)
>

​In the specific case you describe here you could have the server poll the
LDAP server periodically and cache the user names recognized and the
leverage:

"​Multiple user names can be supplied by separating them with commas. A
separate file containing user names can be specified by preceding the file
name with @."

In short, you have to pre-compute which method each user is allowed to
access externally then provide that knowledge to PostgreSQL.

David J.

Re: Authentication?

[Bjørn T Johansen]

On Wed, 7 Mar 2018 07:14:55 -0700
"David G. Johnston"  wrote:

> On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen  wrote:
> 
> > Hi.
> >
> > Is it possible to use one authentication method as default, like LDAP, and
> > if the user is not found, then try to authenticate using
> > md5/scram-sha-256 ?
> >  
> 
> ​In the "Client Authentication" Chapter:​
> 
> ​https://www.postgresql.org/docs/10/static/auth-pg-hba-conf.html​
> 
> ​"""
> ​The first record with a matching connection type, client address,
> requested database, and user name is used to perform authentication. There
> is no “fall-through” or “backup”: if one record is chosen and the
> authentication fails, subsequent records are not considered. If no record
> matches, access is denied.
> """
> 
> David J.

I was hoping I had misunderstood but ok.. :)

BTJ

Re: Authentication?

[David G. Johnston]

On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen  wrote:

> Hi.
>
> Is it possible to use one authentication method as default, like LDAP, and
> if the user is not found, then try to authenticate using
> md5/scram-sha-256 ?
>

​In the "Client Authentication" Chapter:​

​https://www.postgresql.org/docs/10/static/auth-pg-hba-conf.html​

​"""
​The first record with a matching connection type, client address,
requested database, and user name is used to perform authentication. There
is no “fall-through” or “backup”: if one record is chosen and the
authentication fails, subsequent records are not considered. If no record
matches, access is denied.
"""

David J.