Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #828 > unrolled thread

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

Back to article view | Back to comp.databases.postgresql

Contents

  create user mapping for user_in_postgredb server my_remote_ms_server options (username 'domain\myuser', password 'keepsecret'); bjorn.d.jensen@gmail.com - 2018-05-21 23:32 -0700
    Re: create user mapping for user_in_postgredb server my_remote_ms_server options (username 'domain\myuser', password 'keepsecret'); Laurenz Albe <laurenz@nospam.pn> - 2018-05-22 11:27 +0000
    Re: create user mapping for user_in_postgredb server my_remote_ms_server options (username 'domain\myuser', password 'keepsecret'); mattheww@chiark.greenend.org.uk (Matthew Woodcraft) - 2018-05-22 18:07 +0100
      Re: create user mapping for user_in_postgredb server my_remote_ms_server options (username 'domain\myuser', password 'keepsecret'); bjorn.d.jensen@gmail.com - 2018-05-23 08:53 -0700

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

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

[toc] | [next] | [standalone]

#829

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.

[toc] | [prev] | [next] | [standalone]

#830

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-

[toc] | [prev] | [next] | [standalone]

#831

any example you can share how the 
postgre-sql-file should look like that is called by shell script?

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web