In software development the concept of feature toggles are used to selectively turn on and off features. They are, for example, used to restrict some newly introduced features to a select group to see how these features work. While this concept has been long used for user-facing application code, it is also a practice that is useful for database code.

In databases, feature toggles are used for several loosely related purposes. One common use is to separate, or decouple, rollout of database features from deployment of new versions of the application code. This allows developers to release software faster and with less risk.

Feature toggles involve wrapping features in ways that allow you to toggle them on and off for different groups of users. They are designed to give developers a way to manage their feature rollouts and ‘dark launch’ without having to synchronize with the application deployment schedule, and then to quickly remove access to new features that have problems.

In databases, feature toggles are sometimes confused with access controls, which are traditionally used in databases to allow access only to those parts of the database that are necessary for the role of a particular user. A toggle or switch must be able to work easily with procedural SQL, normally by indicating whether the current user is a member of the test group whereas access control will affect the result SQL Queries.

For database work, database roles are, in many RDBMSs, used for controlling access. In their traditional role of data security they ease the work of admin because they can be given membership to other database roles, fixed or user-defined; and inherit their access rights. A large number of users can thereby have their access rights controlled by a single DCL command. Because membership of any user can be queried easily, returning true or false, it is a simple way of implementing feature toggles as well. This is because they are built into the relational database system, designed for inheritance of access privileges, and are both enforced and managed without any extra work.

Database requirements for toggling

It is probably useful at this point to list the problems that database feature toggles solve, and the real-life uses to which they are put.

Allowing partial feature rollout – ‘canary releases’

Sometimes, you will need to control the rollout of database changes for a new feature, so that it can be introduced first to specific groups of ‘beta’ users. The feature rollout is restricted to a selected group so that if there are flaws to the new feature it affects only a few people who are good at giving feedback and aren’t ‘spooked’ by what we tend to call ‘wet paint’ features that might need more work.

If the new feature is severely wrong, you need to be able to switch it out completely so that the users simply revert to the current system. This switch must be simple and well-tested to ensure that reversion is complete

A/B testing

Sometimes, it is difficult to know in advance which is the best approach to the design of a feature. You will need to implement it in alternative forms, for two or more different groups of users, to compare their performance, usability, or other metrics, before making a permanent decision.

Parallel development

Sometimes, different development teams or individuals need to work both independently and concurrently on database features, without conflicts. The difficulty with this is in meeting a common development and deployment schedule because it is difficult to predict when either new feature will be complete and pass all its functional and acceptance tests. They cannot commit to a shared release date so will want a way of toggling these features on or off independently.

Hotfixes and emergency changes

Developers want to be able to do hotfixes or emergency changes to the database without a full release cycle. Instead, the change can be toggled in or out.

Decoupling deployments

Feature toggles can decouple the deployment of database changes from the release of new application versions if the interface between database and application isn’t changed. This allows for more frequent and independent updates to the database schema.

Conditional feature activation for a database

Sometimes, developers are forced to maintain several variants of a database purely because of international differences in commercial procedures, legal requirements, or financial obligations, even when most of the database features are common to all.

Using a single version of a database schema, you need to install several copies, activating database-level features based on context without changes in the schema. The correct settings are put in place on deployment for each installed database.

Concurrent handling of different ‘roles’ for the various types of users

Sometimes it is necessary to host several different types of users on the same database, where individuals or groups (roles) have different tasks, culture, access requirements or geographic locations. When this becomes more complex than access control, it must be possible to switch ‘toggles’ in the database to select the correct version of any procedures and views that need to vary between roles.

Practical techniques

The following sections describe a few typical techniques that are used by many developers to achieve feature toggling.

Application Contexts and Session Contexts

Within applications, it is usual to employ toggles and switches, usually via special application tables that are manipulated by a privileged user. This essentially switches features on or off, but unless you can somehow distinguish the identity of individual users or application logons, it can be complicated to do any toggling of features for individual users or groups of users, or to switch groups of users between different variants of a feature.

The first problem is in identifying the user. If the current session is identified by the user login, then it is a simple matter, and easy to determine whether the user should access the feature.

For applications that share connections, such as websites, Session, or application, contexts are used to identify each session. These are valuable when the application does not make connection to the database via individual user connections, but instead assigns sessions to which the user identity is attached. Basically, the application is responsible for identifying the user. Many relational database systems support Application contexts, or Session contexts, because they can provide fine-grained access control by providing key/value attributes that relate to the current session. As well as holding temporary values, the values in these columns can be to switch features on and off, or to determine whether the session can use a feature. However, if the application user exists as a user or role, then its membership is easily ascertained and easily administered.

Using Database Roles

For most of the leading relational databases, such a device, the database role, already exists within the SQL standard and it can be used to toggle features for groups of users. Relational databases have faced the problem of having users with different organizational roles for a long time. After all, HR records contain highly personal information, and it is standard practice to allow users access only to the information they require for their role within the organisation. Commercial databases have always had the wherewithal to control access to database objects according to the user’s role. Role-based access control (RBAC) also makes access controls easier to administer because users inherit the access rights from the roles of which they are members. To allow a more versatile, and tidier approach, schemas can be used alongside RBAC in the major RDBMSs.

Role-based Access Control (RBAC)

All established techniques for feature toggling rely on a database administrator assigning individual users to roles. You could, for example, become a ‘guinea-pig’ for a new feature by being assigned to, or being a member of, a particular role. The SQL role feature is even more powerful than this because you can assign a role to a role so that, for example, with one line of DCL (Data Control Language) SQL, an administrator can allow all members of the HR role to become testers for your new feature.

Using roles

Database user roles are conventionally used for RBAC to group together a set of privileges or permissions. Instead of assigning permissions to individual users, permissions are granted to roles, and users are assigned to one or more roles. Roles can be granted to users or to roles to provide specific privileges, and roles can be revoked to remove those privileges.

For a feature that needs to be switched, all it needs to do is to check that the user (or application role) has membership of a role with a certain name. If it has, only then will the feature work. The feature will execute different procedural code according to whether the user is a member of that role, either directly or indirectly. To make this happen, the logic must be built into the feature

Using roles and schemas together

Schema-based access control is available on the RDBMSs that are designed for enterprises, such as PostgreSQL and SQL Server. With the introduction of schemas, it becomes easy to apply different logic for each role without putting any extra logic in the feature; we simply assign roles to default to specific schemas. If the feature toggle is based within the application, then the application can, instead, explicitly specify the correct schema for that group of users. Within each schema there is a copy of the ‘interface’ functions, views and procedures.

This way, the features are used when they are used as a default schema. This can be done by the user accessing objects without a schema prefix, so the user will, by default, use the version of each function that is within their default schema. It may also be done by having the application append the schema when calls are made to the database manager.

This means that functions and views that implement the feature can present the same interface to the application, but each version of these objects can provide a different functionality. Different roles see a different ‘variant’ of the database, but all roles share the base tables of the database.

Schema-based access control is designed to ensure that users, by their membership of roles, have access only to the information that they need for their group role, within the organization. For example, only HR users will have access to salary information, and only customer-facing staff will have access to customer information.

I explain Schema-based access control in depth here in Schema-Based Access Control for SQL Server Databases

Determining whether a user is a ‘Guinea Pig’

In our practical examples, we will assume that each user of the system is defined within the system as a database ‘user’, because of the advantages to auditing that this confers. This is unlikely to be true of a website database or a microservice, but here there are several group credentials that can each represent a group of users. This application user is then assigned one or more roles.

Whatever type of role-based access system you use, you are likely to find that the first information you need in procedural code is whether the current user of the connection is allowed to use the feature. For example, let’s imagine you are live testing a new ‘Payroll’ system. Initially, it will be accessible only to members of the ‘Guineapig’ role, who will evaluate it. Membership of ‘Guineapig’ must include only existing members of the HR role. To preserve the current sanity levels of the DBA, it must be possible to revert everyone back to the current Payroll system very quickly, using just one line of DCL code that drops HR from membership of the ‘Guineapigs’ role.

The simplest type of ‘feature switch’ merely determines whether a user is, either directly or by inheritance, a member of a particular role. The interface with the application(s) can then determine how to handle the request. In our payroll example, any user who is a member of the ‘Guineapigs’ role will be allowed to use it. To switch the feature off for HR, you start by dropping membership of the ‘HR’ role from the ‘Guineapigs’ role. You’d then have to ensure that you haven’t given membership member of the ‘Guineapigs’ role by inheritance. Because roles can be nested, it pays to ensure with a system query that that they haven’t an indirect membership, otherwise it isn’t entirely simple to determine this.

In the SQL standard, there is a clear distinction between users and roles, because users do not automatically pass on their privileges to their members, whereas roles do. The better RDBMSs all follow this rule.

SQL Server

In SQL Server, there is a difference between a user and the roles assigned to it. It is easy to determine if a user is a member of a particular group role. This code will tell you if the current user is assigned to a ‘Guineapigs’ role (1 if so, else 0)

Oracle

Oracle, like SQL Server, supports roles as a means of managing access and permissions, but with the addition of predefined user roles that can inherit privileges from other one. However it is sufficient for our purposes to use an ordinary role. The following code will tell you if the current user is assigned to a ‘Guineapigs’ role (1 if so, else 0)

PostgreSQL

In PostgreSQL, there is no real difference between a login role and a group role. To allow users to become members of roles, your SQL roles in PostgreSQL must have the INHERIT attribute, which is the default. With the right privileges, you, in your user role, can create group roles and assign them to, or revoke them from, other user roles.

Conversely, to prevent users from becoming members of users, it is best to specify NOINHERIT for your SQL users.

MariaDB

Roles were implemented as a GSoC 2013 project. In MariaDB, you can create roles, grant privileges to roles, and assign users to roles. Here you can use roles to switch features on and off easily. The only difference seems to be that a role granted directly to a user must be ‘Set’ before inheriting its privileges, unless it is a default role, so it would seem that feature toggles would be easiest to implement if every user had a default role that would determine their privileges.

Implementation issues

A database build or migration is concerned with DDL (Data-definition Language) SQL operations, but neither DML (Data Manipulation Language) nor (Data Control Language). Both DML, usually the loading of the default development data set, and the definition of the access controls are done after the build because these will depend on the database environment. Access controls are entirely different for a production system and the data will, hopefully, be entirely different too. In both cases, I find it best to apply any DML or DCL operations after a migration run completes.

For example, in the Flyway tool, but there are plenty of valid approaches but you’d probably want to have two separate SQL callback scripts, one that ensured that the roles were all defined and is idempotent (makes no assumptions about the state or database version), The second would define the role memberships that determine the state of the toggles and the users. This would be configurable for each separate environment.. It is wise to remember that a DDL database version won’t match either the DDL or DCL state. In both cases, the relationship could be ‘many-to-many’.

If your RDBMS supports schema-based access, this offers a neat way of feature-toggling, but this will mean some re-engineering work to an existing database and to the applications using it. However, if you are having to wrestle with the complications of variants in the way a database works for different groups of users, then it is worth the effort. Otherwise, you can adopt a more relaxed approach where you apply the logic within views or procedures, based on the membership of user roles.

Conclusion

Database roles are part of the SQL Standard, though the way they should be implemented isn’t precisely defined. Nevertheless, they are an excellent way of simplifying the assignments of access rights to individual users but are useful for controlling access of individual users to variants in the functionality. It is simpler to do more complex feature toggling in SQL Server or PostgreSQL because of their use of schemas but database-level can be done by every RDBMS that implements roles, even where users are identified only at the application level.