Skip site navigation (1) Skip section navigation (2)

Fw: Isn't pg_statistic a security hole - Solution Proposal

From: "Joe Conway" <joe(at)conway-family(dot)com>
To: <pgsql-patches(at)postgresql(dot)org>
Subject: Fw: Isn't pg_statistic a security hole - Solution Proposal
Date: 2001-06-01 06:31:51
Message-ID: 007901c0ea64$8bb5a3a0$0205a8c0@jecw2k1 (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-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


-- Joe

> The recent discussions on pg_statistic got me started thinking about how
> 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
> 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
> 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
> and some other variations. According to the Oracle DBA I work with, there
> no special function, and a SQL statement on the view is how he would
> 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
> 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
>   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
> 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
> 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
> ./
> Note: installs the function into template1 by default. Edit to
> change.

Attachment: has_priv.diff
Description: application/octet-stream (7.8 KB)


pgsql-hackers by date

Next:From: Kovacs ZoltanDate: 2001-06-01 06:56:01
Subject: Re: ERROR: cache lookup for proc 43030134 failed
Previous:From: Hannu KrosingDate: 2001-06-01 06:28:36
Subject: Re: Imperfect solutions

pgsql-patches by date

Next:From: Zeugswetter Andreas SBDate: 2001-06-01 07:55:54
Subject: AW: Re: Support for %TYPE in CREATE FUNCTION
Previous:From: Pascal ScheffersDate: 2001-06-01 06:15:39
Subject: Re: Support for %TYPE in CREATE FUNCTION

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group