Why Can a User Log In to the Database After the CONNECT ON DATABASE Permission Is Granted to the REVOKE User?

Answer: After you run the REVOKE CONNECT ON DATABASE dbname FROM u1 command to revoke the u1 permission, you need to specify public because the CONNECT permission of the database is granted to public.

• GaussDB provides an implicitly defined group public that contains all roles. By default, all new users and roles have the permissions of public. To revoke permissions of public from a user or role, or re-grant these permissions to them, add the public keyword in the REVOKE or GRANT statement.
• GaussDB grants the permissions on certain types of objects to public. By default, permissions on the following objects are granted to public:
  • CONNECT permission for databases
  • CREATE TEMP TABLE permission
  • EXECUTE permission for functions
  • USAGE permission for languages and data types (including domains)
• An object owner can revoke the default permissions granted to public users and grant permissions to other users as needed.

Example:

1. Create the u1 user and the db_test database.

   gaussdb=# CREATE USER u1 PASSWORD '********';
   gaussdb=# CREATE DATABASE db_test;

2. The u1 user can connect to the test database. Log out of the database.

   gsql -d db_test -h xxx.xxx.xxx.xxx -p xxxx -U u1 -W '********'

3. Run the REVOKE command to revoke the public user's permission to connect to db_test.

   gaussdb=# REVOKE CONNECT ON DATABASE db_test FROM public;

4. Verify the result and connect to the database as the u1 user. The result shows that the u1 user cannot connect to the database.

   gsql -d db_test -h xxx.xxx.xxx.xxx -p xxxx -U u1 -W '********'
   gsql: FATAL:  permission denied for database "db_test"
   DETAIL:  User does not have CONNECT privilege.
   
   -- Drop.
   gaussdb=# DROP USER u1;
   gaussdb=# DROP DATABASE db_test;