Hi!
I have used script below to remove all rights from user and add some. Today it starts to throw error
ERROR: duplicate key value violates unique constraint "pg_default_acl_role_nsp_obj_index"DETAIL: Key (defaclrole, defaclnamespace, defaclobjtype)=(30152, 186783649, r) already exists.
How to fix this and which is proper way to implement this ?
revoke all on all tables in schema public,firma1,firma3,firma4,firma5,firma6,firma15,firma17,firma19,firma20,firma21,firma22,firma23,firma24,firma25,firma26,firma27,firma30,firma31,firma32,firma34,firma36,firma37,firma41,firma55,firma59,firma60,firma62,firma63,firma65,firma66,firma67,firma68,firma70,firma71,firma73,firma74,firma76,firma77,firma78,firma79,firma80,firma85,firma88,firma89,firma90,firma91,firma93,firma94,firma95,firma96,firma97,firma98,firma100,firma101,firma102,firma103,firma104,firma105,firma106,firma107,firma110,firma111,firma112,firma148,firma149,firma151,firma152,firma153,firma154,firma155,firma156,firma157,firma158,firma159,firma160,firma163,firma164,firma165,firma166,firma172,firma173,firma174,firma175,firma176,firma177,firma178,firma179,firma180,firma181,firma188,firma189,firma190,firma191,firma192,firma193,firma194,firma195,firma196 from "testuser" cascade;
revoke all on all sequences in schema public,firma1,firma3,firma4,firma5,firma6,firma15,firma17,firma19,firma20,firma21,firma22,firma23,firma24,firma25,firma26,firma27,firma30,firma31,firma32,firma34,firma36,firma37,firma41,firma55,firma59,firma60,firma62,firma63,firma65,firma66,firma67,firma68,firma70,firma71,firma73,firma74,firma76,firma77,firma78,firma79,firma80,firma85,firma88,firma89,firma90,firma91,firma93,firma94,firma95,firma96,firma97,firma98,firma100,firma101,firma102,firma103,firma104,firma105,firma106,firma107,firma110,firma111,firma112,firma148,firma149,firma151,firma152,firma153,firma154,firma155,firma156,firma157,firma158,firma159,firma160,firma163,firma164,firma165,firma166,firma172,firma173,firma174,firma175,firma176,firma177,firma178,firma179,firma180,firma181,firma188,firma189,firma190,firma191,firma192,firma193,firma194,firma195,firma196 from "testuser" cascade;
revoke all on database sba,mef,aus,reta,idd,sam,namm,nevoteks,haa,yle,eevakk,swecon,kose,kliima,best,eeva,kiilikvh,pariisi,nopri,laaneteed,belimo,nkaubandus,netroo,kuluk from "testuser" cascade;
revoke all on all functions in schema public,firma1,firma3,firma4,firma5,firma6,firma15,firma17,firma19,firma20,firma21,firma22,firma23,firma24,firma25,firma26,firma27,firma30,firma31,firma32,firma34,firma36,firma37,firma41,firma55,firma59,firma60,firma62,firma63,firma65,firma66,firma67,firma68,firma70,firma71,firma73,firma74,firma76,firma77,firma78,firma79,firma80,firma85,firma88,firma89,firma90,firma91,firma93,firma94,firma95,firma96,firma97,firma98,firma100,firma101,firma102,firma103,firma104,firma105,firma106,firma107,firma110,firma111,firma112,firma148,firma149,firma151,firma152,firma153,firma154,firma155,firma156,firma157,firma158,firma159,firma160,firma163,firma164,firma165,firma166,firma172,firma173,firma174,firma175,firma176,firma177,firma178,firma179,firma180,firma181,firma188,firma189,firma190,firma191,firma192,firma193,firma194,firma195,firma196 from "testuser" cascade;
revoke all on schema public,firma1,firma3,firma4,firma5,firma6,firma15,firma17,firma19,firma20,firma21,firma22,firma23,firma24,firma25,firma26,firma27,firma30,firma31,firma32,firma34,firma36,firma37,firma41,firma55,firma59,firma60,firma62,firma63,firma65,firma66,firma67,firma68,firma70,firma71,firma73,firma74,firma76,firma77,firma78,firma79,firma80,firma85,firma88,firma89,firma90,firma91,firma93,firma94,firma95,firma96,firma97,firma98,firma100,firma101,firma102,firma103,firma104,firma105,firma106,firma107,firma110,firma111,firma112,firma148,firma149,firma151,firma152,firma153,firma154,firma155,firma156,firma157,firma158,firma159,firma160,firma163,firma164,firma165,firma166,firma172,firma173,firma174,firma175,firma176,firma177,firma178,firma179,firma180,firma181,firma188,firma189,firma190,firma191,firma192,firma193,firma194,firma195,firma196 from "testuser" cascade;
revoke sba_owner from "testuser" cascade;
ALTER ROLE "testuser" inherit NOCREATEROLE NOCREATEDB LOGIN;
grant all on all tables in schema public,firma193,firma193,firma195,firma196 to "testuser";
grant all on all sequences in schema public,firma193,firma193,firma195,firma196 to "testuser";
grant all on database sba to "testuser";
grant all on schema public,firma193,firma193,firma195,firma196 to "testuser";
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma193,firma193,firma195,firma196
GRANT all ON TABLES TO "testuser";
revoke all on kasutaja,kaspriv,logifail from "testuser" cascade;
grant select on kaspriv,kasutaja to "testuser";
grant update (eesnimi, nimi,email,amet,islocked,telefon,language,vabakuup,kasilfirma) on kasutaja to "testuser";
grant insert on logifail to "testuser";
Using
PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Andrus.