How to get pg_write_server_files role to use Azure Storage extension for PostgreSQL Flexible Server?

Question

We're trying to use Azure Storage extension in Azure Database for PostgreSQL - Flexible Server as documented on https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-storage-extension to copy data from Postgres to Azure Storage account.

We're using Azure Java SDK.

The query we're using looks like:

COPY table.partition
TO 'https://our-storage-account.blob.core.windows.net/archive/
table.partition.tsv'
CSV HEADER DELIMITER E'\t'

It used to work fine, however since a few weeks ago we get an error:

org.postgresql.util.PSQLException: ERROR: must be superuser or a member of the pg_write_server_files role to COPY to a file

From the documentation in https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-create-users I understand it's not possible to become a superuser, and we don't need that, but we would like to assign pg_write_server_files role to our user, so that we can use the Azure Storage extension. Alternatively we'd like to understand if using \COPY instead of COPY would work with the client using Azure Java SDK.

Moreover, the query with plain COPY works fine from my local Postgres client, using our plain user, which is a member of azure_storage_admin, but not pg_write_server_files role. The azure_storage_admin has been somehow granted by azuresu when the server was created, but when we try to grant pg_write_server_files role to our user if fails since that requires superuser.

Answer 1

Your issue is related to the permissions needed to use the COPY command to export data to Azure Storage since your user lacks the necessary pg_write_server_files role, which typically requires superuser privileges. However, superuser privileges are restricted in managed database services like Azure PostgreSQL Flexible Server for security reasons.

As you pointed out, your user already has the azure_storage_admin role, which was granted automatically when the server was created. The azure_storage_admin role allows interaction with Azure Storage via the extension, so if the query works on your local PostgreSQL client using this role, it suggests that your issue might not be related to permissions but perhaps to differences in client/server behavior.

Ensure that your Azure PostgreSQL Flexible Server is properly set up to use the Azure Storage extension and that the permissions for the user in the Flexible Server instance match those in your local environment.f

The \COPY command is a client-side variant of the COPY command and does not require superuser privileges. It transfers data between a file and a table via the client, bypassing the need for server-side file permissions.

The \COPY command can be run in a local PostgreSQL client, which might work for your setup with the Azure Java SDK as it runs on the client-side.

However, keep in mind that with \COPY, the file will be written to the client machine rather than directly to Azure Storage, so this may not be suitable for your scenario unless you configure your application to upload the data afterward.

In managed services like Azure Database for PostgreSQL Flexible Server, the ability to grant roles like pg_write_server_files is limited due to the lack of superuser privileges.

Since you mentioned that granting pg_write_server_files fails, it's unlikely that this approach will work unless you can escalate privileges through other means (such as creating a ticket with Azure support to configure this).

If the built-in roles are not sufficient and neither COPY nor \COPY solve your problem, you may need to escalate the issue to Azure support. Since pg_write_server_files is a restricted role, Azure support might be able to offer guidance or configuration assistance to enable your workflow.