Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eKbPK-0000ke-F6 for pgsql-admin@arkaria.postgresql.org; Fri, 01 Dec 2017 02:50:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eKbPK-0007TL-10 for pgsql-admin@arkaria.postgresql.org; Fri, 01 Dec 2017 02:50:30 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eKbPJ-0007TB-JE for pgsql-admin@lists.postgresql.org; Fri, 01 Dec 2017 02:50:29 +0000 Received: from mail-pf0-x22f.google.com ([2607:f8b0:400e:c00::22f]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eKbPG-0001Zt-MA for pgsql-admin@postgresql.org; Fri, 01 Dec 2017 02:50:28 +0000 Received: by mail-pf0-x22f.google.com with SMTP id c204so4008930pfc.13 for ; Thu, 30 Nov 2017 18:50:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=message-id:mime-version:to:cc:from:subject:date:importance :thread-topic:in-reply-to:references; bh=wN/qE/tnTqh65m3/sgEHy/G9ZnGIDbPw8iSawrF5xCw=; b=vEJK5sl5H1Q2D7DEnmL7aUb4xdJWf03pJhMymt++te8+y4q3jL5XB0oIRor9pP3ygu DiYDaKbJHNPDtwMd4dH/2i1tYcam9xE8yy53R/7VE66wODfYKbhtjk0V59hSyCewjcjv lTyE2yv/6XmlERPJpLzU+WPs0eDtYR0U2z/6zZPZTol72UH6ytQrFZzmyfgeACbnlvzF JCjuD1ytGYGPUH9/dE20Oz32ssWFpqEbG/cuS851QCtINeiRkcjsgqBTK5FBWXGOY3T1 WOgHrHNsnieC4fZd4K4qfOOKoI4f1/5FstkzDfeXcquvD3ZJ3HeccVQG45rRLQik6c6a OYYg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:mime-version:to:cc:from:subject:date :importance:thread-topic:in-reply-to:references; bh=wN/qE/tnTqh65m3/sgEHy/G9ZnGIDbPw8iSawrF5xCw=; b=nQJm8GT+90Xx2rhqxdXdbiLb7AML3hRcq8IbkQXgsQBRTzqtbRie+TOPY8gfD/+KEw 48aHahG5gyW72ozggjHxJ3f5R6FnOZ5FW5TTd+4Yj7vSwIIhGIKBC7ApRaadnckSUM8Y 9rY8sMC1FrodLeD28oiMR1nr1IaVOPNoXGR6vV48V9T5bl8QlixubXsnUpXaqXpfZm+3 oKoD0SFQzizNRRilCYgcUjiAwChL+YucwxdeyMwe/UP2UoonUwVr+hnquJv4RCbU+pxI L9LnC0FZtvqpRr1JMWbluTuqW1f95Kk5Hz8msXZ8QXT2ffX0+hjs/rpqOOYSNG5wGVxa E/xQ== X-Gm-Message-State: AJaThX6eM5soC04qcBOGzi6RPaPFTXYWajx2wuaDk3jQ70YLqid0FWDY 0pWMN46dNA3kBhdHFPb/HxjRoFIP X-Google-Smtp-Source: AGs4zMawYX8Sk5051BEfsBr3C8LDMWR9Gzskh5fZ8csmp3ypfDw1Gr0nHqsMP7b0k9kSbGkLQr2BiA== X-Received: by 10.99.174.1 with SMTP id q1mr4344073pgf.434.1512096625125; Thu, 30 Nov 2017 18:50:25 -0800 (PST) Received: from ?IPv6:::ffff:192.168.1.24? (c-73-11-89-71.hsd1.or.comcast.net. [73.11.89.71]) by smtp.gmail.com with ESMTPSA id y1sm8945052pge.27.2017.11.30.18.50.23 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 30 Nov 2017 18:50:23 -0800 (PST) Message-ID: <5a20c36f.01b5630a.d9359.836b@mx.google.com> MIME-Version: 1.0 To: "Fehrle, Brian" , "pgsql-admin@postgresql.org" Cc: "@ DBA - West" From: Jorge Torralba Subject: RE: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query:ERROR: permission denied for type my_type_name' Date: Thu, 30 Nov 2017 18:50:22 -0800 Importance: normal X-Priority: 3 Thread-Topic: Upgrading from 9.2 -> 9.6: ERROR with 'could not execute query:ERROR: permission denied for type my_type_name' In-Reply-To: References: Content-Type: multipart/alternative; boundary="_8193F9A8-11E9-473D-AA4D-9307C0C6A53B_" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_8193F9A8-11E9-473D-AA4D-9307C0C6A53B_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" Is =E2=80=9Csome_user=E2=80=9D 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:ERR= OR: permission denied for type my_type_name' Hi all,=C2=A0 I'm upgrading a database from 9.2 to 9.6 and I'm running into an interestin= g error. I've upgraded dozens of very similar databases already in the same= configuration, but each database has different schema definitions, and thi= s is the first one with an error like this that we've seen.=C2=A0 The error for this one is here from=C2=A0upgrade log: pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 214183; 0 0 ACL my_type_na= me some_user pg_restore: [archiver (db)] could not execute query: ERROR:=C2=A0 permissio= n denied for type my_type_name =C2=A0 =C2=A0 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=C2=A0is pretty standard: /usr/pgsql-9.6/bin/pg_upgrade -k --old-bindir=3D/usr/pgsql-9.2/bin/ --new-b= indir=3D/usr/pgsql-9.6/bin/ --old-datadir=3D/mnt/db/data.old/ --new-datadir= =3D/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'.=C2=A0In 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=3D> \dT+ my_type_name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0List of data types =C2=A0Schema |=C2=A0 =C2=A0 =C2=A0Name=C2=A0 =C2=A0 =C2=A0| Internal name |= Size=C2=A0 | Elements |=C2=A0 =C2=A0Owner=C2=A0 =C2=A0 | Access privileges= =C2=A0 | Description --------+--------------+---------------+-------+----------+------------+---= -----------------+------------- =C2=A0public | my_type_name | my_type_name=C2=A0 | tuple |=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | some_user=C2=A0 | =3DU/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 --user= name 'postgres' --exit-on-error --verbose --dbname 'dbname=3Dmy_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 associ= ated with this type before upgrade, and no matter what, results in the same= error. This is 100% reproducible.=C2=A0 Anyone know of anything I may be missing? I don't see how the superuser 'po= stgres' has any permission denied issues.=C2=A0 Extra=C2=A0Details: CentOS release 6.9 (Final) psql (PostgreSQL) 9.2.24=C2=A0via PGDG RPM psql (PostgreSQL) 9.6.5=C2=A0via PGDG RPM=C2=A0=C2=A0 Brian=C2=A0=0DFehrle=0D=C2=A0Database=C2=A0Administrator=C2=A0II=0D=C2=A0|= =C2=A0=0DcomScore,=C2=A0Inc.=0D=0D=0Dbfehrle@comscore.com=0D=0D=0Dcomscore.= com=0D=0D=0D=E2=80=8B=E2=80=8B=E2=80=8BThis e-mail (including any attachmen= ts) may contain information that is private, confidential, or protected by = attorney-client or other privilege. If you received this e-mail in error, p= lease delete it from your system and notify sender.=0D=0D=0D=0D=0D=0D=C2=A0 --_8193F9A8-11E9-473D-AA4D-9307C0C6A53B_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"

Is =E2=80=9Csome_user=E2=80=9D the a= ctual username or are you just masking it for the email?

 

Does the real user start wi= th pg_=C2=A0 ?

 

 

 

Sent from Mail for Windows 10

 

From: = Fehrle, Brian
Sent: Thursday, November 30, 2017 6:43 PM
To: pgsql-admin@postgres= ql.org
Cc: @ DBA - W= est
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 ru= nning into an interesting error. I've upgraded dozens of very similar datab= ases already in the same configuration, but each database has different sch= ema 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:<= /span>

pg_restore: [archiver (db)] Error while PROCESSI= NG TOC:

pg_restore: [archiver (db)] Error from TOC ent= ry 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 A= LL ON TYPE "my_type_name" FROM PUBLIC;

REVOK= E ALL ON TYPE "my_type_name" FROM "some_user";

SET SESSION AUTHORIZATION ...

 

My upgrade command itself is pretty standard:
= /u= sr/pgsql-9.6/bin/pg_upgrade -k --old-bindir=3D/usr/pgsql-9.2/bin/ --new-bin= dir=3D/usr/pgsql-9.6/bin/ --old-datadir=3D/mnt/db/data.old/ --new-datadir= =3D/mnt/db/data/

 <= /o:p>

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 superus= er 'postgres'. In wonder as to why I could be getting this error, I di= d a pg_dump -s (schema only) of the database to be upgraded, and here's the= permission section for this type:

RE= VOKE ALL ON TYPE my_type_name FROM PUBLIC;

REVOKE ALL = ON TYPE my_type_name FROM some_user;

SET SESSION AUTHO= RIZATION some_other_user;

GRANT ALL ON TYPE my_type_na= me TO PUBLIC;

RESET SESSION AUTHORIZATION;

 =

And here is the \dT+ of the type:

<= /div>

my_database=3D> \dT+ my_type_name

                   = ;                     &nb= sp;  List of data types

 Schema |  &nbs= p;  Name     | Internal name | Size  | Elements |&= nbsp;  Owner    | Access privileges  | Description

=

--------+--------------+---------------+-------+----------= +------------+--------------------+-------------

 = ;public | my_type_name | my_type_name  | tuple |      &= nbsp;   | some_user  | =3DU/some_other_user |

(1 row)<= /span>

 =

 = ;

Using ver= bose 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=3Dmy= _database' "pg_upgrade_dump_208717.custom" >> "pg_upgr= ade_dump_208717.log" 2>&1

 

 

I've tried revoking all permissions from PUBLIC and all actual u= sers associated with this type before upgrade, and no matter what, results = in the same error. This is 100% reproducible. 

 

<= p> 

=

Anyone know of anything I m= ay 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  

=

&nbs= p;

Brian 

= Fehrle

 Database Administrator II<= /span>

 | <= /p>

comScore, Inc.

bfehrle@comscore.= com

comscore.com

=

=E2=80=8B=E2=80=8B=E2=80=8BThis e-ma= il (including any attachments) may contain information that is private, con= fidential, or protected by attorney-client or other privilege. If you recei= ved this e-mail in error, please delete it from your system and notify send= er.<= /p>

=

 

 

= --_8193F9A8-11E9-473D-AA4D-9307C0C6A53B_--