Hi,
I am confused about the new subscription parameter: password_required.
I have two instances. The publisher's pg_hba is configured too allow
connections without authentication. On the subscriber, I have an
unprivileged user with pg_create_subscription and CREATE on the database.
I tried using a superuser to create a subsciption without setting the
password_required parameter (the default is true). Then I changed the
owner to the unprivileged user.
This user can use the subscription without limitation (including ALTER
SUBSCRIPTION ENABLE / DISABLE). The \dRs+ metacommand shows that a
password is requiered, which is not the case (or it is but it's not
enforced).
Is this normal? I was expecting the ALTER SUBSCRIPTION .. OWNER to fail.
When I try to drop the subscription with the unprivileged user or a
superuser, I get an error:
ERROR: password is required
DETAIL: Non-superuser cannot connect if the server does not request a
password.
HINT: Target server's authentication method must be changed, or set
password_required=false in the subscription parameters.
I have to re-change the subscription owner to the superuser, to be able
to drop it.
(See password_required.sql and password_required.log)
I tried the same setup and changed the connexion string to add an
application_name with the unprivileged user. In this case, I am reminded
that I need a password. I tried modifying password_required to false
with the superuser and modify the connexion string with the unprivilege
user again. It fails with:
HINT: Subscriptions with the password_required option set to false may
only be created or modified by the superuser.
I think that this part works as intended.
I tried dropping the subscription with the unprivilege user: it works.
Is it normal (given the previous message)?
(see password_required2.sql and password_required2.log)
--
Benoit Lobréau
Consultant
http://dalibo.com