Thread: Trying to change the owner of some tables
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
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
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.
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.
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:
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.
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: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.
--
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
Good thought Guillaume, but the only Dbs on this cluster are:
The one I'm working inOn 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: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.
--
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
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
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
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
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.