INSERT INTO gtest10a (a) VALUES (1);

-- privileges

CREATE USER regress_user11;

CREATE TABLEgtest11s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);

INSERT INTOgtest11s VALUES (1, 10), (2, 20);

GRANT SELECT (a, c) ONgtest11s TO regress_user11;

CREATE TABLEgtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);

INSERT INTOgtest11 VALUES (1, 10), (2, 20);

GRANT SELECT (a, c) ONgtest11 TO regress_user11;

CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE SQL;

REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;

CREATE TABLEgtest12s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);

INSERT INTOgtest12s VALUES (1, 10), (2, 20);

GRANT SELECT (a, c) ONgtest12s TO regress_user11;

CREATE TABLEgtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);

INSERT INTOgtest12 VALUES (1, 10), (2, 20);

GRANT SELECT (a, c) ONgtest12 TO regress_user11;

SET ROLE regress_user11;

SELECT a, b FROMgtest11s; -- not allowed

ERROR: permission denied for tablegtest11s

SELECT a, c FROMgtest11s; -- allowed

SELECT a, b FROMgtest11; -- not allowed

ERROR: permission denied for tablegtest11

SELECT a, c FROMgtest11; -- allowed

a | c

---+----

1 | 20

SELECT gf1(10); -- not allowed

ERROR: permission denied for function gf1

SELECT a, c FROMgtest12s; -- allowed

SELECT a, c FROMgtest12; -- allowed

a | c

---+----

1 | 30

RESET ROLE;

DROP FUNCTION gf1(int); -- fail

ERROR: cannot drop function gf1(integer) because other objects depend on it

DETAIL: column c of tablegtest12s depends on function gf1(integer)

DETAIL: column c of tablegtest12 depends on function gf1(integer)

HINT: Use DROP ... CASCADE to drop the dependent objects too.

DROP TABLEgtest11s, gtest12s;

DROP TABLEgtest11, gtest12;

DROP FUNCTION gf1(int);

DROP USER regress_user11;

-- check constraints

CREATEUSERregress_user11;

CREATETABLEgtest11s (aintPRIMARY KEY, bint, cint GENERATED ALWAYSAS (b*2) STORED);

INSERT INTOgtest11sVALUES (1,10), (2,20);

GRANTSELECT (a, c)ONgtest11s TO regress_user11;

CREATETABLEgtest11 (aintPRIMARY KEY, bint, cint GENERATED ALWAYSAS (b*2) STORED);

INSERT INTOgtest11VALUES (1,10), (2,20);

GRANTSELECT (a, c)ONgtest11 TO regress_user11;

CREATEFUNCTIONgf1(aint) RETURNSintAS $$SELECT a*3 $$ IMMUTABLE LANGUAGE SQL;

REVOKE ALLON FUNCTION gf1(int)FROM PUBLIC;

CREATETABLEgtest12s (aintPRIMARY KEY, bint, cint GENERATED ALWAYSAS (gf1(b)) STORED);

INSERT INTOgtest12sVALUES (1,10), (2,20);

GRANTSELECT (a, c)ONgtest12s TO regress_user11;

CREATETABLEgtest12 (aintPRIMARY KEY, bint, cint GENERATED ALWAYSAS (gf1(b)) STORED);

INSERT INTOgtest12VALUES (1,10), (2,20);

GRANTSELECT (a, c)ONgtest12 TO regress_user11;

SET ROLE regress_user11;

SELECT a, bFROMgtest11s;-- not allowed

SELECT a, cFROMgtest11s;-- allowed

SELECT a, bFROMgtest11;-- not allowed

SELECT a, cFROMgtest11;-- allowed

SELECT gf1(10);-- not allowed

SELECT a, cFROMgtest12s;-- allowed

SELECT a, cFROMgtest12;-- allowed

RESET ROLE;

DROPFUNCTION gf1(int);-- fail

DROPTABLEgtest11s, gtest12s;

DROPTABLEgtest11, gtest12;

DROPFUNCTION gf1(int);

DROPUSER regress_user11;