Fw: Isn't pg_statistic a security hole - Solution Proposal - Mailing list pgsql-patches

Hello all,

Attached is a patch to implement a new internal function, 'has_privilege'.
My proposal below explains the reasoning behind this submittal, although I
never did get any feedback -- positive or negative. If the patch is accepted
I'll be happy to do the work to create the system view as descibed.

The patch applies cleanly against cvs tip. One item I was not sure about was
the selection of the OID value for the new function. I chose 1920 for no
other reason that the highest OID in pg_proc.h was 1909, and this seemed
like a safe value. Is there somewhere I should have looked for guidance on
this?

Thanks,

-- Joe

> The recent discussions on pg_statistic got me started thinking about how
to
> implement a secure form of the view. Based on the list discussion, and a
> suggestion from Tom, I did some research regarding how SQL92 and some of
the
> larger commercial database systems allow access to system privilege
> information.
>
> I reviewed the ANSI SQL 92 specification, Oracle, MSSQL, and IBM DB2
> (documentation only). Here's what I found:
>
> ANSI SQL 92 does not have any functions defined for retrieving privilege
> information. It does, however define an "information schema" and
"definition
> schema" which among other things includes a TABLE_PRIVILEGES view.
>
> With this view available, it is possible to discern what privileges the
> current user has using a simple SQL statement. In Oracle, I found this
view,
> and some other variations. According to the Oracle DBA I work with, there
is
> no special function, and a SQL statement on the view is how he would
gather
> this kind of information when needed.
>
> MSSQL Server 7 also has this same view. Additionally, SQL7 has a T-SQL
> function called PERMISSIONS with the following description:
> "Returns a value containing a bitmap that indicates the statement, object,
> or column permissions for the current user.
> Syntax PERMISSIONS([objectid [, 'column']])".
>
> I only looked briefly at the IBM DB2 documentation, but could find no
> mention of TABLE_PRIVILEGES or any privilege specific function. I imagine
> TABLE_PRIVILEGES might be there somewhere since it seems to be standard
> SQL92.
>
> Based on all of the above, I concluded that there is nothing compelling in
> terms of a specific function to be compatible with. I do think that in the
> longer term it makes sense to implement the SQL 92 information schema
views
> in PostgreSQL.
>
> So, now for the proposal. I created a function (attached) which will allow
> any privilege type to be probed, called has_privilege. It is used like
this:
>
>   select relname from pg_class where has_privilege(current_user, relname,
> 'update');
>
> or
>
>   select has_privilege('postgres', 'pg_shadow', 'select');
>
> where
>   the first parameter is any valid user name
>   the second parameter can be a table, view, or sequence
>   the third parameter  can be 'select', 'insert', 'update', 'delete', or
> 'rule'
>
> The function is currently implemented as an external c function and
designed
> to be built under contrib. This function should really be an internal
> function. If the proposal is acceptable, I would like to take on the task
of
> turning the function into an internal one (with guidance, pointers,
> suggestions greatly appreciated). This would allow a secure view to be
> implemented over pg_statistic as:
>
> create view pg_userstat as (
>  select
>    s.starelid
>   ,s.staattnum
>   ,s.staop
>   ,s.stanullfrac
>   ,s.stacommonfrac
>   ,s.stacommonval
>   ,s.staloval
>   ,s.stahival
>   ,c.relname
>   ,a.attname
>   ,sh.usename
>  from
>    pg_statistic as s
>   ,pg_class as c
>   ,pg_shadow as sh
>   ,pg_attribute as a
>  where
>   has_privilege(current_user,c.relname,'select')
>   and sh.usesysid = c.relowner
>   and a.attrelid = c.oid
>   and c.oid = s.starelid
> );
>
> Then restrict pg_statistic from public viewing. This view would allow the
> current user to view statistics only on relations for which they already
> have 'select' granted.
>
> Comments?
>
> Regards,
> -- Joe
>
> installation:
>
> place in contrib
> tar -xzvf has_priv.tgz
> cd has_priv
> ./install.sh
> Note: installs the function into template1 by default. Edit install.sh to
> change.
>
>