pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUpgrading from 9.2 -> 9.6: ERROR with 'could not execute query: ERROR: permission denied for type my_type_name'
4+ messages / 3 participants
[nested] [flat]
* Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query: ERROR: permission denied for type my_type_name'
@ 2017-12-01 02:43 Fehrle, Brian <bfehrle@comscore.com>
0 siblings, 2 replies; 4+ messages in thread
From: Fehrle, Brian @ 2017-12-01 02:43 UTC (permalink / raw)
To: pgsql-admin; +Cc: @ DBA - West <dba-west@comscore.com>
Hi all,
I'm upgrading a database from 9.2 to 9.6 and I'm running into an interesting error. I've upgraded dozens of very similar databases already in the same configuration, but each database has different schema definitions, and this is the first one with an error like this that we've seen.
The error for this one is here from upgrade log:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 214183; 0 0 ACL my_type_name some_user
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for type my_type_name
Command was: REVOKE ALL ON TYPE "my_type_name" FROM PUBLIC;
REVOKE ALL ON TYPE "my_type_name" FROM "some_user";
SET SESSION AUTHORIZATION ...
My upgrade command itself is pretty standard:
/usr/pgsql-9.6/bin/pg_upgrade -k --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/mnt/db/data.old/ --new-datadir=/mnt/db/data/
I'm running the upgrade as the linux user 'postgres' as well, and verified with the -v (verbose) command that everything in the upgrade is running as the superuser 'postgres'. In wonder as to why I could be getting this error, I did a pg_dump -s (schema only) of the database to be upgraded, and here's the permission section for this type:
REVOKE ALL ON TYPE my_type_name FROM PUBLIC;
REVOKE ALL ON TYPE my_type_name FROM some_user;
SET SESSION AUTHORIZATION some_other_user;
GRANT ALL ON TYPE my_type_name TO PUBLIC;
RESET SESSION AUTHORIZATION;
And here is the \dT+ of the type:
my_database=> \dT+ my_type_name
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
--------+--------------+---------------+-------+----------+------------+--------------------+-------------
public | my_type_name | my_type_name | tuple | | some_user | =U/some_other_user |
(1 row)
Using verbose output, the exact command that pg_restore is running is:
"/usr/pgsql-9.6/bin/pg_restore" --host '/home/postgres' --port 50432 --username 'postgres' --exit-on-error --verbose --dbname 'dbname=my_database' "pg_upgrade_dump_208717.custom" >> "pg_upgrade_dump_208717.log" 2>&1
I've tried revoking all permissions from PUBLIC and all actual users associated with this type before upgrade, and no matter what, results in the same error. This is 100% reproducible.
Anyone know of anything I may be missing? I don't see how the superuser 'postgres' has any permission denied issues.
Extra Details:
CentOS release 6.9 (Final)
psql (PostgreSQL) 9.2.24 via PGDG RPM
psql (PostgreSQL) 9.6.5 via PGDG RPM
Brian Fehrle Database Administrator II | comScore, Inc.
bfehrle@comscore.com
comscore.com
This e-mail (including any attachments) may contain information that is private, confidential, or protected by attorney-client or other privilege. If you received this e-mail in error, please delete it from your system and notify sender.
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query:ERROR: permission denied for type my_type_name'
@ 2017-12-01 02:50 Jorge Torralba <jorge.torralba@gmail.com>
parent: Fehrle, Brian <bfehrle@comscore.com>
1 sibling, 1 reply; 4+ messages in thread
From: Jorge Torralba @ 2017-12-01 02:50 UTC (permalink / raw)
To: Fehrle, Brian <bfehrle@comscore.com>; pgsql-admin; +Cc: @ DBA - West <dba-west@comscore.com>
Is “some_user” the actual username or are you just masking it for the email?
Does the real user start with pg_ ?
Sent from Mail for Windows 10
From: Fehrle, Brian
Sent: Thursday, November 30, 2017 6:43 PM
To: pgsql-admin@postgresql.org
Cc: @ DBA - West
Subject: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query:ERROR: permission denied for type my_type_name'
Hi all,
I'm upgrading a database from 9.2 to 9.6 and I'm running into an interesting error. I've upgraded dozens of very similar databases already in the same configuration, but each database has different schema definitions, and this is the first one with an error like this that we've seen.
The error for this one is here from upgrade log:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 214183; 0 0 ACL my_type_name some_user
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for type my_type_name
Command was: REVOKE ALL ON TYPE "my_type_name" FROM PUBLIC;
REVOKE ALL ON TYPE "my_type_name" FROM "some_user";
SET SESSION AUTHORIZATION ...
My upgrade command itself is pretty standard:
/usr/pgsql-9.6/bin/pg_upgrade -k --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/mnt/db/data.old/ --new-datadir=/mnt/db/data/
I'm running the upgrade as the linux user 'postgres' as well, and verified with the -v (verbose) command that everything in the upgrade is running as the superuser 'postgres'. In wonder as to why I could be getting this error, I did a pg_dump -s (schema only) of the database to be upgraded, and here's the permission section for this type:
REVOKE ALL ON TYPE my_type_name FROM PUBLIC;
REVOKE ALL ON TYPE my_type_name FROM some_user;
SET SESSION AUTHORIZATION some_other_user;
GRANT ALL ON TYPE my_type_name TO PUBLIC;
RESET SESSION AUTHORIZATION;
And here is the \dT+ of the type:
my_database=> \dT+ my_type_name
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
--------+--------------+---------------+-------+----------+------------+--------------------+-------------
public | my_type_name | my_type_name | tuple | | some_user | =U/some_other_user |
(1 row)
Using verbose output, the exact command that pg_restore is running is:
"/usr/pgsql-9.6/bin/pg_restore" --host '/home/postgres' --port 50432 --username 'postgres' --exit-on-error --verbose --dbname 'dbname=my_database' "pg_upgrade_dump_208717.custom" >> "pg_upgrade_dump_208717.log" 2>&1
I've tried revoking all permissions from PUBLIC and all actual users associated with this type before upgrade, and no matter what, results in the same error. This is 100% reproducible.
Anyone know of anything I may be missing? I don't see how the superuser 'postgres' has any permission denied issues.
Extra Details:
CentOS release 6.9 (Final)
psql (PostgreSQL) 9.2.24 via PGDG RPM
psql (PostgreSQL) 9.6.5 via PGDG RPM
Brian
Fehrle
Database Administrator II
|
comScore, Inc.
bfehrle@comscore.com
comscore.com
This e-mail (including any attachments) may contain information that is private, confidential, or protected by attorney-client or other privilege. If you received this e-mail in error, please delete it from your system and notify sender.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query:ERROR: permission denied for type my_type_name'
@ 2017-12-01 07:22 Fehrle, Brian <bfehrle@comscore.com>
parent: Jorge Torralba <jorge.torralba@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Fehrle, Brian @ 2017-12-01 07:22 UTC (permalink / raw)
To: Jorge Torralba <jorge.torralba@gmail.com>; pgsql-admin; +Cc: @ DBA - West <dba-west@comscore.com>
It's masked for email. The real use doesn't start with pg_, they are custom users for internal use.
________________________________
Brian Fehrle Database Administrator II | comScore, Inc.
bfehrle@comscore.com
comscore.com
This e-mail (including any attachments) may contain information that is private, confidential, or protected by attorney-client or other privilege. If you received this e-mail in error, please delete it from your system and notify sender.
From: Jorge Torralba <jorge.torralba@gmail.com>
Sent: Thursday, November 30, 2017 7:50:22 PM
To: Fehrle, Brian; pgsql-admin@postgresql.org
Cc: @ DBA - West
Subject: RE: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query:ERROR: permission denied for type my_type_name'
Is “some_user” the actual username or are you just masking it for the email?
Does the real user start with pg_ ?
Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986; for Windows 10
From: Fehrle, Brian<mailto:bfehrle@comscore.com>
Sent: Thursday, November 30, 2017 6:43 PM
To: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
Cc: @ DBA - West<mailto:dba-west@comscore.com>
Subject: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query:ERROR: permission denied for type my_type_name'
Hi all,
I'm upgrading a database from 9.2 to 9.6 and I'm running into an interesting error. I've upgraded dozens of very similar databases already in the same configuration, but each database has different schema definitions, and this is the first one with an error like this that we've seen.
The error for this one is here from upgrade log:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 214183; 0 0 ACL my_type_name some_user
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for type my_type_name
Command was: REVOKE ALL ON TYPE "my_type_name" FROM PUBLIC;
REVOKE ALL ON TYPE "my_type_name" FROM "some_user";
SET SESSION AUTHORIZATION ...
My upgrade command itself is pretty standard:
/usr/pgsql-9.6/bin/pg_upgrade -k --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/mnt/db/data.old/ --new-datadir=/mnt/db/data/
I'm running the upgrade as the linux user 'postgres' as well, and verified with the -v (verbose) command that everything in the upgrade is running as the superuser 'postgres'. In wonder as to why I could be getting this error, I did a pg_dump -s (schema only) of the database to be upgraded, and here's the permission section for this type:
REVOKE ALL ON TYPE my_type_name FROM PUBLIC;
REVOKE ALL ON TYPE my_type_name FROM some_user;
SET SESSION AUTHORIZATION some_other_user;
GRANT ALL ON TYPE my_type_name TO PUBLIC;
RESET SESSION AUTHORIZATION;
And here is the \dT+ of the type:
my_database=> \dT+ my_type_name
List of data types
Schema | Name | Internal name | Size | Elements | Owner | Access privileges | Description
--------+--------------+---------------+-------+----------+------------+--------------------+-------------
public | my_type_name | my_type_name | tuple | | some_user | =U/some_other_user |
(1 row)
Using verbose output, the exact command that pg_restore is running is:
"/usr/pgsql-9.6/bin/pg_restore" --host '/home/postgres' --port 50432 --username 'postgres' --exit-on-error --verbose --dbname 'dbname=my_database' "pg_upgrade_dump_208717.custom" >> "pg_upgrade_dump_208717.log" 2>&1
I've tried revoking all permissions from PUBLIC and all actual users associated with this type before upgrade, and no matter what, results in the same error. This is 100% reproducible.
Anyone know of anything I may be missing? I don't see how the superuser 'postgres' has any permission denied issues.
Extra Details:
CentOS release 6.9 (Final)
psql (PostgreSQL) 9.2.24 via PGDG RPM
psql (PostgreSQL) 9.6.5 via PGDG RPM
Brian
Fehrle
Database Administrator II
|
comScore, Inc.
bfehrle@comscore.com<mailto:bfehrle@comscore.com>
comscore.com<http://www.comscore.com/;
This e-mail (including any attachments) may contain information that is private, confidential, or protected by attorney-client or other privilege. If you received this e-mail in error, please delete it from your system and notify sender.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query: ERROR: permission denied for type my_type_name'
@ 2017-12-01 15:20 Jerry Sievers <gsievers19@comcast.net>
parent: Fehrle, Brian <bfehrle@comscore.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jerry Sievers @ 2017-12-01 15:20 UTC (permalink / raw)
To: Fehrle\, Brian <bfehrle@comscore.com>; +Cc: pgsql-admin; \@ DBA - West <dba-west@comscore.com>
"Fehrle, Brian" <bfehrle@comscore.com> writes:
> Hi all,
>
>
> I'm upgrading a database from 9.2 to 9.6 and I'm running into an
> interesting error. I've upgraded dozens of very similar databases
> already in the same configuration, but each database has different
> schema definitions, and this is the first one with an error like this
> that we've seen.
Couple things...
I remember a vaguely similar issue we had also going up from some older
version such as 9.2 and it was due to IIRC a bad grantor aspect in the
ACL entry.
The fix was to manually update same in the related catalog entry (see
warning below).
Look further up in the SQL script pg_dump is trying to run to see just
what session auth is set to at the time of failure because is it about
certainly not postgres or any superuser else you would not get the
perm denied error.
I'd suggest you do this looking into the actual pg_dump result that came
from pg_upgrade since it runs pg_dump with an undocumented
--binary-upgrade option which changes a few things, in case this
matters.
!!Please do *not* perform the aforementioned catalog updates without
verifying that there is no other solution.
HTH
>
>
> The error for this one is here from upgrade log:
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 214183; 0 0 ACL
> my_type_name some_user
> pg_restore: [archiver (db)] could not execute query: ERROR:
> permission denied for type my_type_name
> Command was: REVOKE ALL ON TYPE "my_type_name" FROM PUBLIC;
> REVOKE ALL ON TYPE "my_type_name" FROM "some_user";
> SET SESSION AUTHORIZATION ...
>
>
> My upgrade command itself is pretty standard:
> /usr/pgsql-9.6/bin/pg_upgrade -k --old-bindir=/usr/pgsql-9.2/bin/
> --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/mnt/db/data.old/
> --new-datadir=/mnt/db/data/
>
>
> I'm running the upgrade as the linux user 'postgres' as well, and
> verified with the -v (verbose) command that everything in the upgrade
> is running as the superuser 'postgres'. In wonder as to why I could
> be getting this error, I did a pg_dump -s (schema only) of the
> database to be upgraded, and here's the permission section for this
> type:
>
> REVOKE ALL ON TYPE my_type_name FROM PUBLIC;
> REVOKE ALL ON TYPE my_type_name FROM some_user;
> SET SESSION AUTHORIZATION some_other_user;
> GRANT ALL ON TYPE my_type_name TO PUBLIC;
> RESET SESSION AUTHORIZATION;
>
> And here is the \dT+ of the type:
>
> my_database=> \dT+ my_type_name
> List of data types
> Schema | Name | Internal name | Size | Elements | Owner
> | Access privileges | Description
> --------+--------------+---------------+-------+----------+------------+--------------------+-------------
> public | my_type_name | my_type_name | tuple | |
> some_user | =U/some_other_user |
> (1 row)
>
>
>
> Using verbose output, the exact command that pg_restore is running
> is:
>
> "/usr/pgsql-9.6/bin/pg_restore" --host '/home/postgres' --port 50432
> --username 'postgres' --exit-on-error --verbose --dbname 'dbname=
> my_database' "pg_upgrade_dump_208717.custom" >>
> "pg_upgrade_dump_208717.log" 2>&1
>
>
>
> I've tried revoking all permissions from PUBLIC and all actual users
> associated with this type before upgrade, and no matter what, results
> in the same error. This is 100% reproducible.
>
>
>
> Anyone know of anything I may be missing? I don't see how the
> superuser 'postgres' has any permission denied issues.
>
>
> Extra Details:
>
> CentOS release 6.9 (Final)
>
> psql (PostgreSQL) 9.2.24 via PGDG RPM
> psql (PostgreSQL) 9.6.5 via PGDG RPM
>
>
> Brian Fehrle Database Administrator II | comScore, Inc.
> bfehrle@comscore.com
> comscore.com
> This e-mail (including any attachments) may contain information
> that is private, confidential, or protected by attorney-client or
> other privilege. If you received this e-mail in error, please delete
> it from your system and notify sender.
>
>
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-12-01 15:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-12-01 02:43 Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query: ERROR: permission denied for type my_type_name' Fehrle, Brian <bfehrle@comscore.com>
2017-12-01 02:50 ` RE: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query:ERROR: permission denied for type my_type_name' Jorge Torralba <jorge.torralba@gmail.com>
2017-12-01 07:22 ` Re: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query:ERROR: permission denied for type my_type_name' Fehrle, Brian <bfehrle@comscore.com>
2017-12-01 15:20 ` Jerry Sievers <gsievers19@comcast.net>
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