Re: Updates of SE-PostgreSQL 8.4devel patches (r1668) - Mailing list pgsql-hackers

KaiGai Kohei wrote:
> The other one is it has two kind of reader permissions ("select" and 
> "use").
> The "select" permission is applied when user tries to read tables/columns
> and its contents are returned to the user.
> The "use" permission is applied when user tries to read table/columns,
> but its contents are consumed internally (not returned to user directly).
> 
> For example:
>   SELECT a, b FROM t WHERE b > 10 and c = 'aaa';
> 
> In this case,
>   db_column:{select} permission is applied on "t.a".
>   db_column:{select use} permission is applied on "t.b".
>   db_column:{use} permission is applied on "t.c".
>   db_table:{select use} permission is applied on "t"
> 
> However, I don't absolutely oppose to integrate them into a single
> reader "select" permission, because it was originally a single
> permission, then "use" is added.

If you have "use" permisson on c, you can easily use it to find out the 
exact value. Just do queries like "SELECT 'foo' FROM t WHERE b > 10 AND 
c = 'aaa' AND c BETWEEN 1 AND 1000" repeatedly with different ranges to 
zoom into the exact value. So I think separating those two permissions 
is a mistake,

> Please note that user's privileges are not limited to create/alter/drop
> them. One sensitive permission is "db_procedure:{install}".
> It is checked when user defined functions are set up as a function
> internally invoked.
> 
> For example, "pg_conversion.conproc" is internally invoked to translate
> a text, but it does not check pg_proc_aclcheck() in runtime.
> We consider all user defined functions should be checked either of:
>  - "db_procedure:{execute}" permission for the client in runtime
>   or
>  - "db_procedure:{install}" permission for the DBA on installation time
> 
> Needless to say, "{install}" is more sensitive permission because it
> means anyones to invoke it implicitly. So, the default policy only
> allows it on functions defined by DBA, but the "execute" permission
> is allowed normal users to invoke functions defined by himself.

Hmm. We normally rely on the fact that a conversion function needs to be 
a C-function, and because only superusers can create C-functions we have 
assumed that they're safe to call. Which was actually not true until 
recently, when we added checks into all the conversion functions to 
check that the source and target encoding of the strings passed as 
arguments match the ones specified in the CREATE CONVERSION command.

There has been talks of making CREATE CONVERSION superuser-only, so we 
could easily just do that. Can you give some other examples of where the 
"install" permission is used?

But if I've understood correctly, one goal is to restrict the actions of 
superusers as well. Is there something to disallow superusers from 
creating C-functions? If yes, isn't that enough protection from things 
like the conversion functions?

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com