I am working on streaming changes in my Postgres database using Debezium using the Postgres connector offered by Postgres. However, for the connector to work, the user must have REPLICATION and LOGIN rights. Although, the problem seems to be as simple as checking permissions in pg_roles
, it is not.
For example, I have a user with following permissions :
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
test_user | f | t | f | f | t | f | -1 | ******** | | f | | 36123
The test_user
has rolreplication
as false
even then I am able to create a replication slot using the user. Can anyone show some light about what exactly is the right method to get all privileges available to the user?
I also tried looking into the groups of which test_user
is a member but those groups also do not have the rolreplication
as true
. Here we are talking about logical_replication
and not physical_replication
. Am I missing something or is there any other table or query which I should use to get this?
EDIT :
Running select * from pg_replication_slots
gives:
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------------------------------------------------------+----------+-----------+--------+---------------+-----------+--------+------------+------+--------------+--------------+---------------------
test_slot | wal2json | logical | 60811 | test_database | f | t | 21642 | | 5955602 | 1FD/D00BA2E0 | 1FD/D107C598
test_user
does not haverolreplication
assigned directly. So I just wanted to know if it might be inheriting it from somewhere. And if it is so, how can I get to know about it?pg_roles
, the user which has been used to create replication slot does not seem to have the replication right. So my guess is it must be inheriting the right from somewhere. So how can I know if the user actually has the right for the same or not?test_user
cannot make a replication connection, nor can it create a replication slot.