Re: Privileges on PUBLICATION
От | Antonin Houska |
---|---|
Тема | Re: Privileges on PUBLICATION |
Дата | |
Msg-id | 2497.1667546930@antos обсуждение исходный текст |
Ответ на | Re: Privileges on PUBLICATION (Peter Eisentraut <peter.eisentraut@enterprisedb.com>) |
Ответы |
Re: Privileges on PUBLICATION
(Mark Dilger <mark.dilger@enterprisedb.com>)
Re: Privileges on PUBLICATION (Peter Eisentraut <peter.eisentraut@enterprisedb.com>) |
Список | pgsql-hackers |
Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote: > On 03.11.22 01:43, Antonin Houska wrote: > > Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote: > > > >> The CF entry is about privileges on publications. Please rebase that patch > >> and repost it so that the CF app and the CF bot are up to date. > > The rebased patch (with regression tests added) is attached here. > > Some preliminary discussion: > > What is the upgrade strategy? I suppose the options are either that > publications have a default acl that makes them publicly accessible, > thus preserving the existing behavior by default, or pg_dump would need to > create additional GRANT statements when upgrading from pre-PG16. I don't see > anything like either of these mentioned in the patch. What is your plan? So far I considered the first > You might be interested in this patch, which relates to yours: > https://commitfest.postgresql.org/40/3955/ ok, I'll check. > Looking at your patch, I would also like to find a way to refactor away the > ExecGrant_Publication() function. I'll think about that. > > I think you should add some tests under src/test/regress/ for the new GRANT > and REVOKE statements, just to have some basic coverage that it works. > sql/publication.sql would be appropriate, I think. I thought about the whole concept a bit more and I doubt if the PUBLICATION privilege is the best approach. In particular, the user specified in CREATE SUBSCRIPTION ... CONNECTION ... (say "subscription user") needs to have SELECT privilege on the tables replicated. So if the DBA excludes some columns from the publication's column list and sets the (publication) privileges in such a way that the user cannot get the column values via other publications, the user still can connect to the database directly and get values of the excluded columns. As an alternative to the publication privileges, I think that the CREATE SUBSCRIPTION command could grant ACL_SELECT automatically to the subscription user on the individual columns contained in the publication column list, and DROP SUBSCRIPTION would revoke that privilege. Of course a question is what to do if the replication user already has that privilege on some columns: either the CREATE SUBSCRIPTION command should raise ERROR, or we should introduce a new privilege (e.g. ACL_SELECT_PUB) for this purpose, which would effectivelly be ACL_SELECT, but hidden from the users of GRANT / REVOKE. If this approach was taken, the USAGE privilege on schema would need to be granted / revoked in a similar way. What do you think about that? -- Antonin Houska Web: https://www.cybertec-postgresql.com
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Pavel StehuleДата:
Сообщение: Re: PL/pgSQL cursors should get generated portal names by default