--- title: "A dive into Postgres's default settings and their impact on security" publication: "Postgres Weekly" issue: 312 published_at: "2019-07-03" canonical_url: "https://postgresweekly.com/issues/312" --- The latest Postgres news, articles, tools, and projects. #312 — July 3, 2019 [Read on the Web](https://postgresweekly.com/issues/312) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/ocstzavnuapr5ctzzwwd.jpg)](https://www.youtube.com/watch?v=IwahVdNboc8) ▶[Being a Better Developer With EXPLAIN](https://www.youtube.com/watch?v=IwahVdNboc8) — An interesting 40 minute talk that digs into the world of query parsing and planning and how to use the `EXPLAIN` command to create query plans for analyzing the efficiency of your queries and your database’s design. Louise Grandjonc [A Dive into PostgreSQL Defaults and Their Impact on Security](https://info.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1) — A two part exploration ([part 2 is here](https://info.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-2)) of Postgres’s default security settings and how the [crunchy_check_access extension](https://github.com/CrunchyData/crunchy_check_access) can help you dig deeper. Joe Conway [![\](https://copm.s3.amazonaws.com/fb19c0ea.jpg)](https://www.influxdata.com/blog/monitoring-your-postgresql-database-with-telegraf-and-influxdb/?utm_campaign=postgres&utm_medium=newsletter&utm_source=cooperpress) [Monitoring Your PostgreSQL Database with Telegraf and InfluxDB](https://www.influxdata.com/blog/monitoring-your-postgresql-database-with-telegraf-and-influxdb/?utm_campaign=postgres&utm_medium=newsletter&utm_source=cooperpress) — This tutorial will specifically cover the process of setting up Telegraf and InfluxDB to monitor PostgreSQL. InfluxData sponsor [Another Introduction to GIN Indexes](http://www.louisemeta.com/blog/indexes-gin/) — We’ve been seeing many great articles about Postgres indexes lately and Louise Grandjonc has one here focused on GIN (Generalized Inverted Index) indexes, as well as [how to use them](http://www.louisemeta.com/blog/indexes-gin-algorithms/). Louise Grandjonc [The Slow Climb of Postgres and the Value of Persistence](https://thenewstack.io/the-slow-climb-of-postgres-and-the-value-of-persistence/) — Attendees at the recent PostgresVision conference heard from database pioneer Dr. Michael Stonebraker. He gave an account of how he helped bring Postgres into the world — a 15 year journey of amazing highs and lows. Joab Jackson (The New Stack) [The Challenge of Setting `max_connections` and Why to Use a Connection Pooler](http://richyen.com/postgres/2019/06/25/pools_arent_just_for_cars.html) — A look at why `max_connections` can be a tricky Postgres setting to get right and how you can use some benchmark testing to approach a reasonable figure for your own setup. Richard Yen [How the CIS Benchmark for Postgres 11 Works](https://info.crunchydata.com/blog/cis-11-benchmark) — The CIS PostgreSQL Benchmark is a set of guidelines and best practices issued by the _Center for Internet Security_ on how to test Postgres. Here’s a look at how it can be used to improve your own Postgres setup and as part of automating the security verification process. Douglas Hunley [Postgres Ibiza: A Different Type of Conference](https://momjian.us/main/blogs/pgblog/2019.html#June_28_2019) — The Postgres conference scene took a bold step last month with [Postgres Ibiza](https://pgibz.io/), a resort-based conference on Spain’s (in)famous party island. But.. Bruce was won over. Bruce Momjian [How to Regain Access to Postgres If Your Admin User Can't Log In](https://fluca1978.github.io/2019/06/27/PostgreSQLSingleMode.html) — You can lean on Postgres’s _single user mode_ to regain access. Luca Ferrari [PostgreSQL Logging Best Practices](https://www.strongdm.com/postgresql-logging-best-practices/?utm_source=PG%20Weekly&utm_medium=email&utm_campaign=2019-07-03%20-%20%5BSchD%5D%20-%20%5BSDM%5D) — Here are a few best practices to keep everyone on the same page. strongDM sponsor [How to Setup PgBouncer as a Connection Pooling Proxy on Azure DB for PostgreSQL](https://techcommunity.microsoft.com/t5/Azure-Database-for-PostgreSQL/Steps-to-install-and-setup-PgBouncer-connection-pooling-proxy/ba-p/730555) Parikshit Savjani [Bulding Postgres and OpenSSL using MSYS2 and MinGW under Windows](https://www.cybertec-postgresql.com/en/bulding-postgresql-x86-x64-and-openssl-using-msys2-and-mingw-under-windows/) — I’m a little naive to Windows, alas, but this walks through the steps of building Postgres for yourself on Windows using MSYS2, a Cygwin-derived POSIX compatibility layer. Pavlo Golub [PgBouncer 1.10.0 Released](https://pgbouncer.github.io/2019/07/pgbouncer-1-10-0) — PgBouncer is a lightweight connection pooler and this latest release improves TLS 1.3 support amongst [other things](https://pgbouncer.github.io/changelog.html#pgbouncer-110x). Peter Eisentraut [db-to-sqlite 1.0: A CLI Tool for Exporting Tables or Queries From Any SQL Database to SQLite](https://github.com/simonw/db-to-sqlite) Simon Willison supported by [![GitPrime\](https://res.cloudinary.com/cpress/image/upload/v1554813939/kwagarrsergds4sxgjr9.png)](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29) 💡 Tip of the Week Trading durability for extra performance _Durability_ in Postgres is the concept of ensuring that any data-related operations you perform get committed successfully to disk, so that if the server Postgres is running on suddenly restarts or loses power, it can return to the right state later on. The need for durability can vary depending on your use case or what operations you want to perform. For example, when loading a large data set into a new database, you might trade short term durability for raw performance, whereas if you're running an account ledger in real time, durability is more important than performance! If you're happy to trade durability for raw performance, temporarily or not, there are several settings you can tweak (in the file shown by `SHOW config_file;`) to make it happen: - Turn off [the fsync setting](https://www.postgresql.org/docs/9.3/runtime-config-wal.html#GUC-FSYNC) to stop Postgres from _ensuring_ all updates are physically written to disk. They will be eventually anyway, but this stops Postgres from making sure. - Disable [full page writes.](https://www.postgresql.org/docs/9.3/runtime-config-wal.html#GUC-FULL-PAGE-WRITES) This goes hand in hand with fsync to some extent. - Set [synchronous_commit to 'off'](https://www.postgresql.org/docs/9.3/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT) to make transactions be considered successful even before WAL records are written to disk. - Increase the [checkpoint_segments and checkpoint_timeout](https://www.postgresql.org/docs/9.3/runtime-config-wal.html#GUC-CHECKPOINT-SEGMENTS) options to reduce how often WAL entries get committed to disk. Want to return to this tip in future? You'll want Postgres's [Non-Durable Settings documentation.](https://www.postgresql.org/docs/9.3/non-durability.html) This week's tip is sponsored by _[GitPrime](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)_. Get your copy of their new field guide **['20 Patterns to Watch for in Engineering Teams'](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)**, filled with actionable insights to help debug your development process with data. 🗓 Upcoming Postgres Events - [PGConf.Brasil 2019](https://www.pgconf.com.br/2019/en/) (August 1-3 in São Paulo) — A three day conference with talks, tutorials, courses, and lightning talks. - [PGDay Austria 2019](https://pgday.at/en/) (September 6 in Wiener Neustadt, Austria) - [PostgreSQL Conference Asia 2019](http://2019.pgconf.asia/) (September 9-12 in Bali, Indonesia) - [PostgresConf South Africa 2019](https://postgresconf.org/conferences/SouthAfrica2019) (October 8-9 in Johannesburg) — An opportunity for the database administration and developer community who use Postgres to get to know each other. Curated by [Cooperpress](https://cooperpress.com/) and [Craig Kerstiens](https://twitter.com/craigkerstiens). [Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) © Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom