In our recent 13th Mydbops Open Source Meetup, we had done a detailed presentation about the PostgreSQL 15 features. One of the features that stood out to me was Security Invoker Views in PostgreSQL 15. So, in this blog post, Would like to take a closer look at this particular feature and explain its significance.

Before getting into the security invoker view, let’s see first what is security definer.

Security Definer view

Security definer view checks permission for accessing its underlying tables using the privileges of the view owner, rather than the privileges of the user of the view.

Example for Security Definer View:

Creating a new non-superuser called demo and a table called t1 with 1000 records.

 
postgres=# create user demo;
CREATE ROLE

postgres=> create table t1 as select id from generate_series(1,1000) id;
SELECT 1000
	

Next, a security definer view called definer_view is created using the create view command. The view selects all rows from the t1 table where the id value is greater than 5

 
postgres=> CREATE VIEW definer_view AS
postgres->   SELECT id FROM t1 WHERE id > 5;
CREATE VIEW
	

Select privilege on the object definer_view is given to the user demo.

 
postgres=> grant select on definer_view to demo;
GRANT

postgres=> set role to demo;
SET

postgres=> select * from definer_view;
  id  
------
    6
    7
    8
    9
   10
   11
   12
   13
   14
....
1000
	

As we can see here, the user demo is able to execute the view definer_view as the user is having access. But, it is also allowed to read the data from the base tables to which the user does not have access. It is because security definer views checks permission based on the view owner(Here postgres superuser is the view owner), not on the view user (User demo).

Views were always defined as security definer earlier to PostgreSQL 15.

Security Invoker view

Security Invoker view checks permission for accessing its underlying tables using the privileges of the user of the view, rather than the view owner.

This can be achieved by adding security_invoker=true while creating the view statement.

 
CREATE VIEW viewname with(security_invoker=true) AS SELECT query;
	

If needed to create a view using security definer, the security_invoker can be set to false.

Example for Security invoker View:

This is an example of creating a Security Invoker View in PostgreSQL:

Repeating the same test with a view created using the invoker command

 
postgres=# create user demo;
CREATE ROLE

postgres=# create table t1 as select id from generate_series(1,1000) id;
SELECT 1000

postgres=# CREATE VIEW invoker_view with(security_invoker=true) AS
postgres-#   SELECT id FROM t1 WHERE id < 5;
CREATE VIEW

postgres=# grant select on invoker_view to demo;
GRANT

postgres=# select * from invoker_view;
 id 
----
  1
  2
  3
  4
(4 rows)

postgres=# set role TO demo;
SET

postgres=> select * from invoker_view;
ERROR:  permission denied for table t1

	

We can see that the user demo is unable to execute the view because of the lack of permissions to the underlying base table. It is because security invoker views checks permission based on the user of the view(User demo), not on the owner of the view(User Postgres).

Comparison Chart between Definer View and Invoker View

This is about the views in PostgreSQL and its differences. It is essential to carefully consider the security implications when creating views in PostgreSQL, and choose the appropriate type of view depending on the specific requirements of the use case. Security definer views can be less secure as they grant access to underlying tables, which could potentially expose sensitive data to unauthorized users. Invoker views, on the other hand, provide more security as access is restricted to the view itself.

Overall, the choice between invoker views and definer views depends on the specific requirements of the use case and the level of security needed for the data.