Support for roles as owners of distrubuted tables
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
# Description
Currently, there are a lot of issues with tables whose owner is a `ROLE` (a user with `NOLOGIN` set, to be precise). Among them
1. Inability to `create_distibuted_table`, which fails with ` ERROR: connection to the remote node some-role@citus-rebalance-role-owner-bug-worker-1-1:5432 failed with the following error: FATAL: role "some-role" is not permitted to log in`
2. Fail to `rebalance` such tables, with the same error.
All those come from the fact that roles can't login, and Citus clearly doesn't handle such use-case well. While there are workarounds, like changing the ownership/set `LOGIN` to the role on workers temporarily, it would be nice to support such tables out of the box, or, at least, document the workarounds.
# Reproducing
The following script reproduces the issue on the default [Citus compose setup](https://github.com/citusdata/docker/blob/master/docker-compose.yml):
```sql
CREATE ROLE "some-role";
CREATE SCHEMA "some-role";
GRANT ALL ON SCHEMA "some-role" TO "some-role";
SET ROLE "some-role";
SELECT current_user;
CREATE TABLE "some-role".test_table
(
user_id int PRIMARY KEY
);
SELECT tableowner
FROM pg_tables
WHERE tablename = 'test_table';
SELECT create_distributed_table('"some-role".test_table', 'user_id');
-- Result
-- [08006] ERROR: connection to the remote node some-role@citus-rebalance-role-owner-bug-worker-1-1:5432 failed with the following error: FATAL: role "some-role" is not permitted to log in
```
# Workaround
Enable login on worker nodes:
```sql
SELECT run_command_on_workers($cmd$
SET citus.enable_ddl_propagation = false;
ALTER ROLE "some-role" WITH LOGIN ;
$cmd$)
```
Contributor guide
Assessment
This issue has not been assessed yet.