Database authentication

Setting up your database authentication

Don't use the edb_admin database role and edb_admin database created when creating your cluster in your application. Instead, create a new database role and a new database, which provides a high level of isolation in Postgres. If multiple applications are using the same cluster, each database can also contain multiple schemas, essentially a namespace in the database. If you need strict isolation, use a dedicated cluster or dedicated database. If you don't need that strict isolation level, you can deploy a single database with multiple schemas. See Privileges in the PostgreSQL documentation to further customize ownership and roles to your requirements.

To create a new role and database, first connect using psql:

psql -W "postgres://edb_admin@xxxxxxxxx.xxxxx.biganimal.io:5432/edb_admin?sslmode=require"

One database with one application

For one database hosting a single application, replace app1 with your preferred user name:

1. Create a new database user. For example,

   → WrapCopy

   edb_admin=# create user app1 with password 'app1_pwd';

2. Assign the new role to your edb_admin user. Assigning this role allows you to assign ownership to the new user in the next step. For example:

   → WrapCopy

   edb_admin=# grant edb_admin to app1;

3. Create a new database to store application data. For example:

   → WrapCopy

   edb_admin=# create database app1 with owner app1;

Using this example, the username and database in your connection string is app1.

One database with multiple schemas

If you use a single database to host multiple schemas, create a database owner and then roles and schemas for each application. This example shows creating two database roles and two schemas. The default search_path for database roles in Cloud Service is "$user",public. If the role name and schema match, then objects in that schema match first, and no search_path changes or fully qualifying of objects are needed. The PostgreSQL documentation covers the schema search path in detail.

1. Create a database owner and new database. For example:

   → WrapCopy

   edb_admin=# create user prod_admin with password 'prod_pwd';
   edb_admin=# create database prod1 with owner prod_admin;

2. Connect to the new database. For example:

   → WrapCopy

   edb_admin=# \c prod1

3. Create new application roles. For example:

   → WrapCopy

   prod1=# create user app1 with password 'app1_pwd';
   prod1=# grant edb_admin to app1;
   
   prod1=# create user app2 with password 'app2_pwd';
   prod1=# grant edb_admin to app2;

4. Create a new schema for each application with the AUTHORIZATION clause for the application owner. For example:

   → WrapCopy

   prod1=# create schema app1 authorization app1;
   prod1=# create schema app2 authorization app2;

IAM authentication for Postgres

Any user with a supported cloud account connected to a BigAnimal subscription who has the Postgres IAM role iam_aws, iam_azure, or iam_gcp can authenticate to the database using their IAM credentials.

Configuring IAM for Postgres

Provision your cluster before configuring IAM for Postgres.

1. In BigAnimal, turn on the IAM authentication feature when creating or modifying the cluster:

   1. On the Additional Settings tab, under Authentication, select Identity and Access Management (IAM) Authentication.

   2. Select Create Cluster or Save.

      Note

      To turn on IAM authentication using the CLI, see Using IAM authentication on AWS.

2. From your cloud provider, get the user name of each IAM user requiring database access. In the cloud account connected to BigAnimal, use Identity and Access Management (IAM) to perform user management.

3. In Postgres, if the IAM role doesn’t exist yet, use the CREATE ROLE command. For example, for AWS, use:

   → WrapCopy

   CREATE ROLE "iam_aws";

4. For each IAM user, run the CREATE USER Postgres command. For example, for AWS, use:

   → WrapCopy

   CREATE USER "<ARN>" IN ROLE iam_aws;

   Where <ARN> is the Amazon resource name. (For Azure, use the user principal name. For GCP, use the email address.)

Logging in to Postgres using IAM credentials

If IAM integration is configured for your cluster, you can log in to Postgres using your cloud credentials. Alternatively, you can use your token instead of your password. Logging in either way allows you to connect to your Postgres database using your cloud account's IAM standard credentials.

For either method, you must first authenticate to your cloud service provider IAM to get your password or token.

1. Get your credentials for your IAM-managed cloud account.
   • For AWS, your password is your access key (in the form <access key id>:<secret access key>). To get your access key, see get-access-key-info To get your authorization token, see get-authorization-token.
   • For GCP, to get your access token, see Create a short-lived access token.
   • For Azure, to get your access token, see the get-access-token command.
2. Connect to Postgres using your IAM credentials.

Using IAM authentication CLI commands

For information on integrating with IAM on AWS using the CLI, see IAM authentication CLI commands.