This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
Microsoft provides managed relational database services for a variety of popular database engines including Azure SQL Database, Azure Database for MySQL, and Azure Database for PostgreSQL. Azure Database for PostgreSQL, for instance, is a managed version of the PostgreSQL open-source relational database management system. All these database products have built-in roles and accounts that have wide-ranging permissions and security implications. Two such elevated accounts in Azure Database for PostgreSQL are the superuser account (built-in to PostgreSQL) and azure_pg_admin account. Under some conditions, it is possible for the azure_pg_admin account to assume elevated privileges. In this blog we will discuss why the azure_pg_admin security account is needed and best practices to avoid the possibility of abuse.
Superuser is, as you can imagine, the most elevated and trusted account in PostgreSQL and can perform every operation. Today there are a number of PostgreSQL tasks, such as installing extensions, modifying PG catalogs, etc. that require the superuser privilege. This is an important functionality needed by PostgreSQL features, however, it should be limited to highly privileged operations only.
In cloud-based PaaS environments access to a superuser account is restricted to control plane operations only by cloud operators. Therefore, the azure_pg_admin account is added to the database as a pseudo-superuser account. This is similar to other accounts created by many cloud vendors with PostgreSQL PaaS services to work within PaaS cloud model. It’s also the first account created in Azure Database for PostgreSQL after the server is created. As its name implies azure_pg_admin, too, is an elevated account. It’s not quite a superuser account, but it’s very close in its abilities. On the other hand, azure_pg_admin should be thought of as a guardrail rather than a true lower-privilege account. A guardrail is something that helps prevent accidental errors but does not offer security guarantees. This way, using azure_pg_admin also helps drive down support incidents and costs.
The azure_pg_admin account can perform some types of trusted functionality without the need for superuser, most notably, installing custom extensions. The ability to run extensions is an important feature of PostgreSQL engine. However, custom extensions are developed independently by third party developers. When installing these third-party extensions customers need to be sure that their code is trustworthy. Azure Database for PostgreSQL allows you to install well known and mainstream custom extensions . Azure Database for PostgreSQL also adds a concept of allow-listing extensions for better administrative control on which extensions can be installed in the server.
It is important to note that due to the design of PaaS services, critical security boundaries are imposed even with pseudo-superuser accounts to control access to OS, file system and most importantly networking, precluding easy cross-tenant communication from the customer tenant hosting a PostgreSQL service instance. We are continuously working to strengthen these guardrails to make sure that usage of pseudo-superuser accounts such as azure_pg_admin cannot compromise tenant isolation.
Certain high privileged administrative functions, such as installation of extensions or creating event triggers, require temporary escalation of azure_pg_admin to the superuser role. This is why customers cannot easily remove the azure_pg_admin account without the potential for compatibility issues and loss of administrative functions.
So what can you do?
The following is a non-exhaustive list of best practices to mitigate the possibility of unauthorized individuals assuming superuser rights:
- Have an absolute minimum set of users assigned the azure_pg_admin role and don't assign this role to application users.
- Make sure you have control- and data-plane logging enabled to log potential anomalous behavior. pgAudit is an open-source extension commonly used today with PostgreSQL Flexible Server.
- Make sure you restrict network traffic using NSGs (Network Security Groups). This is an optional layer of security for your Azure Virtual Network that acts as a firewall for controlling traffic in and out of one or more subnets.
- Use Azure Active Directory Authentication, so you can manage database user identities and other Microsoft services in a central location, which simplifies permission management.
Additionally, in an effort to continuously improve the products and services that we offer, we plan on collaborating with the PostgreSQL community to improve the granularity of defined roles within the environment, but this will take time.
In summary, this is a complex topic with high regression potential, but we will continue to work with our open source and industry partners to improve the security posture of PostgreSQL including development of additional ways to handle these pseudo-superuser accounts and other classes of issues. We have also seen other topics get raised such as this PostgreSQL: Hardening PostgreSQL via (optional) ban on local file system access.