Special role for subscriptions

Hi hackers! In postgresql 10 and 11 only superuser can create/alter subscriptions. If there was a special role (like pg_monitor), it would be more easy to grant control on subscriptions. I can make a patch if there are no objections against it.

Re: Special role for subscriptions

and superuser. 07.11.2018, 00:52, "Stephen Frost" : > Greetings, > > * Evgeniy Efimkin (efim...@yandex-team.ru) wrote: >>  As a first step I suggest we allow CREATE SUBSCRIPTION for table owner only. > > That's a nice idea but seems like we would want to have a way to filt

Connection limit doesn't work for superuser

Connection limit doesn't work for superuser Hi hackers! It would be nice if ALTER USER ... WITH CONNECTION LIMIT will work for superuser. It would protect against connection leaks. e.g. we have two superusers, one of them reached connection limit but not max_connections, the other is still

Re: [WIP] Special role for subscriptions

09.11.2018, 15:24, "Evgeniy Efimkin" : > Hi! > In order to support create subscription from non-superuser, we need to make > it possible to choose tables on the subscriber side: > 1. add `FOR TABLE` clause in `CREATE SUBSCRIPTION`: >    ``` > CREATE SU

Re: Special role for subscriptions

ons? 07.11.2018, 00:52, "Stephen Frost" : > Greetings, > > * Evgeniy Efimkin (efim...@yandex-team.ru) wrote: >>  As a first step I suggest we allow CREATE SUBSCRIPTION for table owner only. > > That's a nice idea but seems like we would want to have a way to filte

Re: Special role for subscriptions

Hi! As a first step I suggest we allow CREATE SUBSCRIPTION for table owner only. 03.11.2018, 19:20, "Stephen Frost" : > Greetings, > > * Evgeniy Efimkin (efim...@yandex-team.ru) wrote: >>  In postgresql 10 and 11 only superuser can create/alter subscriptions. >>  If

Re: [WIP] CREATE SUBSCRIPTION with FOR TABLES clause (table filter)

Hello! In latest patch i removed `FOR ALL TABLES` clause and `alltables` parameter, now it's look more simple. Add new system view pg_user_subscirption to allow non-superuser use pg_dump and select addition column from pg_subscrption Changes docs. Thanks! Ефимкин Евгений diff --git

Re: [WIP] CREATE SUBSCRIPTION with FOR TABLES clause (table filter)

lumn 03.12.2018, 09:06, "Andrey Borodin" : > Hi, Evgeniy! > > Thanks for working on the feature. >>  28 нояб. 2018 г., в 21:41, Evgeniy Efimkin >> написал(а): >> >>  Hello! >>  I wrote some tests(it's just 01_rep_changes.pl but for non superuser) and >&

Re: [WIP] CREATE SUBSCRIPTION with FOR TABLES clause (table filter)

Hello! New draft attached with filtering table in subscription (ADD/DROP) and allow non-superusers use` CREATE SUBSCRIPTION` for own tables. 14.11.2018, 18:10, "Evgeniy Efimkin" : > Hello! > I started work on patch (draft attached). Draft has changes related only to > `

Re: [WIP] CREATE SUBSCRIPTION with FOR TABLES clause (table filter)

Hello! I wrote some tests(it's just 01_rep_changes.pl but for non superuser) and fix `DROP TABLE` from subscription. Now old and new tests pass. 22.11.2018, 16:23, "Evgeniy Efimkin" : > Hello! > New draft attached with filtering table in subscription (ADD/DROP) and allow >

Re: Special role for subscriptions

Hi! > Perhaps we would want something at database level different from GRANT > CREATE ON DATABASE, but only for subscriptions? How about 4 checks to create subscription for nonsuperuser? 1. Special role for create subscription 2. CREATE ON DATABASE privilege 3. INSERT, UPDATE, DELETE, TRUNCATE,

Re: Special role for subscriptions

Hi! > - If the user's permissions are later revoked, the subscription is unaffected. Now it work the same, if we revoke superuser, subscription is unaffected and replication still work Don't check grants in target database is very dangerous, i create publication with system tables(it's not

Re: Special role for subscriptions

Hi! > I view that as the first step towards building a more granular privilege > system for subscription creation, and that was the second half of what I > was trying to say before- I do think there's value in having something > more granular than just "this role can create ANY subscription". As

Re: Special role for subscriptions

Hi! Thanks for comments! > Just letting the superuser decide who gets to create subscriptions > seems good enough from here. I've prepare patch with new system role, i'm not sure about name, called it "pg_subscription_users". In that patch we don't check permissions on target tables, i don't

Re: Special role for subscriptions

Hi! > Currently, user with pg_subscription_users can create subscription into any > system table, can't they? > We certainly need to change it to more secure way. No, you can't add system tables to publication. In new patch i add privileges checks on target table, non superuser can't

Re: Special role for subscriptions

Hi! I think it's good idea to able create subscription for database owner, but owner do not have permission on all tables in database. At the begging Stephen Frost said about table filter at subscription side. https://www.postgresql.org/message-id/20181106215244.GH18594%40tamriel.snowman.net I

Re: Special role for subscriptions

Hi! >  * What are the most important use cases here? Are we just trying to > avoid the unnecessary use of superuser, or is there a real use case for > subscribing to a subset of a publication? For instance in target database we do not have permission on some table used in publication, but we

Re: [WIP] CREATE SUBSCRIPTION with FOR TABLES clause (table filter)

Hi! Thank you for comments, I’ll fix all inconsistencies in documentation. > I don't understand this requirement. There are a bunch of > password-less, still secured authentication that Postgres can use > depending on the situations. To name one: peer. So this concept > design looks rather broken

Re: Special role for subscriptions

Hi! > These are basically that the truncate, insert, delete and insert > rights for the role creating the subscription. Why would we actually > need that? It's for security reasons. Because possible to attack target server. If publication have system tables for instance pg_authid >

Re: [WIP] CREATE SUBSCRIPTION with FOR TABLES clause (table filter)

Hi! Thanks for comments! I fixed build and return lines about subscription apply process in doc Efimkin Evgeny diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 3f2f674a1a..5d211646bf 100644 --- a/doc/src/sgml/logical-replication.sgml +++

Re: [WIP] CREATE SUBSCRIPTION with FOR TABLES clause (table filter)

Hi! Thanks for comments 1. fixed 2. in non-superuser we have to use authorization, and FOR table clause, i don't known how merge both files. 3. fixed 4. fixed and run pgindent 5. add some new cases in regression test Efimkin Evgeny diff --git a/doc/src/sgml/logical-replication.sgml

Re: [WIP] CREATE SUBSCRIPTION with FOR TABLES clause (table filter)

Hi! 1. done 2. rename to pg_user_subscriptions 3. by pg_dump, i checked upgrade from 10 to 12devel, it's work fine 4. done 5. done 6. I took it from AlterSubscription_refresh, in that function no any free() 7. done Ефимкин Евгений diff --git a/doc/src/sgml/logical-replication.sgml

Re: [survey] New "Stable" QueryId based on normalized query text

> One problem with pg_stat_statement's normalized query is that it's not > stable, it's storing the normalized version of the first query string > passed when an entry is created. So you could have different strings > depending on whether the query was fully qualified or relying on > search

Re: [survey] New "Stable" QueryId based on normalized query text

Hi! What about adding new column in pg_stat_statements e.g. sql_id it's hash from normalized query. Аnd add function which get that hash (using raw_parser, raw_expression_tree_walker) for any query ` postgres=# select get_queryid('select 1'); get_queryid - 680388963 (1 row) `