Re: SQL/MED security

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.

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.

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.

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

--
Itagaki Takahiro