0

I am new to Azure, and I needed to do some tasks that I generally do with the standard superuser in localhost or on premises postgresql installations.

1. Backup case

For tests/debug purposes, I need to get a specific set of data currently hosted on an Azure Database for PostgreSQL managed server. So I would liked to get a backup of that particular database in order to use it on my local dev machine instance. So I launched the following:

pg_dump -h <host> -d <dbname> -U <user> -Fc -f mydbbackup.bak

And of course it failed, as microservices are still using the database.I got some errors like:

pg_dump: error: query failed: ERROR:  permission denied for table <table>
pg_dump: error: query was: LOCK TABLE <table> IN ACCESS SHARE MODE

2. Table transformation case

As we are deploying in different environments, we need to transform database schemas all along the way. At the very beginning, every developer applied different changes/migration manually (I was event not there at that time :-) ). Recently we introduced a proper tool to manage that, with a dedicated migration user/role for that. But as the datatable are not owned by my new migration user, it failed (which is normal) as I tried to change PKs etc... . But my problem is that I even can not change the ownership of the table as I do not have enought privilegies...

I tried:

ALTER TABLE <schema>.<tableName> OWNER TO migration_user;

Which resulted in:

ERROR: must be member of role "migration_user"

So I went a step further:

GRANT migration_user TO admin_user with admin option;
SET ROLE migration_user;
ALTER TABLE <schema>.<tableName> OWNER TO migration_user;

An this time I got:

ERROR: must be owner of table <tableName>

Conclusion : is there no other user available to do that sort of things within the managed azure service? Or did I miss something? In a self hosted/on prem' case, all those operations would work, as I would simply launch the backup/re-owning with the role postgres (who is of the rolsuper database role). But in the case of an Azure Service, the user I am using is not of that role... So, this means that I do not have the permissions to back up the db/change datatable owner... It seems the admin user provided by the azure service is not really super user as in a traditionnal local instance for example. It seems there is one name azure_superuser but it is out of my scope...

What can I do?

4
  • 1
    Looks like <user> doesn't have the permissions needed. We never terminate current connections when making a backup, no need for. Oct 20, 2022 at 12:06
  • 1
    That permission error doesn't depend on concurrent users. Even if no one else was using the database, you still don't have permissions which you don't have. You need to find a user who does have the permission you need.
    – jjanes
    Oct 20, 2022 at 15:07
  • Thanks guys, OK ! I misread the error message. Thought the "lock" was there because of the services, but was due to my backup attempt. Will edit my question.
    – chrisdot
    Oct 24, 2022 at 10:03
  • This question belongs to DB Administration, suggest you to post it in DBA Exchange
    – PratikLad
    Nov 4, 2022 at 4:28

1 Answer 1

1

Those problems were linked to the fact I'm using the timescale extension (omited to point that).

Basically, a first user was creating the database (superuser), another one was creating the schema and the tables (database migration at service startup), and another one was populating the tables (service user identity).

And as I was using the timescale's hypertable feature, the problem only appeared when the tables were starting to get sliced into new chunks. This is why I was under the impression that it was working localy, but not in the service. And this was just due to different aging...

To fix that (to continue to apply the relevant permissions on new created tables and chunks), I had to use the ALTER DEFAULT PRIVILEGES command...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.