Postgres Permissions and Materialized Views

Materialized views in Postgres are a handy way to persist the result of a query to disk. This is helpful when the underlying query is expensive and slow yet high performance SELECT queries are required. When using materialized views they need to be explicitly refreshed to show changes to the underlying table. This is done through the REFRESH MATERIALIZED VIEW <name>; syntax.

Keeping materialized views regularly refreshed is often a delegated to a cron job on a schedule. There is also often a need for database users to manually refresh the data on demand. At this point many users stub their toe on permissions because refreshing a materialized view can only be done by the owner of the materialized view. This post uses a simple example to illustrate how to delegate refresh permissions to other Postgres roles.

Setup

I'll start by defining a SUPERUSER role that will create the materialized view.

CREATE ROLE my_super
    WITH LOGIN SUPERUSER
    PASSWORD 'YouWouldNeverCopyPasteThisRight'
;

Log in via psql as the new my_super role. Querying current_user allows you to double check your current role. This is important when testing role-related permissions.

SELECT current_user;
┌──────────────┐
│ current_user │
╞══════════════╡
│ my_super     │
└──────────────┘

As the my_super role, create a materialized view named test_mv. This example materialized view is quite simple, most MVs are far more exciting!

CREATE MATERIALIZED VIEW test_mv AS
SELECT id, MD5((id * random())::TEXT) AS val
    FROM generate_series(1, 3) id
;

To simulate another (non-SUPERUSER!) user interacting with the, test_mv create a new my_user role. Give SELECT permissions the new test_mv materialized view to the my_user role.

CREATE ROLE my_user WITH NOLOGIN;
GRANT SELECT ON test_mv TO my_user;

To test the SELECT permission use SET ROLE to change the security context to the my_user role and verify with another check of current_user.

SET ROLE my_user;
SELECT current_user;

┌──────────────┐
│ current_user │
╞══════════════╡
│ my_user      │
└──────────────┘

As the my_user you have the ability to run a SELECT query against the test_mv.

SELECT * FROM test_mv;

┌────┬──────────────────────────────────┐
│ id │               val                │
╞════╪══════════════════════════════════╡
│  1 │ 8cbceb707e56e5c7a02669c2f8ecdae5 │
│  2 │ 348f1feccf7785fad4c9c92a075dfc6d │
│  3 │ f67148d909d3f41254c8a686a84cbd55 │
└────┴──────────────────────────────────┘

Eventually, my_user needs to refresh the materialized view. They attempt to run the REFRESH command. Unfortunately, my_user is not the owner of the materialized view and gets the error telling them so.

REFRESH MATERIALIZED VIEW test_mv;
ERROR:  must be owner of materialized view test_mv

Use RESET ROLE to get back to the super user role.

RESET ROLE;

Caveats to must be owner

There are two (2) caveats to the error must be owner from Postgres.

• Superuser
• Member of role that owns the object

The first caveat are superuser roles. Superusers bypass security checks. But, you also should not be granting everyone wanting the ability to REFRESH MATERIALIZED VIEW full superuser permissions! Instead, the materialized views can be owned by another role, or a "group role." Users that need the ability to refresh the materialized view can be added to this group role as the next section shows.

Postgres does not have the concept of "group" or "user" roles. The concept is implied through the use of a role WITH LOGIN (user) and WITH NOLOGIN (group). User roles includes human users as well as system users for webapps, APIs and backend services.

Reassign ownership

Materialized views in Postgres have an owner, same as other object like tables, views, sequences, etc. The my_super role currently owns the materialized view because it created the object. I use the view dd.views from our PgDD extension to view the current owner of the test_mv materialized view.

SELECT v_name, owner
    FROM dd.views
    WHERE v_name = 'test_mv'
;

┌─────────┬──────────┐
│ v_name  │  owner   │
╞═════════╪══════════╡
│ test_mv │ my_super │
└─────────┴──────────┘

Create a new group role owner_mat_view to own the materialized view. Change the owner to this new role.

CREATE ROLE owner_mat_view WITH NOLOGIN;
ALTER MATERIALIZED VIEW test_mv OWNER TO owner_mat_view;

Check ownership again to confirm the change.

┌─────────┬────────────────┐
│ v_name  │     owner      │
╞═════════╪════════════════╡
│ test_mv │ owner_mat_view │
└─────────┴────────────────┘

To make the my_user a "member" of the new owner_mat_view group role use GRANT <this_role> TO <that_role>;

GRANT owner_mat_view TO my_user;

The easiest way to view roles and their membership in other roles is the \du psql slash command. The Member of column lists all roles the Role name is granted access to.

\du my_user

                 List of roles
┌───────────┬──────────────┬──────────────────┐
│ Role name │  Attributes  │    Member of     │
╞═══════════╪══════════════╪══════════════════╡
│ my_user   │ Cannot login │ {owner_mat_view} │
└───────────┴──────────────┴──────────────────┘

Verify the change worked for my_user by switching the role and attempting the refresh. This command will now work because the my_user is a member of owner_mat_view, which owns the materialized view.

SET ROLE my_user;
REFRESH MATERIALIZED VIEW test_mv;

Warning! Being the owner of a materialized view is not simply about giving permissions to REFRESH. It also gives the role the ability to DROP MATERIALIZED VIEW <your_view>;! Observe the principle of least privilege whenever possible.

INHERIT vs NOINHERIT

In Postgres, the default behavior is a login role that is a member of a group role directly inherits the permissions of the group role. This was shown in the previous example where the my_user role can act as the owner of the test_mv materialized view. This is not always the desired behavior and can be changed by setting the role with NOINHERIT.

ALTER ROLE my_user WITH NOINHERIT;

The change away from the default INHERIT property to NOINHERIT is reflected in the results of \du under the Attributes column.

\du my_user

                             List of roles
    ┌───────────┬──────────────────────────────┬──────────────────┐
    │ Role name │          Attributes          │    Member of     │
    ╞═══════════╪══════════════════════════════╪══════════════════╡
    │ my_user   │ No inheritance, Cannot login │ {owner_mat_view} │
    └───────────┴──────────────────────────────┴──────────────────┘

With this change, the my_user role cannot act directly as the owner_mat_view. Instead, it must use SET ROLE owner_mat_view; to switch to the role when it wants to act as the owner.

Implementing in real life

The quick example in this post showed the bare minimum details to illustrate the basic permissions in play. In a production database the owner_mat_view role would almost certainly need to be granted other permissions to allow it to properly create the materialized view. This post bypassed that level of detail by using generate_series() as its data source instead of a table or view in the database.

There is no technical limit to how many users can be assigned to a group role in Postgres. This post has only used one user role (my_user) for the examples, adding additional roles is as simple as another GRANT command.

GRANT owner_mat_view TO your_other_role;

The user/group security model is a common best practice where the login user roles are not granted any direct permissions. Instead, permissions are attached to group roles that login roles are than made a member of.

Cleanup

If you've been following along, you can remove the changes made with these commands.

RESET ROLE;
DROP MATERIALIZED VIEW test_mv;
DROP ROLE my_user;
DROP ROLE owner_mat_view;
SELECT current_user;

Summary

Materialized views are a common part of many Postgres databases and keeping the data refreshed is an important part of maintenance. This post has shown how to setup a dedicated group role to own the materialized view and grant other users to that group role to provide REFRESH permissions. The ability to refresh a materialized view requires the user has ownership rights of the view itself. Granting ownership rights to database objects should not be taken lightly or without understanding the scope of the access being granted.

Looking forward, there is a patch in the Postgres commitfest titled "Incremental Materialized View Maintenance" that may reduce the need for users to manually refresh. The patch is currently listed as a potential for Postgres 15 (ETA Fall 2022?!). I have not reviewed or tested this patch yet, though it looks promising! If the materialized views can keep themselves updated as data new data comes through, the topic of "who can refresh" becomes a non-issue.

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!