MIME-Version: 1.0
From: Jayadevan M <maymala.jayadevan@gmail.com>
Date: Tue, 12 Nov 2024 16:16:21 +0530
Message-ID: <CAFS1N4jSe=o=F=ezqD+rDgcndj5o_13S-b-gNWojTnVfaKGdTQ@mail.gmail.com>
Subject: PostgreSQL logical replication
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000034b8990626b4ee9c"
Archived-At: <https://www.postgresql.org/message-id/CAFS1N4jSe%3Do%3DF%3DezqD%2BrDgcndj5o_13S-b-gNWojTnVfaKGdTQ%40mail.gmail.com>
Precedence: bulk

--00000000000034b8990626b4ee9c
Content-Type: text/plain; charset="UTF-8"

Hello all,

I am using PostgreSQL 16.4. I tried to set up logical replication with
"postgres" user and all worked fine.  Then I created a new user
(my_replication) to be used for the purpose and couldn't figure out which
privileges are necessary. Initially I got an error like "ERROR:  permission
denied for database mydb" . So I used GRANT CREATE ON DATABASE..and that
error disappeared. I have also executed...
ALTER USER my_replication WITH replication;
GRANT usage ON SCHEMA public TO my_replication;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO my_replication;
When I try
 CREATE PUBLICATION my_publication for table public.term;
I am getting an error
ERROR:  must be owner of table term

What privileges should be granted so that I can do
 CREATE PUBLICATION my_publication FOR TABLES IN SCHEMA public;

Regards,
Jayadevan

--00000000000034b8990626b4ee9c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello all,<div><br><div>I am using=C2=A0PostgreSQL 16.4. I=
 tried to set up logical replication with &quot;postgres&quot;=C2=A0user an=
d all worked fine.=C2=A0 Then I created a new user (my_replication) to be u=
sed for the purpose and couldn&#39;t figure out which privileges are necess=
ary. Initially I got an error like &quot;ERROR: =C2=A0permission denied for=
 database mydb&quot; . So I used=C2=A0GRANT CREATE ON DATABASE..and that er=
ror disappeared. I have also executed...</div></div><div>ALTER USER my_repl=
ication WITH replication;<br>GRANT usage ON SCHEMA public TO my_replication=
;<br>GRANT SELECT ON ALL TABLES IN SCHEMA public TO my_replication;</div><d=
iv>When I try=C2=A0</div><div>=C2=A0CREATE PUBLICATION my_publication for t=
able public.term;</div><div>I am getting an error=C2=A0</div><div>ERROR: =
=C2=A0must be owner of table term</div><div><br></div><div>What privileges =
should be granted so that I can do=C2=A0</div><div>=C2=A0CREATE PUBLICATION=
 my_publication FOR TABLES IN SCHEMA public;</div><div><br></div><div>Regar=
ds,</div><div>Jayadevan</div><div><br></div></div>

--00000000000034b8990626b4ee9c--