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

Hi Jakub J Jablonski •,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue:

Solution taken in Cx verbatim:

We believe the root cause is that exporting data from Postgres to Azure Storage requires 2 steps (after the initial CREATE EXTENSION azure_storage):

1. SELECT azure_storage.account_add...
2. COPY … TO 'https://….blob.core.windows.net/…'

 

Until now we have been doing SELECT, and then a series of COPY statements.

However, we are using a pool of connections to the database, so if for any reason the subsequent COPY has been using a different connection than the one with SELECT, we got this (misleading) error message.

We could reproduce it in DBeaver, SELECTing account in one SQL console, and then COPYing in another.

We have changed the behavior to send both SELECT and COPY one just after the other, reducing the risk of them using a different connection. Ideally we would like to put them in the single SQL statement, but we couldn’t find a way to do that.

IMHO the manual in https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-storage-extension#export-data-from-azure-database-for-postgresql-flexible-server-to-azure-blob-storage could be improved to clarify the behavior in case of pooled database connections (which is the usual real-life case).

If you have any other questions or are still running into more issues, please let me know. Thank you again for your time and patience throughout this issue.

Please remember to "Accept Answer" if any answer/reply helped, so that others in the community facing similar issues can easily find the solution.

Answer 2

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.

Answer 3

We have discovered probable cause of the issue and fixed that.

We believe the root cause is that exporting data from Postgres to Azure Storage requires 2 steps (after the initial CREATE EXTENSION azure_storage):

1. SELECT azure_storage.account_add...
2. COPY … TO 'https://….blob.core.windows.net/…'

 

Until now we have been doing SELECT, and then a series of COPY statements.

However, we are using a pool of connections to the database, so if for any reason the subsequent COPY has been using a different connection than the one with SELECT, we got this (misleading) error message.

We could reproduce it in DBeaver, SELECTing account in one SQL console, and then COPYing in another.

We have changed the behavior to send both SELECT and COPY one just after the other, reducing the risk of them using a different connection. Ideally we would like to put them in the single SQL statement, but we couldn’t find a way to do that.

IMHO the manual in https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-storage-extension#export-data-from-azure-database-for-postgresql-flexible-server-to-azure-blob-storage could be improved to clarify the behavior in case of pooled database connections (which is the usual real-life case).