Creating a database user

Permissioning a database user for OmniAI

Creating a PostgreSQL User with Custom Permissions

This section guides you through the process of creating a PostgreSQL user with specific permissions. The user will have read-only access to all existing and future tables, the ability to create new tables, and read-write access to any tables they create.

Prerequisites

Ensure you have administrative access to your PostgreSQL database to execute these commands.

-- Create user
CREATE USER your_username WITH PASSWORD 'your_password';

-- Grant connect to database
GRANT CONNECT ON DATABASE your_database TO your_username;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO your_username;

-- Grant read-only access to all current and future tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_username;

-- Allow user to create tables
GRANT CREATE ON SCHEMA public TO your_username;

Step by step:

Step 1: Create the User

First, connect to your PostgreSQL database using a superuser account or another account with sufficient privileges to create roles and grant permissions.

CREATE USER your_username WITH PASSWORD 'your_password';

Replace your_username and your_password with your desired username and password.

Step 2: Grant Connect Permission

Grant the user the ability to connect to the database.

GRANT CONNECT ON DATABASE your_database TO your_username;

Replace your_database with the name of your database.

Step 3: Grant Usage on Schema

Allow the user to use the default schema, typically public.

GRANT USAGE ON SCHEMA public TO your_username;

Step 4: Grant Read-Only Access to All Current and Future Tables

Grant the user select (read) permission on all current tables and set default permissions for future tables in the schema.

-- Grant select on all current tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_username;

-- Set default select permission for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_username;

Step 5: Allow User to Create Tables

While PostgreSQL does not directly offer a privilege to allow a user to only create tables, you can achieve this by granting the user the CREATE privilege on the schema.

GRANT CREATE ON SCHEMA public TO your_username;

This allows the user to create new objects within the schema, including tables.

Additional Considerations

• If the database schema changes or if tables are moved between schemas, you may need to adjust permissions accordingly.

• Regularly review user permissions to ensure they align with current security policies and database usage patterns.