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.
has_priv.tgz
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
