Creating Database Users
Using the CREATE USER Command
To create a new database user account:
CREATE USER wegg WITH PASSWORD 'LZn2DCajcNHpGR3ZXWHD', COMMENT 'Silas Wegg';
- Creates the user in the database
- Registers the user with the Metadb instance
- Creates a schema with the same name as the user (intended as a workspace)
User Account Recommendations
Best Practices:
- Each user account should be for an individual user and not shared by more than one person
- Prefer user names of 3 to 8 characters in length
Granting Access to Data
A new user has very limited access to data by default. To add privileges for tables and functions, use the GRANT command.
GRANT SELECT ON table_name TO username;
GRANT syntax.
Alternative User Registration
Registering an Existing User
If a user already exists in the database system, it can be enabled to work with the Metadb database using:
REGISTER USER existing_user;
Creating a User Schema
After registering an existing user, create their workspace schema:
CREATE SCHEMA FOR USER existing_user;
Administrative Database Changes
It is possible to make administrative-level changes directly in the underlying PostgreSQL database, such as providing additional tables for users.
Critical Guidelines:The following guidelines must be followed strictly to avoid disrupting Metadb operation:
-
No changes to Metadb-managed objects: Do not modify any database objects created or managed by Metadb. If changes become necessary at the request of Metadb maintainers:
- Stop the server first to prevent cache inconsistencies
- If changes are made inadvertently, stop the server immediately and reverse the changes before restarting
-
Use isolated schemas: Create any new database objects in schemas that will not coincide with Metadb schemas:
- Always set
add_schema_prefix in data source configurations
- Avoid using those prefixes when creating new schemas
-
No database views: Do not create database views in the database
Making changes to Metadb-managed data or schemas may cause data corruption.
