21.7. Roles in Distributed System #
21.7.1. Managing Users and Roles #
Users and roles in a Postgres Pro Shardman cluster are usual PostgreSQL users and roles. You can manage them separately on each server or globally, using broadcast DDL. Postgres Pro Shardman also uses concepts of global users and global roles. And only the global users (or roles) can create and own other Postgres Pro Shardman cluster-wide objects, such as sharded or global tables. Operations on such users are always performed on all replication groups simultaneously. For example, when you include a global role in some other role or drop it, this operation will be performed on all replication groups.
You can create a global user with a CREATE USER ... IN ROLE global
statement, for example:
CREATE USER someuser ENCRYPTED PASSWORD 'somepass' IN ROLE global;
When a global user is created, Postgres Pro Shardman automatically creates user mappings on all replication groups and grants this user with access to all foreign servers corresponding to existing replication groups. Therefore, when you create a global user, you need to specify either a cleartext password, so that it can be saved in a user mapping, or no password at all. A passwordless global user or role is unable to access foreign servers, but you can use such a role to accumulate some permissions and grant it to different users. You can also set a password for a passwordless global user later.
Global users can be created only by user with CREATEROLE
permission on all cluster nodes.
ALTER
and DROP
statements for global users are broadcasted to all replication groups. When a role is granted to a global user, this operation is also broadcasted. Renaming a global user is not supported since this invalidates md5/scram-sha-256 passwords stored in user mappings.
The list of global users is stored in the shardman.users
table.
The role specified in PgSuUsername
(usually, postgres
) is also created as global user during cluster initialization. However, the role specified in PgReplUsername
is created as local user on each replication group.
The role global
is reserved and cannot be used directly in a Shardman cluster. Note that 'global' is not a really defined role but just a reserved word.
21.7.2. Managing Permissions for Sharded Tables #
In Postgres Pro Shardman, a sharded table is basically a partitioned table where partitions are either local shards or foreign tables referencing shards in other replication groups.
Permissions granted on a sharded table are broadcasted to all replication groups and to all partitions of the table.
When a new replication group is added to a cluster, shardmanctl copies the schema from a random existing replication group to the new one. It also creates a foreign server for the new replication group on all existing replication groups and recreates foreign servers on new replication groups. Permissions for the created foreign servers and user mappings are copied from a random foreign server in an existing replication group. In the new replication group, for each partition of the sharded table shardmanctl creates a foreign table referencing the existing shard and replaces the partition with this foreign table. Later some of these foreign tables can be replaced by real tables. This happens during the shardmanctl nodes add
rebalance stage when rebalance is enabled. Data for these partitions is transferred from existing nodes using logical replication. When shardmanctl creates tables (or foreign tables), it copies permissions from the parent table. The parent table must already have correct permissions since they were copied from an existing replication group.