openGauss--user

user
Use CREATE USER and ALTER USER to create and manage database users. openGauss contains one or more named databases. Users and roles are shared across openGauss, but their data is not.
That is, users can connect to any database, but when the connection is successful, any user can only access the database declared in the connection request.

Without separation of powers, openGauss user accounts can only be created and deleted by system administrators or security administrators with the CREATEROLE attribute. With separation of powers, user accounts can only be created by the original user and the security administrator.

Users are authenticated when they log in to openGauss. Users can own databases and database objects such as tables, and users and roles can be granted permissions on these objects to control who can access which object. In addition to system administrators, users with the CREATEDB attribute can create databases and grant permissions to those databases.

Create, modify and delete users
To create a user, use the SQL statement CREATE USER.
For example: create user joe, and set the user to have the CREATEDB attribute.
postgres=# CREATE USER joe WITH CREATEDB PASSWORD "Bigdata@123";
CREATE ROLE

To create a system administrator, use the CREATE USER statement with the SYSADMIN option.

To drop an existing user, use DROP USER.

To change the user account (for example, rename the user or change the password), use ALTER USER.

To see a list of users, query the view PG_USER:

SELECT * FROM pg_user;

To view user attributes, query the system table PG_AUTHID:

SELECT * FROM pg_authid;

private user
In the scenario where there are multiple business departments, different database users are used for business operations between departments, and a database maintenance department at the same level uses a database administrator for maintenance operations, the business department may wish to perform maintenance operations without authorization. , the administrator user can only perform control operations (DROP, ALTER, TRUNCATE) on the data of each department, but cannot perform access operations (INSERT, DELETE, UPDATE, SELECT, COPY). That is, for administrator users, the control rights and access rights of table objects must be separated to improve the data security of ordinary users.

In the case of separation of powers, administrators do not have permissions to tables placed by other users in their respective schemas. However, this non-authority includes non-controlling authority, so it cannot satisfy the above demands. For this purpose, openGauss offers a private user scheme. That is, in the non-segregation of powers mode, create a private user with the INDEPENDENT attribute.

CREATE USER user_independent WITH INDEPENDENT IDENTIFIED BY "1234@abc";

For the object of this user, the system administrator and the security administrator with the CREATEROLE attribute can only perform control operations (DROP, ALTER, TRUNCATE) without their authorization, but do not have the right to perform INSERT, DELETE, SELECT, UPDATE, COPY, GRANT, REVOKE, ALTER OWNER operations.