Обсуждение: User mapping security

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/postgres-fdw.html
Description:

Regarding the documentation pages
https://www.postgresql.org/docs/10/postgres-fdw.html?origin_team=T02HEPYKQ
and https://www.postgresql.org/docs/10/sql-createusermapping.html

I suppose it should be warned on the pages that foreign credentials with be
stored as simple text and will be available for viewing in pg_user_mappings.

On Tue, Jul 16, 2019 at 02:01:00AM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/10/postgres-fdw.html
> Description:
> 
> Regarding the documentation pages
> https://www.postgresql.org/docs/10/postgres-fdw.html?origin_team=T02HEPYKQ
> and https://www.postgresql.org/docs/10/sql-createusermapping.html
> 
> I suppose it should be warned on the pages that foreign credentials with be
> stored as simple text and will be available for viewing in pg_user_mappings.

I know this is four years old, but the attached patch documents it.  I
don't think postgresql-fdw needs it since it relies on user mapping and
discourages passwords in the connection string.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

Greetings,

* Bruce Momjian (bruce@momjian.us) wrote:
> On Tue, Jul 16, 2019 at 02:01:00AM +0000, PG Doc comments form wrote:
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/10/postgres-fdw.html
> > Description:
> >
> > Regarding the documentation pages
> > https://www.postgresql.org/docs/10/postgres-fdw.html?origin_team=T02HEPYKQ
> > and https://www.postgresql.org/docs/10/sql-createusermapping.html
> >
> > I suppose it should be warned on the pages that foreign credentials with be
> > stored as simple text and will be available for viewing in pg_user_mappings.
>
> I know this is four years old, but the attached patch documents it.  I
> don't think postgresql-fdw needs it since it relies on user mapping and
> discourages passwords in the connection string.

A bit on the fence about it ... but I do wonder if we should encourage
use of gssapi and credential delegation now that we support that and
point out that storing passwords isn't required if you're using gssapi.

Thanks,

Stephen

> diff --git a/doc/src/sgml/ref/create_user_mapping.sgml b/doc/src/sgml/ref/create_user_mapping.sgml
> index 55debd5401..e93bfe48f6 100644
> --- a/doc/src/sgml/ref/create_user_mapping.sgml
> +++ b/doc/src/sgml/ref/create_user_mapping.sgml
> @@ -92,7 +92,11 @@ CREATE USER MAPPING [ IF NOT EXISTS ] FOR { <replaceable class="parameter">user_
>        This clause specifies the options of the user mapping.  The
>        options typically define the actual user name and password of
>        the mapping.  Option names must be unique.  The allowed option
> -      names and values are specific to the server's foreign-data wrapper.
> +      names and values are specific to the server's foreign-data
> +      wrapper.  Option values, including passwords, are visible in the
> +      <link
> +      linkend="catalog-pg-user-mapping"><structname>pg_user_mapping</structname></link>
> +      system view.
>       </para>
>      </listitem>
>     </varlistentry>

Bruce Momjian <bruce@momjian.us> writes:
> On Tue, Jul 16, 2019 at 02:01:00AM +0000, PG Doc comments form wrote:
>> I suppose it should be warned on the pages that foreign credentials with be
>> stored as simple text and will be available for viewing in pg_user_mappings.

> I know this is four years old, but the attached patch documents it.  I
> don't think postgresql-fdw needs it since it relies on user mapping and
> discourages passwords in the connection string.

This is far too alarmist.  It ignores the privilege restrictions that
are built into the pg_user_mappings view.  Random users can't see
umoptions.

            regards, tom lane

On Thu, Nov  9, 2023 at 05:03:33PM -0500, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > On Tue, Jul 16, 2019 at 02:01:00AM +0000, PG Doc comments form wrote:
> >> I suppose it should be warned on the pages that foreign credentials with be
> >> stored as simple text and will be available for viewing in pg_user_mappings.
> 
> > I know this is four years old, but the attached patch documents it.  I
> > don't think postgresql-fdw needs it since it relies on user mapping and
> > discourages passwords in the connection string.
> 
> This is far too alarmist.  It ignores the privilege restrictions that
> are built into the pg_user_mappings view.  Random users can't see
> umoptions.

True.  I wasn't sure how much of an issue it was to expose passwords at
the SQL level, but I will drop the idea.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.