create subscription, connection string, password in log not hide

[f***@tin.it]

Hello,
I am tring the use of Logical Replication with Postgres 10.1.
I installed two Postgres 10.1 on two different system Windows 64 bit.
I follow the chapter 31.9. Quick Setup
Everything function, that's good. I want to use in production.
But I found this problem:
I went to see in the log file of the two system what is going on.
I found in the log (C:\Program Files\PostgreSQL\10\data\log) of the subscriber the line of the command:
"CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser password=secret' PUBLICATION mypub;"
Where I can see the connection string exactly as written with the password in clear.
I try to use the password with md5 hash ('md5'+md5(user+password)) and othe combination of encrypted password, in a similar way I can do with "CREATE USER WITH ENCRYPTED ...".
But it seemed to me that the only way is to use the password in clear.
There is any way to avoid to log the password in clear manner?
Thanks
Loris

[Laurenz Albe]

Post by f***@tin.it
I am tring the use of Logical Replication with Postgres 10.1.
I went to see in the log file of the two system what is going on.
"CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser password=secret' PUBLICATION mypub;"
Where I can see the connection string exactly as written with the password in clear.
I try to use the password with md5 hash ('md5'+md5(user+password)) and othe combination of encrypted password, in a similar way I can do with "CREATE USER WITH ENCRYPTED ...".
But it seemed to me that the only way is to use the password in clear.
There is any way to avoid to log the password in clear manner?

You can run

BEGIN;
SET LOCAL log_statement='none';
SET LOCAL log__min_duration_statement=-1;
CREATE SUBSCRIPTION ...;
COMMIT;

to disable logging for the duration of a transaction.

Alternatively, you could allow "trust" authentication for replication
connections from one machine, then you don't have to send a password.

Yours,
Laurenz Albe

[Laurenz Albe]

Please keep the list in CC.

- using trust is not an options on a production system;

It isn't if you are very security concious.

I normally use trust authentication, limited to one IP address,
a user and the database "replication", on production systems.

the password will still be transmitted trought the net in clear form;
the log can be useful when looking for problem. Disabling the log is a thing that I prefer not to do;
I have to remember to do the disable of log every time I create the replication, well,
I can do, but meybe was better to have that inside the same command, like CREATE USER.

On the other hand, if you are very security conscious, you will
use SSL encrypted database connections.

True, it is annoying that the password is transmitted and might
be logged if you are not careful, but things are even worse:
The connection string is stored in "pg_subscription", and any
superuser can SELECT it from that table.


One option for you might be to use a password file on the server
and not put the password into the connection string.

You have to have the password *somewhere* for the standby to connect
to the primary.

Yours,
Laurenz Albe

[f***@tin.it]

I keep the CC, sorry, this is my first post here.

I used to never write the clear password on any production server.
So I am looking for the simplest way to keep safe.
I don't know about the "pg_subscription" where there is the connection string.
The connection with SSL will add complexity that is not required on local databases.
The best solution for my situation will be to create a role dedicated to this job, where I can add some limits: access to one database, access from one ip.
Thank you
Loris






----Messaggio originale----
Da: ***@cybertec.at
Data: 22-gen-2018 19.51
A: "***@tin.it"<***@tin.it>
Cc: <pgsql-***@lists.postgresql.org>
Ogg: Re: R: Re: create subscription, connection string, password in log not hide

Please keep the list in CC.

- using trust is not an options on a production system;

It isn't if you are very security concious.

I normally use trust authentication, limited to one IP address,
a user and the database "replication", on production systems.

the password will still be transmitted trought the net in clear form;
the log can be useful when looking for problem. Disabling the log is a thing that I prefer not to do;
I have to remember to do the disable of log every time I create the replication, well,
I can do, but meybe was better to have that inside the same command, like CREATE USER.

On the other hand, if you are very security conscious, you will
use SSL encrypted database connections.

True, it is annoying that the password is transmitted and might
be logged if you are not careful, but things are even worse:
The connection string is stored in "pg_subscription", and any
superuser can SELECT it from that table.


One option for you might be to use a password file on the server
and not put the password into the connection string.

You have to have the password *somewhere* for the standby to connect
to the primary.

Yours,
Laurenz Albe