13 May 2025
Metadb 1.4 introduces new
commands for user authorization. This article covers two of them,
GRANT and
REVOKE, which define granular
privileges.
GRANT gives a user access to Metadb-managed tables and functions
(explained below), and REVOKE removes that access. GRANT is
similar to SQL GRANT, but one significant difference is that the
privileges continue even when tables and functions are dropped and
recreated. This allows it to work predictably with database objects
that are frequently recreated. Another difference is that it does not
require separate privileges for schemas.
What is a Metadb-managed table or function? It includes:
- System tables such as
metadb.table_update - System functions like
public.mdblog(interval) - Tables streamed from data sources
- Transformed tables such as from JSON or MARC formats
- Tables created by automated execution of external SQL
For example, to give a user bob access to the system function
public.mdblog(interval):
GRANT ACCESS ON FUNCTION public.mdblog(interval) TO bob;
To illustrate further, let's look at how privileges are defined for new users.
In Metadb, new user accounts are created by a system administrator via
the CREATE USER command. In
the following example, we create the users jenny and mark:
CREATE USER jenny WITH PASSWORD 'fChU4R9JvZjRwsEb', COMMENT 'Jenny Wren';
CREATE USER mark WITH PASSWORD 'KBYwLhdLTQTmxXRs', COMMENT 'Mark Tapley';
These users can now connect to the database, but they do not have
privileges to access data. We will look at a few different approaches
to defining privileges. First for jenny:
GRANT ACCESS ON ALL TO jenny;
Then for mark:
GRANT ACCESS ON TABLE library.loans TO mark;
GRANT ACCESS ON TABLE library.patrongroup TO mark;
Here jenny has been granted access to all Metadb-managed tables and
functions. In the case of mark, access to only two tables is
granted.
Suppose that we now want to reverse the privileges for jenny and
mark so that mark can access all tables and functions, and jenny
only the two tables. For jenny, we start by removing all of the
privileges that we previously granted, returning to a blank slate, and
then grant access only to the desired tables:
REVOKE ACCESS ON ALL FROM jenny;
GRANT ACCESS ON TABLE library.loans TO jenny;
GRANT ACCESS ON TABLE library.patrongroup TO jenny;
For mark, we can simply grant access on all:
GRANT ACCESS ON ALL TO mark;
Finally, suppose it turns out that mark should continue having
access to everything except the two tables that jenny can access.
We only need to revoke access to those two tables:
REVOKE ACCESS ON TABLE library.loans FROM mark;
REVOKE ACCESS ON TABLE library.patrongroup FROM mark;
Note that with earlier Metadb releases, system administrators issued
the AUTHORIZE command to give users blanket authorization. GRANT
is intended to be used instead of AUTHORIZE, but there is a
difference in how they work. Since GRANT defines privileges per
table (or function), it can only be applied to tables that already
exist. For this reason, GRANT should be used after the desired
tables have been created.
When upgrading to 1.4, Metadb automatically executes GRANT ACCESS ON ALL for existing users that previously were authorized using
AUTHORIZE.