BigAnimal Access Model

In BigAnimal, when we create a cluster:

• A superuser have created automatically is called biganimal_user.
• we connect as that user (or another user with CREATEROLE privilege) to manage other users, roles, and databases.
• We cannot use the postgres superuser directly (for security reasons).
• But biganimal_user can do almost everything needed for user management.

Connect to the Cluster

biganimal cluster show-connection --id p-96589658

Then copy the connection string, for example:

psql "host=mycluster.xxxx.biganimal.io port=5432 dbname=postgres user=biganimal_user sslmode=require"

Manage via BigAnimal Portal (Optional GUI Way)

You can also do some of this in the BigAnimal Console:

1. Log in to your BigAnimal portal.
2. Go to your cluster → Databases & Users tab.
3. Click “Create User” or “Create Database.”
4. Specify username, password, and assign privileges. This GUI uses the same SQL commands under the hood.

Get Connection Details from BigAnimal

biganimal cluster show-connection --id p-xxxxxxxxxx

Replace p-xxxxxxxxxx with your actual cluster ID.

It will display something like this

Cluster ID: p-xxxxxxxxxx
Cluster Name: prod-db
Connection Details:
  Hostname: prod-db.abcd1234.us-east-1.aws.biganimal.io
  Port: 5432
  Database: postgres
  User: biganimal_user
  SSL Mode: require
  Connection String:
    psql "host=prod-db.abcd1234.us-east-1.aws.biganimal.io port=5432 dbname=postgres user=biganimal_user sslmode=require"

Connect via psql (Command Line)

Requirements:

• psql (PostgreSQL client) must be installed.
• You need the password for biganimal_user (from the BigAnimal console when cluster was created).

Example Command:

psql "host=prod-db.abcd1234.us-east-1.aws.biganimal.io port=5432 dbname=postgres user=biganimal_user sslmode=require"

You’ll be prompted for a password.
After that, you should see:

psql (15.4)
Type "help" for help.

postgres=>

You’re now connected to your BigAnimal cluster.

Optional: Store the Password Securely

You can store your password in a .pgpass file to avoid typing it every time.

Create the file (Linux):
nano ~/.pgpass

And Add below format details:

hostname:port:databasename:user_name:password

Verify Connection

In psql:
\conninfo
Shows connection details (host, port, user, SSL mode, etc.).

Create a new user (invite a collaborator)

An organization administrator manages users by inviting them as collaborators. This process grants the user access to the BigAnimal console and assigns them an organizational role.

1. Log in to the EDB BigAnimal console.
2. Navigate to the Admin panel.
3. Access the Users section to see and manage the list of members in your organization.
4. Initiate the process to invite a new user by providing their email address and assigning them an organizational role.

• The organizational role (e.g., Administrator, Member) determines the user's base-level permissions for managing resources like clusters and other users.

Provide privileges to a user

BigAnimal uses a multi-layered permission system. You manage privileges at both the organizational level (for console access) and the cluster level (for database-specific access).

1. Manage organizational privileges

You can modify a user's permissions within the organization from the Admin panel.

1. In the BigAnimal console, go to the Admin panel > Users.
2. Locate the user whose permissions you want to change.
3. Select the user to modify their assigned organizational role.
4. Save your changes to update their permissions across the entire BigAnimal organization.

2. Manage cluster-level privileges

Once a user has access to your organization, you can manage their access to specific clusters and their underlying databases.

Grant database access via the console

1. Navigate to your desired Cluster in the BigAnimal console.
2. Go to the Users or Permissions section for that specific cluster.
3. Find and select the user you want to grant access to.
4. Assign them a cluster-level role or grant specific database permissions (e.g., READ-ONLY).

Grant superuser access via the console

BigAnimal offers a self-service superuser access feature for users in their own cloud accounts.

1. From the Clusters page, navigate to your desired cluster.
2. Access the cluster details to enable the superuser feature for your user account.
3. Once activated, your user account will have superuser privileges, giving you full control over the database.

---

In the BigAnimal console, you can manage users at both the organizational level for platform access and the database level for data access within a cluster. The console offers a dedicated user management area for inviting new users and assigning organization-wide roles. Database user privileges are then managed by connecting to the database using a tool like psql.

Creating organization users

Organizational users are individuals you invite to join your BigAnimal account. These users are assigned roles that define their access to BigAnimal's management functions, such as creating clusters or managing other users.

1. Log in to the BigAnimal console.
2. Navigate to the Users page from the main menu.
3. Select Invite New User. You must have the correct permissions to see this option, typically Owner or Admin.
4. Enter the email address of the user you wish to invite.
5. Assign an organization-level role to the new user.

• Owner: Has full control, including managing the subscription, editing organization info, and inviting other users.
• Admin: Can invite users, manage user roles, and access all projects.
• Member: Can create projects and access public projects.

1. The user will receive an invitation email to join the organization.

Creating database users and providing privileges

Database users are created within a specific cluster and are managed using standard PostgreSQL commands. For this, you will need to connect to your BigAnimal cluster.

• Connect to your database: Use a client like psql to connect to your BigAnimal cluster. You can find the connection details in the BigAnimal console by navigating to your cluster's details page.

Create a new user: Run the CREATE USER command, replacing app1 and app1_pwd with your desired username and a secure password.

create user arc_user with password 'user_password';

Provide privileges: To grant the user permissions, you can use the GRANT command. The edb_admin role, which is the default for a new BigAnimal cluster, has elevated privileges, and you can grant some of these to your new user.

Grant the edb_admin role: This gives the user many common administrative permissions.
GRANT edb_admin TO arc_user;

Task	SQL Example
Create User	CREATE USER app_user WITH PASSWORD 'pwd';
Grant DB Access	GRANT CONNECT ON DATABASE sales_db TO app_user;
Grant Schema Access	GRANT USAGE ON SCHEMA public TO app_user;
Read-only	GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;
Read/Write	GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;
Role-based Access	CREATE ROLE readonly; GRANT readonly TO app_user;

Full SQL Script: Create Database, Roles & Access

 ===========================================
 1️⃣ Create Roles (Group roles for access control)
 ===========================================

-- Read-only role
CREATE ROLE readonly_role NOLOGIN;

-- Read-write role
CREATE ROLE readwrite_role NOLOGIN;

 ===========================================
 2️⃣ Create Users (Login roles)
 ===========================================

-- Reporting user (only read access)
CREATE USER report_user WITH PASSWORD 'StrongReadOnlyPwd@123';

-- Application user (read/write access)
CREATE USER app_user WITH PASSWORD 'StrongReadWritePwd@123';

 ===========================================
 3️⃣ Assign Users to Roles
 ===========================================
GRANT readonly_role TO report_user;
GRANT readwrite_role TO app_user;

 ===========================================
 4️⃣ Database-level Access
 ===========================================
GRANT CONNECT ON DATABASE sales_db TO readonly_role;
GRANT CONNECT ON DATABASE sales_db TO readwrite_role;

 ===========================================
 5️⃣ Schema-level Access
 ===========================================
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT USAGE ON SCHEMA public TO readwrite_role;

 ===========================================
 6️⃣ Table Privileges
 ===========================================
-- For existing tables:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite_role;

-- For future tables (default privileges):
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite_role;

-- ===========================================
-- 7️⃣ (Optional) Sequence Access
-- ===========================================
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_role;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO readwrite_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO readonly_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO readwrite_role;

-- ===========================================
-- You can verify access as:
-- \c sales_db report_user
-- \c sales_db app_user
-- ===========================================