Mailing-List: contact user-help@hawq.incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hawq.incubator.apache.org
MIME-Version: 1.0
In-Reply-To: <CALqDVuYO3b+_p=rGX-j_87ABZzT5E4062AqQJ1L3x8=-f+kxGw@mail.gmail.com>
References: <CALqDVuYO3b+_p=rGX-j_87ABZzT5E4062AqQJ1L3x8=-f+kxGw@mail.gmail.com>
From: "Alex (Oleksandr) Diachenko" <odiachenko@pivotal.io>
Date: Mon, 30 Jan 2017 11:07:08 -0800
Message-ID: <CA+-yw=KZZGv93adVAEdwF_mFO2wmDb6akt_wm2Cu7z+JO4dzzQ@mail.gmail.com>
Subject: Re: PXF HCatalog Issue for Non-Super User
To: dev@hawq.incubator.apache.org
Cc: user@hawq.incubator.apache.org
Content-Type: multipart/alternative; boundary=001a114e4fbca5236b0547548515
archived-at: Mon, 30 Jan 2017 19:07:20 -0000

--001a114e4fbca5236b0547548515
Content-Type: text/plain; charset=UTF-8

Hi Gagan,

The issue you described was a real limitation before https://issues.apache.
org/jira/browse/HAWQ-1130 was reported.
As for now the issue was resolved and targeted for next 2.1.0.0-incubating
open-source release.

Regards, Alex.


On Mon, Jan 30, 2017 at 10:08 AM, Gagan Brahmi <gaganbrahmi@gmail.com>
wrote:

> Hi All,
>
> I am not sure if anyone has faced issue for a non-privileged HAWQ user
> while access hive table using HCatalog. The issue is encountered when a
> non-privileged user tries to query the hive table (hive_table) under any
> database (default in this example) using hcatalog
> (hcatalog.default.hive_table).
>
> However, when you describe the table (\d) there is no issue encountered.
>
> I have tested this on HDB 2.0.0 and HDB 2.0.1.
>
> For the non-privileged user the following is the behavior when trying to
> access the data using SELECT:
>
> --------------------
>
> postgres=> SELECT * FROM hcatalog.default.hive_table;
> ERROR:  permission denied for relation pg_authid
> LINE 1: SELECT COUNT(*) FROM hcatalog.default.hive_table;
>                              ^
> CONTEXT:  SQL statement "SELECT max(oid) FROM (SELECT max(oid) AS oid FROM
> pg_extprotocol UNION ALL SELECT max(oid) AS oid FROM pg_partition UNION ALL
> SELECT max(oid) AS oid FROM pg_partition_rule UNION ALL SELECT max(oid) AS
> oid FROM pg_filespace UNION ALL SELECT max(oid) AS oid FROM pg_compression
> UNION ALL SELECT max(oid) AS oid FROM pg_filesystem UNION ALL SELECT
> max(oid) AS oid FROM pg_foreign_data_wrapper UNION ALL SELECT max(oid) AS
> oid FROM pg_foreign_server UNION ALL SELECT max(oid) AS oid FROM
> pg_database UNION ALL SELECT max(oid) AS oid FROM pg_type UNION ALL SELECT
> max(oid) AS oid FROM pg_proc UNION ALL SELECT max(oid) AS oid FROM pg_class
> UNION ALL SELECT max(oid) AS oid FROM pg_attrdef UNION ALL SELECT max(oid)
> AS oid FROM pg_constraint UNION ALL SELECT max(oid) AS oid FROM pg_operator
> UNION ALL SELECT max(oid) AS oid FROM pg_opclass UNION ALL SELECT max(oid)
> AS oid FROM pg_am UNION ALL SELECT max(oid) AS oid FROM pg_language UNION
> ALL SELECT max(oid) AS oid FROM pg_rewrite UNION ALL SELECT max(oid) AS oid
> FROM pg_trigger UNION ALL SELECT max(oid) AS oid FROM pg_cast UNION ALL
> SELECT max(oid) AS oid FROM pg_namespace UNION ALL SELECT max(oid) AS oid
> FROM pg_conversion UNION ALL SELECT max(oid) AS oid FROM pg_tablespace
> UNION ALL SELECT max(oid) AS oid FROM pg_resqueue UNION ALL SELECT max(oid)
> AS oid FROM pg_authid UNION ALL SELECT max(oid) AS oid FROM
> pg_user_mapping) AS x"
>
> postgres=> SELECT * FROM hcatalog.default.hive_table;
> ERROR:  database does not have enough available Oids to support HCatalog
> queries
> LINE 1: SELECT * FROM hcatalog.default.hive_table;
>                       ^
> HINT:  Database VACUUM may recycle unused Oids.
>
>
> postgres=> \d hcatalog.default.hive_table
>
>                                                     PXF Hive Table
> "default.hive_table"
>  Column | Type
> --------+------
>  id     | int4
>  fname  | text
>  lname  | text
>
> --------------------
>
> I was able to get around this problem by granting SELECT on pg_authid and
> pg_user_mapping table which fixes the underlying query to grab the max Oid.
>
> Is this is a known issue or is there any better workaround for this
> behavior? Or anything other than granting the user SUPERUSER privileges?
>
>
> Regards,
> Gagan Brahmi
>

--001a114e4fbca5236b0547548515
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Gagan,<div><br></div><div>The issue you described was a=
 real limitation before=C2=A0<a href=3D"https://issues.apache.org/jira/brow=
se/HAWQ-1130" target=3D"_blank">https://issues.apache.<wbr>org/jira/browse/=
HAWQ-1130</a> was reported.</div><div>As for now the issue was resolved and=
 targeted for next=C2=A02.1.0.0-incubating open-source release.</div><div><=
br></div><div>Regards, Alex.</div><div><br></div></div><div class=3D"gmail_=
extra"><br><div class=3D"gmail_quote">On Mon, Jan 30, 2017 at 10:08 AM, Gag=
an Brahmi <span dir=3D"ltr">&lt;<a href=3D"mailto:gaganbrahmi@gmail.com" ta=
rget=3D"_blank">gaganbrahmi@gmail.com</a>&gt;</span> wrote:<br><blockquote =
class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid=
;padding-left:1ex">Hi All,<br>
<br>
I am not sure if anyone has faced issue for a non-privileged HAWQ user<br>
while access hive table using HCatalog. The issue is encountered when a<br>
non-privileged user tries to query the hive table (hive_table) under any<br=
>
database (default in this example) using hcatalog<br>
(hcatalog.default.hive_table).<br>
<br>
However, when you describe the table (\d) there is no issue encountered.<br=
>
<br>
I have tested this on HDB 2.0.0 and HDB 2.0.1.<br>
<br>
For the non-privileged user the following is the behavior when trying to<br=
>
access the data using SELECT:<br>
<br>
--------------------<br>
<br>
postgres=3D&gt; SELECT * FROM hcatalog.default.hive_table;<br>
ERROR:=C2=A0 permission denied for relation pg_authid<br>
LINE 1: SELECT COUNT(*) FROM hcatalog.default.hive_table;<br>
=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^<br>
CONTEXT:=C2=A0 SQL statement &quot;SELECT max(oid) FROM (SELECT max(oid) AS=
 oid FROM<br>
pg_extprotocol UNION ALL SELECT max(oid) AS oid FROM pg_partition UNION ALL=
<br>
SELECT max(oid) AS oid FROM pg_partition_rule UNION ALL SELECT max(oid) AS<=
br>
oid FROM pg_filespace UNION ALL SELECT max(oid) AS oid FROM pg_compression<=
br>
UNION ALL SELECT max(oid) AS oid FROM pg_filesystem UNION ALL SELECT<br>
max(oid) AS oid FROM pg_foreign_data_wrapper UNION ALL SELECT max(oid) AS<b=
r>
oid FROM pg_foreign_server UNION ALL SELECT max(oid) AS oid FROM<br>
pg_database UNION ALL SELECT max(oid) AS oid FROM pg_type UNION ALL SELECT<=
br>
max(oid) AS oid FROM pg_proc UNION ALL SELECT max(oid) AS oid FROM pg_class=
<br>
UNION ALL SELECT max(oid) AS oid FROM pg_attrdef UNION ALL SELECT max(oid)<=
br>
AS oid FROM pg_constraint UNION ALL SELECT max(oid) AS oid FROM pg_operator=
<br>
UNION ALL SELECT max(oid) AS oid FROM pg_opclass UNION ALL SELECT max(oid)<=
br>
AS oid FROM pg_am UNION ALL SELECT max(oid) AS oid FROM pg_language UNION<b=
r>
ALL SELECT max(oid) AS oid FROM pg_rewrite UNION ALL SELECT max(oid) AS oid=
<br>
FROM pg_trigger UNION ALL SELECT max(oid) AS oid FROM pg_cast UNION ALL<br>
SELECT max(oid) AS oid FROM pg_namespace UNION ALL SELECT max(oid) AS oid<b=
r>
FROM pg_conversion UNION ALL SELECT max(oid) AS oid FROM pg_tablespace<br>
UNION ALL SELECT max(oid) AS oid FROM pg_resqueue UNION ALL SELECT max(oid)=
<br>
AS oid FROM pg_authid UNION ALL SELECT max(oid) AS oid FROM<br>
pg_user_mapping) AS x&quot;<br>
<br>
postgres=3D&gt; SELECT * FROM hcatalog.default.hive_table;<br>
ERROR:=C2=A0 database does not have enough available Oids to support HCatal=
og<br>
queries<br>
LINE 1: SELECT * FROM hcatalog.default.hive_table;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 ^<br>
HINT:=C2=A0 Database VACUUM may recycle unused Oids.<br>
<br>
<br>
postgres=3D&gt; \d hcatalog.default.hive_table<br>
<br>
=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=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PXF Hive Table<br>
&quot;default.hive_table&quot;<br>
=C2=A0Column | Type<br>
--------+------<br>
=C2=A0id=C2=A0 =C2=A0 =C2=A0| int4<br>
=C2=A0fname=C2=A0 | text<br>
=C2=A0lname=C2=A0 | text<br>
<br>
--------------------<br>
<br>
I was able to get around this problem by granting SELECT on pg_authid and<b=
r>
pg_user_mapping table which fixes the underlying query to grab the max Oid.=
<br>
<br>
Is this is a known issue or is there any better workaround for this<br>
behavior? Or anything other than granting the user SUPERUSER privileges?<br=
>
<br>
<br>
Regards,<br>
Gagan Brahmi<br>
</blockquote></div><br></div>

--001a114e4fbca5236b0547548515--