PostgreSQL Role management
Problem to solve
PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.
Further details
Having a simple CRD (SGPostgresRole
) for managing database users using the familiar K8s API could help to improve user experience by allowing easy access and creation of databases.
This CRD proposal specification, is an abstraction over the syntax and execution of the commands to manage the roles. This abstraction allows the user to declare in a YAML syntax the users/permissions that want to be created, and SG will perform a synchronization to all clusters that reference this SGPostgresRole
. This synchronization must work for every add/edit/delete of roles to the SGPostgresRole
without any restart required.
The syntax CREATE ROLE adds a new role to a PostgreSQL database cluster. A role is an entity that can own database objects and have database privileges; a role can be considered a “user”, a “group”, or both depending on how it is used.
Roles are defined at the database cluster level, and so are valid in all databases in the cluster, for this reason, we can define the CR SGPostgresRole
and then reuse the configuration in every cluster that make use of it.
In PostgreSQL CREATE USER
is the same as CREATE ROLE
with attribute LOGIN
the NOLOGIN
is the default in CREATE ROLE
, this is an important difference in that StackGres will make use of LOGIN
by default, and passing NOLOGIN
will be required to disallow the LOGIN.
CREATE ROLE
has many parameters, and some of the parameters has some negative alternatives, that in our case could be omitted since are the defaults, so the list of basic attributes that needs to be implemented are:
- NOLOGIN (LOGIN will be explicitly the default)
- SUPERUSER
- CREATEDB
- CREATEROLE
- REPLICATION
- BYPASSRLS
The password must be set by a SecretKeySelector
, and every time this secret or the attributes changes, the operator must reconcile the state in the PostgreSQL Cluster.
Synchronization will not be bi-directional: if changes are applied manually (locally) in the database cluster they will not be reflected back into the SGPostgresRole
(since the SGPostgresRole may affect many clusters at once, and we have no way to monitor and detect local changes). But changes to SGPostgresRole
will synced back to clusters potentially undoing manual changes.
The SGPostgresRole
created in the database could be used in conjunction with pg_hba.conf (see #315) to define futher access rules to the cluster, in this case, you have to set the role name in that CR to pass the user.
Proposal
The basic CRD kind SGPostgresRole:
apiVersion: apiextensions.k8s.io/v1beta1
kind: CustomResourceDefinition
metadata:
name: sgpostgresrole.stackgres.io
spec:
group: stackgres.io
versions:
- name: v1beta1
served: true
storage: true
scope: Namespaced
names:
kind: SGPostgresRole
listKind: SGPostgresRoleList
plural: sgpostgresroles
singular: sgpostgresrole
shortNames:
- sgpgrole
An example of a SGPostgresRole
specification:
apiVersion: stackgres.io/v1beta1
kind: SGPostgresRole
metadata:
name: user-demo-superuser
namespace: myworkspace
spec:
role: myapp
password:
key: passKeyname
name: mySupersecretName
ownDatabase: true
option:
attributes:
- CREATEDB
- CREATEROLE
connectionLimit: 10
validUntil: '2021-01-01'
configurationParameters:
default_transaction_read_only: on
work_mem: 512MB
The secret holding the password could be of any format, it just a secret with a data key, in this example, the key is passKeyname
that is mapped to the CR password
as a secretKeyRef
:
apiVersion: v1
kind: Secret
metadata:
name: mySupersecretName
namespace: myworkspace
data:
passKeyname: Mzk1MjgkdmRnN0pi
This CR basically creates the following SQL:
CREATE ROLE myapp WITH LOGIN PASSWORD '******' CREATEDB CREATEROLE VALID UNTIL '2021-01-01' CONNECTION LIMIT 10;
ALTER ROLE myapp SET default_transaction_read_only TO 'on';
ALTER ROLE myapp SET work_mem TO '512MB';
CREATE DATABASE myapp OWNER myapp;
The role
is the only required parameter in the CRD, if the password
is missing it will set it to NULL
creating the following sql: CREATE ROLE myapp WITH LOGIN PASSWORD NULL
.
Special care should be taken to make commands more or less idempotent, that means, this will run ALTER ROLE if the role already exist and reset all the attributes to the ones in the CR.
There is an special property that's an extension not in CREATE ROLE
, that's ownDatabase
, this simply creates a database (if not exists) with the same name of the role and the owner to the same role, it's optional and false
by default.
The SGPostgresRole
could be reused in differents clusters, the integration of the role in a SGCluster
could be done adding the roles to create in the configuration as an array of roles:
apiVersion: stackgres.io/v1beta1
kind: SGCluster
metadata:
name: stackgres
spec:
configurations:
sgPostgresRoles:
- user-demo-superuser
- user-demo-application
- my-role-replication
This will create all the roles created from the CustomResource in the database cluster, different clusters could use different roles or share the same roles depending on the needs.
NOTE: Is out of scope to define
GRANT
privileges in the role management since different cluster could have different databases and tables.