martin_core/tiles/postgres/
pool.rs

1//! `PostgreSQL` connection pool implementation.
2
3use std::path::PathBuf;
4
5use deadpool_postgres::{Manager, ManagerConfig, Object, Pool, RecyclingMethod};
6use log::{info, warn};
7use postgres::config::SslMode;
8use semver::Version;
9
10use crate::tiles::postgres::PostgresError::{
11    BadPostgisVersion, BadPostgresVersion, PostgisTooOld, PostgresError, PostgresPoolBuildError,
12    PostgresPoolConnError, PostgresqlTooOld,
13};
14use crate::tiles::postgres::PostgresResult;
15use crate::tiles::postgres::tls::{SslModeOverride, make_connector, parse_conn_str};
16
17/// We require `ST_TileEnvelope` that was added in [`PostGIS 3.0.0`](https://postgis.net/2019/10/PostGIS-3.0.0/)
18/// See <https://postgis.net/docs/ST_TileEnvelope.html>
19const MINIMUM_POSTGIS_VERSION: Version = Version::new(3, 0, 0);
20/// Minimum version of postgres required for [`MINIMUM_POSTGIS_VERSION`] according to the [Support Matrix](https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS)
21const MINIMUM_POSTGRES_VERSION: Version = Version::new(11, 0, 0);
22/// After this [`PostGIS`](https://postgis.net/) version we can use margin parameter in `ST_TileEnvelope`
23const ST_TILE_ENVELOPE_POSTGIS_VERSION: Version = Version::new(3, 1, 0);
24/// Before this [`PostGIS`](https://postgis.net/) version, some geometry was missing in some cases.
25/// One example is lines not drawing at zoom level 0, but every other level for very long lines.
26const MISSING_GEOM_FIXED_POSTGIS_VERSION: Version = Version::new(3, 5, 0);
27/// Minimum version of postgres required for [`RECOMMENDED_POSTGIS_VERSION`] according to the [Support Matrix](https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS)
28const RECOMMENDED_POSTGRES_VERSION: Version = Version::new(12, 0, 0);
29
30/// `PostgreSQL` connection pool with `PostGIS` support.
31#[derive(Clone, Debug)]
32pub struct PostgresPool {
33    id: String,
34    pool: Pool,
35    /// Indicates if `ST_TileEnvelope` supports the margin parameter.
36    ///
37    /// `true` if running postgis >= 3.1
38    /// This being `false` indicates that tiles may be cut off at the edges.
39    supports_tile_margin: bool,
40}
41
42impl PostgresPool {
43    /// Creates a new `PostgreSQL` connection pool
44    ///
45    /// Arguments:
46    /// - `connection_string`: the postgres connection string
47    /// - `ssl_cert`: Same as PGSSLCERT ([docs](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLCERT))
48    /// - `ssl_key`: Same as PGSSLKEY ([docs](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLKEY))
49    /// - `ssl_root_cert`: Same as PGSSLROOTCERT ([docs](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLROOTCERT))
50    /// - `pool_size`: Maximum number of connections in the pool
51    pub async fn new(
52        connection_string: &str,
53        ssl_cert: Option<&PathBuf>,
54        ssl_key: Option<&PathBuf>,
55        ssl_root_cert: Option<&PathBuf>,
56        pool_size: usize,
57    ) -> PostgresResult<Self> {
58        let (id, mgr) = Self::parse_config(connection_string, ssl_cert, ssl_key, ssl_root_cert)?;
59
60        let pool = Pool::builder(mgr)
61            .max_size(pool_size)
62            .build()
63            .map_err(|e| PostgresPoolBuildError(e, id.clone()))?;
64        let mut res = Self {
65            id: id.clone(),
66            pool,
67            supports_tile_margin: false,
68        };
69        let conn = res.get().await?;
70        let pg_ver = get_postgres_version(&conn).await?;
71        if pg_ver < MINIMUM_POSTGRES_VERSION {
72            return Err(PostgresqlTooOld(pg_ver, MINIMUM_POSTGRES_VERSION));
73        }
74
75        let postgis_ver = get_postgis_version(&conn).await?;
76        if postgis_ver < MINIMUM_POSTGIS_VERSION {
77            return Err(PostgisTooOld(postgis_ver, MINIMUM_POSTGIS_VERSION));
78        }
79
80        // In the warning cases below, we could technically run.
81        // This is not ideal for reasons explained in the warnings
82        if pg_ver < RECOMMENDED_POSTGRES_VERSION {
83            warn!(
84                "PostgreSQL {pg_ver} is older than the recommended minimum {RECOMMENDED_POSTGRES_VERSION}."
85            );
86        }
87        res.supports_tile_margin = postgis_ver >= ST_TILE_ENVELOPE_POSTGIS_VERSION;
88        if !res.supports_tile_margin {
89            warn!(
90                "PostGIS {postgis_ver} is older than {ST_TILE_ENVELOPE_POSTGIS_VERSION}. Margin parameter in ST_TileEnvelope is not supported, so tiles may be cut off at the edges."
91            );
92        }
93        if postgis_ver < MISSING_GEOM_FIXED_POSTGIS_VERSION {
94            warn!(
95                "PostGIS {postgis_ver} is older than the recommended minimum {MISSING_GEOM_FIXED_POSTGIS_VERSION}. In the used version, some geometry may be hidden on some zoom levels. If You encounter this bug, please consider updating your postgis installation. For further details please refer to https://github.com/maplibre/martin/issues/1651#issuecomment-2628674788"
96            );
97        }
98        info!("Connected to PostgreSQL {pg_ver} / PostGIS {postgis_ver} for source {id}");
99        Ok(res)
100    }
101
102    /// Parse configuration from connection string
103    ///
104    /// Arguments:
105    /// - `connection_string`: the postgres connection string
106    /// - `ssl_cert`: Same as PGSSLCERT ([docs](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLCERT))
107    /// - `ssl_key`: Same as PGSSLKEY ([docs](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLKEY))
108    /// - `ssl_root_cert`: Same as PGSSLROOTCERT ([docs](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLROOTCERT))
109    fn parse_config(
110        connection_string: &str,
111        ssl_cert: Option<&PathBuf>,
112        ssl_key: Option<&PathBuf>,
113        ssl_root_cert: Option<&PathBuf>,
114    ) -> PostgresResult<(String, Manager)> {
115        let (pg_cfg, ssl_mode) = parse_conn_str(connection_string)?;
116
117        let id = pg_cfg.get_dbname().map_or_else(
118            || format!("{:?}", pg_cfg.get_hosts()[0]),
119            ToString::to_string,
120        );
121
122        let mgr_config = ManagerConfig {
123            recycling_method: RecyclingMethod::Fast,
124        };
125
126        let mgr = if pg_cfg.get_ssl_mode() == SslMode::Disable {
127            info!("Connecting without SSL support: {pg_cfg:?}");
128            let connector = deadpool_postgres::tokio_postgres::NoTls {};
129            Manager::from_config(pg_cfg, connector, mgr_config)
130        } else {
131            match ssl_mode {
132                SslModeOverride::Unmodified(_) => {
133                    info!("Connecting with SSL support: {pg_cfg:?}");
134                }
135                SslModeOverride::VerifyCa => {
136                    info!("Using sslmode=verify-ca to connect: {pg_cfg:?}");
137                }
138                SslModeOverride::VerifyFull => {
139                    info!("Using sslmode=verify-full to connect: {pg_cfg:?}");
140                }
141            }
142            let connector = make_connector(ssl_cert, ssl_key, ssl_root_cert, ssl_mode)?;
143            Manager::from_config(pg_cfg, connector, mgr_config)
144        };
145
146        Ok((id, mgr))
147    }
148
149    /// Retrieves an [`Object`] from this [`PostgresPool`] or waits for one to become available.
150    ///
151    /// # Errors
152    ///
153    /// See [`PostgresPoolConnError`] for details.
154    pub async fn get(&self) -> PostgresResult<Object> {
155        self.pool
156            .get()
157            .await
158            .map_err(|e| PostgresPoolConnError(e, self.id.clone()))
159    }
160
161    /// ID under which this [`PostgresPool`] is identified externally
162    #[must_use]
163    pub fn get_id(&self) -> &str {
164        &self.id
165    }
166
167    /// Indicates if `ST_TileEnvelope` supports the margin parameter.
168    ///
169    /// `true` if running postgis >= `3.1`
170    /// This being false indicates that tiles may be cut off at the edges.
171    #[must_use]
172    pub fn supports_tile_margin(&self) -> bool {
173        self.supports_tile_margin
174    }
175}
176
177/// Get [PostgreSQL version](https://www.postgresql.org/support/versioning/).
178/// `PostgreSQL` only has a Major.Minor versioning, so we use 0 the patch version
179async fn get_postgres_version(conn: &Object) -> PostgresResult<Version> {
180    let version: String = conn
181        .query_one(
182            r"
183SELECT (regexp_matches(
184           current_setting('server_version'),
185           '^(\d+\.\d+)',
186           'g'
187       ))[1] || '.0' as version;",
188            &[],
189        )
190        .await
191        .map(|row| row.get("version"))
192        .map_err(|e| PostgresError(e, "querying postgres version"))?;
193
194    let version: Version = version
195        .parse()
196        .map_err(|e| BadPostgresVersion(e, version))?;
197
198    Ok(version)
199}
200
201/// Get [PostGIS version](https://postgis.net/docs/PostGIS_Lib_Version.html)
202async fn get_postgis_version(conn: &Object) -> PostgresResult<Version> {
203    let version: String = conn
204        .query_one(
205            r"
206SELECT (regexp_matches(
207           PostGIS_Lib_Version(),
208           '^(\d+\.\d+\.\d+)',
209           'g'
210       ))[1] as version;",
211            &[],
212        )
213        .await
214        .map(|row| row.get("version"))
215        .map_err(|e| PostgresError(e, "querying postgis version"))?;
216
217    let version: Version = version.parse().map_err(|e| BadPostgisVersion(e, version))?;
218
219    Ok(version)
220}
221
222#[cfg(test)]
223mod tests {
224    use deadpool_postgres::tokio_postgres::Config;
225    use postgres::NoTls;
226    use testcontainers_modules::postgres::Postgres;
227    use testcontainers_modules::testcontainers::ImageExt as _;
228    use testcontainers_modules::testcontainers::runners::AsyncRunner as _;
229
230    use super::*;
231
232    #[tokio::test]
233    async fn parse_version() {
234        let node = Postgres::default()
235            .with_name("postgis/postgis")
236            .with_tag("11-3.0") // purposely very old and stable
237            .start()
238            .await
239            .expect("container launched");
240
241        let pg_config = Config::new()
242            .host(node.get_host().await.unwrap().to_string())
243            .port(node.get_host_port_ipv4(5432).await.unwrap())
244            .dbname("postgres")
245            .user("postgres")
246            .password("postgres")
247            .to_owned();
248
249        let mgr_config = ManagerConfig {
250            recycling_method: RecyclingMethod::Fast,
251        };
252
253        let mgr = Manager::from_config(pg_config, NoTls, mgr_config);
254        let pool = Pool::builder(mgr)
255            .max_size(2)
256            .build()
257            .expect("pool created");
258        let conn = pool
259            .get()
260            .await
261            .expect("able to establish connection to the pool");
262
263        let pg_version = get_postgres_version(&conn)
264            .await
265            .expect("postgres version can be retrieved");
266        assert_eq!(pg_version.major, 11);
267        assert!(pg_version.minor >= 10); // we don't want to break this testcase just because postgis updates that image
268        assert_eq!(pg_version.patch, 0);
269
270        let postgis_version = get_postgis_version(&conn)
271            .await
272            .expect("postgis version can be retrieved");
273        assert_eq!(postgis_version.major, 3);
274        assert_eq!(postgis_version.minor, 0);
275        assert!(postgis_version.patch >= 3); // we don't want to break this testcase just because postgis updates that image
276    }
277}
martin_core/tiles/postgres/pool.rs

martin_core/tiles/postgres/
pool.rs
