So far, we have been focusing our attention on setting up and interacting with logical schemas as the administrator of the database. However, we need to set up a virtual schema that only exposes the necessary parts of the database to users or applications with proper authorizations in place for security. In this article, we will discuss some concepts that are relevant for providing a secure virtual schema, views, and roles.

Views

In the first article of this database series, we briefly touched on virtual schemas that can be created for applications, and this can be achieved using views. Views are virtual tables resulting from queries involving one or more real tables in the database, and they can be easily created using CREATE VIEW <view_name> AS <query>;. They are a useful tool when we want to hide certain columns from database users and simplify queries by preparing joined virtual tables.

For example, when we want to create a receipt for an order with all the relevant information and total price, we would need to write an extremely long SQL query like SELECT name, price, quantity, price * quantity as total FROM OrderItems JOIN Product ON OrderItems.product_id=Product.id WHERE OrderItems.id=1 UNION SELECT "Total", NULL, NULL, SUM(price * quantity) FROM OrderItems JOIN Product ON OrderItems.product_id=Product.id WHERE Order.id=1; without views. However, we can set up a view called receipt like CREATE VIEW receipt AS SELECT OrderItems.id, name, price, quantity, price * quantity as total FROM OrderItems JOIN Product ON OrderItems.product_id=Product.id;, which can shorten the user query substantially to SELECT name, price, quantity, total FROM receipt WHERE id=1 UNION SELECT "Total", NULL, NULL, SUM(total) FROM receipt WHERE id=1;.

User Roles

PostgreSQL and other DBMS operate under role-based access control (RBAC), where database access is limited by a user's roles. The default user postgres is a superuser with all privileges, which should not be used by applications for security reasons. For example, we don't want a web application to access the database as postgres, as a successful hack of the web application could allow attackers to steal all thedata and destroy the database. Similarly, we don't want analytics teams to have superuser privileges, as this could lead to accidental database modification or deletion.

Instead, we can create user roles like webapp and analytics with their assigned privileges. User roles can be created using commands like CREATE ROLE webapp LOGIN; and CREATE ROLE analytics LOGIN PASSWORD 'secret';, and passwords can be added or altered with ALTER USER webapp PASSWORD 'websecret'; by a privileged user. Access can then be granted using commands like GRANT ALL ON Product TO webapp; and GRANT SELECT ON DATABASE prod_db TO analytics;, which assigns all privileges on the Product table to webapp and select privilege to all tables in the prod_db database to analytics, respectively. We can also manage access to views and stored procedures (GRANT EXECUTE ON <procedure_name> TO <role_name>;) to expose only the necessary data and functions.

While user roles are convenient for smaller projects, they become intractable as the number of roles and tables grows. For example, we might want to set up an api user role with the same privileges as webapp to account for potential future privilege changes, and this involves running GRANT commands manually for every privilege on every table. Similarly, managing multiple analytics teams with the same privileges also requires manual GRANT commands. Furthermore, adding new privileges for a new table to analytics teams involves running the same GRANT command manually for all user roles for all the teams.

Group Roles

To simplify user role management, we can create group roles that user roles can belong to, avoiding the need to manually duplicate privileges. For example, we can set up a client role where both webapp and api belong, so that configurations of the client role are reflected in webapp and api, and adding a new user role with the same privileges, like mobile, can be easily accomplished by adding it to the client group. The same principle applies to analytics teams, where we can set up an analytics group role, to which analytics_team1 and analytics_team2 can be added.

Group roles can be created using commands like CREATE ROLE client; (without LOGIN and/or PASSWORD) and CREATE ROLE analytics;, and their privileges are assigned in the same way as for user roles. A user role can then become a member of a group role using commands like GRANT client TO webapp, api; and GRANT analytics TO analytics_team1, analytics_team2;. Both individual and group privileges can then be easily managed. A user role can be removed from a group role using REVOKE client FROM api;. There are also useful predefined group roles, such as pg_read_all_data, which can be used to set up analytics with GRANT pg_read_all_data TO analytics;. Removing a role can be tricky, especially if the role has ownership of a table or membership in a group role. Therefore, it's necessary to resolve ownership and membership before dropping a role using DROP ROLE <role_name>;.

Conclusion

In this article, we covered the concepts of RBAC and how to implement them with views and roles, which are essential for setting up a secure and easy-to-use database. With views and roles configured, clients (like psycopg2 for Python, postgres.js, node-postgres for JavaScript and Node.JS, etc.) can log in with a username and password and access virtual schemas containing only the relevant information based on their permissions. For more details on these concepts and implementations, I recommend checking out the official documentation of the corresponding DBMS (cited at the bottom of the article for PostgreSQL).

Resources

• PostgreSQL. n.d. PostgreSQL 17.4 Documentation. PostgreSQL.