Thread: FDWs, foreign servers and user mappings

Hi,

We've been discussing the subject in the pyrseas-general ML, but I think
it would be beneficial to get feedback from a broader audience.

The Pyrseas dbtoyaml utility outputs the objects in YAML, which ends up
looking like a tree (see
http://pyrseas.readthedocs.org/en/latest/dbtoyaml.html ) and similar to
the tree in pgAdmin's Object Browser panel.

Any object that is owned by a schema (tables, functions, etc.) is listed
naturally under the schema.  The objects (columns, constraints, etc.)
that belong to a table are listed under the table, and so forth.  Only a
few object types fall outside the "schema tree," e.g., casts, languages
(extensions, collations in 9.1).

Because FDW's, foreign servers and user mappings are not directly tied
to a schema and their identifiers must be unique within a given
database, I first added them at the top level, e.g.,

foreign data wrapper fdw1:
  ...
schema public:
  ...
server fs1:
  wrapper: fdw1
user mapping for PUBLIC server fs1:
  options:
  - xxx=yyy

A Pyrseas user suggested that servers ought to be listed under the
associated FDW, e.g.,

foreign data wrapper fdw1:
  server fs1:
  ...

The question is whether user mappings should also be listed under the
server, i.e.,

foreign data wrapper fdw1:
  server fs1:
    user mapping for PUBLIC:
      options:
      - xxx=yyy

Does that make sense?  And if so, will it make sense in the future
(considering potential FDW developments)?

A related question was whether user mapping options, which may include
sensitive data such as passwords, should be output by default.  I'm not
sure if this should extend to other FDW-related options, since a server
option could presumably be a URI that includes logon information.

Thanks in advance.

Joe

(2012/03/08 6:16), Joe Abbate wrote:
> Does that make sense?  And if so, will it make sense in the future
> (considering potential FDW developments)?

I think that makes, and will make sense.  Because SQL/MED standard
mentions about schema for only foreign table in "4.12 SQL-schemas" section.

FYI, pgAdmin III shows them as a tree like:

Database
  FDW
    Server
      User Mapping
  Schema
    Foreign Table

> A related question was whether user mapping options, which may include
> sensitive data such as passwords, should be output by default.  I'm not
> sure if this should extend to other FDW-related options, since a server
> option could presumably be a URI that includes logon information.

FDW options of user mappings are hidden from non-superusers for security
reason.  So, I think it's reasonable to show every visible option for
the user who is used for the dbtoyaml invocation.

I'm not sure about other object types, but IMO secure information such
as URI which includes password should be stored in user mappings rather
than servers.

Regards,
--
Shigeru Hanada

On 03/08/2012 12:06 AM, Shigeru Hanada wrote:
> I think that makes, and will make sense.  Because SQL/MED standard
> mentions about schema for only foreign table in "4.12 SQL-schemas" section.
>
> FYI, pgAdmin III shows them as a tree like:
>
> Database
>   FDW
>     Server
>       User Mapping
>   Schema
>     Foreign Table

Thanks.  Incidentally, what version of pgAdmin shows that?  I didn't see
it in 1.14.0.

Joe

On Thu, 2012-03-08 at 10:04 -0500, Joe Abbate wrote:
> On 03/08/2012 12:06 AM, Shigeru Hanada wrote:
> > I think that makes, and will make sense.  Because SQL/MED standard
> > mentions about schema for only foreign table in "4.12 SQL-schemas" section.
> >
> > FYI, pgAdmin III shows them as a tree like:
> >
> > Database
> >   FDW
> >     Server
> >       User Mapping
> >   Schema
> >     Foreign Table
>
> Thanks.  Incidentally, what version of pgAdmin shows that?  I didn't see
> it in 1.14.0.
>

It is in 1.14, but you probably don't have enabled them to be displayed.
See menu File/Options, and then in the Display tab.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com