Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[YSQL] Backups: Add new ROLEs related flags #20972

Open
1 task done
OlegLoginov opened this issue Feb 7, 2024 · 0 comments
Open
1 task done

[YSQL] Backups: Add new ROLEs related flags #20972

OlegLoginov opened this issue Feb 7, 2024 · 0 comments
Assignees
Labels
area/ysql Yugabyte SQL (YSQL) kind/new-feature This is a request for a completely new feature priority/medium Medium priority issue status/awaiting-triage Issue awaiting triage

Comments

@OlegLoginov
Copy link
Contributor

OlegLoginov commented Feb 7, 2024

Jira Link: DB-9947

Description

As it's discussed in the design doc the new flags must be added into the yb_backup.py scripts:
--backup_roles
--restore_roles
--ignore_existing_roles
--use_roles
The last flag should be passed into ysqlsh -v use_roles=true ....
In the YSQL dump should be implemented the new logic:

\if :{?use_roles}
\else
\set use_roles false
\endif

\if :{?ignore_existing_roles}
\else
\set ignore_existing_roles false
\endif

\if :ignore_existing_roles
IF EXISTS (SELECT 1 FROM pg_roles WHERE spcname = 'u2') THEN
     RAISE NOTICE 'User % already exists.', 'u2';
ELSE
     CREATE ROLE u2 ...
END IF;
\elif
CREATE ROLE u2 ...
\endif
...
-- Set privileges on t1
\if :use_roles
ALTER TABLE public.t1 OWNER TO u2;
GRANT SELECT ON TABLE public.t1 TO u2;
\endif

Issue Type

kind/new-feature

Warning: Please confirm that this issue does not contain any sensitive information

  • I confirm this issue does not contain any sensitive information.
@OlegLoginov OlegLoginov added area/ysql Yugabyte SQL (YSQL) status/awaiting-triage Issue awaiting triage labels Feb 7, 2024
@yugabyte-ci yugabyte-ci added kind/new-feature This is a request for a completely new feature priority/medium Medium priority issue labels Feb 7, 2024
@OlegLoginov OlegLoginov changed the title [YSQL] Backups: Add new ROLEs related flags #20389 [YSQL] Backups: Add new ROLEs related flags Feb 7, 2024
@OlegLoginov OlegLoginov self-assigned this Feb 9, 2024
OlegLoginov added a commit that referenced this issue Mar 4, 2024
Summary:
The following **NEW** `yb_backup.py` flags were implemented:
* `--backup_roles`
* `--restore_roles`
* `--ignore_existing_roles`
* `--use_roles`
2 last flags are passed into `ysqlsh -v ignore_existing_roles=true -v use_roles=true ....`.

**SEMANTICS**:
Backup-create: "--backup_roles" ~ backup Roles
Backup-restore: "--restore_roles" ~ restore the stored Roles
Backup-restore: "--ignore_existing_roles" ~ do not create the role if it already exists
Backup-restore: "--use_roles" ~ use the Roles for the tables via
`ALTER TABLE ... OWNER TO <role>` and
`REVOKE/GRANT ... ON TABLE ... FROM/TO <role>` syntax.

NOTE: The API is changed only when `ENABLE_STOP_ON_YSQL_DUMP_RESTORE_ERROR`
is set into `True` (now it's `False`).

In the YSQL dump the new logic is implemented:
```
\if :{?ignore_existing_roles}
\else
\set ignore_existing_roles false
\endif

\set role_exists false
\if :ignore_existing_roles
    SELECT EXISTS(SELECT 1 FROM pg_roles WHERE rolname = 'role_admin') AS role_exists \gset
\endif
\if :role_exists
    \echo 'Role role_admin already exists.'
\else
    CREATE ROLE role_admin;
\endif
```

```
\if :{?use_roles}
\else
\set use_roles true
\endif

CREATE TABLE ...

\if :use_roles
ALTER TABLE t OWNER TO role_admin;
\endif

\if :use_roles
GRANT SELECT ON TABLE t TO role_admin;
\endif
```
Jira: DB-9947

Test Plan:
Set ENABLE_STOP_ON_YSQL_DUMP_RESTORE_ERROR=True in yb_backup.py.

New tests:
ybd --java-test org.yb.pgsql.TestYbBackup#testBackupRestoreRoles
ybd --java-test org.yb.pgsql.TestYbBackup#testBackupRolesWithoutUseRoles
ybd --java-test org.yb.pgsql.TestYbBackup#testBackupRolesWithoutRestoreRoles

Other related tests:
ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpWithYbMetadata
ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpWithoutYbMetadata
ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpAllWithoutYbMetadata
ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpAllWithYbMetadata
ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpLegacyColocatedDB
ybd --java-test org.yb.pgsql.TestYsqlDump#ysqlDumpColocatedDB

ybd --java-test org.yb.pgsql.TestYbBackup#testExtensionBackupUsingTestExtension
ybd --java-test org.yb.pgsql.TestYbBackup#testBackupRestoreTablespaces
ybd --java-test org.yb.pgsql.TestYbBackup#testFailureOnExistingTablespaces
ybd --java-test org.yb.pgsql.TestYbBackup#testIgnoreExistingTablespaces

ybd --java-test org.yb.pgsql.TestYbBackup#testGeoPartitioning
ybd --java-test org.yb.pgsql.TestYbBackup#testGeoPartitioningNoRegions
ybd --java-test org.yb.pgsql.TestYbBackup#testGeoPartitioningOneRegion
ybd --java-test org.yb.pgsql.TestYbBackup#testGeoPartitioningRestoringIntoExisting
ybd --java-test org.yb.pgsql.TestYbBackup#testGeoPartitioningWithTablespaces
ybd --java-test org.yb.pgsql.TestYbBackup#testGeoPartitioningNoRegionsWithTablespaces
ybd --java-test org.yb.pgsql.TestYbBackup#testGeoPartitioningOneRegionWithTablespaces
ybd --java-test org.yb.pgsql.TestYbBackup#testGeoPartitioningRestoringIntoExistingWithTablespaces

Reviewers: mihnea, tverona, myang, yguan, vkumar, yng

Reviewed By: myang

Subscribers: yugaware, yql

Differential Revision: https://phorge.dev.yugabyte.com/D32252
OlegLoginov added a commit that referenced this issue Jul 23, 2024
Summary:
The diff applies post-commit comments from the diff:
5659b73
https://phorge.dev.yugabyte.com/D32252

2 YB specific local variables were renamed - to simplify any future Postgres code-base migrations.
Jira: DB-9947

Test Plan: Jenkins builds

Reviewers: mihnea, jason

Reviewed By: jason

Subscribers: yql

Differential Revision: https://phorge.dev.yugabyte.com/D36755
jasonyb pushed a commit that referenced this issue Jul 24, 2024
…s in PG code

Summary:
Merge:

- pg_dump.c
  - dumpACL:
    - variable declarations of if (sql->len > 0): YB master
      7414711 renames use_roles_sql to
      yb_use_roles_sql.  Upstream PG
      ea9125304dc6e90eabad165bd120eb1e667525d4 adds aclDeps and nDeps.
      Adjacent conflict.
    - ArchiveEntry call: YB master
      7414711 does a similar rename for
      one of the arguments.  Upstream PG
      f831d4accda00b9144bc647ede2e2f848b59f39d changes the format of the
      arguments.  Merge.

The diff applies post-commit comments from the diff:
5659b73
https://phorge.dev.yugabyte.com/D32252

2 YB specific local variables were renamed - to simplify any future Postgres code-base migrations.
Jira: DB-9947

Test Plan: Jenkins: rebase: pg15-cherrypicks

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D36820
jasonyb pushed a commit that referenced this issue Jul 24, 2024
Summary:
 Excluded: 7414711 [#20972] YSQL: Fix new local variable names in PG code
 3484013 [#20766] YSQL: Remove time stuff from the GetTableKeyRanges functions.
 8b23629 [#18360] YSQL: fix yb_hash_code compared to an out of range constant
 4ee3511 [PLAT-13438]: Add support for preview gflags
 fe5e675 [#23218] YSQL: Closing the backend connection on receiving the error "Database might have been dropped by another user"
 0901f6c [doc] Default databases (#23261)
 afaf353 [PLAT-14722]Upgrade YBC client and server versio to 2.2.0.0-b3
 fdffc33 [#13358] YSQL: DDL Atomicity Stress Test
 f0b3c46 [ #21499] YSQL: Skipping the test with connection manager as they are unsuitable
 5ed864d [#23260] CDCSDK: Propagate stream expiry & GC error to walsender
 e37d791 [PLAT-14627][PLAT-14729][PLAT-14726]Process group-based specific gflags comprehensively across multiple callsites in YBA
 0e17c97 [PLAT-14737]Missing/incorrect universe status loader positions on dashboard page
 6477bad [PLAT-12434] YNP fix XDG_RUNTIME_DIR to be set in .bashrc for non login shells
 f3ffb18 [docs] Tablet metadata and ASH updates (#23221)

Test Plan: Jenkins: rebase: pg15-cherrypicks

Reviewers: jason, tfoucher

Subscribers: yql

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D36814
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/ysql Yugabyte SQL (YSQL) kind/new-feature This is a request for a completely new feature priority/medium Medium priority issue status/awaiting-triage Issue awaiting triage
Projects
None yet
Development

No branches or pull requests

2 participants