Re: Redacting params in PostgreSQL logs

[Vijaykumar Jain]

FYI

This is what I had tried wrt logstash.

https://gist.github.com/cabecada/dd765a30f6946fdbf0bec0eb31fba047


From: Vijaykumar Jain 
Date: Friday, March 30, 2018 at 11:33 PM
To: "pgsql-general@lists.postgresql.org" 
Subject: Re: Redacting params in PostgreSQL logs

https://github.com/lfittl/pg_query/blob/master/README.md#parsing-a-normalized-query

OK found this :)


Regards,
Vijay

From: Vijaykumar Jain
Sent: Friday, March 30, 2018 11:24:30 PM
To: pgsql-general@lists.postgresql.org
Subject: Redacting params in PostgreSQL logs

Hi,

I have a project of sending postgres logs to ELK stack. It is working fine.

Now there are concerns that logs have query statements with parameters.
and then GDPR and other PII issues.

Is there a way I can redact the params in statements in the logs by some config 
or extension ?
Pls note: I want the statements in logs but not the values :)

like insert into foo values (‘xxx’) etc



Regards,
Vijay

Problem with connection to host (wrong host)

[Mike Martin]

Hi
I am just setting up a postgres server, details
Host 192.168.0.3
pg_hba.conf
# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all peer
# IPv4 local connections:
hostall all 192.168.0.0/32ident
# IPv6 local connections:
hostall all 127.0.0.1/32ident

hostall all ::1/128 ident

postgresql.conf
listen_addresses-'*'

however when I try to connect from my laptop (ip 192.168.0.2) I get

psql -h 192.168.0.3 -U usevideo -W
Password for user usevideo:
psql: FATAL:  no pg_hba.conf entry for host "192.168.0.2", user "usevideo",
database "usevideo", SSL off

So a bit confused, is psql ignoring the host parameter

thanks

Re: Problem with connection to host (wrong host)

[Peter Eisentraut]

On 3/31/18 11:44, Mike Martin wrote:
> Hi
> I am just setting up a postgres server, details
> Host 192.168.0.3
> pg_hba.conf
> # TYPE  DATABASE    USER    ADDRESS METHOD
> 
> # "local" is for Unix domain socket connections only
> local   all all peer
> # IPv4 local connections:
> host    all all 192.168.0.0/32
>     ident

This entry should probably be something like

192.168.0.0/24

Writing .0/32 doesn't make much sense.

> # IPv6 local connections:
> host    all all 127.0.0.1/32
>     ident
> 
> host    all all ::1/128 ident
> 
> postgresql.conf
> listen_addresses-'*'
> 
> however when I try to connect from my laptop (ip 192.168.0.2) I get
> 
> psql -h 192.168.0.3 -U usevideo -W
> Password for user usevideo:
> psql: FATAL:  no pg_hba.conf entry for host "192.168.0.2", user
> "usevideo", database "usevideo", SSL off
> 
> So a bit confused, is psql ignoring the host parameter

.3 is the host you are connecting to, as seen from the client.

.2 is the host your connection is coming from, as seen from the server.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Problem with connection to host (wrong host)

[Melvin Davidson]

On Sat, Mar 31, 2018 at 11:44 AM, Mike Martin  wrote:

> Hi
> I am just setting up a postgres server, details
> Host 192.168.0.3
> pg_hba.conf
> # TYPE  DATABASEUSERADDRESS METHOD
>
> # "local" is for Unix domain socket connections only
> local   all all peer
> # IPv4 local connections:
> hostall all 192.168.0.0/32ident
> # IPv6 local connections:
> hostall all 127.0.0.1/32ident
>
> hostall all ::1/128 ident
>
> postgresql.conf
> listen_addresses-'*'
>
> however when I try to connect from my laptop (ip 192.168.0.2) I get
>
> psql -h 192.168.0.3 -U usevideo -W
> Password for user usevideo:
> psql: FATAL:  no pg_hba.conf entry for host "192.168.0.2", user
> "usevideo", database "usevideo", SSL off
>
> So a bit confused, is psql ignoring the host parameter
>
> thanks
>
>
>




*>So a bit confused, is psql ignoring the host parameter It is not ignoring
your entry, you simply have not entry for the laptop you are connecting
from,So simply add the following:*
*hostall all 192.168.0.2/32
ident *   <- note, you may have to change the authentication
method depending on your environment



-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

is pg_advisory_lock() suitable for long runs

[Radoslav Nedyalkov]

Hi all,
it's very simple and intuitive case but let me describe first.
1. session 1 calls pg_advisory_lock(1234) and succeeds.
2. session 2 calls pg_advisory_lock(1234) and stops on waiting.
All fine BUT pid for session2 appears already with backend_xmin in
pg_stat_activity
which means vacuum won't be able to remove rows younger than session2
backend_xmin.

Well, we planned to use pg_advisory_lock() as a boot phase in a hot-standby
appserver
and apparently this will be problematic as the session2 might wait for
weeks.

Any thoughts ? Do we miss something ?

Thanks and Regards
Radoslav Nedyalkov

unreliable behaviour of track_functions

[pinker]

Hi All!

I've been experimenting with track_functions options and what I've saw it's
really puzzling me.
Documentation says: 
/ SQL-language functions that are simple enough to be "inlined" into the
calling query will not be tracked, regardless of this setting./

But it came up, it depends on much more factors, like duration or placing it
in the query, it is totally non-deterministic behaviour.

This really simple SQL function:
CREATE FUNCTION a(a bigint)
  RETURNS bigint
STABLE
LANGUAGE SQL
AS $$
SELECT $1
$$;

Is not shown in the pg_stat_user_functions at all. It is started to be shown
when one line:
select pg_sleep(1);

is added???

Another one, gets tracked only if I use:
SELECT get_unique_term(2556);

If it lands in FROM then it's not tracked...
SELECT * FROM get_unique_term(2556);

That's the body of the function:
CREATE FUNCTION get_unique_term(i_game_pin bigint)
  RETURNS TABLE(term text, category text)
STABLE
LANGUAGE SQL
AS $$
SELECT
  i.term,
  i.dict_category_id
FROM (SELECT
categories.term,
categories.dict_category_id
  FROM categories
  EXCEPT ALL
  SELECT
games.term,
games.category
  FROM games
  WHERE game_pin = $1) i
ORDER BY (random())
LIMIT 1;
$$;

What's going on here? That's pretty unreliable behaviour...


My version of postgres:
 PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
20140911 (Red Hat 4.8.3-9), 64-bit

show track_functions;
 track_functions 
-
 all
(1 wiersz)



 




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: unreliable behaviour of track_functions

[Adrian Klaver]

On 03/31/2018 04:40 PM, pinker wrote:

Hi All!

I've been experimenting with track_functions options and what I've saw it's
really puzzling me.
Documentation says:
/ SQL-language functions that are simple enough to be "inlined" into the
calling query will not be tracked, regardless of this setting./

But it came up, it depends on much more factors, like duration or placing it
in the query, it is totally non-deterministic behaviour.

This really simple SQL function:
CREATE FUNCTION a(a bigint)
   RETURNS bigint
STABLE
LANGUAGE SQL
AS $$
SELECT $1
$$;

Is not shown in the pg_stat_user_functions at all. It is started to be shown
when one line:
select pg_sleep(1);

is added???

Another one, gets tracked only if I use:
SELECT get_unique_term(2556);

If it lands in FROM then it's not tracked...
SELECT * FROM get_unique_term(2556);

That's the body of the function:
CREATE FUNCTION get_unique_term(i_game_pin bigint)
   RETURNS TABLE(term text, category text)
STABLE
LANGUAGE SQL
AS $$
SELECT
   i.term,
   i.dict_category_id
FROM (SELECT
 categories.term,
 categories.dict_category_id
   FROM categories
   EXCEPT ALL
   SELECT
 games.term,
 games.category
   FROM games
   WHERE game_pin = $1) i
ORDER BY (random())
LIMIT 1;
$$;

What's going on here? That's pretty unreliable behaviour...


?:

https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-USER-FUNCTIONS-VIEW

"...But if you want to see new results with each query, be sure to do 
the queries outside any transaction block. Alternatively, you can invoke 
pg_stat_clear_snapshot(), which will discard the current transaction's 
statistics snapshot (if any). The next use of statistical information 
will cause a new snapshot to be fetched.


A transaction can also see its own statistics (as yet untransmitted to 
the collector) in the views pg_stat_xact_all_tables, 
pg_stat_xact_sys_tables, pg_stat_xact_user_tables, and 
pg_stat_xact_user_functions. These numbers do not act as stated above; 
instead they update continuously throughout the transaction.


"



My version of postgres:
  PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
20140911 (Red Hat 4.8.3-9), 64-bit

show track_functions;
  track_functions
-
  all
(1 wiersz)



  





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





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

Re: unreliable behaviour of track_functions

[pinker]

Adrian Klaver-4 wrote
> https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-USER-FUNCTIONS-VIEW
> 
> "...But if you want to see new results with each query, be sure to do 
> the queries outside any transaction block. Alternatively, you can invoke 
> pg_stat_clear_snapshot(), which will discard the current transaction's 
> statistics snapshot (if any). The next use of statistical information 
> will cause a new snapshot to be fetched.
> 
> A transaction can also see its own statistics (as yet untransmitted to 
> the collector) in the views pg_stat_xact_all_tables, 
> pg_stat_xact_sys_tables, pg_stat_xact_user_tables, and 
> pg_stat_xact_user_functions. These numbers do not act as stated above; 
> instead they update continuously throughout the transaction.
> 
> "
> Adrian Klaver

> adrian.klaver@


Thank you for that, but does this strange behaviour should not be better
documented? 
If somebody (like me, I have some time ago being unaware of this behaviour)
wants to build a monitoring system that base on the view
pg_stat_user_functions, should not be informed what to expect?




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: unreliable behaviour of track_functions

[Adrian Klaver]

On 03/31/2018 05:17 PM, pinker wrote:

Adrian Klaver-4 wrote

https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-USER-FUNCTIONS-VIEW

"...But if you want to see new results with each query, be sure to do
the queries outside any transaction block. Alternatively, you can invoke
pg_stat_clear_snapshot(), which will discard the current transaction's
statistics snapshot (if any). The next use of statistical information
will cause a new snapshot to be fetched.

A transaction can also see its own statistics (as yet untransmitted to
the collector) in the views pg_stat_xact_all_tables,
pg_stat_xact_sys_tables, pg_stat_xact_user_tables, and
pg_stat_xact_user_functions. These numbers do not act as stated above;
instead they update continuously throughout the transaction.

"
Adrian Klaver



adrian.klaver@



Thank you for that, but does this strange behaviour should not be better
documented?


This came from the documentation, so I am not sure what you mean by 
better documented?



If somebody (like me, I have some time ago being unaware of this behaviour)
wants to build a monitoring system that base on the view
pg_stat_user_functions, should not be informed what to expect?


See comment above.






--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





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

Please suggest the best suited unit test frame work for postgresql database.

[Raghavendra Rao J S V]

Good morning.

Please suggest the best suited unit test frame work for postgresql database
and also shared the related documents to understand the framework.

-- 
Regards,
Raghavendra Rao J S V