Re: SQL/MED security

2010/7/23 Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>:
> 2010/7/22 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> I see a SQL/MED security very unclean - it have to be very vell documented :(
>> ERROR:  password is required
>> DETAIL:  Non-superuser cannot connect if the server does not request a password.
>
> The security model of current FDW heavily depends on implementation of
> existing COPY FROM and dblink. It should be improved in the future.
>
> Postgresql_fdw is based on dblink, that always requires password
> for non-superuser connections. File_fdw is based on COPY FROM,
> that only allows superusers to read local files. So, present FDWs
> also require password or superuser privileges to read foreign tables.
>
> I think the ideal design is creating foreign tables are restricted
> only for superusers, or requiring password on the creation time,
> but don't require such privileges on SELECT time. But I didn't
> develop such feature at the moment.

I agree on 50%. Only superusers can create these tables, but he can
grant SELECT to some non superusers. And this behave have to be done
from start not later. Ok, It is nice, so supersuser can have a foreign
tables, but is very unpractical so only this user can use these
tables. I understand where is core of this limit - but it have to be
solved before commiting. Usually people don't like to use a superuser
account.

>
> There are some security issues here - ALTERing generic options.
> Superusers can define file_fdw on a local file, and can grant the
> ownership to non-superusers. But the non-superusers should
> not modify 'filename' option with ALTER OPTION. If allowed, they
> can read another local file on the server.
>

I am thinking so for fdw_file nonsuper user can't to change any
options. I don't see any use case.

> There is another security issue: password can be retrieved by all users to
> query system catalogs. The password is stored in generic options,
> that are visible for all users and not encrypted. We can allow users to read
> other normal options, but should not password and local filesystem path.
> However, we don't have such equipments for now.
>

sure, it must by solved first. Probably we have to have to store
password to separate file inside a data directory :( - I am not a
security engineer - probably have to use a same mechanism, that use a
postgres for storing a passwords.

Regards

Pavel Stehule

> Such security issues are ones of the most complex problems in FDW.
> Comments and ideas welcome.
>

> --
> Itagaki Takahiro
>