pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTrying to change the owner of some tables
12+ messages / 6 participants
[nested] [flat]
* Trying to change the owner of some tables
@ 2015-06-25 02:44 John Scalia <jayknowsunix@gmail.com>
2015-06-25 03:00 ` Re: Trying to change the owner of some tables Alvaro Herrera <alvherre@2ndquadrant.com>
2015-06-25 03:01 ` Re: Trying to change the owner of some tables David G. Johnston <david.g.johnston@gmail.com>
2015-06-25 13:45 ` Re: Trying to change the owner of some tables Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 12+ messages in thread
From: John Scalia @ 2015-06-25 02:44 UTC (permalink / raw)
To: pgsql-admin
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.
Any assistance would be gratefully accepted,
Jay
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
@ 2015-06-25 03:00 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-06-25 17:33 ` Re: Trying to change the owner of some tables Jerry Sievers <gsievers19@comcast.net>
2 siblings, 1 reply; 12+ messages in thread
From: Alvaro Herrera @ 2015-06-25 03:00 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 03:00 ` Re: Trying to change the owner of some tables Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2015-06-25 17:33 ` Jerry Sievers <gsievers19@comcast.net>
2015-06-25 17:42 ` Re: Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Jerry Sievers @ 2015-06-25 17:33 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 03:00 ` Re: Trying to change the owner of some tables Alvaro Herrera <alvherre@2ndquadrant.com>
2015-06-25 17:33 ` Re: Trying to change the owner of some tables Jerry Sievers <gsievers19@comcast.net>
@ 2015-06-25 17:42 ` John Scalia <jayknowsunix@gmail.com>
2015-06-25 18:12 ` Re: Trying to change the owner of some tables David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: John Scalia @ 2015-06-25 17:42 UTC (permalink / raw)
To: Jerry Sievers <gsievers19@comcast.net>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 03:00 ` Re: Trying to change the owner of some tables Alvaro Herrera <alvherre@2ndquadrant.com>
2015-06-25 17:33 ` Re: Trying to change the owner of some tables Jerry Sievers <gsievers19@comcast.net>
2015-06-25 17:42 ` Re: Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
@ 2015-06-25 18:12 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: David G. Johnston @ 2015-06-25 18:12 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Jerry Sievers <gsievers19@comcast.net>; Alvaro Herrera <alvherre@2ndquadrant.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
@ 2015-06-25 03:01 ` David G. Johnston <david.g.johnston@gmail.com>
2015-06-25 03:42 ` Re: Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2015-06-25 03:01 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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...
http://www.postgresql.org/docs/9.4/static/sql-reassign-owned.html
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 03:01 ` Re: Trying to change the owner of some tables David G. Johnston <david.g.johnston@gmail.com>
@ 2015-06-25 03:42 ` John Scalia <jayknowsunix@gmail.com>
2015-06-25 08:17 ` Re: Trying to change the owner of some tables Guillaume Lelarge <guillaume@lelarge.info>
2015-06-25 15:10 ` Re: Trying to change the owner of some tables Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 2 replies; 12+ messages in thread
From: John Scalia @ 2015-06-25 03:42 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
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 <mailto: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...
>
> http://www.postgresql.org/docs/9.4/static/sql-reassign-owned.html
>
> David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 03:01 ` Re: Trying to change the owner of some tables David G. Johnston <david.g.johnston@gmail.com>
2015-06-25 03:42 ` Re: Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
@ 2015-06-25 08:17 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-06-25 14:21 ` Re: Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Guillaume Lelarge @ 2015-06-25 08:17 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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...
>
> http://www.postgresql.org/docs/9.4/static/sql-reassign-owned.html
>
> David J.
>
>
>
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 03:01 ` Re: Trying to change the owner of some tables David G. Johnston <david.g.johnston@gmail.com>
2015-06-25 03:42 ` Re: Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 08:17 ` Re: Trying to change the owner of some tables Guillaume Lelarge <guillaume@lelarge.info>
@ 2015-06-25 14:21 ` John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: John Scalia @ 2015-06-25 14:21 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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...
>>
>> http://www.postgresql.org/docs/9.4/static/sql-reassign-owned.html
>>
>> David J.
>>
>>
>>
>
>
> --
> Guillaume.
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 03:01 ` Re: Trying to change the owner of some tables David G. Johnston <david.g.johnston@gmail.com>
2015-06-25 03:42 ` Re: Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
@ 2015-06-25 15:10 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-06-25 15:55 ` Re: Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Alvaro Herrera @ 2015-06-25 15:10 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 03:01 ` Re: Trying to change the owner of some tables David G. Johnston <david.g.johnston@gmail.com>
2015-06-25 03:42 ` Re: Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 15:10 ` Re: Trying to change the owner of some tables Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2015-06-25 15:55 ` John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: John Scalia @ 2015-06-25 15:55 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trying to change the owner of some tables
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
@ 2015-06-25 13:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2015-06-25 13:45 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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 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.
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2015-06-25 18:12 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-06-25 02:44 Trying to change the owner of some tables John Scalia <jayknowsunix@gmail.com>
2015-06-25 03:00 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-06-25 17:33 ` Jerry Sievers <gsievers19@comcast.net>
2015-06-25 17:42 ` John Scalia <jayknowsunix@gmail.com>
2015-06-25 18:12 ` David G. Johnston <david.g.johnston@gmail.com>
2015-06-25 03:01 ` David G. Johnston <david.g.johnston@gmail.com>
2015-06-25 03:42 ` John Scalia <jayknowsunix@gmail.com>
2015-06-25 08:17 ` Guillaume Lelarge <guillaume@lelarge.info>
2015-06-25 14:21 ` John Scalia <jayknowsunix@gmail.com>
2015-06-25 15:10 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-06-25 15:55 ` John Scalia <jayknowsunix@gmail.com>
2015-06-25 13:45 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox