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)

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)

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)

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)