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 requiredDETAIL: 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
--
\c tests_pg16 postgres
You are now connected to database "tests_pg16" as user "postgres".
--
SELECT version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.3.1 20230508 (Red Hat 12.3.1-1), 64-bit
(1 row)
--
CREATE SUBSCRIPTION sub_pg16
CONNECTION 'host=/var/run/postgresql port=5437 user=user_pub_pg16 dbname=tests_pg16'
PUBLICATION pub_pg16;
psql:/home/benoit/tmp/password_required.sql:8: NOTICE: created replication slot "sub_pg16" on publisher
CREATE SUBSCRIPTION
--
ALTER SUBSCRIPTION sub_pg16 OWNER TO sub_owner ;
ALTER SUBSCRIPTION
--
\x
Expanded display is on.
\dRs+
List of subscriptions
-[ RECORD 1 ]------+------------------------------------------------------------------------
Name | sub_pg16
Owner | sub_owner
Enabled | t
Publication | {pub_pg16}
Binary | f
Streaming | off
Two-phase commit | d
Disable on error | f
Origin | any
Password required | t
Run as owner? | f
Synchronous commit | off
Conninfo | host=/var/run/postgresql port=5437 user=user_pub_pg16 dbname=tests_pg16
Skip LSN | 0/0
\du+
List of roles
-[ RECORD 1 ]-----------------------------------------------------------
Role name | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Description |
-[ RECORD 2 ]-----------------------------------------------------------
Role name | sub_owner
Attributes |
Description |
\l tests_pg16
List of databases
-[ RECORD 1 ]-----+----------------------
Name | tests_pg16
Owner | postgres
Encoding | UTF8
Locale Provider | libc
Collate | C
Ctype | C
ICU Locale |
ICU Rules |
Access privileges | =Tc/postgres +
| postgres=CTc/postgres+
| sub_owner=C/postgres
\x
Expanded display is off.
--
\c - sub_owner
You are now connected to database "tests_pg16" as user "sub_owner".
--
ALTER SUBSCRIPTION sub_pg16 DISABLE;
ALTER SUBSCRIPTION
--
ALTER SUBSCRIPTION sub_pg16 ENABLE;
ALTER SUBSCRIPTION
--
ALTER SUBSCRIPTION sub_pg16 RENAME TO sub_pg16_renamed;
ALTER SUBSCRIPTION
--
DROP SUBSCRIPTION sub_pg16_renamed ;
psql:/home/benoit/tmp/password_required.sql:26: 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.
--
\c - postgres
You are now connected to database "tests_pg16" as user "postgres".
--
DROP SUBSCRIPTION sub_pg16_renamed;
psql:/home/benoit/tmp/password_required.sql:30: 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.
--
ALTER SUBSCRIPTION sub_pg16_renamed OWNER TO postgres;
ALTER SUBSCRIPTION
--
DROP SUBSCRIPTION sub_pg16_renamed ;
psql:/home/benoit/tmp/password_required.sql:34: NOTICE: dropped replication slot "sub_pg16" on publisher
DROP SUBSCRIPTION
password_required.sql
Description: application/sql
--
\c tests_pg16 postgres
You are now connected to database "tests_pg16" as user "postgres".
--
SELECT version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.3.1 20230508 (Red Hat 12.3.1-1), 64-bit
(1 row)
--
CREATE SUBSCRIPTION sub_pg16
CONNECTION 'host=/var/run/postgresql port=5437 user=user_pub_pg16 dbname=tests_pg16'
PUBLICATION pub_pg16;
psql:/home/benoit/tmp/password_required2.sql:8: NOTICE: created replication slot "sub_pg16" on publisher
CREATE SUBSCRIPTION
--
ALTER SUBSCRIPTION sub_pg16 OWNER TO sub_owner ;
ALTER SUBSCRIPTION
--
\x
Expanded display is on.
\dRs+
List of subscriptions
-[ RECORD 1 ]------+------------------------------------------------------------------------
Name | sub_pg16
Owner | sub_owner
Enabled | t
Publication | {pub_pg16}
Binary | f
Streaming | off
Two-phase commit | d
Disable on error | f
Origin | any
Password required | t
Run as owner? | f
Synchronous commit | off
Conninfo | host=/var/run/postgresql port=5437 user=user_pub_pg16 dbname=tests_pg16
Skip LSN | 0/0
\du+
List of roles
-[ RECORD 1 ]-----------------------------------------------------------
Role name | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Description |
-[ RECORD 2 ]-----------------------------------------------------------
Role name | sub_owner
Attributes |
Description |
\l tests_pg16
List of databases
-[ RECORD 1 ]-----+----------------------
Name | tests_pg16
Owner | postgres
Encoding | UTF8
Locale Provider | libc
Collate | C
Ctype | C
ICU Locale |
ICU Rules |
Access privileges | =Tc/postgres +
| postgres=CTc/postgres+
| sub_owner=C/postgres
\x
Expanded display is off.
--
\c - sub_owner
You are now connected to database "tests_pg16" as user "sub_owner".
--
ALTER SUBSCRIPTION sub_pg16 CONNECTION 'host=/var/run/postgresql port=5437 user=user_pub_pg16 dbname=tests_pg16 application_name=preq';
psql:/home/benoit/tmp/password_required2.sql:20: ERROR: password is required
DETAIL: Non-superusers must provide a password in the connection string.
--
\c - postgres
You are now connected to database "tests_pg16" as user "postgres".
--
ALTER SUBSCRIPTION sub_pg16 SET (password_required = false) ;
ALTER SUBSCRIPTION
--
\c - sub_owner
You are now connected to database "tests_pg16" as user "sub_owner".
--
ALTER SUBSCRIPTION sub_pg16 CONNECTION 'host=/var/run/postgresql port=5437 user=user_pub_pg16 dbname=tests_pg16 application_name=preq';
psql:/home/benoit/tmp/password_required2.sql:28: ERROR: password_required=false is superuser-only
HINT: Subscriptions with the password_required option set to false may only be created or modified by the superuser.
--
ALTER SUBSCRIPTION sub_pg16 DISABLE;
psql:/home/benoit/tmp/password_required2.sql:30: ERROR: password_required=false is superuser-only
HINT: Subscriptions with the password_required option set to false may only be created or modified by the superuser.
--
ALTER SUBSCRIPTION sub_pg16 ENABLE;
psql:/home/benoit/tmp/password_required2.sql:32: ERROR: password_required=false is superuser-only
HINT: Subscriptions with the password_required option set to false may only be created or modified by the superuser.
--
DROP SUBSCRIPTION sub_pg16;
psql:/home/benoit/tmp/password_required2.sql:34: NOTICE: dropped replication slot "sub_pg16" on publisher
DROP SUBSCRIPTION
password_required2.sql
Description: application/sql
