ECCN for PostgreSQL
Hello, this is Panasonic software development department. Is there any ECCN restriction for PostgreSQL. Thank you. ** 楊 瑜萍（Yang YuPing） 蘇州松下生産科技有限公司（PFSS） R&Dセンター ソフト開発部 中国江蘇省蘇州工業園区唯亭鎮臨埠街1号 Panasonic Factory Solutions Suzhou Co.,Ltd (PFSS) R&D Center TEL：+86-(0)512-80991188-393 FAX：+86-(0)512-62957913 **
Re: PCI-DSS Requirements
On Thu, 2022-09-22 at 06:16 +, Inzamam Shafiq wrote: > From: Ron > > > Anyone on PCI-DSS requirements for PostgreSQL DB, need help for some of > > > the points. > > > > Can you be more specific? (Typically. the auditors or the "audit > > pre-check" team > > will ask for a bunch of details on how your instance is configured.) > > > > The usual questions I get are: > > - What password hash algorithm is used? > > - How frequently to passwords expire? > > - Is SSL used when communicating with applications? > > Actually we are in a starting phase and I have done instance level encryption > (CYBERTECH TDE Patch) but if someone take dump and restore it on another > server > the data get restored successfully. Also the problem is that the data is in > plain text. > > So I want to ask if disk or instance level encryption useful or we should > focus on > column level encryption? > > Also if any error occurred during DML and a plain query will be written into > the > logs which may not be compliant with PCI. How to overcome that? Yes, data-at-rest encryption is expensive, but of limited effectiveness. If you store sensitive information about credit cards and their owners, the best you can do is encrypt sensitive columns in the application, so that unencrypted data never touch the database. Make sure you don't encrypt everything, only the sensitive data, so that you can retain some degree of usability. A great deal will also rest on your database user management and authentication, and how well access to the system is logged and controlled. All that said, there is always a difference between good security and passing a certification exam... Yours, Laurenz Albe
Re: ECCN for PostgreSQL
On Thu, 2022-09-22 at 06:43 +, YangYuping(杨瑜萍) wrote: > Hello, this is Panasonic software development department. > Is there any ECCN restriction for PostgreSQL. You should consult and pay a qualified lawyer on that. That said, PostgreSQL is freely available to everybody, so that it is never exported at all. I don't see how it can be subject to any regulation on export. Yours, Laurenz Albe
Re: ECCN for PostgreSQL
There is no ECCN restriction, there was a thread talking about this, see [0] [0]: https://www.postgresql.org/message-id/17562.1329923596%40sss.pgh.pa.us On Thu, Sep 22, 2022 at 3:45 PM YangYuping(杨瑜萍) wrote: > > Hello, this is Panasonic software development department. > > Is there any ECCN restriction for PostgreSQL. > Thank you. > > > > ** > > 楊 瑜萍（Yang YuPing） > > 蘇州松下生産科技有限公司（PFSS） R&Dセンター ソフト開発部 > > 中国江蘇省蘇州工業園区唯亭鎮臨埠街1号 > > Panasonic Factory Solutions Suzhou Co.,Ltd (PFSS) R&D Center > > TEL：+86-(0)512-80991188-393 FAX：+86-(0)512-62957913 > > ** > > -- Regards Junwang Zhao
Re: [EXT] pg_stat_activity.backend_xmin
On Wed, 2022-09-21 at 16:22 +, Dirschel, Steve wrote: > > > > > We are troubleshooting an issue where autovacuum is not cleaning up a > > > > > table. > > > > > The application using this database runs with autocommit turned off. > > > > > We can see in pg_stat_activity lots of sessions “idle in transaction” > > > > > even though those sessions have not executed any DML- they have > > > > > executed selects but no DML. The database’s isolation level is set > > > > > to read committed. > > > > > > > > "backend_xmin" is set when the session has an active snapshot. Such a > > > > snapshot is held > > > > for the whole duration of a transaction in the REPEATABLE READ > > > > isolation level, but > > > > there are cases where you can see that in READ COMMITTED isolation > > > > level as well: > > > > > > > > - if there is a long running query > > > > > > > > - if there is a cursor open > > > > > > > > Perhaps you could ask your developers if they have long running > > > > read-only transactions with cursors. > > > > > > Thanks for the reply Laurenz. For an application session in this "state" > > > pg_stat_activity > > > shows the state of "idle in transaction" and backend_xmin is populated. > > > The query shows the > > > last select query it ran. It is not currently executing a query. And > > > dev has said they are > > > not using a cursor for the query. So it does not appear they have long > > > running read-only > > > transactions with cursors. That does not follow. You can execute: DECLARE c CURSOR FOR SELECT /* whatever */; FETCH 50 FROM c; SELECT /* something entirely different */ So you have an open cursor (portal), even though the last statement executed does not use a cursor at all. > > > Outside that scenario can you think of any others where a session: > > > 1. Login to the database > > > 2. Set autocommit off > > > 3. Run select query, query completes, session does nothing after that > > > query completes. > > > 4. transaction isolation level is read committed No. > > > That session sitting there idle in transaction has backend_xmin > > > populated. When I run that > > > test backend_xmin does not get populated unless I set my transaction > > > isolation level to > > > repeatable read. We have enabled statement logging so we can see if > > > their sessions are > > > changing that transaction isolation level behind the scenes that they are > > > not aware of > > > but so far we have not seen that type of command logged. > > > > What stack is the application using? Anything like Spring or Hibernate > > involved? > > Java is the stack. I'm not saying that you shouldn't trust your developers, but they may be using a cursor without being aware of it. If they use "setFetchSize()" to set a fetch size different from 0, they *are* using a cursor. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Patroni question
We had a failover.
I would read the Patroni logs below as following.
2022-09-21 11:13:56,384 secondary did a HTTP GET request to primary. This
failed with a read timeout.
2022-09-21 11:13:56,792 secondary promoted itself to primary
2022-09-21 11:13:57,279 primary did a HTTP GET request to secondary. An
exception happend. Probably also due to read timeout.
2022-09-21 11:13:57,983 primary demoted itself
So, the failover has been caused by a network timeout between primary and
secondary.
QUESTION 1 : Do you agree?
I thought that the Patroni nodes do not communicate directly with each other
but only by DCS?
QUESTION 2: Is this not correct anymore?
===
patroni version: 2.1.3
===
Patroni Logfile of Host szhm49346 (IP 10.9.132.13) => Primary until Failover
...
...
2022-09-21 11:13:57,279 DEBUG: API thread: 10.9.132.16 - - "GET /patroni
HTTP/1.1" 200 - latency: 2245.090 ms
2022-09-21 11:13:57,378 ERROR:
Traceback (most recent call last):
File "/usr/lib/python3.6/site-packages/patroni/dcs/etcd.py", line 566, in
wrapper
retval = func(self, *args, **kwargs) is not None
File "/usr/lib/python3.6/site-packages/patroni/dcs/etcd.py", line 696, in
_update_leader
return self.retry(self._client.write, self.leader_path, self._name,
prevValue=self._name, ttl=self._ttl)
File "/usr/lib/python3.6/site-packages/patroni/dcs/etcd.py", line 447, in
retry
return retry(*args, **kwargs)
File "/usr/lib/python3.6/site-packages/patroni/utils.py", line 334, in
__call__
return func(*args, **kwargs)
File "/usr/lib/python3.6/site-packages/etcd/client.py", line 500, in write
response = self.api_execute(path, method, params=params)
File "/usr/lib/python3.6/site-packages/patroni/dcs/etcd.py", line 257, in
api_execute
return self._handle_server_response(response)
File "/usr/lib/python3.6/site-packages/etcd/client.py", line 987, in
_handle_server_response
etcd.EtcdError.handle(r)
File "/usr/lib/python3.6/site-packages/etcd/__init__.py", line 306, in handle
raise exc(msg, payload)
etcd.EtcdCompareFailed: Compare failed : [pcl_p011@szhm49346 !=
pcl_p011@szhm49345]
2022-09-21 11:13:57,558 WARNING: Exception happened during processing of
request from 10.9.132.16:49080
2022-09-21 11:13:57,965 ERROR: failed to update leader lock
2022-09-21 11:13:57,983 INFO: Demoting self (immediate-nolock)
2022-09-21 11:13:58,214 WARNING: Traceback (most recent call last):
File "/usr/lib64/python3.6/socketserver.py", line 654, in
process_request_thread
self.finish_request(request, client_address)
File "/usr/lib64/python3.6/socketserver.py", line 364, in finish_request
self.RequestHandlerClass(request, client_address, self)
File "/usr/lib64/python3.6/socketserver.py", line 724, in __init__
self.handle()
File "/usr/lib64/python3.6/http/server.py", line 418, in handle
self.handle_one_request()
File "/usr/lib/python3.6/site-packages/patroni/api.py", line 652, in
handle_one_request
BaseHTTPRequestHandler.handle_one_request(self)
File "/usr/lib64/python3.6/http/server.py", line 406, in handle_one_request
method()
File "/usr/lib/python3.6/site-packages/patroni/api.py", line 198, in
do_GET_patroni
self._write_status_response(200, response)
File "/usr/lib/python3.6/site-packages/patroni/api.py", line 94, in
_write_status_response
self._write_json_response(status_code, response)
File "/usr/lib/python3.6/site-packages/patroni/api.py", line 53, in
_write_json_response
self._write_response(status_code, json.dumps(response, default=str),
content_type='application/json')
File "/usr/lib/python3.6/site-packages/patroni/api.py", line 50, in
_write_response
self.wfile.write(body.encode('utf-8'))
File "/usr/lib64/python3.6/socketserver.py", line 803, in write
self._sock.sendall(b)
BrokenPipeError: [Errno 32] Broken pipe
...
...
===
Patroni Logfile of Host szhm49345 (IP 10.9.132.16) => Standby until Failover
...
...
2022-09-21 11:13:54,381 DEBUG: Starting new HTTP connection (1):
szhm49346.global.szh.loc:8009
2022-09-21 11:13:56,384 WARNING: Request failed to pcl_p011@szhm49346: GET
http://szhm49346.global.szh.loc:8009/patroni
(HTTPConnectionPool(host='szhm49346.global.szh.loc', port=8009): Max retries
exceeded with url: /patroni (Caused by
ReadTimeoutError("HTTPConnectionPool(host='szhm49346.global.szh.loc',
port=8009): Read timed out. (read timeout=2)",)))
2022-09-21 11:13:56,484 DEBUG: Writing pcl_p011@szhm49345 to key
/patroni/pcl_p011/leader ttl=30 dir=False append=False
2022-09-21 11:13:56,485 DEBUG: Converted retries value: 0 -> Retry(total=0,
connect=None, read=None, redirect=0, status=None)
2022-09-21 11:13:56,562 DEBUG: http://10.7.211.13:2379 "PUT
/v2/keys/patroni/pcl_p011/leader HTTP/1.1" 201 197
2022-09-21 11:13:56,562 DEBUG: Issuing read for key /patroni/pcl_p011/ with
args {'recursive': True, 'retry': }
2022-09-21 11:13:56,563 DEBUG: Converted ret
Re: PCI-DSS Requirements
We use PgBackRest to create encrypted backups, but the nature of pg_dump means that the only way for them to be encrypted is to add that feature to pg_dump. On 9/22/22 01:16, Inzamam Shafiq wrote: Hi Ron, Thank you for the response. Actually we are in a starting phase and I have done instance level encryption (CYBERTECH TDE Patch) but if someone take dump and restore it on another server the data get restored successfully. Also the problem is that the data is in plain text. So I want to ask if disk or instance level encryption useful or we should focus on column level encryption? Also if any error occurred during DML and a plain query will be written into the logs which may not be compliant with PCI. How to overcome that? Thanks. Regards, /Inzamam Shafiq/ /Sr. DBA/ *From:* Ron *Sent:* Tuesday, September 20, 2022 10:44 PM *To:* pgsql-general@lists.postgresql.org *Subject:* Re: PCI-DSS Requirements On 9/20/22 04:27, Inzamam Shafiq wrote: Hi Team, Anyone on PCI-DSS requirements for PostgreSQL DB, need help for some of the points. Can you be more specific? (Typically. the auditors or the "audit pre-check" team will ask for a bunch of details on how your instance is configured.) The usual questions I get are: - What password hash algorithm is used? - How frequently to passwords expire? - Is SSL used when communicating with applications? -- Angular momentum makes the world go 'round. -- Angular momentum makes the world go 'round.
Database Horizon
Hi, I was reading through snapshot chapter in Egor Rogov's postgres internals and there I came across the below.. I am not sure how this is possible and how can I reproduce? Can someone explain the below 2 points if possible? A real transaction at the Read Committed isolation level holds the database horizon in the same way, even if it is not executing any operators (being in the “idle in trasaction” state). A virtual transaction at the Read Committed isolation level holds the horizon only while executing operators. Thanks, Goti
pg_dump failed with error code 255, but I don't see why
v9.6.2.23 I always run pg_dump with the --verbose option, and the bash script echos $?, so there's a 25,664 line log file for me to examine which explicitly shows the return code. But however much I search the log file for strings like ERROR, FATAL, WARN, INFO, and "archiver", I don't see any problems. What else should I look for? -- Angular momentum makes the world go 'round.
Re: pg_dump failed with error code 255, but I don't see why
Ron writes: > v9.6.2.23 > I always run pg_dump with the --verbose option, and the bash script echos > $?, so there's a 25,664 line log file for me to examine which explicitly > shows the return code. A quick search of the 9.6 pg_dump source code shows that it only ever calls exit() with values 0 or 1. 255 would have to be coming from some outside factor. You sure your shell script isn't misbehaving and corrupting the reported return code? regards, tom lane
Re: pg_dump failed with error code 255, but I don't see why
On 9/22/22 09:34, Tom Lane wrote: Ron writes: v9.6.2.23 I always run pg_dump with the --verbose option, and the bash script echos $?, so there's a 25,664 line log file for me to examine which explicitly shows the return code. A quick search of the 9.6 pg_dump source code shows that it only ever calls exit() with values 0 or 1. 255 would have to be coming from some outside factor. You sure your shell script isn't misbehaving and corrupting the reported return code? Good to know. The RC might be from ssh (the actual command being "ssh postgres@HOSTNAME pg_dump ...". -- Angular momentum makes the world go 'round.
Validate the internal consistency of pg_dump output?
In my case, it's directory format backups. (I'm not asking whether the pg_dump output is exactly the same as the data in the database; that can only be done inside of pg_dump, and I don't see any option for that anyway.) Is there a tool for validating that: - there are "\N" lines at the end of every .dat(.gz) file, - toc.dat isn't "missing" data at the end of the file, and that - there are .dat(.gz) files corresponding to all the tables mentioned in toc.dat? The use case would be in validating that pg_dump actually dumped all the data when there's a question of whether or not it completed. -- Angular momentum makes the world go 'round.
Re: Database Horizon
On Thu, 2022-09-22 at 19:30 +0530, Goti wrote: > I was reading through snapshot chapter in Egor Rogov's postgres internals and > there I > came across the below.. I am not sure how this is possible and how can I > reproduce? > Can someone explain the below 2 points if possible? > > A real transaction at the Read Committed isolation level holds the database > horizon > in the same way, even if it is not executing any operators (being in the > “idle in trasaction” state). > > A virtual transaction at the Read Committed isolation level holds the horizon > only while > executing operators. A transaction that changed something (this is what is meant by a "real transaction") has a transaction ID. VACUUM will not clean up tuples that have been invalidated after the start of such a transaction, if the transaction is still active. The transaction ID sets the "xmin horizon" in such a case. For a reading transaction, it is the xmin horizon of the current snapshot that holds back VACUUM. For a READ COMMITTED transaction, there is only a snapshot for running statements and open cursors. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: pg_dump failed with error code 255, but I don't see why
Ron writes: > On 9/22/22 09:34, Tom Lane wrote: > >> Ron writes: >>> v9.6.2.23 >>> I always run pg_dump with the --verbose option, and the bash script echos >>> $?, so there's a 25,664 line log file for me to examine which explicitly >>> shows the return code. >> A quick search of the 9.6 pg_dump source code shows that it only >> ever calls exit() with values 0 or 1. 255 would have to be coming >> from some outside factor. You sure your shell script isn't >> misbehaving and corrupting the reported return code? > > Good to know. The RC might be from ssh (the actual command being "ssh > postgres@HOSTNAME pg_dump ...". FWIW, here's what my ssh man page says, and a contrived example. So it seems your ssh call itself borked for some reason perhaps. EXIT STATUS ssh exits with the exit status of the remote command or with 255 if an error occurred. tmp$ ssh localhost false; echo $? 1 tmp$ ssh no-such-host true; echo $? ssh: Could not resolve hostname no-such-host: nodename nor servname provided, or not known 255 tmp$
