{"id":10387,"date":"2017-07-31T09:07:48","date_gmt":"2017-07-31T07:07:48","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/"},"modified":"2017-07-31T09:07:48","modified_gmt":"2017-07-31T07:07:48","slug":"re-assigning-all-objects-from-on-role-to-another-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/","title":{"rendered":"Re-assigning all objects from on role to another in PostgreSQL"},"content":{"rendered":"

From time to time it might be required to move objects (tables, indexes, whatever) from one user to another user in a database system. You could do that by dumping all the objects with pg_dump<\/a> and then load it to the target user. But there are other solutions which are faster than that. Lets go.<\/p>\n

<\/p>\n

Obviously we need two users when we want to move objects from one user to another (actually it is not really moving the objects but more about changing the ownership):<\/p>\n

\npostgres=# create role a login password 'a';\nCREATE ROLE\npostgres=# create role b login password 'b';\nCREATE ROLE\npostgres=# create schema a authorization a;\nCREATE SCHEMA\npostgres=# create schema b authorization b;\nCREATE SCHEMA\npostgres=# alter role a set search_path=a;\nALTER ROLE\npostgres=# alter role b set search_path=b;\nALTER ROLE\npostgres=# \n<\/pre>\n
Lets create some objects in schema “a” owned by user “a”:<\/p>\n
\npostgres=# c postgres a\nYou are now connected to database \"postgres\" as user \"a\".\npostgres=> ! cat a.sql\ncreate table a ( a int );\ncreate table b ( a int );\ncreate table c ( a int );\ncreate table d ( a int );\ncreate index i1 on a (a);\ncreate index i2 on b (a);\ncreate index i3 on c (a);\ncreate index i4 on d (a);\n\npostgres=> i a.sql\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE TABLE\nCREATE INDEX\nCREATE INDEX\nCREATE INDEX\nCREATE INDEX\n<\/pre>\n
By joining pg_class<\/a> and pg_roles<\/a> we can verify who is actually the owner of the objects:<\/p>\n
\npostgres=> select t.relname, d.rolname \n             from pg_class t, pg_roles d \n            where t.relowner = d.oid and d.rolname = 'a';\n relname | rolname \n---------+---------\n a       | a\n b       | a\n c       | a\n d       | a\n i1      | a\n i2      | a\n i3      | a\n i4      | a\n(8 rows)\n<\/pre>\n
The easiest way to make these objects owned by another user (call it “c”) would be:<\/p>\n
\npostgres=# alter user a rename to c;\nNOTICE:  MD5 password cleared because of role rename\nALTER ROLE\npostgres=# select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'c';\n relname | rolname \n---------+---------\n a       | c\n b       | c\n c       | c\n d       | c\n i1      | c\n i2      | c\n i3      | c\n i4      | c\n(8 rows)\n<\/pre>\n
Not a good idea though as the schema still is named “a” and this at least will create some confusion with the naming. Of course we could rename the schema as well:<\/p>\n
\npostgres=# alter schema a rename to c;\nALTER SCHEMA\npostgres=# c postgres c\nYou are now connected to database \"postgres\" as user \"c\".\npostgres=> select count(*) from a;\n2017-07-28 15:51:25.499 CEST [3415] ERROR:  relation \"a\" does not exist at character 22\n2017-07-28 15:51:25.499 CEST [3415] STATEMENT:  select count(*) from a;\nERROR:  relation \"a\" does not exist\nLINE 1: select count(*) from a;\n<\/pre>\n
… but now we have another mess. Because the search_path is still set to “a” we can not see the objects by default but we will need to use the fully qualified name:<\/p>\n
\npostgres=> select count(*) from c.a;\n count \n-------\n     0\n(1 row)\n<\/pre>\n
Finally we would need to adjust the search_path to get back the previous behavior:<\/p>\n
\npostgres=> set search_path=c;\nSET\npostgres=> select count(*) from a;\n count \n-------\n     0\n(1 row)\n<\/pre>\n
A lot of steps to follow. Easier is:<\/p>\n
\npostgres=# reassign owned by c to b;\nREASSIGN OWNED\npostgres=# alter user b set search_path=c,b;\nALTER ROLE\npostgres=# c postgres b\nYou are now connected to database \"postgres\" as user \"b\".\npostgres=> select count(*) from a;\n count \n-------\n     0\n(1 row)\npostgres=> select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'b';\n relname | rolname \n---------+---------\n d       | b\n i4      | b\n c       | b\n i3      | b\n b       | b\n i2      | b\n a       | b\n i1      | b\n(8 rows)\n<\/pre>\n
Cool \ud83d\ude42 There is also a command to drop all objects of a user:<\/p>\n
\npostgres=> drop owned by b;\nDROP OWNED\npostgres=> select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'b';\n relname | rolname \n---------+---------\n(0 rows)\n<\/pre>\n
Nice …<\/p>\n","protected":false},"excerpt":{"rendered":"
From time to time it might be required to move objects (tables, indexes, whatever) from one user to another user in a database system. You could do that by dumping all the objects with pg_dump and then load it to the target user. But there are other solutions which are faster than that. Lets go.<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-10387","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nRe-assigning all objects from on role to another in PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Re-assigning all objects from on role to another in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"From time to time it might be required to move objects (tables, indexes, whatever) from one user to another user in a database system. You could do that by dumping all the objects with pg_dump and then load it to the target user. But there are other solutions which are faster than that. Lets go.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-07-31T07:07:48+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Re-assigning all objects from on role to another in PostgreSQL\",\"datePublished\":\"2017-07-31T07:07:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/\"},\"wordCount\":243,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/\",\"name\":\"Re-assigning all objects from on role to another in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-07-31T07:07:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Re-assigning all objects from on role to another in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Re-assigning all objects from on role to another in PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Re-assigning all objects from on role to another in PostgreSQL","og_description":"From time to time it might be required to move objects (tables, indexes, whatever) from one user to another user in a database system. You could do that by dumping all the objects with pg_dump and then load it to the target user. But there are other solutions which are faster than that. Lets go.","og_url":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2017-07-31T07:07:48+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Re-assigning all objects from on role to another in PostgreSQL","datePublished":"2017-07-31T07:07:48+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/"},"wordCount":243,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/","name":"Re-assigning all objects from on role to another in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-07-31T07:07:48+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/re-assigning-all-objects-from-on-role-to-another-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Re-assigning all objects from on role to another in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10387","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10387"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10387\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10387"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10387"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10387"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10387"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}