Microsoft Flexible Server for PostgreSQL

This Blog is about some findings on Microsoft’s new Flexible Server offering for PostgreSQL which is in Customer Preview at the moment.

Findings about the infrastructure

The Microsoft Flexible Server for PostgreSQL is using Ubuntu as OS infrastructure, I think it is worth to be noticed that Microsoft’s new offering is switching from Windows to Linux.

postgres=> select version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit

Ubuntu upgrade ist planned as i know, Ubuntu 16.04 is end of support 04/2021.

Findings about performance

We have tested the performance which is more stable than on the old Single Server offering.

Differences

The new setup is one virtual machine per instance, instead of one big machine with many instances on. On the old setup there is in any case a gateway in between, which causes security and in some points also performance issues. On the old setup, with many instances, this gateway moves the connection for any instance to Port 5432 which is PostgreSQL default, with show port; we have seen many instances running originally in the 20000 port region.

The mapping from ports in the 20000 region to 5432 is done by this gateway and this is gone with the new offering.

Collation

The default collation on Azure Flexible Server Offering is UTF-8 which is PostgreSQL default.

postgres=> show server_encoding;
server_encoding
-----------------
UTF8

postgres=> l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------------+----------------+----------+------------+------------+-----------------------------------
azure_maintenance | azuresu | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | azure_pg_admin | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | azuresu | UTF8 | en_US.utf8 | en_US.utf8 | =c/azuresu +
| | | | | azuresu=CTc/azuresu
template1 | azure_pg_admin | UTF8 | en_US.utf8 | en_US.utf8 | =c/azure_pg_admin +
| | | | | azure_pg_admin=CTc/azure_pg_admin

SSL

is stable:

postgres=> select * from pg_stat_ssl;
pid | ssl | version | cipher | bits | compression | client_dn | client_serial | issuer_dn
-------+-----+---------+-----------------------------+------+-------------+---------------------------------------------+------------------------------------------------+-----------------------------------
51 | f | | | | | | |
55 | f | | | | | | |
56 | f | | | | | | |
57 | f | | | | | | |
28669 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f | /CN=azuresu.b92f28e7b317.database.azure.com | 1293482480206780384144623282355891870454996725 | /DC=GBL/DC=AME/CN=AME INFRA CA 01
28013 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f | | |
49 | f | | | | | | |
48 | f | | | | | | |
50 | f |

Extensions

Not all extensions provided by the contrib package are available on Azure, this may have some consequences if your application requires one of them:

• address_standardizer | 2.5.1 | | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
• address_standardizer_data_us | 2.5.1 | | Address Standardizer US dataset example
• adminpack | 2.0 | | administrative functions for PostgreSQL
• amcheck | 1.1 | | functions for verifying relation integrity
• autoinc | 1.0 | | functions for autoincrementing fields
• azure | 1.0 | 1.0 | azure extension for PostgreSQL service
• bloom | 1.0 | | bloom access method – signature file based index
• btree_gin | 1.3 | | support for indexing common datatypes in GIN
• btree_gist | 1.5 | | support for indexing common datatypes in GiST
• citext | 1.5 | | data type for case-insensitive character strings
• cube | 1.4 | | data type for multidimensional cubes
• dblink | 1.2 | | connect to other PostgreSQL databases from within a database
• dict_int | 1.0 | | text search dictionary template for integers
• dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing
• earthdistance | 1.1 | | calculate great-circle distances on the surface of the Earth
• file_fdw | 1.0 | | foreign-data wrapper for flat file access
• fuzzystrmatch | 1.1 | | determine similarities and distance between strings
• hstore | 1.5 | | data type for storing sets of (key, value) pairs
• insert_username | 1.0 | | functions for tracking who changed a table
• intagg | 1.1 | | integer aggregator and enumerator (obsolete)
• intarray | 1.2 | | functions, operators, and index support for 1-D arrays of integers
• isn | 1.2 | | data types for international product numbering standards
• lo | 1.1 | | Large Object maintenance
• ltree | 1.1 | | data type for hierarchical tree-like structures
• moddatetime | 1.0 | | functions for tracking last modification time
• pageinspect | 1.7 | | inspect the contents of database pages at a low level
• pg_buffercache | 1.3 | | examine the shared buffer cache
• pg_cron | 1.2 | | Job scheduler for PostgreSQL
• pg_freespacemap | 1.2 | | examine the free space map (FSM)
• pg_prewarm | 1.2 | | prewarm relation data
• pg_stat_statements | 1.6 | | track execution statistics of all SQL statements executed
• pg_trgm | 1.4 | | text similarity measurement and index searching based on trigrams
• pg_visibility | 1.2 | | examine the visibility map (VM) and page-level visibility info
• pgaudit | 1.3.1 | | provides auditing functionality
• pgcrypto | 1.3 | | cryptographic functions
• pglogical | 2.3.2 | | PostgreSQL Logical Replication
• pglogical_origin | 1.0.0 | | Dummy extension for compatibility when upgrading from Postgres 9.4
• pgrowlocks | 1.2 | | show row-level locking information
• pgstattuple | 1.5 | | show tuple-level statistics
• plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
• postgis | 2.5.1 | | PostGIS geometry, geography, and raster spatial types and functions
• postgis_sfcgal | 2.5.1 | | PostGIS SFCGAL functions
• postgis_tiger_geocoder | 2.5.1 | | PostGIS tiger geocoder and reverse geocoder
• postgis_topology | 2.5.1 | | PostGIS topology spatial types and functions
• postgres_fdw | 1.0 | | foreign-data wrapper for remote PostgreSQL servers
• refint | 1.0 | | functions for implementing referential integrity (obsolete)
• seg | 1.3 | | data type for representing line segments or floating-point intervals
• sslinfo | 1.2 | | information about SSL certificates
• tablefunc | 1.0 | | functions that manipulate whole tables, including crosstab
• tcn | 1.0 | | Triggered change notifications
• timetravel | 1.0 | | functions for implementing time travel
• tsm_system_rows | 1.0 | | TABLESAMPLE method which accepts number of rows as a limit
• tsm_system_time | 1.0 | | TABLESAMPLE method which accepts time in milliseconds as a limit
• unaccent | 1.1 | | text search dictionary that removes accents
• uuid-ossp | 1.1 | | generate universally unique identifiers (UUIDs)
• xml2 | 1.1 | | XPath querying and XSLT

These extensions are missing compared to a default community setup with the contrib package:

• hstore_plperl
• hstore_plperlu
• jsonb_plperl
• jsonb_plperlu
• pg_stat_kcache

These extension are not in the default Linux setup with the contrib package:

• address_standardizer_data_us
• azure
• pg_cron
• pgaudit
• pglogical
• pglogical_origin
• postgis
• postgis_sfcgal
• postgis_tiger_geocoder
• postgis_topology

Findings about security

This is a very important point for someone who has to fulfill security requirements by corporate security. On the configuration page it is possible to enable a firewall rule to allow Azure services to connect, what does this mean in reality?
select * from pg_hba_file_rules; is blocked on the new offering, so we discussed with Microsoft if there is any change here.

This is, according to information of Microsoft, not the case, so that this finding is still present:

pg_hba.conf contains more than 10900 entries including 45 /16 Networks, means these 45 /16 networks allowing to connect 45 x 65025 IP Adresses = 2.926 Million IP addresses allowed to connect with host and password security only, with enabled Azure Services to connect.

It is interesting that with enabled SSL enforcement, the entries inside pg_hba.conf are still on host and not on hostssl. According to the PostgreSQL documentation also non SSL connections are allowed: https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

host
This record matches connection attempts made using TCP/IP. Host records match either SSL or non-SSL connection attempts.
hostssl
This record matches connection attempts made using TCP/IP, but only when the connection is made with SSL encryption.
password
Require the client to supply an unencrypted password for authentication. Since the password is sent in clear text over the network, this should not be used on untrusted networks.

Password hashing is still configured to MD5instead of scram-sha-256.

On the old Single Server Offering we see broken SSL connections with select * from pg_stat_ssl; SSLwas given from your client to the Gateway, but broken from the gateway to the instance.

A restart of the instance solves the issue, but SSL status on PostgreSQL level is not monitored by Microsoft, this issue is also gone with the new Flexible Server offering by removing the Gateway with the new Linux based setup.

Yes we see many improvements, but also many things that need to be discussed.