DDL Support

Open marcocitus opened this issue 4 years ago • 1 comments

The following DDL/utility commands are currently not propagated to worker nodes by Citus:

Database-level statements:

[x] ANALYZE -- only propagated on tables
[ ] COMMENT
[x] ALTER/CREATE/DROP DOMAIN https://github.com/citusdata/citus/issues/4200
[x] ALTER/CREATE/DROP FUNCTION -- currently requires create_distributed_function
[x] ALTER/CREATE/DROP SEQUENCE -- only propagated when created via serial column. So, a minor tech debt
[ ] ALTER DEFAULT PRIVILEGES https://github.com/citusdata/citus/issues/7144
[x] GRANT/REVOKE on database objects other than on tables and schemas (type, function, sequence, ...)
[ ] GRANT/REVOKE ... (COLUMN) ON TABLE TO ROLE #7287
[x] SECURITY LABEL ON ROLE (https://github.com/citusdata/citus/pull/7304)
[ ] SECURITY LABEL ON (objects other than roles)
[x] VACUUM -- only propagated on tables

Schema-level statements:

ALTER SCHEMA .. OWNER TO ..

create schema my_schema;
create role non_super_user_role;
alter schema my_schema owner to non_super_user_role ;

SELECT pg_get_userbyid(nspowner) AS schema_owner
FROM pg_namespace
WHERE nspname = 'my_schema';
┌─────────────────────┐
│    schema_owner     │
├─────────────────────┤
│ non_super_user_role │
└─────────────────────┘

select result from run_command_on_workers ($$
  SELECT pg_get_userbyid(nspowner) AS schema_owner
  FROM pg_namespace
  WHERE nspname = 'my_schema'
$$);
┌─────────────┐
│   result    │
├─────────────┤
│ onurctirtir │
│ onurctirtir │
└─────────────┘
(2 rows)

Table-level statements:

[x] ALTER TABLE that changes multiple columns
[x] ALTER TABLE that creates constraints without names https://github.com/citusdata/citus/issues/4776
[x] ALTER TABLE that sets default to a function or sequence https://github.com/citusdata/citus/issues/4721
[x] ALTER TABLE .. ADD CONSTRAINT .. UNIQUE USING INDEX .. https://github.com/citusdata/citus/issues/4774
[ ] ALTER TABLE .. DROP EXPRESSION https://github.com/citusdata/citus/issues/4070
[x] ALTER/CREATE/DROP TRIGGER
[ ] ALTER TABLE .. ALTER COLUMN on a distribution column
[ ] Foreign keys between 2 distributed tables without a distribution column
[ ] Foreign key from a reference table to a distributed table
[x] Identity columns https://github.com/citusdata/citus/issues/4660

Session-level statements:

[ ] SET
[ ] SET CONSTRAINTS
[ ] SET ROLE
[ ] SET SESSION AUTHORIZATION
[x] SET TRANSACTION https://github.com/citusdata/citus/pull/4945

System-level statements (may be outside of Citus scope):

[x] ALTER/CREATE/DROP DATABASE https://github.com/citusdata/citus/pull/7240 https://github.com/citusdata/citus/pull/7181 https://github.com/citusdata/citus/pull/7172 https://github.com/citusdata/citus/pull/7253
[ ] ALTER/CREATE/DROP TABLESPACE
[ ] ALTER LARGE OBJECT
[ ] ALTER SYSTEM
[ ] CHECKPOINT
[x] GRANT/REVOKE .. ON DATATABASE
[x] REASSIGN .. OWNED (https://github.com/citusdata/citus/pull/7319)
[x] DROP OWNED BY
[x] GRANT one_user TO another_user;
[ ] GRANT/REVOKE ON PARAMETER ...

Statements that are typically only used by extensions (which are already propagated):

[ ] ALTER/CREATE/DROP ACCESS METHOD
[ ] ALTER/CREATE/DROP CONVERSION
[ ] ALTER/CREATE/DROP EVENT TRIGGER
[ ] ALTER/CREATE/DROP FOREIGN DATA WRAPPER
[ ] ALTER/CREATE/DROP LANGUAGE
[ ] ALTER/CREATE/DROP OPERATOR
[ ] ALTER/CREATE/DROP OPERATOR CLASS
[ ] ALTER/CREATE/DROP OPERATOR FAMILY
[x] ALTER/CREATE/DROP TEXT SEARCH CONFIGURATION
[x] ALTER/CREATE/DROP TEXT SEARCH DICTIONARY
[ ] ALTER/CREATE/DROP TEXT SEARCH PARSER
[ ] ALTER/CREATE/DROP TEXT SEARCH TEMPLATE
[ ] CREATE/DROP TRANSFORM
[ ] CREATE/DROP CAST

Statements work as intended, but could have a distributed implementation:

[ ] ALTER/CREATE/DROP FOREIGN TABLE / FOREIGN DATA WRAPPER / SERVER / USER MAPPING
[ ] ALTER/CREATE/DROP MATERIALIZED VIEW
[x] ALTER/CREATE/DROP PUBLICATION
[ ] ALTER/CREATE/DROP RULE
[ ] ALTER/CREATE/DROP SUBSCRIPTION
[ ] ALTER/CREATE/DROP TABLE -- could automatically have Citus manage new tables
[x] ALTER/CREATE/DROP VIEW

Mar 11 '21 18:03 marcocitus

There also appears to be an issue with table ownership on distributed tables. (readonly and readwrite already exist) as the postgres user:

create role schemaadmin;
    grant readwrite to schemaadmin;
    grant schemaadmin to a;
    grant schemaadmin to b;
    create schema if not exists temporary authorization schemaadmin;
    grant usage on schema temporary to public;
    revoke create on schema temporary from public;
    grant select on all tables in schema temporary to readonly;
    grant select on all sequences in schema temporary to readonly;
    grant usage on schema temporary to readonly;
    alter default privileges in schema temporary grant select on tables to readonly;
    alter default privileges in schema temporary grant select on sequences to readonly;

    grant select on all tables in schema temporary to readonly;
    grant select on all sequences in schema temporary to readonly;
    grant usage on schema temporary to readonly;
    alter default privileges in schema temporary grant select on tables to readonly;
    alter default privileges in schema temporary grant select on sequences to readonly;

    grant insert, update, delete on all tables in schema temporary to readwrite;
    grant usage on all sequences in schema temporary to readwrite;
    alter default privileges in schema temporary grant insert, update, delete on tables to readwrite;
    alter default privileges in schema temporary grant usage on sequences to readwrite;

Then when logging in as user a and executing:

create unlogged table if not exists temporary.token_temp as table token limit 0;
alter table if exists temporary.token_temp owner to schemaadmin;
select create_distributed_table('temporary.token_temp', 'token_id', colocate_with => 'token');

I am able to successfully

truncate temporary.token_temp

as user a but when i attempt to login with user b I get the following error on the coordinator:

ERROR:  failure on connection marked as essential: 10.224.0.6:7433```

and on the worker i see

ERROR:  permission denied for table nft_temp
STATEMENT:  SET citus.enable_ddl_propagation TO 'off';
	LOCK temporary.nft_temp IN ACCESS EXCLUSIVE MODE;
	SET citus.enable_ddl_propagation TO 'on'

This only happens for distributed tables.

Dec 13 '23 14:12 jnels124