fix(engine): by default, dump only databases to which user have access

Description

By default, dump all databases to which user have access in case of an empty databases section while running logicalDump

If DLE admin declares a specific list of DBs, then the permissions check won't be applied – unaccessible DBs won't be skipped, so there will be an error at dump runtime. The change affects only "default" path, when the list of DBs is not specified / is empty.

Related issue

#324 (closed)

Checklist

• MR description has been reviewed
• MR changes are functionally tested
• MR does NOT have text changes OR there are text changes and they have been reviewed
• MR does NOT have API/CLI changes OR there are API/CLI changes and they have been reviewed
• MR does NOT have UI changes OR there are UI changes and they have been reviewed

requested review from @stansler, @fomin.list, @NikolayS, and @vitabaks

assigned to @akartasov

added 6 commits

• 4913387e...21759a29 - 4 commits from branch master
• 39fa1dd0 - Merge branch 'master' into dump-available-databases
• b69eef31 - fix linter

Compare with previous version

approved this merge request

marked the checklist item MR description has been reviewed as completed

changed title from fix(engine): dump only databases to which user have access to fix(engine): by default, dump only databases to which user have access

changed the description

@akartasov thanks, looks good to me – I edited MR desc and title prefixing them both with "by default, " (feel free to adjust if you want)

Test:

Prepare source:

postgres=# select datname from pg_catalog.pg_database where not datistemplate;
 datname  
----------
 postgres
 test
 test2
(3 rows)
test=# 
postgres=# create role myuser with password 'myuser-pass';
CREATE ROLE
postgres=# grant CONNECT ON DATABASE test TO myuser ;
GRANT
postgres=# ALTER ROLE "myuser" WITH LOGIN;
ALTER ROLE
postgres=# \c test
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1), server 13.2 (Ubuntu 13.2-1.pgdg20.04+1))
You are now connected to database "test" as user "postgres".
test=# 
test=# -- Grant access to existing tables (SCHEMA public)
test=# GRANT USAGE ON SCHEMA public TO myuser;
GRANT
test=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;
GRANT
test=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO myuser;
GRANT
test=# -- Check role_table_grants
test=# SELECT * FROM information_schema.role_table_grants WHERE grantee = 'myuser';
 grantor  | grantee | table_catalog | table_schema |    table_name    | privilege_type | is_grantable | with_hierarchy 
----------+---------+---------------+--------------+------------------+----------------+--------------+----------------
 postgres | myuser  | test          | public       | pgbench_history  | SELECT         | NO           | YES
 postgres | myuser  | test          | public       | pgbench_accounts | SELECT         | NO           | YES
 postgres | myuser  | test          | public       | pgbench_branches | SELECT         | NO           | YES
 postgres | myuser  | test          | public       | pgbench_tellers  | SELECT         | NO           | YES
(4 rows)
test=# 
-- Get a list of databases with the right to CONNECT
postgres=# select datname from pg_catalog.pg_database where not datistemplate and has_database_privilege('myuser', datname, 'CONNECT');
 datname 
---------
 test
(1 row)

Test 1: Databases is not specified

DLE config:

...
  spec:
    logicalDump:
      options:
        <<: *db_container
        dumpLocation: "/var/lib/dblab/dblab_pool/dump"
        source:
          type: remote
          connection:
            dbname: test
            host: 172.31.23.34
            port: 5435
            username: myuser
            password: myuser-pass
        parallelJobs: 2
    logicalRestore:
      options:
        <<: *db_container
        dumpLocation: "/var/lib/dblab/dblab_pool/dump"
        parallelJobs: 2
        forceInit: true
...

DLE log:

...
2022/03/25 17:11:16 [INFO]   Running dump command:  [pg_dump --create --host 172.31.23.34 --port 5435 --username myuser --dbname test --jobs 2 --format directory --file /var/lib/dblab/dblab_pool/dump/test]
...
2022/03/25 17:11:56 [INFO]   Extract database name:  pg_restore --list /var/lib/dblab/dblab_pool/dump/test | grep dbname: | tr -d '[;]'
2022/03/25 17:11:57 [INFO]   Found the directory dump:  test
2022/03/25 17:11:57 [INFO]   Running restore command for test [pg_restore --username postgres --dbname postgres --no-privileges --no-owner --exit-on-error --create --clean --if-exists --jobs 2 /var/lib/dblab/
dblab_pool/dump/test]

Result: only the database "test" has been restored (there is no test2 database) - OK

ubuntu@ip-172-31-11-169:~$ dblab clone create --username test --password test-pass --id clone_01
{
    "id": "clone_01",
    "protected": false,
    "deleteAt": null,
    "createdAt": "2022-03-25 10:12:50 -07:00",
    "status": {
        "code": "OK",
        "message": "Clone is ready to accept Postgres connections."
    },
    "db": {
        "connStr": "host=localhost port=6000 user=test dbname=postgres",
        "host": "localhost",
        "port": "6000",
        "username": "test",
        "password": "",
        "dbName": ""
    },
    "snapshot": {
        "id": "dblab_pool_01@snapshot_20220325171116",
        "createdAt": "2022-03-25 10:12:33 -07:00",
        "dataStateAt": "2022-03-25 10:11:16 -07:00",
        "pool": "dblab_pool_01",
        "numClones": 1,
        "physicalSize": "0 B",
        "logicalSize": "317 MiB"
    },
    "metadata": {
        "cloningTime": 1.271436058,
        "maxIdleMinutes": 120,
        "cloneDiffSize": "183 KiB",
        "logicalSize": "317 MiB"
    }
}
ubuntu@ip-172-31-11-169:~$ PGPASSWORD=test-pass psql "host=localhost port=6000 user=test dbname=test"
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1), server 14.2 (Debian 14.2-1.pgdg110+1))
WARNING: psql major version 12, server major version 14.
         Some psql features might not work.
Type "help" for help.

test=# \l+
                                                                   List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 8665 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 8401 kB | pg_default | unmodifiable empty database
           |          |          |            |            | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 8529 kB | pg_default | default template for new databases
           |          |          |            |            | postgres=CTc/postgres |         |            | 
 test      | postgres | UTF8     | C.UTF-8    | C.UTF-8    |                       | 158 MB  | pg_default | 
(4 rows)

test=# \q

Test 2: With a list of databases in the DLE config

DLE config:

    logicalDump:
      options:
        <<: *db_container
        dumpLocation: "/var/lib/dblab/dblab_pool/dump"
        source:
          type: remote
          connection:
            dbname: test
            host: 172.31.23.34
            port: 5435
            username: myuser
            password: myuser-pass
        databases:
          test:
          test2:
        parallelJobs: 2
    logicalRestore:
      options:
        <<: *db_container
        dumpLocation: "/var/lib/dblab/dblab_pool/dump"
        parallelJobs: 2
        forceInit: true
...

DLE log:

2022/03/25 17:18:18 [INFO]   Running cleanup command:  [rm -rf /var/lib/dblab/dblab_pool/dump/test /var/lib/dblab/dblab_pool/dump/test2]
2022/03/25 17:18:18 [INFO]   Running dump command:  [pg_dump --create --port 5435 --username myuser --dbname test --jobs 2 --host 172.31.23.34 --format directory --file /var/lib/dblab/dblab_pool/dump/test]
2022/03/25 17:18:19 [INFO]   Dumping job for the database "test" has been finished
2022/03/25 17:18:19 [INFO]   Running dump command:  [pg_dump --create --username myuser --dbname test2 --jobs 2 --host 172.31.23.34 --port 5435 --format directory --file /var/lib/dblab/dblab_pool/dump/test2]
2022/03/25 17:18:19 [INFO]   Container logs:

2022/03/25 17:18:19 [INFO]   Removing container ID: d6c90de57281ff39293e6b1a65d9d2b0d4110ca87be6f41dada59dddfde11e0c
2022/03/25 17:18:50 [INFO]   Container "d6c90de57281ff39293e6b1a65d9d2b0d4110ca87be6f41dada59dddfde11e0c" has been stopped
2022/03/25 17:18:50 [INFO]   Container "d6c90de57281ff39293e6b1a65d9d2b0d4110ca87be6f41dada59dddfde11e0c" has been removed
2022/03/25 17:18:50 [ERROR]  Failed to run the data retrieval service: failed to dump the database test2: failed to dump a database: exit code: 1
2022/03/25 17:18:50 [INFO]   If you have problems or questions, please contact Postgres.ai: https://postgres.ai/contact

Result:

Error when trying to dump the "test2" database because there are no connection permissions - OK

approved this merge request

marked the checklist item MR changes are functionally tested as completed

resolved all threads

mentioned in commit 19531238

merged

resolved all threads