RE: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query:ERROR: permission denied for type my_type_name'

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx
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@xxxxxxxxxxxx

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.

 

 


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux