AnalyticDB for PostgreSQL Data Agent's Ontology feature (semantic layer + knowledge graph) assigns permissions by user role: We recommend creating a dedicated account for the creator role; the query role can authorize multiple existing business accounts, which are independent of each other. Both roles can also share a single account (only the creator role authorization is needed, as creator permissions cover query permissions). All operations are idempotent and can be repeated.
Prerequisites
-
An AnalyticDB for PostgreSQL 7.0 instance is created, and the kernel minor version is 7.2.1.0 or later (7.2.1.4 or later is recommended, as the
ageextension is pre-installed in this version). -
If the kernel version is lower, first refer to Install, update, and uninstall extensions to install the
ageextension, or upgrade the kernel minor version. -
A business database is created and business tables are in place.
-
Use the initial high-privilege account to execute all SQL statements in this topic.
Procedure
Step 1: Environment preparation (one-time, executed by administrator)
Execute the following statements on the target business database:
CREATE EXTENSION IF NOT EXISTS age;
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM ag_catalog.ag_graph WHERE name = 'ontology_graph') THEN
PERFORM ag_catalog.create_graph('ontology_graph');
END IF;
END $$;
CREATE SCHEMA IF NOT EXISTS semantic;
Tables and views in the semantic layer are automatically created by the desktop Initialize operation. No manual table creation is required.
Step 2: Authorize the "creator" role (executed by administrator)
Create a dedicated account (recommended; only one account is recommended for the creator role):
CREATE ROLE ontology_builder LOGIN PASSWORD '<password>';
-- Replace your_account with ontology_builder below
Use an existing account: Skip account creation and replace your_account with the actual account name.
-- ---- Ontology-specific objects (write scope is limited to these, business tables are not involved) ----
GRANT USAGE, CREATE ON SCHEMA semantic TO your_account;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA semantic TO your_account;
ALTER DEFAULT PRIVILEGES IN SCHEMA semantic
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO your_account;
GRANT USAGE, CREATE ON SCHEMA ontology_graph TO your_account;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ontology_graph TO your_account;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA ontology_graph TO your_account;
ALTER DEFAULT PRIVILEGES IN SCHEMA ontology_graph
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO your_account;
ALTER DEFAULT PRIVILEGES IN SCHEMA ontology_graph
GRANT USAGE, SELECT ON SEQUENCES TO your_account;
GRANT USAGE ON SCHEMA ag_catalog TO your_account;
GRANT EXECUTE ON FUNCTION ag_catalog.cypher(name, cstring, ag_catalog.agtype) TO your_account;
GRANT SELECT ON TABLE ag_catalog.ag_graph TO your_account;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE ag_catalog.ag_label TO your_account;
ALTER TABLE ontology_graph._ag_label_vertex OWNER TO your_account;
ALTER TABLE ontology_graph._ag_label_edge OWNER TO your_account;
ALTER SEQUENCE ontology_graph._ag_label_vertex_id_seq OWNER TO your_account;
ALTER SEQUENCE ontology_graph._ag_label_edge_id_seq OWNER TO your_account;
ALTER SEQUENCE ontology_graph._label_id_seq OWNER TO your_account;
ALTER INDEX ontology_graph._ag_label_vertex_pkey OWNER TO your_account;
ALTER INDEX ontology_graph._ag_label_edge_pkey OWNER TO your_account;
-- ---- Business data read-only ----
DO $$
DECLARE r record;
BEGIN
FOR r IN SELECT unnest(ARRAY['public', 'ods', 'dwd', 'dws', 'dim', 'ads']) AS nspname
LOOP
EXECUTE format('GRANT USAGE ON SCHEMA %I TO your_account', r.nspname);
EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO your_account', r.nspname);
EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO your_account', r.nspname);
END LOOP;
END $$;
Replace the business schema list (public, ods, dwd, dws, dim, ads) with the actual schemas where business tables are stored. Do not remove the GRANT USAGE line.
Step 3: Build on the desktop
-
Open the ADB-PG Data Agent desktop client, go to the Ontology page, and use the creator role account as the data source.
-
Click Initialize, then execute Generate Semantic → Sync YAML → Build in sequence to complete Ontology building.
Step 4: Authorize the "query" role (executed by administrator, after building is complete)
Skip this step if the creator and query roles use the same account.
The query role supports multiple accounts: execute the following authorization block once for each account that needs to query data (create a new account first if needed, or directly authorize existing business accounts). Each account is independent with no limit on the number of accounts. This step must be executed after Initialize is complete — semantic layer tables and views exist only after this point. Authorization is effective once and does not need to be repeated.
Create a dedicated account:
CREATE ROLE ontology_reader LOGIN PASSWORD '<password>';
-- Replace your_account with ontology_reader below
Use an existing business account: Skip account creation and replace your_account with the actual account name (existing business read permissions will be re-granted, which is harmless).
-- ---- Ontology read-only ----
GRANT USAGE ON SCHEMA semantic TO your_account;
GRANT SELECT ON ALL TABLES IN SCHEMA semantic TO your_account;
GRANT USAGE ON SCHEMA ontology_graph TO your_account;
GRANT SELECT ON ALL TABLES IN SCHEMA ontology_graph TO your_account;
GRANT USAGE ON SCHEMA ag_catalog TO your_account;
GRANT EXECUTE ON FUNCTION ag_catalog.cypher(name, cstring, ag_catalog.agtype) TO your_account;
GRANT SELECT ON TABLE ag_catalog.ag_graph TO your_account;
GRANT SELECT ON TABLE ag_catalog.ag_label TO your_account;
-- ---- Business data read-only ----
DO $$
DECLARE r record;
BEGIN
FOR r IN SELECT unnest(ARRAY['public', 'ods', 'dwd', 'dws', 'dim', 'ads']) AS nspname
LOOP
EXECUTE format('GRANT USAGE ON SCHEMA %I TO your_account', r.nspname);
EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO your_account', r.nspname);
EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO your_account', r.nspname);
END LOOP;
END $$;
Step 5: Query data on the desktop
-
Single-account mode: Start querying data directly in a conversation.
-
Multi-account mode: Switch the data source of daily conversations to the query role account to start querying data.
FAQ
Q: Initialize reports "Missing prerequisites"
Step 1 was not executed or was executed on a different database. Make sure all three statements in Step 1 are executed on the target business database.
Q: Initialize reports "Incomplete graph label registration" or "Failed to register graph structure"
The authorization block in Step 2 was not fully executed. Re-execute the entire block and try again.
Q: "permission denied for schema" error during query
The corresponding authorization block is missing GRANT USAGE ON SCHEMA. Re-execute the corresponding authorization block.
Q: Revoke creator permissions after building is complete
In multi-account mode, lock the creator account. The query account is not affected:
ALTER ROLE ontology_builder NOLOGIN;
Q: Add new query accounts later
Execute the Step 4 authorization block once for each new account after building is complete. No other operations are required.
Q: Do I need to re-execute authorization for daily data updates?
No. Authorization is effective long-term — subsequent operations such as updating the semantic layer or rebuilding graphs on the desktop use normal data read/write permissions. If multiple people in the team need to perform update operations, execute the Step 2 authorization block once for each account to enable parallel use. Only when changing the creator account do you need to re-execute Step 2 for the new account (the OWNER statements will transfer ownership to the new account).