Thread: Trying to change the owner of some tables

Trying to change the owner of some tables

From
John Scalia
Date:
Hi all,

I'm trying to build a new server from a copy of one of our live Dbs, and I imported the schema from there and am now
tryingto get this new server setup with the right ownership 
and permissions. All the tables are/were owned by user A, and I've changed most of them to user B (names changed to
protectthe innocent, etc.,) However, some tables from the 
pg_dump I used to grab the schema, do not show up using \d nor can I see them in pg_class. I only found them when I
triedto drop user A and psql complained. They appear to be in a 
different schema and I could change them one at a time, but there are more than 2200 of these. For the tables I've
alreadychanged, I just performed an update on pg_class where 
relowner = numeric ID of user A to set that to the numeric ID of user B.

Now, this is a 9.2 server on CentOS, but I've not seen this behavior anywhere before. Where else should I see these?
Theonly success I've had is \d+ new_schema.* and that doesn't 
help me change them.

Any assistance would be gratefully accepted,
Jay



Re: Trying to change the owner of some tables

From
Alvaro Herrera
Date:
John Scalia wrote:
> Hi all,
>
> I'm trying to build a new server from a copy of one of our live Dbs, and I imported the schema from there and am now
tryingto get this new server setup with the right ownership 
> and permissions. All the tables are/were owned by user A, and I've changed most of them to user B (names changed to
protectthe innocent, etc.,) However, some tables from the 
> pg_dump I used to grab the schema, do not show up using \d nor can I see them in pg_class. I only found them when I
triedto drop user A and psql complained. They appear to be in a 
> different schema and I could change them one at a time, but there are more than 2200 of these. For the tables I've
alreadychanged, I just performed an update on pg_class where 
> relowner = numeric ID of user A to set that to the numeric ID of user B.

You could try the REASSIGN OWNED BY command.

Doing manual updates of relowner might not be the greatest idea ever; if
any of these tables have grants, the representation of the grantor might
be borked after that.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: Trying to change the owner of some tables

From
"David G. Johnston"
Date:
On Wed, Jun 24, 2015 at 10:44 PM, John Scalia <jayknowsunix@gmail.com> wrote:
Hi all,

I'm trying to build a new server from a copy of one of our live Dbs, and I imported the schema from there and am now trying to get this new server setup with the right ownership
and permissions. All the tables are/were owned by user A, and I've changed most of them to user B (names changed to protect the innocent, etc.,) However, some tables from the
pg_dump I used to grab the schema, do not show up using \d nor can I see them in pg_class. I only found them when I tried to drop user A and psql complained. They appear to be in a
different schema and I could change them one at a time, but there are more than 2200 of these. For the tables I've already changed, I just performed an update on pg_class where
relowner = numeric ID of user A to set that to the numeric ID of user B.

Now, this is a 9.2 server on CentOS, but I've not seen this behavior anywhere before. Where else should I see these? The only success I've had is \d+ new_schema.* and that doesn't
help me change them.

​Possibly this...

David J.

Re: Trying to change the owner of some tables

From
John Scalia
Date:
Thanks guys, I was not aware of that command, but it did NOT succeed in changing these strangely missing tables. The tables appear, when I try to drop the old owner as:

second_schema.partition_table_name_one;

then two, three, etc.,  I've only been successful using

alter table second_schema.partition_table_name_one owner to userB;

But I'd rather not do that for 2000+ entries.

On 6/24/2015 8:01 PM, David G. Johnston wrote:
On Wed, Jun 24, 2015 at 10:44 PM, John Scalia <jayknowsunix@gmail.com> wrote:
Hi all,

I'm trying to build a new server from a copy of one of our live Dbs, and I imported the schema from there and am now trying to get this new server setup with the right ownership
and permissions. All the tables are/were owned by user A, and I've changed most of them to user B (names changed to protect the innocent, etc.,) However, some tables from the
pg_dump I used to grab the schema, do not show up using \d nor can I see them in pg_class. I only found them when I tried to drop user A and psql complained. They appear to be in a
different schema and I could change them one at a time, but there are more than 2200 of these. For the tables I've already changed, I just performed an update on pg_class where
relowner = numeric ID of user A to set that to the numeric ID of user B.

Now, this is a 9.2 server on CentOS, but I've not seen this behavior anywhere before. Where else should I see these? The only success I've had is \d+ new_schema.* and that doesn't
help me change them.

​ Possibly this...

David J.

Re: Trying to change the owner of some tables

From
Guillaume Lelarge
Date:


2015-06-25 5:42 GMT+02:00 John Scalia <jayknowsunix@gmail.com>:
Thanks guys, I was not aware of that command, but it did NOT succeed in changing these strangely missing tables. The tables appear, when I try to drop the old owner as:

second_schema.partition_table_name_one;

then two, three, etc.,  I've only been successful using

alter table second_schema.partition_table_name_one owner to userB;

But I'd rather not do that for 2000+ entries.


The only reason that would explain why you can't see them with \d and in pg_class is that they are on another database. Same cluster but another database. You should connect to the other databases and use REASSIGN OWNED in each of them.
 

On 6/24/2015 8:01 PM, David G. Johnston wrote:
On Wed, Jun 24, 2015 at 10:44 PM, John Scalia <jayknowsunix@gmail.com> wrote:
Hi all,

I'm trying to build a new server from a copy of one of our live Dbs, and I imported the schema from there and am now trying to get this new server setup with the right ownership
and permissions. All the tables are/were owned by user A, and I've changed most of them to user B (names changed to protect the innocent, etc.,) However, some tables from the
pg_dump I used to grab the schema, do not show up using \d nor can I see them in pg_class. I only found them when I tried to drop user A and psql complained. They appear to be in a
different schema and I could change them one at a time, but there are more than 2200 of these. For the tables I've already changed, I just performed an update on pg_class where
relowner = numeric ID of user A to set that to the numeric ID of user B.

Now, this is a 9.2 server on CentOS, but I've not seen this behavior anywhere before. Where else should I see these? The only success I've had is \d+ new_schema.* and that doesn't
help me change them.

​ Possibly this...

David J.




--

Re: Trying to change the owner of some tables

From
Tom Lane
Date:
John Scalia <jayknowsunix@gmail.com> writes:
> I'm trying to build a new server from a copy of one of our live Dbs, and I imported the schema from there and am now
tryingto get this new server setup with the right ownership 
> and permissions. All the tables are/were owned by user A, and I've changed most of them to user B (names changed to
protectthe innocent, etc.,) However, some tables from the 
> pg_dump I used to grab the schema, do not show up using \d nor can I see them in pg_class. I only found them when I
triedto drop user A and psql complained. They appear to be in a 
> different schema and I could change them one at a time, but there are more than 2200 of these. For the tables I've
alreadychanged, I just performed an update on pg_class where 
> relowner = numeric ID of user A to set that to the numeric ID of user B.

That was a seriously bad idea.

The problem that you now have is that pg_class.relowner is out of sync
with the ownership data in pg_shdepend.  As Alvaro said, it's also going
to be out of sync with pg_class.relacl, if any of those tables had
explicit GRANTs.

REASSIGN OWNED, by itself, will not fix this situation since it relies on
pg_class.relowner to decide which objects need to be reassigned.  What you
need to do is undo that manual catalog hacking and then use REASSIGN OWNED
to get to where you wanted to be.

If you don't remember exactly which tables you changed manually, you could
probably do a join against pg_shdepend to find them: look for tables that
have a matching pg_shdepend row but the relowner doesn't match the
refobjid.

            regards, tom lane


Re: Trying to change the owner of some tables

From
John Scalia
Date:
Good thought Guillaume, but the only Dbs on this cluster are:

The one I'm working in
postgres
template0
template1

and that's it. Thinking these errant tables might have got created in the postgres db, I did a \c postgres, then did \d in there. It came back with no relations found.

On Thu, Jun 25, 2015 at 1:17 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote:


2015-06-25 5:42 GMT+02:00 John Scalia <jayknowsunix@gmail.com>:
Thanks guys, I was not aware of that command, but it did NOT succeed in changing these strangely missing tables. The tables appear, when I try to drop the old owner as:

second_schema.partition_table_name_one;

then two, three, etc.,  I've only been successful using

alter table second_schema.partition_table_name_one owner to userB;

But I'd rather not do that for 2000+ entries.


The only reason that would explain why you can't see them with \d and in pg_class is that they are on another database. Same cluster but another database. You should connect to the other databases and use REASSIGN OWNED in each of them.
 

On 6/24/2015 8:01 PM, David G. Johnston wrote:
On Wed, Jun 24, 2015 at 10:44 PM, John Scalia <jayknowsunix@gmail.com> wrote:
Hi all,

I'm trying to build a new server from a copy of one of our live Dbs, and I imported the schema from there and am now trying to get this new server setup with the right ownership
and permissions. All the tables are/were owned by user A, and I've changed most of them to user B (names changed to protect the innocent, etc.,) However, some tables from the
pg_dump I used to grab the schema, do not show up using \d nor can I see them in pg_class. I only found them when I tried to drop user A and psql complained. They appear to be in a
different schema and I could change them one at a time, but there are more than 2200 of these. For the tables I've already changed, I just performed an update on pg_class where
relowner = numeric ID of user A to set that to the numeric ID of user B.

Now, this is a 9.2 server on CentOS, but I've not seen this behavior anywhere before. Where else should I see these? The only success I've had is \d+ new_schema.* and that doesn't
help me change them.

​ Possibly this...

David J.




--

Re: Trying to change the owner of some tables

From
Alvaro Herrera
Date:
John Scalia wrote:
> Thanks guys, I was not aware of that command, but it did NOT succeed in changing these strangely missing tables. The
tablesappear, when I try to drop the old owner as: 
>
> second_schema.partition_table_name_one;
>
> then two, three, etc.,  I've only been successful using
>
> alter table second_schema.partition_table_name_one owner to userB;
>
> But I'd rather not do that for 2000+ entries.

I think the current situation is that the pg_class entries point to one
user, but the pg_shdepend entries point to the original owner.  That is
an example case of catalog "corruption" -- or rather inconsistency.

I think what you should do is update pg_class back to the original
state, and then apply REASSIGN OWNED BY.  If the new owner had tables
other than those owned by the one you're trying to delete, this would be
pretty complicated, though -- in this case you should probably compare
the pg_shdepend entries to find out which tables were owned by the other
user, so that you know which ones to update.  This should doable, if a
bit tricky.

Hopefully this episode demonstrates the folly of updating catalogs "by
hand".  I suggest never to do that again, and if somebody else tells you
to, point them here.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: Trying to change the owner of some tables

From
John Scalia
Date:
I actually did one step better than that. I dropped the database and recreated it and then imported the schema again. Working much better since then, but I'm still having some issues with dropping a group role which is telling me that the role has privileges for over 4000 objects currently. I tried querying all the schemas and doing a revoke all privileges on each schema for this role, but it's still arguing with me.

On Thu, Jun 25, 2015 at 8:10 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
John Scalia wrote:
> Thanks guys, I was not aware of that command, but it did NOT succeed in changing these strangely missing tables. The tables appear, when I try to drop the old owner as:
>
> second_schema.partition_table_name_one;
>
> then two, three, etc.,  I've only been successful using
>
> alter table second_schema.partition_table_name_one owner to userB;
>
> But I'd rather not do that for 2000+ entries.

I think the current situation is that the pg_class entries point to one
user, but the pg_shdepend entries point to the original owner.  That is
an example case of catalog "corruption" -- or rather inconsistency.

I think what you should do is update pg_class back to the original
state, and then apply REASSIGN OWNED BY.  If the new owner had tables
other than those owned by the one you're trying to delete, this would be
pretty complicated, though -- in this case you should probably compare
the pg_shdepend entries to find out which tables were owned by the other
user, so that you know which ones to update.  This should doable, if a
bit tricky.

Hopefully this episode demonstrates the folly of updating catalogs "by
hand".  I suggest never to do that again, and if somebody else tells you
to, point them here.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Trying to change the owner of some tables

From
Jerry Sievers
Date:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:

> John Scalia wrote:
>
>> Hi all,
>>
>> I'm trying to build a new server from a copy of one of our live Dbs, and I imported the schema from there and am now
tryingto get this new server setup with the right ownership 
>> and permissions. All the tables are/were owned by user A, and I've changed most of them to user B (names changed to
protectthe innocent, etc.,) However, some tables from the 
>> pg_dump I used to grab the schema, do not show up using \d nor can I see them in pg_class. I only found them when I
triedto drop user A and psql complained. They appear to be in a 
>> different schema and I could change them one at a time, but there are more than 2200 of these. For the tables I've
alreadychanged, I just performed an update on pg_class where 
>> relowner = numeric ID of user A to set that to the numeric ID of user B.
>
> You could try the REASSIGN OWNED BY command.
>
> Doing manual updates of relowner might not be the greatest idea ever; if
> any of these tables have grants, the representation of the grantor might
> be borked after that.

And I wonder about dependency records too?

>
> --
> Álvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


Re: Trying to change the owner of some tables

From
John Scalia
Date:
Everything has been corrected. The newly reloaded database is behaving much better. I did manage to get all my permission issues satisfied, although I have a followup question: For the two roles I wished to delete, I issued a revoke all privileges on each schema in the DB with a cascade option, but if I looked at table, relname in pg_class, I would still see that role as having select, update, and delete for the role's permission. Why wouldn't those be revoked when doing a revoke all privileges? Did I miss something?

On Thu, Jun 25, 2015 at 10:33 AM, Jerry Sievers <gsievers19@comcast.net> wrote:
Alvaro Herrera <alvherre@2ndquadrant.com> writes:

> John Scalia wrote:
>
>> Hi all,
>>
>> I'm trying to build a new server from a copy of one of our live Dbs, and I imported the schema from there and am now trying to get this new server setup with the right ownership
>> and permissions. All the tables are/were owned by user A, and I've changed most of them to user B (names changed to protect the innocent, etc.,) However, some tables from the
>> pg_dump I used to grab the schema, do not show up using \d nor can I see them in pg_class. I only found them when I tried to drop user A and psql complained. They appear to be in a
>> different schema and I could change them one at a time, but there are more than 2200 of these. For the tables I've already changed, I just performed an update on pg_class where
>> relowner = numeric ID of user A to set that to the numeric ID of user B.
>
> You could try the REASSIGN OWNED BY command.
>
> Doing manual updates of relowner might not be the greatest idea ever; if
> any of these tables have grants, the representation of the grantor might
> be borked after that.

And I wonder about dependency records too?

>
> --
> Álvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

Re: Trying to change the owner of some tables

From
"David G. Johnston"
Date:
On Thu, Jun 25, 2015 at 1:42 PM, John Scalia <jayknowsunix@gmail.com> wrote:
Everything has been corrected. The newly reloaded database is behaving much better. I did manage to get all my permission issues satisfied, although I have a followup question: For the two roles I wished to delete, I issued a revoke all privileges on each schema in the DB with a cascade option, but if I looked at table, relname in pg_class, I would still see that role as having select, update, and delete for the role's permission. Why wouldn't those be revoked when doing a revoke all privileges? Did I miss something?

Some permissions are inherited via PUBLIC and thus must be revoked by revoking them from PUBLIC and not the user inheriting them.  Revoke revokes stuff, it does not add the equivalent of a "deny" permission.  All permissions are denied until explicitly or implicitly granted.

HTH

David J.