public inbox for [email protected]
help / color / mirror / Atom feedCreate temp table query hangs
9+ messages / 8 participants
[nested] [flat]
* Create temp table query hangs
@ 2021-04-13 18:26 Novak Ivan <[email protected]>
2021-04-13 19:18 ` Re: Create temp table query hangs Ron <[email protected]>
2021-04-13 20:02 ` Re: Create temp table query hangs Tom Lane <[email protected]>
2024-05-02 22:43 ` postgresql in docker to improve security Nguyen, Long (IM&T, St. Lucia) <[email protected]>
0 siblings, 3 replies; 9+ messages in thread
From: Novak Ivan @ 2021-04-13 18:26 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: Kümin Antonio <[email protected]>
Greetings,
We’ve a hanging query which creates a temp table.
Some context:
* The application transforms from one data model to another one (to a canonical datamodel). The source tables for these transformations are on one schema of the postgresDB (we call it LandingZone) and the target tables of these transformations are in a different schema of the exact same postgresDB (we call it the CDM schema).
Our problem in summary:
* a query for creating temporary table on target schema is hanging forever and we are out of ideas why.
The query is not generated by our own "mapper" tool that we wrote. We don't use any third party product here (except JOOQ library for communicating to DB)
When we try to create a (temporary) table with one statement, and that query takes forever (even after >24h the query is still there). In the Postgres Database we see three identical active PIDs for the same query (with same starttimestamp). But we only sent the statement once to the DB. See screenshot below.
The query hangs when creating of temporary tables (see below) and also creating normal tables.
The query hangs using our go-to library for Database queries (JOOQ) and also when we do it manually (we rewrote the implementation using java JDBC) -> so it is not dependent on that library
The query hangs also after updating the default JDBC drivers to newest version (42.2.19)
The query does NOT hang when running on local development environment (not containerized) -> we see 3 PIDs in database (which is strange), but the query finishes after some seconds
The query does NOT hang when running directly on the database using DBeaver, i.e. not using java -> we see 3 PIDs in database (which is strange), but the query finishes after some seconds
This doesn't happen every time we create a table. For source tables that have no new data (or are empty) the query completes. Otherwise it doesn’t seem to be volume dependent (small vs large vs huge volume)
It does consistently happen when creating temp table selecting on specific tables that have some volume.
In all environments (ours or clients) the Postgres DB are on own VM and not containerized.
On local dev environment (noncontainerized) the query does not hang. It only hangs when using a container environment (local docker or openshift)
The process of a delta load is as follows (for each source/target table combination):
1) get max "lastupdate" timestamp from target table
2) delete data on target that has been replaced (i.e. data that was marked for deletion since "lastupdate")
3) create temporary_table with new data from the source table (new means greater than "lastupdate"; this is defined in the subselect)
4) insert into target table as select * from temporary_table
The query on step3 is the one that hangs
Content of the query:
The "create table as select from" has a small to large resultset (can be 0 or millions of rows). The select statement has 2 joins with the same table (a code table to resolve some code values for later WHERE clauses) and an additional condition in the WHERE clause with a subselect inside (the subselect selects from the same table as the parent select. It checks if this record is in a list of "to be updated" records. i.e. for a "delta" load).
Postgres version: PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
Postgres JDBC driver version: 42.2.19
Java version: 11.0.9.1
* All those parameters are the same whether we run the java code containerized or not containerized.
Attachments:
* The query that is hanging
* Excerpt of the sessions showing the 3 PIDs (see screenshot below as well)
Any help is appreciated,
Ivan
[cid:[email protected]]
pid | client_addr | state | wait_event_type | backend_start | xact_start | state_change | query
--------+-------------+--------+-----------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
30 | | | Activity | 2021-04-01 13:14:17.262872+02 | | |
32 | | | Activity | 2021-04-01 13:14:17.263749+02 | | |
373883 | | | | 2021-04-07 14:04:37.551133+02 | 2021-04-07 08:49:40.152171+02 | |
373884 | | | | 2021-04-07 14:04:37.551576+02 | 2021-04-07 08:49:40.152171+02 | |
28 | | | Activity | 2021-04-01 13:14:17.262323+02 | | |
27 | | | Activity | 2021-04-01 13:14:17.262752+02 | | |
29 | | | Activity | 2021-04-01 13:14:17.262753+02 | | |
224406 | 10.37.1.216 | idle | Client | 2021-04-06 16:31:56.921563+02 | | 2021-04-07 14:04:37.37835+02 |
373309 | 10.37.2.66 | idle | Client | 2021-04-07 14:03:23.608589+02 | | 2021-04-07 14:03:23.613093+02 | SET application_name = 'PostgreSQL JDBC Driver'
40 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:18.015224+02 | | 2021-04-07 14:02:37.628649+02 | COMMIT
372004 | 10.37.2.66 | idle | Client | 2021-04-07 14:00:35.253338+02 | | 2021-04-07 14:00:35.257102+02 | SET application_name = 'PostgreSQL JDBC Driver'
371383 | 10.37.2.66 | idle | Client | 2021-04-07 13:59:15.547588+02 | | 2021-04-07 13:59:15.551386+02 | SET application_name = 'PostgreSQL JDBC Driver'
367152 | 10.37.2.66 | idle | Client | 2021-04-07 13:50:11.790021+02 | | 2021-04-07 13:50:11.794277+02 | SET application_name = 'PostgreSQL JDBC Driver'
365608 | 10.37.2.66 | idle | Client | 2021-04-07 13:46:53.135327+02 | | 2021-04-07 13:46:53.140432+02 | SET application_name = 'PostgreSQL JDBC Driver'
365280 | 10.37.2.66 | idle | Client | 2021-04-07 13:46:11.134051+02 | | 2021-04-07 13:46:11.140538+02 | SET application_name = 'PostgreSQL JDBC Driver'
365259 | 10.37.2.66 | idle | Client | 2021-04-07 13:46:08.697428+02 | | 2021-04-07 13:46:08.701173+02 | SET application_name = 'PostgreSQL JDBC Driver'
364928 | 10.37.2.66 | idle | Client | 2021-04-07 13:45:26.021603+02 | | 2021-04-07 13:45:26.026252+02 | SET application_name = 'PostgreSQL JDBC Driver'
364841 | 10.37.2.66 | idle | Client | 2021-04-07 13:45:15.036377+02 | | 2021-04-07 13:45:15.045453+02 | SET application_name = 'PostgreSQL JDBC Driver'
225902 | | active | IPC | 2021-04-07 08:49:40.197802+02 | 2021-04-07 08:49:40.152171+02 | 2021-04-07 08:49:40.238357+02 | create temporary table "adr_select_45115964_cb0c_4d6d_b782_2dce92595df2" as select t01_7.ZUSATZ4 as "a_0", t01_7.ZUSATZ0 as "a_1", t01_7.ZUSATZ5 as "a_2", t01_7.ZUSATZ1 as "a_3", t01_7.ZUSATZ2 as "a_4", t01_7.ZUSATZ3 as "a_5", case when t01_7.ADRESSORDER is not null then '-5280/' || t01_7.ADRESSORDER else null end as "a_6", case when t01_7.KANTON is not null then '-250/' || t01_7.KANTON else null end as "a_7", case when t01_7.GNRKANTON is not null then '-250/' || t01_7.GNRKANTON else null end as "a_8", t01_7.GEMEINDENAME as "a_9", t01_7.BFSNR as "a_10", t01_7.ITSCONTACTPOINTDEF as "a_11", t01_7.LAND as "a_12", t01_7.CREATED as "a_13", t01_7.CREATEDUSER as "a_14", t01_7.EXTCONTACTPOINTID as "a_15", case when t01_7.EXTQUELLE is not null then '-51131/' || t01_7.EXTQUELLE else null end as "a_16", t01_7.HAUSNUMMER as "a_17", t01_7.HAUSNUMMER_ZUSATZ as "a_18", t01_7.BOID as "a_19", case when t01_7.ISPOSTFACHOHNENR is not null then '-1580/' || t01_7.ISPOSTFACHOHNENR else null end as "a_20", t01_7.LASTUPDATE as "a_21", t01_7.METABO as "a_22", t01_7.DATAAPIFROM as "a_23", t01_7.DATAAPIUPTO as "a_24", t01_7.PROCESSID as "a_25", t01_7.ITSCPMUGRUND as "a_26", t01_7.ITSADRPARENTADRESS as "a_27", t01_7.ITSPARTNER as "a_28", t01_7.POSTFACH as "a_29", t01_7.STATEFROM as "a_30", t01_7.STATEUPTO as "a_31", t01_7.PLZ as "a_32", t01_7.PLZ_ZUSATZ as "a_33", t01_7.BEMERKUNGEN as "a_34", t01_7.REPLACED as "a_35", t01_7.REPLACEDUSER as "a_36", t01_7.STATEBEGIN as "a_37", t01_7.STATEEND as "a_38", t01_7.STATEFROM as "a_39", t01_7.STATEUPTO as "a_40", t01_7.STRASSE as "a_41", t01_7.ORT as "a_42", case when t01_7.ADRESSETYP is not null then '-6240/' || t01_7.ADRESSETYP else null end as "a_43", case when t01_7.REPLACED < to_date('3000-01-01', 'YYYY-MM-DD') then 'D' when t01_7.GUELTAB = t01_7.GUELTBIS then 'D' else 'I' end as "a_44", t01_7.GUELTAB as "a_45", t01_7.GUELTBIS as "a_46" from "dataapi_lz"."adresse" as "t01_7" join "dataapi_lz"."code" as "t02_14" on ((t01_7.ADRESSETYP = t02_14.BOID) and (t02_14."replaced" = timestamp '3000-01-01 00:00:00.0')) join "dataapi_lz"."code" as "t03_14" on ((t01_7.LIFECYCLE = t03_14.BOID) and (t03_14."replaced" = timestamp '3000-01-01 00:00:00.0')) where ((t02_14.INTERNALNAME IN ('AdresseTyp_Domizil','AdresseTyp_Zusatz')) and (t03_14.INTERNALNAME = 'SYR_Code_Active') and (t01_7."replaced" = timestamp '3000-01-01 00:00:00.0') and t01_7."boid" in (select "dataapi_lz"."adresse"."boid" from "dataapi_lz"."adresse" where (1 = 1 and "dataapi_lz"."adresse"."replaced" = timestamp '3000-01-01 00:00:00.0' and "dataapi_lz"."adresse"."dataapifrom" > timestamp '2021-03-31 09:42:27.0')))
225901 | | active | IPC | 2021-04-07 08:49:40.197379+02 | 2021-04-07 08:49:40.152171+02 | 2021-04-07 08:49:40.243261+02 | create temporary table "adr_select_45115964_cb0c_4d6d_b782_2dce92595df2" as select t01_7.ZUSATZ4 as "a_0", t01_7.ZUSATZ0 as "a_1", t01_7.ZUSATZ5 as "a_2", t01_7.ZUSATZ1 as "a_3", t01_7.ZUSATZ2 as "a_4", t01_7.ZUSATZ3 as "a_5", case when t01_7.ADRESSORDER is not null then '-5280/' || t01_7.ADRESSORDER else null end as "a_6", case when t01_7.KANTON is not null then '-250/' || t01_7.KANTON else null end as "a_7", case when t01_7.GNRKANTON is not null then '-250/' || t01_7.GNRKANTON else null end as "a_8", t01_7.GEMEINDENAME as "a_9", t01_7.BFSNR as "a_10", t01_7.ITSCONTACTPOINTDEF as "a_11", t01_7.LAND as "a_12", t01_7.CREATED as "a_13", t01_7.CREATEDUSER as "a_14", t01_7.EXTCONTACTPOINTID as "a_15", case when t01_7.EXTQUELLE is not null then '-51131/' || t01_7.EXTQUELLE else null end as "a_16", t01_7.HAUSNUMMER as "a_17", t01_7.HAUSNUMMER_ZUSATZ as "a_18", t01_7.BOID as "a_19", case when t01_7.ISPOSTFACHOHNENR is not null then '-1580/' || t01_7.ISPOSTFACHOHNENR else null end as "a_20", t01_7.LASTUPDATE as "a_21", t01_7.METABO as "a_22", t01_7.DATAAPIFROM as "a_23", t01_7.DATAAPIUPTO as "a_24", t01_7.PROCESSID as "a_25", t01_7.ITSCPMUGRUND as "a_26", t01_7.ITSADRPARENTADRESS as "a_27", t01_7.ITSPARTNER as "a_28", t01_7.POSTFACH as "a_29", t01_7.STATEFROM as "a_30", t01_7.STATEUPTO as "a_31", t01_7.PLZ as "a_32", t01_7.PLZ_ZUSATZ as "a_33", t01_7.BEMERKUNGEN as "a_34", t01_7.REPLACED as "a_35", t01_7.REPLACEDUSER as "a_36", t01_7.STATEBEGIN as "a_37", t01_7.STATEEND as "a_38", t01_7.STATEFROM as "a_39", t01_7.STATEUPTO as "a_40", t01_7.STRASSE as "a_41", t01_7.ORT as "a_42", case when t01_7.ADRESSETYP is not null then '-6240/' || t01_7.ADRESSETYP else null end as "a_43", case when t01_7.REPLACED < to_date('3000-01-01', 'YYYY-MM-DD') then 'D' when t01_7.GUELTAB = t01_7.GUELTBIS then 'D' else 'I' end as "a_44", t01_7.GUELTAB as "a_45", t01_7.GUELTBIS as "a_46" from "dataapi_lz"."adresse" as "t01_7" join "dataapi_lz"."code" as "t02_14" on ((t01_7.ADRESSETYP = t02_14.BOID) and (t02_14."replaced" = timestamp '3000-01-01 00:00:00.0')) join "dataapi_lz"."code" as "t03_14" on ((t01_7.LIFECYCLE = t03_14.BOID) and (t03_14."replaced" = timestamp '3000-01-01 00:00:00.0')) where ((t02_14.INTERNALNAME IN ('AdresseTyp_Domizil','AdresseTyp_Zusatz')) and (t03_14.INTERNALNAME = 'SYR_Code_Active') and (t01_7."replaced" = timestamp '3000-01-01 00:00:00.0') and t01_7."boid" in (select "dataapi_lz"."adresse"."boid" from "dataapi_lz"."adresse" where (1 = 1 and "dataapi_lz"."adresse"."replaced" = timestamp '3000-01-01 00:00:00.0' and "dataapi_lz"."adresse"."dataapifrom" > timestamp '2021-03-31 09:42:27.0')))
225598 | 10.37.2.66 | active | IO | 2021-04-07 08:20:29.59069+02 | 2021-04-07 08:49:40.152171+02 | 2021-04-07 08:49:40.15374+02 | create temporary table "adr_select_45115964_cb0c_4d6d_b782_2dce92595df2" as select t01_7.ZUSATZ4 as "a_0", t01_7.ZUSATZ0 as "a_1", t01_7.ZUSATZ5 as "a_2", t01_7.ZUSATZ1 as "a_3", t01_7.ZUSATZ2 as "a_4", t01_7.ZUSATZ3 as "a_5", case when t01_7.ADRESSORDER is not null then '-5280/' || t01_7.ADRESSORDER else null end as "a_6", case when t01_7.KANTON is not null then '-250/' || t01_7.KANTON else null end as "a_7", case when t01_7.GNRKANTON is not null then '-250/' || t01_7.GNRKANTON else null end as "a_8", t01_7.GEMEINDENAME as "a_9", t01_7.BFSNR as "a_10", t01_7.ITSCONTACTPOINTDEF as "a_11", t01_7.LAND as "a_12", t01_7.CREATED as "a_13", t01_7.CREATEDUSER as "a_14", t01_7.EXTCONTACTPOINTID as "a_15", case when t01_7.EXTQUELLE is not null then '-51131/' || t01_7.EXTQUELLE else null end as "a_16", t01_7.HAUSNUMMER as "a_17", t01_7.HAUSNUMMER_ZUSATZ as "a_18", t01_7.BOID as "a_19", case when t01_7.ISPOSTFACHOHNENR is not null then '-1580/' || t01_7.ISPOSTFACHOHNENR else null end as "a_20", t01_7.LASTUPDATE as "a_21", t01_7.METABO as "a_22", t01_7.DATAAPIFROM as "a_23", t01_7.DATAAPIUPTO as "a_24", t01_7.PROCESSID as "a_25", t01_7.ITSCPMUGRUND as "a_26", t01_7.ITSADRPARENTADRESS as "a_27", t01_7.ITSPARTNER as "a_28", t01_7.POSTFACH as "a_29", t01_7.STATEFROM as "a_30", t01_7.STATEUPTO as "a_31", t01_7.PLZ as "a_32", t01_7.PLZ_ZUSATZ as "a_33", t01_7.BEMERKUNGEN as "a_34", t01_7.REPLACED as "a_35", t01_7.REPLACEDUSER as "a_36", t01_7.STATEBEGIN as "a_37", t01_7.STATEEND as "a_38", t01_7.STATEFROM as "a_39", t01_7.STATEUPTO as "a_40", t01_7.STRASSE as "a_41", t01_7.ORT as "a_42", case when t01_7.ADRESSETYP is not null then '-6240/' || t01_7.ADRESSETYP else null end as "a_43", case when t01_7.REPLACED < to_date('3000-01-01', 'YYYY-MM-DD') then 'D' when t01_7.GUELTAB = t01_7.GUELTBIS then 'D' else 'I' end as "a_44", t01_7.GUELTAB as "a_45", t01_7.GUELTBIS as "a_46" from "dataapi_lz"."adresse" as "t01_7" join "dataapi_lz"."code" as "t02_14" on ((t01_7.ADRESSETYP = t02_14.BOID) and (t02_14."replaced" = timestamp '3000-01-01 00:00:00.0')) join "dataapi_lz"."code" as "t03_14" on ((t01_7.LIFECYCLE = t03_14.BOID) and (t03_14."replaced" = timestamp '3000-01-01 00:00:00.0')) where ((t02_14.INTERNALNAME IN ('AdresseTyp_Domizil','AdresseTyp_Zusatz')) and (t03_14.INTERNALNAME = 'SYR_Code_Active') and (t01_7."replaced" = timestamp '3000-01-01 00:00:00.0') and t01_7."boid" in (select "dataapi_lz"."adresse"."boid" from "dataapi_lz"."adresse" where (1 = 1 and "dataapi_lz"."adresse"."replaced" = timestamp '3000-01-01 00:00:00.0' and "dataapi_lz"."adresse"."dataapifrom" > timestamp '2021-03-31 09:42:27.0')))
41 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:18.036269+02 | | 2021-04-06 17:14:49.372727+02 | select ta.attname, ia.attnum, ic.relname, n.nspname, NULL from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class ic, pg_catalog.pg_index i, pg_catalog.pg_namespace n where ic.relname = 'forecastkrit_pkey' AND n.nspname = 'dataapi_lz' AND ic.oid = i.indexrelid AND n.oid = ic.relnamespace AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] AND (NOT ta.attisdropped) AND (NOT ia.attisdropped) order by ia.attnum
37 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:16.638695+02 | | 2021-04-01 13:15:16.86598+02 | DEALLOCATE "_PLAN0x7f46fc35eed0"
38 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:16.691856+02 | | 2021-04-01 13:15:16.69662+02 | show max_identifier_length
(24 rows)
Attachments:
[image/png] image001.png (396.2K, 3-image001.png)
download | view image
[text/plain] db_queries_fulltext.txt (17.6K, 4-db_queries_fulltext.txt)
download | inline:
pid | client_addr | state | wait_event_type | backend_start | xact_start | state_change | query
--------+-------------+--------+-----------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
30 | | | Activity | 2021-04-01 13:14:17.262872+02 | | |
32 | | | Activity | 2021-04-01 13:14:17.263749+02 | | |
373883 | | | | 2021-04-07 14:04:37.551133+02 | 2021-04-07 08:49:40.152171+02 | |
373884 | | | | 2021-04-07 14:04:37.551576+02 | 2021-04-07 08:49:40.152171+02 | |
28 | | | Activity | 2021-04-01 13:14:17.262323+02 | | |
27 | | | Activity | 2021-04-01 13:14:17.262752+02 | | |
29 | | | Activity | 2021-04-01 13:14:17.262753+02 | | |
224406 | 10.37.1.216 | idle | Client | 2021-04-06 16:31:56.921563+02 | | 2021-04-07 14:04:37.37835+02 |
373309 | 10.37.2.66 | idle | Client | 2021-04-07 14:03:23.608589+02 | | 2021-04-07 14:03:23.613093+02 | SET application_name = 'PostgreSQL JDBC Driver'
40 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:18.015224+02 | | 2021-04-07 14:02:37.628649+02 | COMMIT
372004 | 10.37.2.66 | idle | Client | 2021-04-07 14:00:35.253338+02 | | 2021-04-07 14:00:35.257102+02 | SET application_name = 'PostgreSQL JDBC Driver'
371383 | 10.37.2.66 | idle | Client | 2021-04-07 13:59:15.547588+02 | | 2021-04-07 13:59:15.551386+02 | SET application_name = 'PostgreSQL JDBC Driver'
367152 | 10.37.2.66 | idle | Client | 2021-04-07 13:50:11.790021+02 | | 2021-04-07 13:50:11.794277+02 | SET application_name = 'PostgreSQL JDBC Driver'
365608 | 10.37.2.66 | idle | Client | 2021-04-07 13:46:53.135327+02 | | 2021-04-07 13:46:53.140432+02 | SET application_name = 'PostgreSQL JDBC Driver'
365280 | 10.37.2.66 | idle | Client | 2021-04-07 13:46:11.134051+02 | | 2021-04-07 13:46:11.140538+02 | SET application_name = 'PostgreSQL JDBC Driver'
365259 | 10.37.2.66 | idle | Client | 2021-04-07 13:46:08.697428+02 | | 2021-04-07 13:46:08.701173+02 | SET application_name = 'PostgreSQL JDBC Driver'
364928 | 10.37.2.66 | idle | Client | 2021-04-07 13:45:26.021603+02 | | 2021-04-07 13:45:26.026252+02 | SET application_name = 'PostgreSQL JDBC Driver'
364841 | 10.37.2.66 | idle | Client | 2021-04-07 13:45:15.036377+02 | | 2021-04-07 13:45:15.045453+02 | SET application_name = 'PostgreSQL JDBC Driver'
225902 | | active | IPC | 2021-04-07 08:49:40.197802+02 | 2021-04-07 08:49:40.152171+02 | 2021-04-07 08:49:40.238357+02 | create temporary table "adr_select_45115964_cb0c_4d6d_b782_2dce92595df2" as select t01_7.ZUSATZ4 as "a_0", t01_7.ZUSATZ0 as "a_1", t01_7.ZUSATZ5 as "a_2", t01_7.ZUSATZ1 as "a_3", t01_7.ZUSATZ2 as "a_4", t01_7.ZUSATZ3 as "a_5", case when t01_7.ADRESSORDER is not null then '-5280/' || t01_7.ADRESSORDER else null end as "a_6", case when t01_7.KANTON is not null then '-250/' || t01_7.KANTON else null end as "a_7", case when t01_7.GNRKANTON is not null then '-250/' || t01_7.GNRKANTON else null end as "a_8", t01_7.GEMEINDENAME as "a_9", t01_7.BFSNR as "a_10", t01_7.ITSCONTACTPOINTDEF as "a_11", t01_7.LAND as "a_12", t01_7.CREATED as "a_13", t01_7.CREATEDUSER as "a_14", t01_7.EXTCONTACTPOINTID as "a_15", case when t01_7.EXTQUELLE is not null then '-51131/' || t01_7.EXTQUELLE else null end as "a_16", t01_7.HAUSNUMMER as "a_17", t01_7.HAUSNUMMER_ZUSATZ as "a_18", t01_7.BOID as "a_19", case when t01_7.ISPOSTFACHOHNENR is not null then '-1580/' || t01_7.ISPOSTFACHOHNENR else null end as "a_20", t01_7.LASTUPDATE as "a_21", t01_7.METABO as "a_22", t01_7.DATAAPIFROM as "a_23", t01_7.DATAAPIUPTO as "a_24", t01_7.PROCESSID as "a_25", t01_7.ITSCPMUGRUND as "a_26", t01_7.ITSADRPARENTADRESS as "a_27", t01_7.ITSPARTNER as "a_28", t01_7.POSTFACH as "a_29", t01_7.STATEFROM as "a_30", t01_7.STATEUPTO as "a_31", t01_7.PLZ as "a_32", t01_7.PLZ_ZUSATZ as "a_33", t01_7.BEMERKUNGEN as "a_34", t01_7.REPLACED as "a_35", t01_7.REPLACEDUSER as "a_36", t01_7.STATEBEGIN as "a_37", t01_7.STATEEND as "a_38", t01_7.STATEFROM as "a_39", t01_7.STATEUPTO as "a_40", t01_7.STRASSE as "a_41", t01_7.ORT as "a_42", case when t01_7.ADRESSETYP is not null then '-6240/' || t01_7.ADRESSETYP else null end as "a_43", case when t01_7.REPLACED < to_date('3000-01-01', 'YYYY-MM-DD') then 'D' when t01_7.GUELTAB = t01_7.GUELTBIS then 'D' else 'I' end as "a_44", t01_7.GUELTAB as "a_45", t01_7.GUELTBIS as "a_46" from "dataapi_lz"."adresse" as "t01_7" join "dataapi_lz"."code" as "t02_14" on ((t01_7.ADRESSETYP = t02_14.BOID) and (t02_14."replaced" = timestamp '3000-01-01 00:00:00.0')) join "dataapi_lz"."code" as "t03_14" on ((t01_7.LIFECYCLE = t03_14.BOID) and (t03_14."replaced" = timestamp '3000-01-01 00:00:00.0')) where ((t02_14.INTERNALNAME IN ('AdresseTyp_Domizil','AdresseTyp_Zusatz')) and (t03_14.INTERNALNAME = 'SYR_Code_Active') and (t01_7."replaced" = timestamp '3000-01-01 00:00:00.0') and t01_7."boid" in (select "dataapi_lz"."adresse"."boid" from "dataapi_lz"."adresse" where (1 = 1 and "dataapi_lz"."adresse"."replaced" = timestamp '3000-01-01 00:00:00.0' and "dataapi_lz"."adresse"."dataapifrom" > timestamp '2021-03-31 09:42:27.0')))
225901 | | active | IPC | 2021-04-07 08:49:40.197379+02 | 2021-04-07 08:49:40.152171+02 | 2021-04-07 08:49:40.243261+02 | create temporary table "adr_select_45115964_cb0c_4d6d_b782_2dce92595df2" as select t01_7.ZUSATZ4 as "a_0", t01_7.ZUSATZ0 as "a_1", t01_7.ZUSATZ5 as "a_2", t01_7.ZUSATZ1 as "a_3", t01_7.ZUSATZ2 as "a_4", t01_7.ZUSATZ3 as "a_5", case when t01_7.ADRESSORDER is not null then '-5280/' || t01_7.ADRESSORDER else null end as "a_6", case when t01_7.KANTON is not null then '-250/' || t01_7.KANTON else null end as "a_7", case when t01_7.GNRKANTON is not null then '-250/' || t01_7.GNRKANTON else null end as "a_8", t01_7.GEMEINDENAME as "a_9", t01_7.BFSNR as "a_10", t01_7.ITSCONTACTPOINTDEF as "a_11", t01_7.LAND as "a_12", t01_7.CREATED as "a_13", t01_7.CREATEDUSER as "a_14", t01_7.EXTCONTACTPOINTID as "a_15", case when t01_7.EXTQUELLE is not null then '-51131/' || t01_7.EXTQUELLE else null end as "a_16", t01_7.HAUSNUMMER as "a_17", t01_7.HAUSNUMMER_ZUSATZ as "a_18", t01_7.BOID as "a_19", case when t01_7.ISPOSTFACHOHNENR is not null then '-1580/' || t01_7.ISPOSTFACHOHNENR else null end as "a_20", t01_7.LASTUPDATE as "a_21", t01_7.METABO as "a_22", t01_7.DATAAPIFROM as "a_23", t01_7.DATAAPIUPTO as "a_24", t01_7.PROCESSID as "a_25", t01_7.ITSCPMUGRUND as "a_26", t01_7.ITSADRPARENTADRESS as "a_27", t01_7.ITSPARTNER as "a_28", t01_7.POSTFACH as "a_29", t01_7.STATEFROM as "a_30", t01_7.STATEUPTO as "a_31", t01_7.PLZ as "a_32", t01_7.PLZ_ZUSATZ as "a_33", t01_7.BEMERKUNGEN as "a_34", t01_7.REPLACED as "a_35", t01_7.REPLACEDUSER as "a_36", t01_7.STATEBEGIN as "a_37", t01_7.STATEEND as "a_38", t01_7.STATEFROM as "a_39", t01_7.STATEUPTO as "a_40", t01_7.STRASSE as "a_41", t01_7.ORT as "a_42", case when t01_7.ADRESSETYP is not null then '-6240/' || t01_7.ADRESSETYP else null end as "a_43", case when t01_7.REPLACED < to_date('3000-01-01', 'YYYY-MM-DD') then 'D' when t01_7.GUELTAB = t01_7.GUELTBIS then 'D' else 'I' end as "a_44", t01_7.GUELTAB as "a_45", t01_7.GUELTBIS as "a_46" from "dataapi_lz"."adresse" as "t01_7" join "dataapi_lz"."code" as "t02_14" on ((t01_7.ADRESSETYP = t02_14.BOID) and (t02_14."replaced" = timestamp '3000-01-01 00:00:00.0')) join "dataapi_lz"."code" as "t03_14" on ((t01_7.LIFECYCLE = t03_14.BOID) and (t03_14."replaced" = timestamp '3000-01-01 00:00:00.0')) where ((t02_14.INTERNALNAME IN ('AdresseTyp_Domizil','AdresseTyp_Zusatz')) and (t03_14.INTERNALNAME = 'SYR_Code_Active') and (t01_7."replaced" = timestamp '3000-01-01 00:00:00.0') and t01_7."boid" in (select "dataapi_lz"."adresse"."boid" from "dataapi_lz"."adresse" where (1 = 1 and "dataapi_lz"."adresse"."replaced" = timestamp '3000-01-01 00:00:00.0' and "dataapi_lz"."adresse"."dataapifrom" > timestamp '2021-03-31 09:42:27.0')))
225598 | 10.37.2.66 | active | IO | 2021-04-07 08:20:29.59069+02 | 2021-04-07 08:49:40.152171+02 | 2021-04-07 08:49:40.15374+02 | create temporary table "adr_select_45115964_cb0c_4d6d_b782_2dce92595df2" as select t01_7.ZUSATZ4 as "a_0", t01_7.ZUSATZ0 as "a_1", t01_7.ZUSATZ5 as "a_2", t01_7.ZUSATZ1 as "a_3", t01_7.ZUSATZ2 as "a_4", t01_7.ZUSATZ3 as "a_5", case when t01_7.ADRESSORDER is not null then '-5280/' || t01_7.ADRESSORDER else null end as "a_6", case when t01_7.KANTON is not null then '-250/' || t01_7.KANTON else null end as "a_7", case when t01_7.GNRKANTON is not null then '-250/' || t01_7.GNRKANTON else null end as "a_8", t01_7.GEMEINDENAME as "a_9", t01_7.BFSNR as "a_10", t01_7.ITSCONTACTPOINTDEF as "a_11", t01_7.LAND as "a_12", t01_7.CREATED as "a_13", t01_7.CREATEDUSER as "a_14", t01_7.EXTCONTACTPOINTID as "a_15", case when t01_7.EXTQUELLE is not null then '-51131/' || t01_7.EXTQUELLE else null end as "a_16", t01_7.HAUSNUMMER as "a_17", t01_7.HAUSNUMMER_ZUSATZ as "a_18", t01_7.BOID as "a_19", case when t01_7.ISPOSTFACHOHNENR is not null then '-1580/' || t01_7.ISPOSTFACHOHNENR else null end as "a_20", t01_7.LASTUPDATE as "a_21", t01_7.METABO as "a_22", t01_7.DATAAPIFROM as "a_23", t01_7.DATAAPIUPTO as "a_24", t01_7.PROCESSID as "a_25", t01_7.ITSCPMUGRUND as "a_26", t01_7.ITSADRPARENTADRESS as "a_27", t01_7.ITSPARTNER as "a_28", t01_7.POSTFACH as "a_29", t01_7.STATEFROM as "a_30", t01_7.STATEUPTO as "a_31", t01_7.PLZ as "a_32", t01_7.PLZ_ZUSATZ as "a_33", t01_7.BEMERKUNGEN as "a_34", t01_7.REPLACED as "a_35", t01_7.REPLACEDUSER as "a_36", t01_7.STATEBEGIN as "a_37", t01_7.STATEEND as "a_38", t01_7.STATEFROM as "a_39", t01_7.STATEUPTO as "a_40", t01_7.STRASSE as "a_41", t01_7.ORT as "a_42", case when t01_7.ADRESSETYP is not null then '-6240/' || t01_7.ADRESSETYP else null end as "a_43", case when t01_7.REPLACED < to_date('3000-01-01', 'YYYY-MM-DD') then 'D' when t01_7.GUELTAB = t01_7.GUELTBIS then 'D' else 'I' end as "a_44", t01_7.GUELTAB as "a_45", t01_7.GUELTBIS as "a_46" from "dataapi_lz"."adresse" as "t01_7" join "dataapi_lz"."code" as "t02_14" on ((t01_7.ADRESSETYP = t02_14.BOID) and (t02_14."replaced" = timestamp '3000-01-01 00:00:00.0')) join "dataapi_lz"."code" as "t03_14" on ((t01_7.LIFECYCLE = t03_14.BOID) and (t03_14."replaced" = timestamp '3000-01-01 00:00:00.0')) where ((t02_14.INTERNALNAME IN ('AdresseTyp_Domizil','AdresseTyp_Zusatz')) and (t03_14.INTERNALNAME = 'SYR_Code_Active') and (t01_7."replaced" = timestamp '3000-01-01 00:00:00.0') and t01_7."boid" in (select "dataapi_lz"."adresse"."boid" from "dataapi_lz"."adresse" where (1 = 1 and "dataapi_lz"."adresse"."replaced" = timestamp '3000-01-01 00:00:00.0' and "dataapi_lz"."adresse"."dataapifrom" > timestamp '2021-03-31 09:42:27.0')))
41 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:18.036269+02 | | 2021-04-06 17:14:49.372727+02 | select ta.attname, ia.attnum, ic.relname, n.nspname, NULL from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class ic, pg_catalog.pg_index i, pg_catalog.pg_namespace n where ic.relname = 'forecastkrit_pkey' AND n.nspname = 'dataapi_lz' AND ic.oid = i.indexrelid AND n.oid = ic.relnamespace AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] AND (NOT ta.attisdropped) AND (NOT ia.attisdropped) order by ia.attnum
37 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:16.638695+02 | | 2021-04-01 13:15:16.86598+02 | DEALLOCATE "_PLAN0x7f46fc35eed0"
38 | 10.37.2.70 | idle | Client | 2021-04-01 13:15:16.691856+02 | | 2021-04-01 13:15:16.69662+02 | show max_identifier_length
(24 rows)
[application/octet-stream] deltatranformation_hanging_query.sql (4.5K, 5-deltatranformation_hanging_query.sql)
download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Create temp table query hangs
2021-04-13 18:26 Create temp table query hangs Novak Ivan <[email protected]>
@ 2021-04-13 19:18 ` Ron <[email protected]>
2 siblings, 0 replies; 9+ messages in thread
From: Ron @ 2021-04-13 19:18 UTC (permalink / raw)
To: [email protected]
On 4/13/21 1:26 PM, Novak Ivan wrote:
>
> Greetings,
>
> We’ve a hanging query which creates a temp table.
>
> Some context:
>
> * The application transforms from one data model to another one (to a
> canonical datamodel). The source tables for these transformations are
> on one schema of the postgresDB (we call it LandingZone) and the
> target tables of these transformations are in a different schema of
> the exact same postgresDB (we call it the CDM schema).
>
> Our problem in summary:
>
> * a query for creating temporary table on target schema is hanging
> forever and we are out of ideas why.
>
> The query is not generated by our own "mapper" tool that we wrote. We
> don't use any third party product here (except JOOQ library for
> communicating to DB)
>
> When we try to create a (temporary) table with one statement, and that
> query takes forever (even after >24h the query is still there). In the
> Postgres Database we see three identical active PIDs for the same query
> (with same starttimestamp). But we only sent the statement *once* to the
> DB. See screenshot below.
>
> The query hangs when creating of temporary tables (see below) and also
> creating normal tables.
>
> The query hangs using our go-to library for Database queries (JOOQ) and
> also when we do it manually (we rewrote the implementation using java
> JDBC) -> so it is not dependent on that library
>
> The query hangs also after updating the default JDBC drivers to newest
> version (42.2.19)
>
> The query does NOT hang when running on local development environment (not
> containerized) -> we see 3 PIDs in database (which is strange), but the
> query finishes after some seconds
>
> The query does NOT hang when running directly on the database using
> DBeaver, i.e. not using java -> we see 3 PIDs in database (which is
> strange), but the query finishes after some seconds
>
> This doesn't happen every time we create a table. For source tables that
> have no new data (or are empty) the query completes. Otherwise it doesn’t
> seem to be volume dependent (small vs large vs huge volume)
>
> It does consistently happen when creating temp table selecting on specific
> tables that have some volume.
>
Something similar happens to us when running a long script full of DDL from
JDBC on Postgresql 9.6.6.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Create temp table query hangs
2021-04-13 18:26 Create temp table query hangs Novak Ivan <[email protected]>
@ 2021-04-13 20:02 ` Tom Lane <[email protected]>
2 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2021-04-13 20:02 UTC (permalink / raw)
To: Novak Ivan <[email protected]>; +Cc: [email protected] <[email protected]>; Kümin Antonio <[email protected]>
Novak Ivan <[email protected]> writes:
> * a query for creating temporary table on target schema is hanging forever and we are out of ideas why.
Looking at wait_event as well as wait_event_type in pg_stat_activity
might give some more clues.
> When we try to create a (temporary) table with one statement, and that query takes forever (even after >24h the query is still there). In the Postgres Database we see three identical active PIDs for the same query (with same starttimestamp). But we only sent the statement once to the DB. See screenshot below.
I think this is just a parallelized query with two worker processes.
Were the query's source tables freshly created or freshly populated?
If so, you might need to issue ANALYZE commands on them before you
start the query itself, to make sure the query planner has valid
statistics to work with. It's possible that the problem is just a
very dumb choice of query plan due to lack of stats.
Have you tried comparing EXPLAIN output for this query across the
scenarios where it works well and where it doesn't? If it is a
bad-plan problem then there'd be obvious differences in the shape
of the plan.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* postgresql in docker to improve security
2021-04-13 18:26 Create temp table query hangs Novak Ivan <[email protected]>
@ 2024-05-02 22:43 ` Nguyen, Long (IM&T, St. Lucia) <[email protected]>
2024-05-03 03:23 ` Re: postgresql in docker to improve security Kashif Zeeshan <[email protected]>
2 siblings, 1 reply; 9+ messages in thread
From: Nguyen, Long (IM&T, St. Lucia) @ 2024-05-02 22:43 UTC (permalink / raw)
To: [email protected] <[email protected]>
Good day. This is a general db question.
I start exploring containerisation and start learning docker. Would having postgresql in docker improve security in the sense that users could only access to the db through the port mapped to the environment outside of docker, and if they somehow are able to hack and access outside the db, the access is limited within the container not the OS that host the container.
Thanks.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgresql in docker to improve security
2021-04-13 18:26 Create temp table query hangs Novak Ivan <[email protected]>
2024-05-02 22:43 ` postgresql in docker to improve security Nguyen, Long (IM&T, St. Lucia) <[email protected]>
@ 2024-05-03 03:23 ` Kashif Zeeshan <[email protected]>
2024-05-03 05:06 ` Re: postgresql in docker to improve security gunnar.wagner <[email protected]>
2024-05-03 05:14 ` Re: postgresql in docker to improve security vrms <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: Kashif Zeeshan @ 2024-05-03 03:23 UTC (permalink / raw)
To: Nguyen, Long (IM&T, St. Lucia) <[email protected]>; +Cc: [email protected] <[email protected]>
Hi
Yes docker container improves the security and following are the ways it
does.
1. Isolation : When you run postgres in a container, you are isolating it
from host os and other containers so it limits the attack surface.
2. Port mapping : By mapping only the necessary container port and allowing
access only using that port limits the attack surface.
3. You can manage the access privileges of the users that run container
4. Docker containers use namespaces for process isolation and security.
Regards
Kashif Zeeshan
Bitnine Global
On Fri, May 3, 2024 at 3:44 AM Nguyen, Long (IM&T, St. Lucia) <
[email protected]> wrote:
> Good day. This is a general db question.
>
>
>
> I start exploring containerisation and start learning docker. Would
> having postgresql in docker improve security in the sense that users could
> only access to the db through the port mapped to the environment outside of
> docker, and if they somehow are able to hack and access outside the db, the
> access is limited within the container not the OS that host the container.
>
>
>
> Thanks.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgresql in docker to improve security
2021-04-13 18:26 Create temp table query hangs Novak Ivan <[email protected]>
2024-05-02 22:43 ` postgresql in docker to improve security Nguyen, Long (IM&T, St. Lucia) <[email protected]>
2024-05-03 03:23 ` Re: postgresql in docker to improve security Kashif Zeeshan <[email protected]>
@ 2024-05-03 05:06 ` gunnar.wagner <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: gunnar.wagner @ 2024-05-03 05:06 UTC (permalink / raw)
To: [email protected]
interesting points @Kashif.
On the other hand I often, that containers are by design ephemeral and
tend to crash. This would be a threat to data integrity (allegedly more
then running in a VM i.e.).
Admittedly the environment I am working in is not very open to, nor
experienced with container-technology in general, so these claims might
be based on hearsay and those issues might not be actual problems any
more these days.
Any thoughts on that?
Also I made a mark in my mind head thad podman, by design, was just a
little more secure then Docker. I think it was due to the fact
Containers can run without the need of requiring root privileges for the
user running a podman container.
On 5/3/24 5:23 AM, Kashif Zeeshan wrote:
> Hi
>
> Yes docker container improves the security and following are the ways
> it does.
> 1. Isolation : When you run postgres in a container, you are isolating
> it from host os and other containers so it limits the attack surface.
> 2. Port mapping : By mapping only the necessary container port and
> allowing access only using that port limits the attack surface.
> 3. You can manage the access privileges of the users that run container
> 4. Docker containers use namespaces for process isolation and security.
>
> Regards
> Kashif Zeeshan
> Bitnine Global
>
> On Fri, May 3, 2024 at 3:44 AM Nguyen, Long (IM&T, St. Lucia)
> <[email protected]> wrote:
>
> Good day. This is a general db question.
>
> I start exploring containerisation and start learning docker.
> Would having postgresql in docker improve security in the sense
> that users could only access to the db through the port mapped to
> the environment outside of docker, and if they somehow are able to
> hack and access outside the db, the access is limited within the
> container not the OS that host the container.
>
> Thanks.
>
--
Gunnar Wagner | Jahnstr. 5, 19386 Lübz | mob +49.176.7080.9090
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgresql in docker to improve security
2021-04-13 18:26 Create temp table query hangs Novak Ivan <[email protected]>
2024-05-02 22:43 ` postgresql in docker to improve security Nguyen, Long (IM&T, St. Lucia) <[email protected]>
2024-05-03 03:23 ` Re: postgresql in docker to improve security Kashif Zeeshan <[email protected]>
@ 2024-05-03 05:14 ` vrms <[email protected]>
2024-05-03 05:17 ` Re: postgresql in docker to improve security Kashif Zeeshan <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: vrms @ 2024-05-03 05:14 UTC (permalink / raw)
To: [email protected]
interesting points @Kashif.
On the other hand I often, that containers are by design ephemeral and
tend to crash. This would be a threat to data integrity (allegedly more
then running in a VM i.e.).
Admittedly the environment I am working in is not very open to, nor
experienced with container-technology in general, so these claims might
be based on hearsay and those issues might not be actual problems any
more these days.
Any thoughts on that?
Also I made a mark in my mind head thad podman, by design, was just a
little more secure then Docker. I think it was due to the fact
Containers can run without the need of requiring root privileges for the
user running a podman container.
On 5/3/24 5:23 AM, Kashif Zeeshan wrote:
> Hi
>
> Yes docker container improves the security and following are the ways
> it does.
> 1. Isolation : When you run postgres in a container, you are isolating
> it from host os and other containers so it limits the attack surface.
> 2. Port mapping : By mapping only the necessary container port and
> allowing access only using that port limits the attack surface.
> 3. You can manage the access privileges of the users that run container
> 4. Docker containers use namespaces for process isolation and security.
>
> Regards
> Kashif Zeeshan
> Bitnine Global
>
> On Fri, May 3, 2024 at 3:44 AM Nguyen, Long (IM&T, St. Lucia)
> <[email protected]> wrote:
>
> Good day. This is a general db question.
>
> I start exploring containerisation and start learning docker.
> Would having postgresql in docker improve security in the sense
> that users could only access to the db through the port mapped to
> the environment outside of docker, and if they somehow are able to
> hack and access outside the db, the access is limited within the
> container not the OS that host the container.
>
> Thanks.
>
On 5/3/24 5:23 AM, Kashif Zeeshan wrote:
> Hi
>
> Yes docker container improves the security and following are the ways
> it does.
> 1. Isolation : When you run postgres in a container, you are isolating
> it from host os and other containers so it limits the attack surface.
> 2. Port mapping : By mapping only the necessary container port and
> allowing access only using that port limits the attack surface.
> 3. You can manage the access privileges of the users that run container
> 4. Docker containers use namespaces for process isolation and security.
>
> Regards
> Kashif Zeeshan
> Bitnine Global
>
> On Fri, May 3, 2024 at 3:44 AM Nguyen, Long (IM&T, St. Lucia)
> <[email protected]> wrote:
>
> Good day. This is a general db question.
>
> I start exploring containerisation and start learning docker.
> Would having postgresql in docker improve security in the sense
> that users could only access to the db through the port mapped to
> the environment outside of docker, and if they somehow are able to
> hack and access outside the db, the access is limited within the
> container not the OS that host the container.
>
> Thanks.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: postgresql in docker to improve security
2021-04-13 18:26 Create temp table query hangs Novak Ivan <[email protected]>
2024-05-02 22:43 ` postgresql in docker to improve security Nguyen, Long (IM&T, St. Lucia) <[email protected]>
2024-05-03 03:23 ` Re: postgresql in docker to improve security Kashif Zeeshan <[email protected]>
2024-05-03 05:14 ` Re: postgresql in docker to improve security vrms <[email protected]>
@ 2024-05-03 05:17 ` Kashif Zeeshan <[email protected]>
2024-05-03 08:05 ` AW: postgresql in docker to improve security Dischner, Anton <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Kashif Zeeshan @ 2024-05-03 05:17 UTC (permalink / raw)
To: vrms <[email protected]>; +Cc: [email protected]
On Fri, May 3, 2024 at 10:14 AM vrms <[email protected]> wrote:
> interesting points @Kashif.
>
> On the other hand I often, that containers are by design ephemeral and
> tend to crash. This would be a threat to data integrity (allegedly more
> then running in a VM i.e.).
>
Yes that's true, but for that we have K8 and which can automate the
recovery process.
>
> Admittedly the environment I am working in is not very open to, nor
> experienced with container-technology in general, so these claims might be
> based on hearsay and those issues might not be actual problems any more
> these days.
>
Yes I agree, the technology is changing rapidly but there are still
loopholes and what we can do is to avoid as many risks as possible as
nothing is 100% secure.
>
> Any thoughts on that?
>
>
> Also I made a mark in my mind head thad podman, by design, was just a
> little more secure then Docker. I think it was due to the fact Containers
> can run without the need of requiring root privileges for the user running
> a podman container.
>
>
>
>
> On 5/3/24 5:23 AM, Kashif Zeeshan wrote:
>
> Hi
>
> Yes docker container improves the security and following are the ways it
> does.
> 1. Isolation : When you run postgres in a container, you are isolating it
> from host os and other containers so it limits the attack surface.
> 2. Port mapping : By mapping only the necessary container port and
> allowing access only using that port limits the attack surface.
> 3. You can manage the access privileges of the users that run container
> 4. Docker containers use namespaces for process isolation and security.
>
> Regards
> Kashif Zeeshan
> Bitnine Global
>
> On Fri, May 3, 2024 at 3:44 AM Nguyen, Long (IM&T, St. Lucia) <
> [email protected]> wrote:
>
>> Good day. This is a general db question.
>>
>>
>>
>> I start exploring containerisation and start learning docker. Would
>> having postgresql in docker improve security in the sense that users could
>> only access to the db through the port mapped to the environment outside of
>> docker, and if they somehow are able to hack and access outside the db, the
>> access is limited within the container not the OS that host the container.
>>
>>
>>
>> Thanks.
>>
>
>
>
> On 5/3/24 5:23 AM, Kashif Zeeshan wrote:
>
> Hi
>
> Yes docker container improves the security and following are the ways it
> does.
> 1. Isolation : When you run postgres in a container, you are isolating it
> from host os and other containers so it limits the attack surface.
> 2. Port mapping : By mapping only the necessary container port and
> allowing access only using that port limits the attack surface.
> 3. You can manage the access privileges of the users that run container
> 4. Docker containers use namespaces for process isolation and security.
>
> Regards
> Kashif Zeeshan
> Bitnine Global
>
> On Fri, May 3, 2024 at 3:44 AM Nguyen, Long (IM&T, St. Lucia) <
> [email protected]> wrote:
>
>> Good day. This is a general db question.
>>
>>
>>
>> I start exploring containerisation and start learning docker. Would
>> having postgresql in docker improve security in the sense that users could
>> only access to the db through the port mapped to the environment outside of
>> docker, and if they somehow are able to hack and access outside the db, the
>> access is limited within the container not the OS that host the container.
>>
>>
>>
>> Thanks.
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* AW: postgresql in docker to improve security
2021-04-13 18:26 Create temp table query hangs Novak Ivan <[email protected]>
2024-05-02 22:43 ` postgresql in docker to improve security Nguyen, Long (IM&T, St. Lucia) <[email protected]>
2024-05-03 03:23 ` Re: postgresql in docker to improve security Kashif Zeeshan <[email protected]>
2024-05-03 05:14 ` Re: postgresql in docker to improve security vrms <[email protected]>
2024-05-03 05:17 ` Re: postgresql in docker to improve security Kashif Zeeshan <[email protected]>
@ 2024-05-03 08:05 ` Dischner, Anton <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Dischner, Anton @ 2024-05-03 08:05 UTC (permalink / raw)
To: 'Kashif Zeeshan' <[email protected]>; vrms <[email protected]>; +Cc: [email protected] <[email protected]>
Hi all,
i know of an installation where virtual Servers are running under VMWare.
On a VM there is docker running which does virtualisation again.
Then there is running a kubernetes cluster.
In my eyes this is complete insane.
Configuration complexity explodes.
Resources are wasted.
Kubernetes cluster are meant do run on dozens if not hundreds of real hardware. If one failes from hardware there should be no problem.
IMHO If you do a proper installation on the OS you will need no docker, kubernetes and so on.
Look for hardening you OS if you have increased security needs.
Take this as a beginning:
https://tuxcare.com/blog/linux-system-hardening-top-10-security-tips/
Please someone correct me if there are reasons against my opinion,
Best
Von: Kashif Zeeshan <[email protected]>
Gesendet: Freitag, 3. Mai 2024 07:18
An: vrms <[email protected]>
Cc: [email protected]
Betreff: Re: postgresql in docker to improve security
On Fri, May 3, 2024 at 10:14 AM vrms <[email protected]<mailto:[email protected]>> wrote:
interesting points @Kashif.
On the other hand I often, that containers are by design ephemeral and tend to crash. This would be a threat to data integrity (allegedly more then running in a VM i.e.).
Yes that's true, but for that we have K8 and which can automate the recovery process.
Admittedly the environment I am working in is not very open to, nor experienced with container-technology in general, so these claims might be based on hearsay and those issues might not be actual problems any more these days.
Yes I agree, the technology is changing rapidly but there are still loopholes and what we can do is to avoid as many risks as possible as nothing is 100% secure.
Any thoughts on that?
Also I made a mark in my mind head thad podman, by design, was just a little more secure then Docker. I think it was due to the fact Containers can run without the need of requiring root privileges for the user running a podman container.
On 5/3/24 5:23 AM, Kashif Zeeshan wrote:
Hi
Yes docker container improves the security and following are the ways it does.
1. Isolation : When you run postgres in a container, you are isolating it from host os and other containers so it limits the attack surface.
2. Port mapping : By mapping only the necessary container port and allowing access only using that port limits the attack surface.
3. You can manage the access privileges of the users that run container
4. Docker containers use namespaces for process isolation and security.
Regards
Kashif Zeeshan
Bitnine Global
On Fri, May 3, 2024 at 3:44 AM Nguyen, Long (IM&T, St. Lucia) <[email protected]<mailto:[email protected]>> wrote:
Good day. This is a general db question.
I start exploring containerisation and start learning docker. Would having postgresql in docker improve security in the sense that users could only access to the db through the port mapped to the environment outside of docker, and if they somehow are able to hack and access outside the db, the access is limited within the container not the OS that host the container.
Thanks.
On 5/3/24 5:23 AM, Kashif Zeeshan wrote:
Hi
Yes docker container improves the security and following are the ways it does.
1. Isolation : When you run postgres in a container, you are isolating it from host os and other containers so it limits the attack surface.
2. Port mapping : By mapping only the necessary container port and allowing access only using that port limits the attack surface.
3. You can manage the access privileges of the users that run container
4. Docker containers use namespaces for process isolation and security.
Regards
Kashif Zeeshan
Bitnine Global
On Fri, May 3, 2024 at 3:44 AM Nguyen, Long (IM&T, St. Lucia) <[email protected]<mailto:[email protected]>> wrote:
Good day. This is a general db question.
I start exploring containerisation and start learning docker. Would having postgresql in docker improve security in the sense that users could only access to the db through the port mapped to the environment outside of docker, and if they somehow are able to hack and access outside the db, the access is limited within the container not the OS that host the container.
Thanks.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-05-03 08:05 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-04-13 18:26 Create temp table query hangs Novak Ivan <[email protected]>
2021-04-13 19:18 ` Ron <[email protected]>
2021-04-13 20:02 ` Tom Lane <[email protected]>
2024-05-02 22:43 ` postgresql in docker to improve security Nguyen, Long (IM&T, St. Lucia) <[email protected]>
2024-05-03 03:23 ` Re: postgresql in docker to improve security Kashif Zeeshan <[email protected]>
2024-05-03 05:06 ` Re: postgresql in docker to improve security gunnar.wagner <[email protected]>
2024-05-03 05:14 ` Re: postgresql in docker to improve security vrms <[email protected]>
2024-05-03 05:17 ` Re: postgresql in docker to improve security Kashif Zeeshan <[email protected]>
2024-05-03 08:05 ` AW: postgresql in docker to improve security Dischner, Anton <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox