IMetabasePolicy.AllowDBMSObjectsCreation

Syntax

AllowDBMSObjectsCreation: Boolean;

Description

The AllowDBMSObjectsCreation property determines whether objects can be created based on PostgreSQL DBMS by the users who are not schema owners.

Comments

Available values:

IMPORTANT. JSC FORESIGHT is not responsible for any improper use of the property.
The use of the property can cause vulnerabilities, user update issues, and object access issues for users who are repository administrators.
For details about possible issues see the article at the EDB website.
If the AllowDBMSObjectsCreation property set to true and the Manage Rights on Objects checkbox are used at the same time, the schema owner cannot delete the objects created by other users from database server. Deletion will be available only from the repository.
At the platform level, one cannot manage access, and one may need to grant permissions at DBMS level.

To minimize possible issues one should prepare everything necessary. Preparation steps for property use depend on the schema in use:

    1. Create a role in DBMS:

CREATE ROLE "ROLE_NAME";

Where ROLE_NAME - name of the created role.

IMPORTANT. The ADMIN user name is unavailable for use.

    1. Include user accounts to the created role for the users who need to get permission to create objects in the schema, except for the schema owner:

GRANT "ROLE_NAME" TO "USER_NAME";

Where USER_NAME - user name.

    1. Grant the CREATE and USAGE permissions for the schema with data for the created role:

GRANT CREATE, USAGE ON SCHEMA "SCHEMA_NAME" TO "ROLE_NAME";

Where SCHEMA_NAME - custom schema identifier.

    1. Allow creating objects by setting the AllowDBMSObjectsCreation property to true.

CREATE ROLE "ROLE_NAME";

Where ROLE_NAME - name of the created role.

IMPORTANT. The ADMIN user name is unavailable for use.

    1. Include user accounts to the created role for the users who need to get permission to create objects in the schema, except for the schema owner:

GRANT "ROLE_NAME" TO "USER_NAME";

Where USER_NAME - user name.

GRANT CREATE, USAGE ON SCHEMA "SCHEMA_NAME" TO "ROLE_NAME";

    1. Create a role in DBMS:

CREATE ROLE "ROLE_NAME";

Where ROLE_NAME - name of the created role.

IMPORTANT. The ADMIN user name is unavailable for use.

    1. Include user accounts to the created role for the users who need to get permission to create objects in the schema, except for the schema owner:

GRANT "ROLE_NAME" TO "USER_NAME";

Where USER_NAME - user name.

    1. Grant the CREATE permission for the schema with data for the created role:

GRANT CREATE ON SCHEMA "public" TO "ROLE_NAME";

    1. Revoke the CREATE permissions for the schema for the PUBLIC role. Execute the step if the schema was based on PostgreSQL DBMS 14 or earlier:

REVOKE CREATE ON SCHEMA "public" FROM PUBLIC;

NOTE. If the schema is based on PostgreSQL 15 or later, one does not need to execute the step.

    1. Allow creating objects by setting the AllowDBMSObjectsCreation property to true.

CREATE ROLE "ROLE_NAME";

Where ROLE_NAME - name of the created role.

IMPORTANT. The ADMIN user name is unavailable for use.

    1. Include user accounts to the created role for the users who need to get permission to create objects in the schema, except for the schema owner:

GRANT "ROLE_NAME" TO "USER_NAME";

Where USER_NAME - user name.

GRANT CREATE ON SCHEMA "public" TO "DB_OWNER";

Where DB_OWNER - database owner name.

    1. Grant the CREATE permission for the schema for the created role:

GRANT CREATE ON SCHEMA "public" TO "ROLE_NAME";

REVOKE CREATE ON SCHEMA "public" FROM PUBLIC;

NOTE. If the schema is based on PostgreSQL 15 or later, one does not need to execute the step.

Example

To execute the example, follow the steps described above.

Add a link to the Metabase system assembly.

Sub UserProc;
Var
    MB: IMetabase;
    MS: IMetabaseSecurity;
    MU: IMetabasePolicy;
Begin
    MB := MetabaseClass.Active;
    MS := MB.Security;
    MU := MS.Policy;
    // Allow creating objects in database by the users who are not schema owners
    MU.AllowDBMSObjectsCreation := True;
    // Save changes
    MS.Apply;
End Sub UserProc;

After executing the example creating objects in the database is allowed for the schema owner and privileged users.

See also:

IMetabasePolicy