Managing Postgres access
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 strict isolation is needed, use a dedicated cluster or dedicated database. If that strict isolation level isn't required, you can deploy a single database with multiple schemas. Refer to Privileges in the PostgreSQL documentation to further customize ownership and roles to your requirements.
To create a new role and database, first connect using
Avoid storing data in the
postgres system database.
edb_adminrole does not have superuser privileges by default. Contact Support to request superuser privileges for
edb_admin. If you request superuser privileges, you must take care to limit the number of connections used by superusers to avoid degraded service and/or compromising availability.
Changes to system configuration (GUCs) made by edb_admin or other Postgres users are not persisted though a reboot or maintenance. Use the BigAnimal portal to modify system configuration.
You have to remember your
edb_adminpassword as EDB does not have access to it. If you forget it, you can set a new one in the BigAnimal portal on the Edit Cluster page.
Don't use the
edb_adminuser or the
edb_admindatabase in your applications. Instead, use
CREATE USER; GRANT; CREATE DATABASE.
BigAnimal stores all database-level authentication securely and directly in PostgreSQL. The
edb_adminuser password is SCRAM-SHA-256 hashed prior to storage. This hash, even if compromised, cannot be replayed by an attacker to gain access to the system.
For one database hosting a single application, replace app1 with your preferred user name:
Create a new database user. For example,
Assign the new role to your
edb_adminuser. Assigning this role allows you to assign ownership to the new user in the next step. For example:
Create a new database to store application data. For example:
Using this example, the username and database in your connection string is
If a single database is used 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 BigAnimal 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.
Create a database owner and new database. For example:
Connect to the new database. For example:
Create new application roles. For example:
Create a new schema for each application with the AUTHORIZATION clause for the application owner. For example:
Any AWS user with an AWS account connected to a BigAnimal subscription who has the Postgres role of "iam_aws" can authenticate to the database using their AWS IAM credentials.
Provision your cluster before configuring IAM for Postgres.
In BigAnimal, turn on the IAM authentication feature when creating or modifying the cluster:
- On the Additional Settings tab, under Authentication, select Identity and Access Management (IAM) Authentication.
- Select Create Cluster or Save.
In AWS, get the ARN of each IAM user requiring database access. In the AWS account connected to BigAnimal, use AWS Identity and Access Management (IAM) to perform user management. See the AWS documentation.
In Postgres, if the IAM role doesn’t exist yet, run this Postgres command:
For each IAM user, run this Postgres command:
If IAM integration is configured for your cluster, you can log in to Postgres using your AWS Amazon Resource Name (ARN) and access key. Using this ARN + access key combination allows you to connect to your Postgres database using your AWS IAM standard credentials.
You can continue to log in using your Postgres username and password. However, doing so doesn’t provide IAM authentication even if this feature is configured.
- Using your AWS CLI or Cloud shell, obtain your ARN and access key. For guidance on obtaining your ARN and access key, see Managing access keys for IAM users.
- Connect to Postgres using your IAM credentials.
- When prompted for the password, enter your access key (<access key ID>:<secret access key>).