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, a 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
edb_adminrole does not have superuser priviledges by default. Contact Support to request superuser priviledges 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: