Requesting additional privileges for Azure Postgres User

Ash 1 Reputation point
2021-02-06T13:09:16.75+00:00

Hi.. I need some help regarding privileges. I have an Azure Database for Postgresql server with you. I have access to a Postgres user and a PGDBO user account both of which have very restricted permissions. I'm trying to perform a data migration but in order to do so I need to have PG_READ_SERVER_FILES granted to my Postgres user account, do you know how I go about requesting this? I have extracted large CLOBS/BLOBS/XML data from my Oracle database into standalone files in my filesystem. I have then created my tables within my Azure Postgres database and loaded in all data except the CLOBS/BLOBS/XML data. Now I need to load my files containing CLOBS/BLOBS/XML data into my Postgres database via an update statement. I am using an automated approach which runs the command below per file:

PSQL> update CLOB_TABLE set CLOB_COLUMN=(select pg_read_file('/opt/app/test/CLOB_TABLE_CLOB_COL_1AA.clob')) where id=1 and id2='AA';

However, when I run this it fails due to " ERROR: absolute path not allowed ". I have tested this in my local VM hosting Postgres and it is resolved by granting PG_READ_SERVER_FILES. Are you able to grant this to my Azure user accounts please? Or is there another way I can access server files to load in?

Azure Database for PostgreSQL
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Navtej Singh Saini 4,221 Reputation points Microsoft Employee
    2021-02-09T01:01:13.083+00:00

    @Ash

    We don’t grant superuser role in the Azure PostgreSQL. You need not create pg_read_server_files role, please check if instead you will need to use the COPY command. So when you use COPY, it is server based whereas /COPY is client based. Please try using /COPY as mentioned as in the below article and check if this meets your requirement.

    Document to refer and CopyWiki

    Please check if you can copy files first and then export into the Database. Also check these migration limitations from Oracle to Azure DB for Postgresql.

    Regards
    Navtej S