Thread: Re: [HACKERS] Re: Fw: Isn't pg_statistic a security hole - Solution Proposal

> I have just thought of a possible compromise.  Peter is right that we
> don't want case conversion on table names that are extracted from
> catalogs.  But I think we do want it on table names expressed as string
> literals.  Could we make the assumption that table names in catalogs
> will be of type 'name'?  If so, it'd work to make two versions of the
> has_table_privilege function, one taking type "name" and the other
> taking type "text".  The "name" version would take its input as-is,
> the "text" version would do case folding and truncation.  This would
> work transparently for queries selecting relation names from the system
> catalogs, and it'd also work transparently for queries using unmarked
> string literals (which will be preferentially resolved as type "text").
> Worst case if the system makes the wrong choice is you throw in an
> explicit coercion to name or text.  Comments?

OK -- here's take #5.

It "make"s and "make check"s clean against current cvs tip.

There are now both Text and Name variants, and the regression test support
is rolled into the patch. Note that to be complete wrt Name based variants,
there are now 12 user visible versions of has_table_privilege:

has_table_privilege(Text usename, Text relname, Text priv_type)
has_table_privilege(Text usename, Name relname, Text priv_type)
has_table_privilege(Name usename, Text relname, Text priv_type)
has_table_privilege(Name usename, Name relname, Text priv_type)
has_table_privilege(Text relname, Text priv_type) /* assumes current_user */
has_table_privilege(Name relname, Text priv_type) /* assumes current_user */
has_table_privilege(Text usename, Oid reloid, Text priv_type)
has_table_privilege(Name usename, Oid reloid, Text priv_type)
has_table_privilege(Oid reloid, Text priv_type)  /* assumes current_user */
has_table_privilege(Oid usesysid, Text relname, Text priv_type)
has_table_privilege(Oid usesysid, Name relname, Text priv_type)
has_table_privilege(Oid usesysid, Oid reloid, Text priv_type)

For the Text based inputs, a new internal function, get_Name is used
(shamelessly copied from get_seq_name in sequence.c) to downcase if not
quoted, or remove quotes if quoted, and truncate. I also added a few test
cases for the downcasing, quote removal, and Name based variants to the
regression test.

Only thing left (I hope!) is documentation. I'm sure I either have or can
get the DocBook tools, but I've never used them. Would it be simpler to
clone and hand edit one of the existing docs? Any suggestions to get me
started?

Thanks,

-- Joe

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

> > I have just thought of a possible compromise.  Peter is right that we
> > don't want case conversion on table names that are extracted from
> > catalogs.  But I think we do want it on table names expressed as string
> > literals.  Could we make the assumption that table names in catalogs
> > will be of type 'name'?  If so, it'd work to make two versions of the
> > has_table_privilege function, one taking type "name" and the other
> > taking type "text".  The "name" version would take its input as-is,
> > the "text" version would do case folding and truncation.  This would
> > work transparently for queries selecting relation names from the system
> > catalogs, and it'd also work transparently for queries using unmarked
> > string literals (which will be preferentially resolved as type "text").
> > Worst case if the system makes the wrong choice is you throw in an
> > explicit coercion to name or text.  Comments?
>
> OK -- here's take #5.
>
> It "make"s and "make check"s clean against current cvs tip.
>
> There are now both Text and Name variants, and the regression test support
> is rolled into the patch. Note that to be complete wrt Name based variants,
> there are now 12 user visible versions of has_table_privilege:
>
> has_table_privilege(Text usename, Text relname, Text priv_type)
> has_table_privilege(Text usename, Name relname, Text priv_type)
> has_table_privilege(Name usename, Text relname, Text priv_type)
> has_table_privilege(Name usename, Name relname, Text priv_type)
> has_table_privilege(Text relname, Text priv_type) /* assumes current_user */
> has_table_privilege(Name relname, Text priv_type) /* assumes current_user */
> has_table_privilege(Text usename, Oid reloid, Text priv_type)
> has_table_privilege(Name usename, Oid reloid, Text priv_type)
> has_table_privilege(Oid reloid, Text priv_type)  /* assumes current_user */
> has_table_privilege(Oid usesysid, Text relname, Text priv_type)
> has_table_privilege(Oid usesysid, Name relname, Text priv_type)
> has_table_privilege(Oid usesysid, Oid reloid, Text priv_type)
>
> For the Text based inputs, a new internal function, get_Name is used
> (shamelessly copied from get_seq_name in sequence.c) to downcase if not
> quoted, or remove quotes if quoted, and truncate. I also added a few test
> cases for the downcasing, quote removal, and Name based variants to the
> regression test.
>
> Only thing left (I hope!) is documentation. I'm sure I either have or can
> get the DocBook tools, but I've never used them. Would it be simpler to
> clone and hand edit one of the existing docs? Any suggestions to get me
> started?
>
> Thanks,
>
> -- Joe
>
>
>
>
>
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I don't know about other people but the 48 hours notice email and the
web page of outstanding patches seems to be working well for me.

> > I have just thought of a possible compromise.  Peter is right that we
> > don't want case conversion on table names that are extracted from
> > catalogs.  But I think we do want it on table names expressed as string
> > literals.  Could we make the assumption that table names in catalogs
> > will be of type 'name'?  If so, it'd work to make two versions of the
> > has_table_privilege function, one taking type "name" and the other
> > taking type "text".  The "name" version would take its input as-is,
> > the "text" version would do case folding and truncation.  This would
> > work transparently for queries selecting relation names from the system
> > catalogs, and it'd also work transparently for queries using unmarked
> > string literals (which will be preferentially resolved as type "text").
> > Worst case if the system makes the wrong choice is you throw in an
> > explicit coercion to name or text.  Comments?
>
> OK -- here's take #5.
>
> It "make"s and "make check"s clean against current cvs tip.
>
> There are now both Text and Name variants, and the regression test support
> is rolled into the patch. Note that to be complete wrt Name based variants,
> there are now 12 user visible versions of has_table_privilege:
>
> has_table_privilege(Text usename, Text relname, Text priv_type)
> has_table_privilege(Text usename, Name relname, Text priv_type)
> has_table_privilege(Name usename, Text relname, Text priv_type)
> has_table_privilege(Name usename, Name relname, Text priv_type)
> has_table_privilege(Text relname, Text priv_type) /* assumes current_user */
> has_table_privilege(Name relname, Text priv_type) /* assumes current_user */
> has_table_privilege(Text usename, Oid reloid, Text priv_type)
> has_table_privilege(Name usename, Oid reloid, Text priv_type)
> has_table_privilege(Oid reloid, Text priv_type)  /* assumes current_user */
> has_table_privilege(Oid usesysid, Text relname, Text priv_type)
> has_table_privilege(Oid usesysid, Name relname, Text priv_type)
> has_table_privilege(Oid usesysid, Oid reloid, Text priv_type)
>
> For the Text based inputs, a new internal function, get_Name is used
> (shamelessly copied from get_seq_name in sequence.c) to downcase if not
> quoted, or remove quotes if quoted, and truncate. I also added a few test
> cases for the downcasing, quote removal, and Name based variants to the
> regression test.
>
> Only thing left (I hope!) is documentation. I'm sure I either have or can
> get the DocBook tools, but I've never used them. Would it be simpler to
> clone and hand edit one of the existing docs? Any suggestions to get me
> started?
>
> Thanks,
>
> -- Joe
>
>
>
>
>
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

>
> I don't know about other people but the 48 hours notice email and the
> web page of outstanding patches seems to be working well for me.
>

Sorry -- my "into the ether" comment wasn't griping about you :-)

I've been having serious problems with my "mail.com" account. I originally
sent this post Saturday afternoon, but it didn't even make it to the
pgsql-patches list
until Monday morning (and I only know that because I started reading the
comp.databases.postgresql.patches news feed).
 :(

Joe

Patch applied by Tom for oid and Name versions.

> > I have just thought of a possible compromise.  Peter is right that we
> > don't want case conversion on table names that are extracted from
> > catalogs.  But I think we do want it on table names expressed as string
> > literals.  Could we make the assumption that table names in catalogs
> > will be of type 'name'?  If so, it'd work to make two versions of the
> > has_table_privilege function, one taking type "name" and the other
> > taking type "text".  The "name" version would take its input as-is,
> > the "text" version would do case folding and truncation.  This would
> > work transparently for queries selecting relation names from the system
> > catalogs, and it'd also work transparently for queries using unmarked
> > string literals (which will be preferentially resolved as type "text").
> > Worst case if the system makes the wrong choice is you throw in an
> > explicit coercion to name or text.  Comments?
>
> OK -- here's take #5.
>
> It "make"s and "make check"s clean against current cvs tip.
>
> There are now both Text and Name variants, and the regression test support
> is rolled into the patch. Note that to be complete wrt Name based variants,
> there are now 12 user visible versions of has_table_privilege:
>
> has_table_privilege(Text usename, Text relname, Text priv_type)
> has_table_privilege(Text usename, Name relname, Text priv_type)
> has_table_privilege(Name usename, Text relname, Text priv_type)
> has_table_privilege(Name usename, Name relname, Text priv_type)
> has_table_privilege(Text relname, Text priv_type) /* assumes current_user */
> has_table_privilege(Name relname, Text priv_type) /* assumes current_user */
> has_table_privilege(Text usename, Oid reloid, Text priv_type)
> has_table_privilege(Name usename, Oid reloid, Text priv_type)
> has_table_privilege(Oid reloid, Text priv_type)  /* assumes current_user */
> has_table_privilege(Oid usesysid, Text relname, Text priv_type)
> has_table_privilege(Oid usesysid, Name relname, Text priv_type)
> has_table_privilege(Oid usesysid, Oid reloid, Text priv_type)
>
> For the Text based inputs, a new internal function, get_Name is used
> (shamelessly copied from get_seq_name in sequence.c) to downcase if not
> quoted, or remove quotes if quoted, and truncate. I also added a few test
> cases for the downcasing, quote removal, and Name based variants to the
> regression test.
>
> Only thing left (I hope!) is documentation. I'm sure I either have or can
> get the DocBook tools, but I've never used them. Would it be simpler to
> clone and hand edit one of the existing docs? Any suggestions to get me
> started?
>
> Thanks,
>
> -- Joe
>
>
>
>
>
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026