How to change ownership for all objects in PostgreSQL

In this post, I will provide you with one script which is helping to change ownership of all PostgreSQL objects.

Why ownership is a require to change ?

During my research and development with PostgreSQL, I require to change ownership of all tables, sequences, views, functions.

I have created one application user for our production PostgreSQL database.

But system admin and deployment manager made one mistake to execute  objects by different database user.

After deployment, we came to know that objects not deployed under proper ownership.

Now we have 2500 tables, 450 views and 230 stored functions are required to change ownership.

Now I require to solve this by using only one script because of manual changing ownership of this many objects is take too much time .

I found solution from this url:

here I modified some part and prepared post for generic use.

Below is my demonstration:

Let’s First, create one function with $parameter value so that you can use this function as a template.

Now generate two select statements to get alter script.

Below select statement is for Tables, Sequences and Views.

Below select statement is for all functions.

In above code, preparing one alter statement to change ownership for all objects from assign user to mentioned ” app_user “. You can change ” app_user ”  by your require user name.

After executing above select statements, you can find alter scripts in a result

Copy this result and execute this alter scripts using super user.

All ownership will be changed after executing this alter script.

Other Related Articles,

How to create job in PostgreSQL.

PostgreSQL XML Type

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

1 Comment on "How to change ownership for all objects in PostgreSQL"

Notify of

Sort by:   newest | oldest | most voted
9 months 9 days ago

Someone essentially help to make seriously posts I would state. This is the first time I frequented your web page and thus far? I surprised with the research you made to create this particular publish amazing. Wonderful job!