1

I'm running PostgreSQL 11.11 in Azure (Azure Database for PostgreSQL). I have two users, A and B, who are both members of the default azure_pg_admin role. My problem is that role B can drop tables created (and owned) by user A, which shouldn't happen.

According to Postgres documentation, "only the table owner, the schema owner, and superuser can drop a table." A owns the table, as well as the schema in question. None of the roles involved are superusers (which are not available in Azure-PostgreSQL anyway).

The root cause seems to be the shared membership in the azure_pg_admin role. Revoking the membership prevents B from dropping the table. However, I cannot figure out what is happening. From the documentation, I gather that A and B should inherit the privileges of azure_pg_admin, but not the other way around. Why, then, is ownership of the tables passed laterally on to B?

Edmond Dantès
  • 11
  • 1
  • The PostgreSQL docs applies to PostgreSQL. You are not running PostgreSQL, you are running "Azure Database for PostgreSQL". – jjanes Aug 17 '21 at 15:01
  • 1
    Which is a "a relational database service in the Microsoft cloud based on the PostgreSQL Community Edition database engine" and should thus conform to Postgres unless MS states otherwise, and I cannot find anything to that end from their documentation. MS even refers to the Postgres documentation here: [link](https://learn.microsoft.com/en-us/azure/postgresql/howto-create-users), where the azure_pg_admin role is discussed. No special capabilities are mentioned either. Anyway, I'm looking for explanation of this behaviour, whether from MS or Postgres. – Edmond Dantès Aug 18 '21 at 07:07
  • If you can't reproduce the issue in pure PostgreSQL, then PostgreSQL can't explain it. I don't speak for Microsoft, even if you want me to. – jjanes Aug 18 '21 at 16:14
  • I don't expect you specifically to speak for MS _or_ Postgres, but am looking for information that can explain this behaviour and make sure that I haven't misunderstood anything. Anyway, I installed PostgreSQL 11.11 and tried rebuilding a similar architecture for testing, starting by creating a role with similar privileges to azure_pg_admin. Indeed, I could not drop tables created by user A while connected as user B. So, is the conclusion that MS has given azure_pg_admin some undocumented privileges that allow it to drop tables owned by (at least) its members? – Edmond Dantès Aug 19 '21 at 09:28

0 Answers0