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

Anvesh Patel

Leave a Reply

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

Notify of
Sort by:   newest | oldest | most voted

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!