Azure Database for PostgreSQL - Flexible Server - How to modify table owner

Question

Does anyone know how to modify table owner in Azure Database for PostgreSQL - Flexible Server (postgres v13)？

I have a administrator role account admindemo (member of azure_pg_admin) and another account named 'liquibase for managing tables, functions, and other database objects.

However, neither the admindemo nor the liquibase (tables owner) accounts can change the table owner：
DO $$

DECLARE

r RECORD;

BEGIN

FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename NOT LIKE 'databasechangelog%')

LOOP

EXECUTE 'ALTER TABLE public.' || quote_ident(r.tablename) || ' OWNER TO claimroot';

END LOOP;

END $$;

When execute by liquibase: must be member of role "claimroot"

When execute by claimroot: must be owner of table role

Accepted Answer

Hi @aya,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

It is generally not recommended to have multiple owners for a table in PostgreSQL, as it can lead to confusion and potential conflicts. Instead, you can consider using a different approach to manage table ownership and access control.

You can try with PostgreSQL Roles with Inheritance, Set up a role that has the required permissions on tables and make both liquibase and claimroot members of this role. This way, liquibase can create and modify tables with permissions inherited from the role.

CREATE ROLE table_manager_role;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA your_schema TO table_manager_role;

GRANT table_manager_role TO liquibase;

GRANT table_manager_role TO claimroot;

Either, you can dynamically change the table ownership, you can use a PostgreSQL function that takes the table name and new owner as parameters, and executes the ALTER TABLE statement to change the ownership.

For example:

CREATE FUNCTION change_table_owner(tablename text, newowner text) RETURNS void AS $$

BEGIN

EXECUTE 'ALTER TABLE ' || quote_ident(tablename) || ' OWNER TO ' || quote_ident(newowner);

END;

$$ LANGUAGE plpgsql;

You can then grant the EXECUTE privilege on this function to the liquibase role, so that it can change the ownership of the tables dynamically.

GRANT EXECUTE ON FUNCTION change_table_owner TO liquibase;

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer

Hi @aya,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

As we understand that, you need to change the owner of the existing table. In order to perform this activity, you should have super user access or requires membership in a specific role.

You can try following the below mentioned method.

Changing a table's ownership requires that:

The executing role is either the current owner of the table or a superuser.
The new owner “claimroot” is a member of any roles that currently own the table.

STEP1: Make claimroot a Member of the liquibase Role,

Since liquibase owns the tables, PostgreSQL requires claimroot to have membership in liquibase to assume ownership. Execute this command as admindemo.

GRANT liquibase TO claimroot;

This grants claimroot sufficient permissions to take ownership of objects owned by liquibase.

STEP2: You can change the owner of each table in your database, make sure that, new user also have all privileges on the database. Now, as liquibase, run your query.

STEP3: Revoke Membership if Temporary, If this role membership was only intended for the ownership transfer, you can now revoke it

REVOKE liquibase FROM claimroot;

NOTE: To change or modify the table’s owner in PostgreSQL, use the “ALTER TABLE tab_name OWNER TO new_owner_name;” command. To alter the table’s owner, the user must be a superuser, or must have the “ALTER TABLE” permissions.

Please refer to the below mentioned links for more information.

https://commandprompt.com/education/how-to-change-the-table-owner-in-postgresql/#:~:text=To%20change%20or%20modify%20the%20table's%20owner%20in%20PostgreSQL%2C%20use,owner%20of%20a%20particular%20table.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-create-users

https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps-2#granting-permissions-in-postgresql

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Azure Database for PostgreSQL - Flexible Server - How to modify table owner

1 additional answer

Your answer