0

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
Amit Singh
  • 101
  • 1
  • 3
  • 1
    Please be a little more specific. Some SQL, some error messages, some log entries. I complete fail to understand "The test_user has rolreplication as false even then it has a replication to create replication slot." – Laurenz Albe Sep 13 '19 at 07:40
  • 1
    @LaurenzAlbe Edited the question. Also added result of pg_replication_slot. What kind of logs might be helpful. I am basically trying to understand how can I confirm if a user has logical replication rights. Clearly test_user does not have rolreplication 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? – Amit Singh Sep 13 '19 at 07:57
  • Debezium Postgres connector creates a logical replication slot in Postgres to read WALs. But if I check 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? – Amit Singh Sep 13 '19 at 08:01
  • As far as I understand, only a single level of privileges are inherited by a member. And I used the query specified in https://dba.stackexchange.com/a/56098/183656 to get all rights available to my user but still could not find it having access to replication privileges. So just wanted to know if I am missing something. – Amit Singh Sep 13 '19 at 08:03
  • test_user cannot make a replication connection, nor can it create a replication slot. – Laurenz Albe Sep 13 '19 at 11:59
  • I can't reproduce this at all. Can you show the actual commands you execute? – jjanes Sep 13 '19 at 14:49

0 Answers0