DDL Support

[marcocitus]

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

Database-level statements:

• ANALYZE -- only propagated on tables
• COMMENT
• ALTER/CREATE/DROP DOMAIN Propagate DOMAIN types #4200
• ALTER/CREATE/DROP FUNCTION -- currently requires create_distributed_function
• ALTER/CREATE/DROP SEQUENCE -- only propagated when created via serial column. So, a minor tech debt
• ALTER DEFAULT PRIVILEGES ALTER DEFAULT PRIVILEGES is not propagated, also doesn't error out #7144
• GRANT/REVOKE on database objects other than on tables and schemas (type, function, sequence, ...)
• GRANT/REVOKE ... (COLUMN) ON TABLE TO ROLE adding GRANT/REVOKE ... (COLUMN) ON TABLE TO ROLE #7287
• SECURITY LABEL ON ROLE (Propagate SECURITY LABEL ON ROLE stmt #7304)
• SECURITY LABEL ON (objects other than roles)
• 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:

• ALTER TABLE that changes multiple columns
• ALTER TABLE that creates constraints without names ERROR: cannot create constraint without a name on a distributed table #4776
• ALTER TABLE that sets default to a function or sequence Alter distributed table add column bigserial/sequence fails #4721
• ALTER TABLE .. ADD CONSTRAINT .. UNIQUE USING INDEX .. ALTER TABLE ... ADD CONSTRAINT error #4774
• ALTER TABLE .. DROP EXPRESSION Postgres 13: Drop Expression #4070
• 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
• Identity columns Support IDENTITY columns with citus tables and table operations #4660

Session-level statements:

• SET
• SET CONSTRAINTS
• SET ROLE
• SET SESSION AUTHORIZATION
• SET TRANSACTION Propagate SET TRANSACTION commands when propagate_set_commands = 'local' #4945

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

• ALTER/CREATE/DROP DATABASE
  Adds create / drop database propagation support #7240
  Adds alter database set option #7181
  Adds alter database propagation - with and refresh collation  #7172
  Adds additional alter database propagation support #7253
• ALTER/CREATE/DROP TABLESPACE
• ALTER LARGE OBJECT
• ALTER SYSTEM
• CHECKPOINT
• GRANT/REVOKE .. ON DATATABASE
• REASSIGN .. OWNED (Adds REASSIGN OWNED BY propagation #7319)
• DROP OWNED BY
• 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
• ALTER/CREATE/DROP TEXT SEARCH CONFIGURATION
• 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
• ALTER/CREATE/DROP PUBLICATION
• ALTER/CREATE/DROP RULE
• ALTER/CREATE/DROP SUBSCRIPTION
• ALTER/CREATE/DROP TABLE -- could automatically have Citus manage new tables
• ALTER/CREATE/DROP VIEW

[jnels124]

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.