Re: SQL/MED security

Hello

I see a SQL/MED security very unclean - it have to be very vell documented :(

I have a database on port 5401. With user Tom - it require a password

[pavel(at)nemesis pgsql]$ psql-dev1 postgres -U tom
Password for user tom:
Timing is on.
psql-dev1 (9.0devel)
Type "help" for help.

postgres=>

I can't to do selects to table from this database as some non superuser

create foreign table test_table(id numeric) server dev1;

postgres=# create user mapping for public server dev1;
CREATE USER MAPPING
Time: 2,507 ms
postgres=# select count(*) from test_table;
count
---------
1000002
(1 row)

postgres=# create user mapping for tom server dev1 options (user
'tom', password 'tom');
CREATE USER MAPPING
Time: 32,929 ms
postgres=# set role to tom;
SET
Time: 0,450 ms
postgres=> select * from test_table;
ERROR: password is required
DETAIL: Non-superuser cannot connect if the server does not request a password.
HINT: Target server's authentication method must be changed.

What I doing wrong?

Regards
Pavel Stehule

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

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
>