Feature Description

In databases such as PostGIS and KingBase, a Schema is an important method for organizing database objects. It is used to logically group database objects (such as tables, views, functions), thereby achieving permission separation and data management in multi-user environments.

Starting from SuperMap iDesktopX 2025 Update1, a Schema parameter has been added to the datasource connection information to meet the following usage scenarios:

• Secure and Compliant Deployment: The datasource (SuperMap sm system tables) and PostGIS extensions can be stored separately, avoiding the creation of user tables in public Schemas (e.g., public) to meet stringent enterprise security requirements.
• Multi-Platform Data Isolation: Allows SuperMap datasources to create and manage data under multiple independent Schemas, ensuring operational isolation and permission control when using Schemas for shared database libraries across multiple platforms.
• Migration and Tiered Storage: Allows data to be written directly into a specified Schema, enabling logical tiered data management. During data migration, the original database's organizational structure can be preserved.

Applicable Database Datasources: PostGIS, POLARDBGanos, KingBase, HighGoDB, SHENTONG, Vastbase.

Setting Schema When Opening a Datasource

Setting Instructions

Supports specifying a Schema when opening a datasource:

• If a Schema is specified, newly added datasets (created datasets, imported datasets, copied datasets) will be stored in the specified Schema.
• If not specified, the Schema where the datasource is located will be used by default.

Permission Instructions

After the datasource is opened, datasets from all Schemas for which the user has read or edit permissions will be displayed. The actual permissions for a dataset depend on the read/write permissions of the connected user for the corresponding Schema and its data, and are unrelated to the Schema specified when opening the datasource. For detailed permission management, please refer to Dataset Permissions Management.

Name Display Rules

• The alias format is: server address_database name_Schema name.
• The dataset name is displayed in the format schema.datasetname (e.g., region.sichuan). The connection symbol can be set to an underscore (e.g., region_sichuan) via SchemaJoin in SuperMap.xml. For specific settings, please refer to Engine-Related Settings.

Note:

If the entered Schema does not exist or the name is incorrect, the datasource can still be opened normally. The system does not validate the Schema name upon opening. However, write operations (e.g., creating a dataset, importing a dataset) will fail with a prompt.

Setting Schema When Creating a New Datasource

Setting Instructions

Supports specifying a Schema when creating a new datasource. This allows the datasource and PostGIS extensions to be stored under different Schemas. When you create a datasource, the system automatically determines the Schema for datasource creation based on the creation location of the PostGIS extension, the user's operation permissions on the Schema, and the user-specified Schema parameter.

• PostGIS Extension Already Created:
  • If the current user has CREATE permission on the Schema where the PostGIS extension is located, the datasource is created in that Schema (compatible with older versions).
  • If the current user does not have CREATE permission on the Schema where the PostGIS extension is located, but only has USAGE permission, the datasource is created in the SDX Schema.
• PostGIS Extension Not Created: (In this case, you need to use a superuser to create a datasource.)
  • If the current user specifies a Schema in the datasource connection information, the datasource is created under the specified Schema.
  • If the current user does not specify a Schema in the datasource connection information, the datasource is created in the Public Schema.

You can confirm the Schema where the PostGIS extension is located and the current user's operation permissions on the Schema using the following SQL on the database side.

	  
// Query the Schema where the PostGIS extension is located
SELECT n.nspname AS schema_name, e.extname AS extension_name
FROM pg_extension e
JOIN pg_namespace n ON e.extnamespace = n.oid
WHERE e.extname = 'postgis';
Result Explanation:
If a record is returned (e.g., Public), the extension is installed under this Schema.
If no record is returned, it indicates the PostGIS extension is not installed.
 
// Query the current user's operation permissions on Schemas
SELECT
    nspname AS schema_name,
    has_schema_privilege('username', nspname, 'CREATE') AS can_create,
    has_schema_privilege('username', nspname, 'USAGE') AS can_use
FROM pg_namespace
WHERE nspname != 'schema name';
 
Result Explanation:
In the returned records, [V] indicates the user has permission for this operation, [ ] indicates no permission.  	

Compatibility Between Versions

Related Topics

New Datasource

Open Datasource