1. Forum
  2. Usenet
  3. COMP.DATABASES.POSTGRESQL

  • create user mapping for user_in_postgredb server my_remote_ms_server op

    From bjorn.d.jensen@gmail.com@21:1/5 to All on Mon May 21 23:32:42 2018
    Hi!
    I have to manage PostgreSQL scripts, that will create
    objects (tables, views, functions etc), including
    foreign data wrapper.


    I have a shell script that will start execution of a couple
    of postgresql-script files creating these objects.

    One of the script files includes code like this:


    create user mapping for user_in_postgredb server my_remote_ms_server options (username 'domain\myuser', password 'keepsecret');


    I don't like the idea having the password in clear text as part of the script files stored in GIT repository accessbile by non-db-admins.

    How to avoid this?

    BR
    Bjorn

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Laurenz Albe@21:1/5 to bjorn.d.jensen on Tue May 22 11:27:23 2018
    On Mon, 21 May 2018 23:32:42 -0700, bjorn.d.jensen wrote:
    I have to manage PostgreSQL scripts, that will create objects (tables,
    views, functions etc), including foreign data wrapper.


    I have a shell script that will start execution of a couple of postgresql-script files creating these objects.

    One of the script files includes code like this:


    create user mapping for user_in_postgredb server my_remote_ms_server
    options (username 'domain\myuser', password 'keepsecret');


    I don't like the idea having the password in clear text as part of the
    script files stored in GIT repository accessbile by non-db-admins.

    How to avoid this?

    The documentation is quite outspoken here:

    Only superusers may connect to foreign servers without password authentication, so always specify the password option for user mappings belonging to non-superusers.

    You could use a connections as superuser and wrap the access to the
    foreign table in a SECURITY DEFINER function.

    Maybe it is a solution to keep CREATE USER MAPPING in a separate script
    that is not managed by Git.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Matthew Woodcraft@21:1/5 to bjorn.d.jensen@gmail.com on Tue May 22 18:07:53 2018
    In article <eac5adca-47de-435b-acfb-871feabe7990@googlegroups.com>,
    <bjorn.d.jensen@gmail.com> wrote:
    I have to manage PostgreSQL scripts, that will create
    objects (tables, views, functions etc), including
    foreign data wrapper.


    I have a shell script that will start execution of a couple
    of postgresql-script files creating these objects.

    One of the script files includes code like this:


    create user mapping for user_in_postgredb server my_remote_ms_server options (username 'domain\myuser', password 'keepsecret');


    I don't like the idea having the password in clear text as part of the script files stored in GIT repository accessbile by non-db-admins.

    How to avoid this?


    The usual thing is to either:

    - put the password in some separate location outside git, and have the
    shell script retrieve it; or

    - encrypt the password stored in git, and have the shell script decrypt it

    You might consider one of these projects to help with the second
    approach:

    https://github.com/StackExchange/blackbox
    https://dotat.at/prog/regpg/

    -M-

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From bjorn.d.jensen@gmail.com@21:1/5 to All on Wed May 23 08:53:00 2018
    any example you can share how the
    postgre-sql-file should look like that is called by shell script?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)