Users and roles in a Postgres Pro Shardman cluster can be usual unmanaged PostgreSQL and managed introduced and managed by Postgres Pro Shardman. You can manage them separately on each server or cluster-wide.

Postgres Pro Shardman also uses concepts of managed users and managed roles. And only the managed users (or roles) can create and own other Postgres Pro Shardman cluster-wide objects, such as sharded or replicated tables. Managed roles are applied for the entire distributed cluster. However, even if roles are managed, the privileges granted to a role can be specific to an object.

Operations on managed users are always performed on all replication groups simultaneously. For example, when you include a managed role in some other role or drop it, this operation will be performed on all replication groups.

You can create a managed user with a CREATE USER ... GLOBAL statement, for example:

CREATE USER someuser ENCRYPTED PASSWORD 'somepass' GLOBAL;

When a managed 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 managed 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 managed 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 managed user later.

Managed users can be created only by user with CREATEROLE permission on all cluster nodes.

ALTER and DROP statements for managed users are broadcasted to all replication groups. When a role is granted to a managed user, this operation is also broadcasted. Renaming a managed user is not supported since this invalidates md5/scram-sha-256 passwords stored in user mappings.

The list of managed users is stored in the shardman.users table.

The role specified in PgSuUsername (usually, postgres) is also created as managed user during cluster initialization. However, the role specified in PgReplUsername is created as unmanaged user on each replication group.

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.