Access Control

Metadb provides specialized grant and revoke commands that maintain privileges even when tables or functions are dropped and recreated. This is essential for streaming data platforms where schema changes are common.

How Metadb Access Control Works

Metadb’s access control differs from standard SQL grants:

Persistent Privileges

Privileges remain valid even if objects are dropped and recreated

Metadb-Managed Objects

Works with tables and functions managed by Metadb

User Registration Required

Only registered users can receive Metadb access grants

Granular or Broad

Grant access to specific objects or all Metadb-managed data

Metadb-Managed Objects

These commands work with:

System tables: metadb.table_update, metadb.log, metadb.base_table
System functions: mdblog(), mdbversion(), mdbusers(), ps()
Main and current tables: Tables streamed from data sources
Transformed tables: Tables created from JSON or other transformations
External SQL tables: Tables created by automated execution of external SQL

Grant Access

Enable access to Metadb-managed tables and functions.

Syntax

grant access
    on { table table_name | function function_name | all }
    to user_name

Parameters

table_name

string

An existing Metadb-managed table (schema-qualified name).

function_name

string

An existing Metadb-managed function.

all

keyword

Grants access to all current and future Metadb-managed objects.

user_name

string

required

An existing registered user to be granted access.

Examples

Grant Access to a Single Table

grant access on table library.patrongroup to anna;

Grant Access to a Function

grant access on function public.mdblog to anna;

Grant Access to Everything

grant access on all to bob;

Security Consideration: grant access on all allows a user to access all Metadb-managed tables and functions, including future ones. Use this carefully and only for trusted users.

Grant Limited Access Only

To grant a user access to only specific tables and nothing else:

-- First, ensure no broad access
revoke access on all from bob;

-- Then grant specific access
grant access on table library.patrongroup to bob;
grant access on table library.checkouts to bob;

Revoke Access

Remove access privileges from a user.

Syntax

revoke access
    on { table table_name | function function_name | all }
    from user_name

Parameters

table_name

string

An existing Metadb-managed table.

function_name

string

An existing Metadb-managed function.

all

keyword

Revokes access to all Metadb-managed objects.

user_name

string

required

An existing user that will have access removed.

Examples

Revoke Access to a Single Table

revoke access on table library.patrongroup from bob;

Revoke All Access

revoke access on all from bob;

Revoke Function Access

revoke access on function public.ps from charlie;

Access Control Patterns

Read-Only User
Administrator
Department Access
Temporary Access

Create a user with access to specific tables:

-- Create and register the user
create user analyst with password 'secure_pass', comment 'Data Analyst';

-- Grant access to specific tables
grant access on table library.checkouts to analyst;
grant access on table library.items to analyst;
grant access on table library.patrons to analyst;

-- Grant access to monitoring functions
grant access on function public.mdblog to analyst;

Create a user with full access:

-- Create the admin user
create user admin_user with password 'admin_secure_pass', comment 'System Admin';

-- Grant full access
grant access on all to admin_user;

Grant access to all tables in a specific schema:

-- For a sensor department user
create user sensor_user with password 'secure_pass', comment 'Sensor Dept';

-- Grant access to all sensor tables
grant access on table sensor.temperature to sensor_user;
grant access on table sensor.humidity to sensor_user;
grant access on table sensor.pressure to sensor_user;

Grant temporary access that you plan to revoke:

-- Grant temporary access
grant access on table library.sensitive_data to contractor;

-- Later, revoke when no longer needed
revoke access on table library.sensitive_data from contractor;

Best Practices

Principle of Least Privilege

Grant users only the minimum access they need. Start with specific table grants rather than grant access on all.

Document Access Grants

Keep a record of why each user has been granted specific access. Use the comment option when creating users to document their role.

Regular Access Reviews

Periodically review user access and revoke unnecessary privileges. Users’ roles change over time.

Use Groups for Common Patterns

If you have multiple users needing the same access, consider creating a role pattern you can replicate:

-- Example: Create multiple analysts with the same access
create user analyst1 with password 'pass1', comment 'Analyst One';
grant access on table library.checkouts to analyst1;
grant access on table library.items to analyst1;

create user analyst2 with password 'pass2', comment 'Analyst Two';
grant access on table library.checkouts to analyst2;
grant access on table library.items to analyst2;

Checking User Access

To see what access a user has, you can query their privileges using PostgreSQL system catalogs:

-- View table privileges
select 
    schemaname, 
    tablename, 
    privilege_type
from information_schema.table_privileges
where grantee = 'username';

Relationship with User Management

Registration Required: Users must be registered with Metadb before they can receive access grants. Use create user or register user first.

Automatic Deregistration: When you use deregister user, it automatically executes revoke access on all and prevents future grants.

User Management

Create and manage database users

Data Sources

Configure data sources and tables

CLI Commands

SQL Extensions

System Functions

System Tables

Configuration

Access Control

How Metadb Access Control Works

Persistent Privileges

Metadb-Managed Objects

User Registration Required

Granular or Broad

Metadb-Managed Objects

Grant Access

Syntax

Parameters

Examples

Grant Access to a Single Table

Grant Access to a Function

Grant Access to Everything

Grant Limited Access Only

Revoke Access

Syntax

Parameters

Examples

Revoke Access to a Single Table

Revoke All Access

Revoke Function Access

Access Control Patterns

Best Practices

Checking User Access

Relationship with User Management

See Also

User Management

Data Sources

Build docs developers (and LLMs) love

CLI Commands

SQL Extensions

System Functions

System Tables

Configuration

​How Metadb Access Control Works

Persistent Privileges

Metadb-Managed Objects

User Registration Required

Granular or Broad

​Metadb-Managed Objects

​Grant Access

​Syntax

​Parameters

​Examples

​Grant Access to a Single Table

​Grant Access to a Function

​Grant Access to Everything

​Grant Limited Access Only

​Revoke Access

​Syntax

​Parameters

​Examples

​Revoke Access to a Single Table

​Revoke All Access

​Revoke Function Access

​Access Control Patterns

​Best Practices

​Checking User Access

​Relationship with User Management

​See Also

User Management

Data Sources

Build docs developers (and LLMs) love

How Metadb Access Control Works

Metadb-Managed Objects

Grant Access

Syntax

Parameters

Examples

Grant Access to a Single Table

Grant Access to a Function

Grant Access to Everything

Grant Limited Access Only

Revoke Access

Syntax

Parameters

Examples

Revoke Access to a Single Table

Revoke All Access

Revoke Function Access

Access Control Patterns

Best Practices

Checking User Access

Relationship with User Management

See Also