Unable to change ownership of tables to Entra ID group

Question

Hi all,

I am trying to change ownership of a table from postgres user to Entra ID group. However, I receive below error:

neworcdb1=> select current_user;

current_user

---

postgres

(1 row)

neworcdb1=> alter table orcdb1tbl1 owner to "<entra ID group name>";

ERROR: must be able to SET ROLE "<Entra ID Group Name>"

The table is owned by Postgres user:

neworcdb1=> \dt

       List of relations

Schema | Name | Type | Owner

--------+------------+-------+----------

public | orcdb1tbl1 | table | postgres

public | orcdb1tbl2 | table | postgres

(2 rows)

May I please know how do I change ownership of above table?

Answer 1

Hi Vora, Ishi •,

Welcome to Microsoft Q&A forum.

As I understand, you want to change change ownership in Azure postgres server user to Entra ID group.

You can do this for your server by execution of following commands:

CREATE USER <db_user> PASSWORD '<StrongPassword!>';

GRANT CONNECT ON DATABASE <newdb> TO <db_user>;

Using an admin account, you may need to grant other privileges to secure the objects in the database. Refer to the PostgreSQL documentation for further details on database roles and privileges. For example:

GRANT ALL PRIVILEGES ON DATABASE <newdb> TO <db_user>;

If a user creates a table "role", the table belongs to that user. If another user needs access to the table, you must grant privileges to the other user on the table level.

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <db_user>;

Sign in to your server, specifying the designated database, using the new username and password. This example shows the psql command line. With this command, you're prompted for the password for the user name. Replace your own server name, database name, and user name.

psql --host=mydemoserver.postgres.database.azure.com --port=5432 --username=db_user --dbname=newdb

Let us know if above helped.

Awaiting your reply.

Thanks