This document mainly focuses on interface-based database account operations on the console.
Creating an Account
TencentDB for PostgreSQL does not provide superuser role attributes and pg_execute_server_program, pg_read_server_files and pg_write_server_files roles for users to use. However, as some operations require superuser permissions, TencentDB for PostgreSQL offers the pg_tencentdb_superuser role as a substitute for the superuser role. For more information on pg_tencentdb_superuser, refer to User and Permission Operations.
There are two types of console accounts: pg_tencentdb_superuser accounts and ordinary accounts. Any account that is a member of the pg_tencentdb_superuser role is a pg_tencentdb_superuser account; otherwise, it is an ordinary account.
You can log in to the PostgreSQL console, and in the instance list, click Instance ID or Manage in the Operation column to go to the instance details page. Click Account Management > Create account to create an account. Details are as follows:
Note:
When an account is created on the console, the account name should meet the following criteria: 1-16 characters, consisting only of letters, numbers, or underscores; not being postgres; not beginning with a number, pg_, or tencentdb_; case insensitive for all rules.
The console only supports the creation of accounts for instance versions 9.5 and above. For instances whose version is below 9.5, upgrade their version first.
Account Display
Note:
1. The console account list can display all accounts in the database instance that are in normal or locked status. These accounts can be created through the console or other clients connected to the database.
2. The console only supports the display of users, not roles.
3. Only accounts created on the console will have their creation time recorded by the management system. Be aware of this.
4. When the system permissions of an account include nologin or CONNECTION = 0, the account is in the locked status.
5. The console only supports the display of database accounts that meet the account name requirements. Be aware of this.
Database accounts created through the console, other clients or programs can all be displayed in the account list. See the figure below for details:
Modifying an Account
Resetting the Password
You can reset the login password of an existing account on the console. If you have currently logged in to the database with the account, the reset password will be valid for subsequent connections. Click Operation > Reset Password, as shown in the figure below:
The pop-up box is shown below:
Modifying Remarks
You can modify the account remarks on the console. Click Operation > More > Modify Remarks, as shown in the figure below:
The pop-up box is shown below:
Modifying the Type
There are two types of console accounts: pg_tencentdb_superuser accounts and ordinary accounts. Any account that is a member of the pg_tencentdb_superuser role is a pg_tencentdb_superuser account; otherwise, it is an ordinary account. For more information on pg_tencentdb_superuser, refer to User and Permission Operations.
You can change the account type on the console. Click Operation > More > Modify Type, with details shown below:
The pop-up box is shown below:
Modifying Permissions
Description of Account Object Permissions
An account's object permissions consist of three parts:
Permissions inherited from roles: In PostgreSQL, a user can belong to one or more roles, and these roles can have specific permissions. For example, a role might have the permission to access a database or modify a table. If a user belongs to this role, then it will inherit all the permissions of the role.
Permissions inherited from PUBLIC: As mentioned earlier, PUBLIC is a special predefined group to which all users automatically belong. If a permission is granted to the PUBLIC group, then all users will have the permission.
Directly granted permissions: Besides the permissions inherited from roles and PUBLIC, a user can be directly granted permissions. For example, a database administrator can directly grant a user the permission to access a particular database or modify a table.
These three aspects of permissions can overlap with each other. To revoke a specific permission from a user, the permission should be revoked in all the three aspects. Granting and revoking object permissions do not apply to the OWNER. The OWNER has all permissions on an object.
Note:
The console's ability to modify permissions applies to directly granted permissions. If a permission of an account still exists after being revoked, you need to further check if the user has inherited the permission from the PUBLIC group or is the OWNER of the object.
On the console, you can grant or revoke multiple different permissions for multiple objects for an account, or grant or revoke multiple different permissions for multiple objects of the same category for an account in bulk.
Granting or Revoking Multiple Different Permissions for Multiple Objects for an Account
Click Operation > Modify Permissions. The pop-up box is as follows:
The pop-up box is as follows:
Note:
Since account permissions can be modified through multiple clients or programs, it is strongly recommended that you click the Refresh button each time before operating permissions on the Console to retrieve the latest permissions.
Note:
PUBLIC is a special predefined group to which all users automatically belong. When permissions are granted to the PUBLIC group, they are actually granted to all users, including existing and future users.
If a certain permission of an object is already possessed by the PUBLIC group, to revoke the permission, you should remove it from the PUBLIC group for the revocation to take effect.
Once a permission is revoked from the PUBLIC group, all users will lose the permission, so operate with caution.
To help you quickly revert to the initialization state after an erroneous click, click the Reset button. This will restore all interface operations to the current account permission status, allowing you to reoperate on this basis.
Click OK. The system will summarize all the permission operations you are about to initiate for the currently selected account, so as to facilitate your second confirmation. Details are shown in the figure below:
As shown above, this account's permission modification operations involve two databases: nomal_1 and db_priv, with details shown below:
1. Operations on the nomal_1 database involve the following three aspects:
1.1 A REVOKE TEMPORARY operation is performed on the nomal_1 database.
1.2 A REVOKE INSERT operation is performed on the id field of the amyttt table in the sche_priv schema.
1.3 A REVOKE UPDATE operation is performed on the am_s sequence in the sche_priv schema.
2. A REVOKE CREATE operation is performed on the db_priv database.
Granting or Revoking Multiple Different Permissions for Multiple Objects of the Same Category for an Account
When you need to handle multiple permissions for objects of the same category for an account simultaneously, you can use the Batch Authorize/Deauthorize feature. The pop-up box is as follows:
Note:
The Bulk Authorization/Revocation feature is for bulk operations on objects of the same category for an account. It does not support performing a bulk operation that includes the database and schema.
In bulk mode, the display of authorized status is not supported because permissions may vary. Operate with caution.
Deleting an Account
When you no longer need a certain database account, the console supports the deletion operation. Click Operation > More > Delete Account, with details shown below:
An account can be deleted only when it has no object permissions; otherwise, an error will be reported when the account is deleted, as shown in the figure below:
If an error is reported when a database account is deleted, we can check whether the account has permissions on certain objects or is the OWNER of certain objects. If so, revoke the corresponding permissions or replace the object's OWNER before re-authorizing the account. In PostgreSQL, aclitem is used to represent permissions on a specific database object. For a detailed explanation, refer to the Overview of Database Permissions.
Methods to check whether an account has permissions on certain objects are as follows:
(1) To check whether the account has permissions on certain databases, refer to the following command:
As shown in the figure above, the datacl field clearly describes the account's permissions on the database. We find the corresponding account's permissions, revoke them, and then delete the account.
(2) To check whether the account has permissions on certain schemas, you can query under each database. Refer to the following command:
nomal_1=> select nspname,nspacl from pg_namespace;
As shown in the figure above, the datacl field clearly describes the account's permissions on the schema. We find the corresponding account's permissions, revoke them, and then delete the account.
(3) To check whether the account has permissions on certain objects, you can query under each database. Refer to the following command:
nomal_1=> select relname,a.typname as reltype ,relacl from pg_class join pg_type a on pg_class.reltype=a.oid ;
Examples of checking whether an account is the OWNER of a certain object are as follows:
(1) To check whether an account is the OWNER of a certain database, refer to the following example:
nomal_1=> SELECT r.rolname,d.datdba,datname AS database_name FROM pg_database d left JOIN pg_catalog.pg_roles r on d.datdba=r.oid WHERE d.datdba in (SELECT usesysid FROM pg_user WHERE usename in('dbadmin','nomal_usr1','nomal_usr2'));
rolname | datdba | database_name
------------+--------+---------------
dbadmin | 16398 | db_priv
nomal_usr1 | 16401 | nomal_1
(2) To check whether an account is the OWNER of a certain schema, refer to the following example:
nomal_1=> SELECT n.nspname as schema_name, pg_catalog.pg_get_userbyid(n.nspowner) as schema_owner FROM pg_catalog.pg_namespace n ORDER BY schema_name;
schema_name | schema_owner
--------------------+--------------
information_schema | postgres
pg_catalog | postgres
pg_temp_1 | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
public | postgres
sche_priv | dbadmin
(3) To check whether the account is the OWNER of a certain object, refer to the following example:
nomal_1=> select * from (select relname,relnamespace as schema_name ,a.typname as reltype,pg_catalog.pg_get_userbyid(relowner )as owner from pg_class join pg_type a on pg_class.reltype=a.oid ) d where owner in('dbadmin','nomal_1');
Yes
No
Was this page helpful?