Row level security insert policy does not validate update new values/content?

[alias]

*Hi,*
*original query*

> BEGIN;
> RESET session AUTHORIZATION;
> SET search_path = test;
> SET session AUTHORIZATION alice;
> CREATE TABLE emp (
> name text,
> paydate date,
> income numeric
> );
> GRANT ALL ON TABLE emp TO public;
> INSERT INTO emp
> VALUES ('John', '12-01-2009', 5), ('Jake', '12-01-2009', 7),
> ('Jill', '12-21-2009', 85000.75), ('Jonn', '12-27-2009', 12.5),
> ('June', '01-01-2010', 10.25), ('Joey', '01-01-2010', NULL), ('Jack',
> '01-01-2010', 12), ('Jane', '01-01-2010', 11.75), ('Jean',
> '01-01-2010', NULL), ('Joon', '01-01-2010', NULL);
> ALTER TABLE emp ENABLE ROW LEVEL SECURITY;
> CREATE POLICY test_special_date ON emp
> FOR INSERT TO public
> WITH CHECK ((income > 10 AND paydate = '2010-01-01'::date)
> OR (income IS NULL AND paydate = '2010-01-01'::date)
> OR (paydate != '2010-01-01'::date));
> CREATE POLICY emp_sel ON emp
> FOR SELECT TO public
> USING (TRUE);
> CREATE POLICY emp_del ON emp
> FOR DELETE TO public
> USING (TRUE);
> -- create policy emp_upd on emp for update to public using (true);
> CREATE POLICY test_special_date_upd ON emp
> FOR UPDATE TO public
> WITH CHECK ((income > 10 AND paydate = '2010-01-01'::date)
> OR (income IS NULL AND paydate = '2010-01-01'::date)
> OR (paydate != '2010-01-01'::date));
> COMMIT;
>

then
*use user sessions that cannot bypass row level security* to execute the
following command return 0 rows. (that's what i intended).

update emp set income = 11 where name = 'Jane' returning *;

However:
*comment *out policy test_special_date_upd, *uncomment *out policy emp_upd
, the following query will return 1 row.

> update emp set income = 11 where name = 'Jane' returning *;
>
> /*
+--+++
| name |  paydate   | income |
+--+++
| Jane | 2010-01-01 | 11 |
+--+++
*/


My thought process:

> update = delete  + insert.
> so * create policy emp_upd on emp for update to public using (true); *should
> be ok for updating every row, let insert policy handle new row
> *. *
> since there is only one check_expression, also no need to worry about
> permissive/restrictive.
>

but it seems, I need to create the same policy as insert operation to
update operation to validate the new content/row of update operation.

generated column cast from timestamptz to timestamp not OK.

[alias]

CREATE TABLE test_g (
a timestamptz,
b timestamp GENERATED ALWAYS AS (a::timestamp) STORED
);
then an error occurred.

> ERROR:  42P17: generation expression is not immutable
> LOCATION:  cookDefault, heap.c:2768
>

However  the following 2 commands is ok.

CREATE TABLE test_i (
> a int,
> b bigint GENERATED ALWAYS AS (a::bigint) STORED
> );
>


> CREATE TABLE test_c (
> a varchar,
> b text GENERATED ALWAYS AS (a::text) STORED
> );
>

I didn't get it. timestamptz changes then timestamp also changes. timestamp
is part of timestamptz...
Even if column timestamptz is some value that is constantly changing (like
now() ), it changes/updates then just in the mean time captures timestamp
to column b.

Re: Deferred constraint trigger semantics

[alias]

>
>
> It adds the "initially deferred" decoration to the "create constraint
> trigger" statement. This is (still) the result:
>
> INFO:  trg fired. new.v = 10, n = 5
> INFO:  trg fired. new.v = 20, n = 5
> INFO:  trg fired. new.v = 30, n = 5
> INFO:  trg fired. new.v = 40, n = 5
> INFO:  trg fired. new.v = 50, n = 5
> INFO:  trg fired. new.v = 60, n = 8
> INFO:  trg fired. new.v = 70, n = 8
> INFO:  trg fired. new.v = 80, n = 8
>
Because You can do
create constraint trigger trg
after insert on t2
deferrable initially deferred
for each row
execute function trg_fn();

You didn't explicitly defer the trigger trg on t1!. That means after you
insert on t1 then the trigger trg on t1 invoked rather than on commit
time.
If you
create constraint trigger trg
after insert on t1
deferrable initially deferred
for each row
execute function trg_fn();

create constraint trigger trg
after insert on t2
deferrable initially deferred
for each row
execute function trg_fn();
then you will get

> INFO:  0: trg fired. new.v = 10, n = 8
> INFO:  0: trg fired. new.v = 20, n = 8
> INFO:  0: trg fired. new.v = 30, n = 8
> INFO:  0: trg fired. new.v = 40, n = 8
> INFO:  0: trg fired. new.v = 50, n = 8
> INFO:  0: trg fired. new.v = 60, n = 8
> INFO:  0: trg fired. new.v = 70, n = 8
> INFO:  0: trg fired. new.v = 80, n = 8
>




On Thu, May 12, 2022 at 4:13 AM Bryn Llewellyn  wrote:

> *david.g.johns...@gmail.com  wrote:*
>
> *b...@yugabyte.com  wrote:*
>
>
> Thanks for the point-by-point reply, David.
>
> ...makes no mention of what you might expect to see in an AFTER EACH
> ROW trigger.
>
>
> ...the absence of a comment declaring a guarantee of order means that,
> like the comment for the row-level BEFORE trigger, the row-level AFTER row
> ordering is not guaranteed (even if one cannot produce a counter-example in
> today's codebase).
>
>
> Got it!
>
> ...unless anybody contradicts me.
>
>
> Caveat emptor...? I wouldn't be surprised that doing so is technically
> possible in all cases - as to whether a particular algorithm is sound, to
> some extent, isn't something we try to predict. We do try to describe all
> the known interactions though - and let the user work within what those
> mean for them.
>
>
> Got it again!
>
> ...implies that there's no such unpredictability in the AFTER EACH ROW
> cases.
>
>
> I would not read it that way. In general, absence of mention of
> predictability like this means there is none - that some other sentence
> goes into more detail doesn't change that.
>
>
> OK.
>
> But there has to be a subtle caveat here for the deferred constraint
> trigger when the txn changes two or more tables, all of which participate
> in the query that the trigger function issues… The "raise info"
> output below illustrates my point (n changes from 5 to 8).
>
>
> I'm failing to see the deferral aspect of that example. First statement
> finishes, sees the 5 inserts, next statement finishes, sees 3 more inserts.
> Not, both statements finish, triggers fire, triggers see all 8 inserts
> (which I suspect they will if you actually perform deferral).
>
>
> Oops. I did a copy-and-paste error on going from my test env. to email and
> missed out the "deferral" that I'd intended. For completeness, here's the
> test that I meant:
>
> create table t1(k serial primary key, v int not null);
> create table t2(k serial primary key, v int not null);
>
> create function trg_fn()
>   returns trigger
>   language plpgsql
> as $body$
> declare
>   n int not null := 0;
> begin
>   n := (select count(*) from t1) + (select count(*) from t2);
>   raise info 'trg fired. new.v = %, n = %', new.v, n;
>   return new;
> end;
> $body$;
>
> create constraint trigger trg
> after insert on t1
> for each row
> execute function trg_fn();
>
> create constraint trigger trg
> after insert on t2
> initially deferred
> for each row
> execute function trg_fn();
>
> set default_transaction_isolation = 'read committed';
> do $body$
> begin
>   insert into t1(v)
>   values (10), (20), (30), (40), (50);
>
>   insert into t2(v)
>   values (60), (70), (80);
> end;
> $body$;
>
> It adds the "initially deferred" decoration to the "create constraint
> trigger" statement. This is (still) the result:
>
> INFO:  trg fired. new.v = 10, n = 5
> INFO:  trg fired. new.v = 20, n = 5
> INFO:  trg fired. new.v = 30, n = 5
> INFO:  trg fired. new.v = 40, n = 5
> INFO:  trg fired. new.v = 50, n = 5
> INFO:  trg fired. new.v = 60, n = 8
> INFO:  trg fired. new.v = 70, n = 8
> INFO:  trg fired. new.v = 80, n = 8
>
> Even though both inserts have completed by commit time, only the trigger
> firing caused by the second statement sees the final state that obtains the
> moment before commit. The first statement sees only the state after it
> finishes and before the second statement executes. You said « I suspect
> [that both statements will see the final state] if you actually perform
> deferral ». My test shows that this is not the case.
>
> *Did I 

Re: How to set password in psql -h -d -U command line?

[alias]

Don't Do This - PostgreSQL wiki


On Thu, Apr 28, 2022 at 3:13 PM Josha Inglis  wrote:

> https://www.postgresql.org/docs/current/libpq-envars.html
>
> Either set the PGPASSWORD environment variable or set up a .pgpass file
>
> Josha
>
> On Thu, 28 Apr 2022 at 19:33, Shaozhong SHI 
> wrote:
>
>> I tried various ways to set password in psql command line, but got no
>> luck.
>>
>> Can anyone help?
>>
>> Regards,
>>
>> David
>>
> --
> Kind Regards
>
> Josha Inglis
>

row level security on conflict do update

[alias]

git.postgresql.org Git - postgresql.git/blob -
src/test/regress/sql/rowsecurity.sql


> 58
> 
> CREATE TABLE category (
> 59
> 
> cidint primary key,
> 60
> 
> cname  text
> 61
> 
> );
> 62
> 
> GRANT ALL ON category TO public;
> 63
> 
> INSERT INTO category VALUES
> 64
> 
> (11, 'novel'),
> 65
> 
> (22, 'science fiction'),
> 66
> 
> (33, 'technology'),
> 67
> 
> (44, 'manga');
> 68
> 
> 69
> 
> CREATE TABLE document (
> 70
> 
> did int primary key,
> 71
> 
> cid int references category(cid),
> 72
> 
> dlevel  int not null,
> 73
> 
> dauthor name,
> 74
> 
> dtitle  text
> 75
> 
> );
> 76
> 
> GRANT ALL ON document TO public;
> 77
> 
> INSERT INTO document VALUES
> 78
> 
> ( 1, 11, 1, 'regress_rls_bob', 'my first novel'),
>