Postgres GRANT operation cost¶
GRANT operation mechanics
Introduction¶
As many other databases Postgres supports access management framework via privileges which are provided to the users.
In terms of SQL it means a pair of commands GRANT and REVOKE.
To simplify things to the users postgres also supports mass ALL option for these commands.
Specifying either GRANT ALL or REVOKE ALL allows to apply command against multiple objects or against multiple privileges or for a mix of both.
Note: If you are an experienced postgres user you could navigate directly to the GRANT execution mechanics section.
But have you ever thought how lightweight such a command?
Imagine a situation that you have a lot of functions or packages (organized as separate schemas) and many users/roles at the same time.
And you have to make sure that specific role(s) has an EXECUTE privilege on all functions.
What will be the cost of executing the following command?
This post will reveal some hidden obstacles regarding GRANT/REVOKE command that not listed in official documentation.
GRANT meta information¶
Postgres stores issued privileges on different objects in specific to that object type pg_catalog schema tables.
For example, for tables and views they are stored in pg_class.relacl column.
For functions and procedures in pg_proc.proacl.
For schemas in pg_namespace.nspacl and so on.
Abbreviation ACL most probably stands for access control list.
All these columns have aclitem[] datatype. There is also aclexplode function is available which allows to parse this array of values.
=> select * from pg_namespace where nspname = 'pg_catalog';
oid | nspname | nspowner | nspacl
-----+------------+----------+------------------------------------
11 | pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
=> select n.nspname
, acl.grantor
, acl.grantee
, acl.privilege_type
, acl.is_grantable
from pg_namespace n
cross join
aclexplode(n.nspacl) acl
where n.nspname = 'pg_catalog';
nspname | grantor | grantee | privilege_type | is_grantable
------------+---------+---------+----------------+--------------
pg_catalog | 10 | 10 | USAGE | f
pg_catalog | 10 | 10 | CREATE | f
pg_catalog | 10 | 0 | USAGE | f
By default, object owner has all the privileges that are applicable for a specific object type.
For schemas such privileges are USAGE and CREATE.
Hence you see 2 records for these privileges where grantor is equal to the grantee.
Grantee with id = 0 is a special PUBLIC role.
This means that all users will be able to access objects in this schema if they have more specific grants on them.
GRANT execution mechanics¶
Every execution of GRANT or REVOKE command will update ACL in one of the system pg_* tables introduced in a previous chapter.
Let's create user account some_user and table t.
By default there are no grants on t are available.
=> select ctid, xmin, xmax, relacl from pg_class where relname = 't';
ctid | xmin | xmax | relacl
--------+------+------+--------
(0,57) | 550 | 0 |
ctid, xmin, xmax columns to display current row physical location in a block and last processing transaction of this row.
As seen from the query current row is stored in block 0 as a tuple 57.
To examine physical structure of block we could use pageinspect extension.
=> create schema pageinspect;
CREATE SCHEMA
=> create extension pageinspect schema pageinspect;
CREATE EXTENSION
=> set search_path = pageinspect, public;
SET
=> select t_xmin, t_xmax, t_ctid, t_data
from heap_page_items(get_raw_page('pg_class', 0))
where t_ctid = '(0,57)';
t_xmin | t_xmax | t_ctid | t_data
--------+--------+--------+-------
550 | 0 | (0,57) | \xa240000074...
57 in a block 0. It is created by transaction 550 and has some data.
Now let's grant SELECT privilege on this table to our created some_user.
=> grant select on t to some_user;
GRANT
=> select ctid, xmin, xmax, relacl from pg_class where relname = 't';
ctid | xmin | xmax | relacl
--------+------+------+--------------------------------------------------
(0,58) | 555 | 0 | {postgres=arwdDxt/postgres,some_user=r/postgres}
relacl is no longer empty.
This happens because object privileges differ from the default privileges defined for that object type.
Difference is some_user=r/postgres statement which depicts executed by us command.
Also tuple number has got increased to 58 as well as transaction id which is now set to 555.
Examining block structure
=> select t_xmin, t_xmax, t_ctid, t_data
from heap_page_items(get_raw_page('pg_class', 0))
where t_ctid in ('(0,57)', '(0,58)');
t_xmin | t_xmax | t_ctid | t_data
--------+--------+--------+------
550 | 555 | (0,58) | \xa240000074...
555 | 0 | (0,58) | \xa240000074...
550 closed by newly transaction 555.
This closed tuple is an old version of a row in pg_class for a given table.
New version has t_xmin = 555 and t_xmax = 0.
This is how MVCC is implemented in postgres. We have a set of row versions while only one is latest.
So far so good.
But what will happen if now we will revoke non existing grant on table t from some_user?
Logically to expect that because such privilege wasn't granted before - nothing should happen.
Let's check.
=> revoke update on t from some_user;
REVOKE
=> select ctid, xmin, xmax, relacl from pg_class where relname = 't';
ctid | xmin | xmax | relacl
--------+------+------+--------------------------------------------------
(0,59) | 556 | 0 | {postgres=arwdDxt/postgres,some_user=r/postgres}
Wow...this is quite surprising. We revoked non existing grant from the table. But it again lead to the new tuple creation!
Checking block structure again we observe already three versions of the original row.
=> select t_xmin, t_xmax, t_ctid, t_data
from heap_page_items(get_raw_page('pg_class', 0))
where t_ctid in ('(0,57)', '(0,58)', '(0,59)');
t_xmin | t_xmax | t_ctid | t_data
--------+--------+--------+------
550 | 555 | (0,58) | \xa240000074...
555 | 556 | (0,59) | \xa240000074...
556 | 0 | (0,59) | \xa240000074...
Funny thing is that if you will repeat REVOKE command N times it will generate N amount of new tuples in a block.
Despite of the fact that starting from the second iteration grant doesn't exist anymore and there is nothing to change in ACL.
Same happens for GRANT.
Needless to say that such multiple row versions (if not cleaned) will lead to system dictionaries blowing up and cluttering.
And if you have in a database many objects for which you grant many different privileges that impact will be even more bigger.
If now we will execute VACUUM on a pg_class table those dead tuples will go away.
=> vacuum pg_class;
VACUUM
=> select t_xmin, t_xmax, t_ctid, t_data
from heap_page_items(get_raw_page('pg_class', 0))
where t_ctid in ('(0,57)', '(0,58)', '(0,59)');
t_xmin | t_xmax | t_ctid | t_data
--------+--------+--------+------
556 | 0 | (0,59) | \xa240000074...
I'm not sure about default VACUUM mode implementation details.
But because we also have VACUUM FULL variation this means that default VACUUM might result into left dead tuples or cluttered row data.
With the time this may lead to system dictionary blow up and overall system performance degrade.
And executing VACUUM FULL against system tables on actively used database is probably last thing that you will want to do.
Summary¶
Every GRANT or REVOKE command execution leads to new tuple created in a corresponding system dictionary table.
This happens despite of the presence/absence of the required grant.
For mass grant commands this will put an additional load to the system dictionary.
In worst case scenario it might lead to system dictionary blow up and impact overall system performance.