Managing Postgres access
You control access to your Postgres database using database authentication implemented by creating databases with specific roles and privileges. Database authentication differs from portal authentication, which controls access to the BigAnimal portal.
For information on portal authentication, see:
- Setting up your identity provider if you purchased BigAnimal directly from EDB
- Setting up your Azure Marketplace account if you purchased BigAnimal through Azure Marketplace
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
Avoid storing data in the postgres system database.
So that we can effectively manage the cloud resources and ensure users are protected against security threats, BigAnimal provides a special administrative role, pg_ba_admin. The edb_admin user is a member of the pg_ba_admin role. The pg_ba_admin role has privileges similar to a Postgres superuser. Like the edb_admin user, the pg_ba_admin role shouldn't be used for day-to-day application operations and access to the role must be controlled carefully. See pg_ba_admin role for details.
When using your own cloud account, you can grant the edb_admin role superuser privileges for a cluster. See Superuser access. If you grant superuser privileges, you must take care to limit the number of connections used by superusers to avoid degraded service or compromising availability.
Superuser privileges allow you to make Postgres configuration changes using
ALTER SYSTEM queries. We recommend that you don't do this because it might lead to an unpredictable or unrecoverable state of the cluster. In addition,
ALTER SYSTEM changes aren't replicated across the cluster.
See the PostgreSQL documentation on superusers for best practices.
Changes to system configuration (GUCs) made by edb_admin or other Postgres users don't persist through a reboot or maintenance. Use the BigAnimal portal to modify system configuration.
You have to remember your edb_admin password, as EDB doesn't 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_admin user or the edb_admin database 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-256hashed prior to storage. This hash, even if compromised, can't 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_admin user. 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 app1.
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 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
AUTHORIZATIONclause for the application owner. For example:
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.
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.
To turn on IAM authentication using the CLI, see Using IAM authentication on AWS.
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.
In Postgres, if the IAM role doesn’t exist yet, use the
CREATE ROLEcommand. For example, for AWS, use:
For each IAM user, run the
CREATE USERPostgres command. For example, for AWS, use:
Where <ARN> is the Amazon resource name. (For Azure, use the user principal name. For GCP, use the email address.)
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.
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.
- 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.
- Connect to Postgres using your IAM credentials.
For information on integrating with IAM on AWS using the CLI, see IAM authentication CLI commands.