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

[bjorn.d...@gmail.com]

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

[Laurenz Albe]

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.

[Matthew Woodcraft]

In article <eac5adca-47de-435b...@googlegroups.com>,

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-

[bjorn.d...@gmail.com]

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