Bug when there is a change/diff in the ACLs

[kapilagarwal007]

Describe the bug

Customer is using the schema compare process in PG Admin 4 version 6.12 today on two databases and I ran into a problem. Under the Compare button, there are two check boxes for Ignore Owner and Ignore Whitespace.
Kindly refer below screen-shot for your reference, customer has checked each of these and still list all objects as different even though the only difference is the owner of the object.

Reproduced

We have tried to test this at our local machine, and we are able to pin-point the issue here as below..

When you perform any manual GRANTS on table, then the relacl value of that table (which stores the details about the Access privileges) get updated. Hence, when you run schema_diff after manually executing the grant then the value of relacl gets updated in accordance of your (permitted) user and you see a difference in schema even you choose to ignore the owner.

To Reproduce

Below are the sql to reproduce the issue.

create user u1;
create user u2;
create database db1;
create database db2;
\c db1
create schema sm1;
create table sm1.account (id bigint NOT NULL, programname varchar(20) NOT NULL,CONSTRAINT accountprogram_pkye PRIMARY KEY (id))with (OIDS=FALSE) TABLESPACE pg_default;
alter table sm1.account owner to u1;
\c db2
create schema sm2;
create table sm2.account (id bigint NOT NULL, programname varchar(20) NOT NULL,CONSTRAINT accountprogram_pkye PRIMARY KEY (id))with (OIDS=FALSE) TABLESPACE pg_default;
alter table sm2.account owner to u2;
\c db1
grant ALL on TABLE sm1.account to u1;
\c db2
grant ALL on TABLE sm2.account to u2;

Expected behaviour

If we are selecting ignore owner it should not show any difference in schema related to the owner output.

Screenshots

Attached screen shots of two comparisons that customer completed, one without the ignore options, one with the ignore options.
Each compare shows the same information.

Desktop (please complete the following information):

• OS: centos7
• Version: pgadmin4 6.12
• Mode: server
• Browser (if running in server mode): chrome
• Package type: RPM

[naveedkarajgi]

Hi Team,

Below are the sql to reproduce the issue.

create user u1;
create user u2;
create database db1;
create database db2;
\c db1 
create schema sm1;
create table sm1.account (id bigint NOT NULL, programname varchar(20) NOT NULL,CONSTRAINT accountprogram_pkye PRIMARY KEY (id))with (OIDS=FALSE) TABLESPACE pg_default;
alter table sm1.account owner to u1;
\c db2
create schema sm2;
create table sm2.account (id bigint NOT NULL, programname varchar(20) NOT NULL,CONSTRAINT accountprogram_pkye PRIMARY KEY (id))with (OIDS=FALSE) TABLESPACE pg_default;
alter table sm2.account owner to u2;
\c db1
grant ALL on TABLE sm1.account to u1;
\c db2 
grant ALL on TABLE sm2.account to u2;

[FaharAbbasRizvi]

This is fixed in the latest snapshot build:
https://www.postgresql.org/ftp/pgadmin/pgadmin4/snapshots/2023-01-05/

[dhananjayjejurkar]

Hi @FaharAbbasRizvi ,
3days back @akshay-joshi mentioned about 6.19 version. However, your above-mentioned link redirects us to the 6.18 version. Hence, can we ask the customer to use the 6.18 latest snapshot for testing it?

[akshay-joshi]

@dhananjayjejurkar

The link provided by Fahar is of the snapshot(nightly) build, so it will show version 6.18 as we change the version when we will create the candidate build.