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.
CREATE OR REPLACE FUNCTION ChangeObjectsOwnerShip(text)
returns text language plpgsql volatile
Now generate two select statements to get alter script.
Below select statement is for Tables, Sequences and Views.
'ALTER TABLE ' || quote_ident(s.nspname) || '.' ||quote_ident(s.relname) || ' owner TO app_user'
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname NOT LIKE E'pg\\_%'
AND nspname <> 'information_schema'
AND relkind IN ('r','S','v') ORDER BY relkind = 'S'
Below select statement is for all functions.
'ALTER FUNCTION ' || quote_ident(s.nspname) || '.' ||quote_ident(s.function_name) || '('||s.parms||') owner TO app_user'
,proname AS function_name
, pg_catalog.oidvectortypes(proargtypes) AS parms
FROM pg_catalog.pg_proc AS c
JOIN pg_namespace n
ON (c.pronamespace = n.oid)
WHERE nspname != 'information_schema'
AND nspname NOT LIKE E'pg\\_%'
ORDER BY proname
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,
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 dbrnd.com, 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.