Skip to main content
Metadb provides SQL commands to create and manage database users, including automatic user schema creation and privilege management integration.

​
Create User

Define a new database user with automatic schema creation.

​
Syntax

create user user_name with option 'value' [, ... ]

​
Parameters

​
user_name
string
required
The name of the new user.
​
password
string
required
Sets the user’s password.
​
comment
string
Stores a comment about the user, e.g., the user’s real name. The comment can be viewed in psql using the \du+ command.

​
What CREATE USER Does

create user performs three operations:
  1. Creates a new database user
  2. Registers the user with the Metadb instance (enables privilege grants)
  3. Creates a user schema as a workspace for the user
The user schema has the same name as the user and serves as a personal workspace where the user can create tables, views, and other objects.

​
Example

Create a user with password and comment: 
create user wegg with password 'LZn2DCajcNHpGR3ZXWHD', comment 'Silas Wegg';

​
Register User

Register an existing user to receive database privileges.

​
Syntax

register user user_name

​
Parameters

​
user_name
string
required
The name of the user to register.

​
Description

register user associates an existing user with the Metadb instance. This allows the user to be granted privileges via the grant command.
Normally there is no need to issue register user, because create user executes it automatically. However, if an existing user was created in some other way (e.g., directly in PostgreSQL), register user can enable it for the Metadb instance.

​
Example

Register an existing PostgreSQL user: 
register user beatrice;

​
Create Schema for User

Create a user workspace schema for an existing registered user.

​
Syntax

create schema for user user_name

​
Parameters

​
user_name
string
required
The name of the user, which will also be the name of the new schema.

​
Description

create schema creates a user schema that serves as a workspace. The user automatically receives appropriate privileges on this schema.
Typically user schemas are created automatically by create user. However, if a user already exists and is registered but does not have a user schema, create schema for user can be used to create it.

​
Example

Register an existing user and create their schema: 
register user jane;

create schema for user jane;

​
Deregister User

Remove a user’s ability to have database privileges.

​
Syntax

deregister user user_name

​
Parameters

​
user_name
string
required
The name of the user to deregister.

​
Description

deregister user removes the ability to have database privileges from a user. It performs two operations:
  1. Automatically executes revoke access on all
  2. Prevents subsequent grant commands for this user
This does not delete the user or their schema. Use drop user to completely remove a user.

​
Example

Deregister a user: 
deregister user wegg;

​
Drop User

Completely remove a database user.

​
Syntax

drop user user_name

​
Parameters

​
user_name
string
required
The name of the user to be removed.

​
Description

drop user removes a database user completely. Before doing so, it attempts to find and revoke most authorizations held by the user that would typically prevent removal.
Multi-Instance Consideration: If a user is registered in more than one Metadb instance, drop user will not be able to remove all authorizations. In that case, use deregister user in each instance first, then issue drop user.

​
Example

Remove a user: 
drop user wegg;

​
User Management Workflow

​
View Users

List all registered users using the system function: 
select * from mdbusers();
Or view all database users (including non-registered) using PostgreSQL: 
\du

​
See Also

Access Control

Grant and revoke access to data

System Functions

View users and system information

Build docs developers (and LLMs) love

Get started for freeTalk to us