date:20180930

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

2018-09-30 Thread Charles Clavadetscher (SwissPUG)

Hello


On 29.09.2018 20:24:45, Adrian Klaver  wrote:
On 9/28/18 11:35 PM, Carl Sverre wrote:
> *Context*
> I am using row-level security along with triggers to implement a pure
> SQL RBAC implementation. While doing so I encountered a weird behavior
> between INSERT triggers and SELECT row-level security policies.
>
> *Question*
> I have posted a very detailed question on StackOverflow here:
> https://stackoverflow.com/questions/52565720/postgres-trigger-side-effect-is-occurring-out-of-order-with-row-level-security-s
>
> For anyone who is just looking for a summary/repro, I am seeing the
> following behavior:
>
> CREATE TABLE a (id TEXT);
> ALTER TABLE a ENABLE ROW LEVEL SECURITY;
> ALTER TABLE a FORCE ROW LEVEL SECURITY;
>
> CREATE TABLE b (id TEXT);
>
> CREATE POLICY ON a FOR SELECT
> USING (EXISTS(
>     select * from b where a.id = b.id
> ));
>
> CREATE POLICY ON a FOR INSERT
> WITH CHECK (true);
>
> CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$
> BEGIN
>     RAISE NOTICE USING MESSAGE = 'inside trigger handler';
>     INSERT INTO b (id) VALUES (NEW.id);
>     RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER reproTrigger BEFORE INSERT ON a
> FOR EACH ROW EXECUTE PROCEDURE reproHandler();
>
> INSERT INTO a VALUES ('fails') returning id;
> NOTICE:  inside trigger handler
> ERROR:  new row violates row-level security policy for table "a"
>
> Rather than the error, I expect that something along these lines should
> occur instead:
>
> 1. A new row ('fails') is staged for INSERT
> 2. The BEFORE trigger fires with NEW set to the new row
> 3. The row ('fails') is inserted into b and returned from the trigger
> procedure unchanged
> 4. The INSERT's WITH CHECK policy true is evaluated to true
> 5. The SELECT's USING policy select * from b where a.id =
> b.id is evaluated.  *This should return true due to step 3*
> 6. Having passed all policies, the row ('fails') is inserted in table
> 7. The id (fails) of the inserted row is returned
>
> If anyone can point me in the right direction I would be extremely thankful.

When I tried to reproduce the above I got:

test=# CREATE POLICY ON a FOR SELECT
test-# USING (EXISTS(
test(# select * from b where a.id = b.id
test(# ));
ERROR: syntax error at or near "ON"
LINE 1: CREATE POLICY ON a FOR SELECT
^
test=#
test=# CREATE POLICY ON a FOR INSERT
test-# WITH CHECK (true);
ERROR: syntax error at or near "ON"
LINE 1: CREATE POLICY ON a FOR INSERT

Changing your code to:

CREATE TABLE a (id TEXT);
ALTER TABLE a ENABLE ROW LEVEL SECURITY;
ALTER TABLE a FORCE ROW LEVEL SECURITY;

CREATE TABLE b (id TEXT);

CREATE POLICY a_select ON a FOR SELECT
USING (EXISTS(
select * from b where a.id = b.id
));

CREATE POLICY a_insert ON a FOR INSERT
WITH CHECK (true);

CREATE FUNCTION reproHandler() RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE USING MESSAGE = 'inside trigger handler';
INSERT INTO b (id) VALUES (NEW.id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reproTrigger BEFORE INSERT ON a
FOR EACH ROW EXECUTE PROCEDURE reproHandler();

Resulted in:

test=# INSERT INTO a VALUES ('fails') returning id;
NOTICE: inside trigger handler
id
---
fails
(1 row)

INSERT 0 1
test=# select * from a;
id
---
fails
(1 row)


>
> Carl Sverre
>
> http://www.carlsverre.com


--
Adrian Klaver
adrian.kla...@aklaver.com

[Charles] : I did the same test with PG version 10 on Windows and PG 9.6.2 on 
Linux (RedHat) with exactly the same result.

db=# INSERT INTO a VALUES ('fails') returning id;
NOTICE:  inside trigger handler
  id
---
 fails
(1 row)

INSERT 0 1
db=# select * from a;
  id
---
 fails
(1 row)

db=# select * from b;
  id
---
 fails
(1 row)

Regards
Charles

Re: How to maintain the csv log files in pg_log directory only for past 30 days

2018-09-30 Thread Peter J. Holzer

On 2018-09-29 07:12:32 -0700, Adrian Klaver wrote:
> On 9/28/18 7:05 PM, Raghavendra Rao J S V wrote:
> > Hope you all are recommending below settings to maintain only max 30
> > days logs in *pg_log* directory. Please correct me if I am wrong.
> 
> Well it would actually be 31 days as:
> 
> http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html
> 
> %d
> Replaced by the day of the month as a decimal number [01,31]. [ tm_mday]

Additionally, the log file for the 31st of a month is usually only
overwritten after 61 days.

> Not sure if that matters or not.

It may or may not. Personally I prefer to use find -mtime (or logrotate,
or cleandir, or keepfree, ...) to avoid the irregularities of the
Gregorian calendar.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

vacuum question

2018-09-30 Thread Torsten Förtsch

Hi,

I have a table with a really small number of rows, usually about 1500,
sometimes may be up to 5000. The usage pattern of that table is such that
rows are inserted and kept for a while, mostly seconds or minutes but
theoretically up to 1 year. After that they are deleted. No updates, just
insert, delete. The table is pretty actively written, sometimes >100
transactions per second.

Although the table is frequently auto-vacuumed, its size is growing over
time. The size of the table corresponds to the remaining number of pages in
the autovacuum log.

 pages: 0 removed, 30069 remain, 0 skipped due to pins, 29985 skipped
frozen
 tuples: 136 removed, 1643 remain, 15 are dead but not yet removable
 buffer usage: 44327 hits, 0 misses, 21 dirtied
 avg read rate: 0.000 MB/s, avg write rate: 0.036 MB/s
 system usage: CPU 0.06s/0.05u sec elapsed 4.59 sec

What does "29985 skipped frozen" mean? I presume these skipped buffers is
the table bloat.

My way to get rid of the bloat is to cluster the table. That takes less
than half a second and the next autovacuum log then looks like this:

 pages: 0 removed, 3493 remain, 0 skipped due to pins, 0 skipped frozen
 tuples: 0 removed, 58873 remain, 57470 are dead but not yet removable
 buffer usage: 9425 hits, 0 misses, 0 dirtied
 avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
 system usage: CPU 0.02s/0.03u sec elapsed 1.01 sec

For a number of autovacuum cycles "skipped frozen" remains 0 until
eventually:

 pages: 0 removed, 4822 remain, 0 skipped due to pins, 4433 skipped frozen
 tuples: 227 removed, 2609 remain, 13 are dead but not yet removable
 buffer usage: 5269 hits, 0 misses, 315 dirtied
 avg read rate: 0.000 MB/s, avg write rate: 2.065 MB/s
 system usage: CPU 0.03s/0.01u sec elapsed 1.19 sec

Is there anything I can do to prevent that bloat from accumulating in the
first place?

Another thing that seems odd is the number of dead but not removable tuples
in the record in the middle. Sifting through the log, I can see numbers up
to 8. One of our replicas is configured with hot_standby_feedback. Can
that be the culprit?

This is 9.6.10.

Thanks,
Torsten

Re: vacuum question

2018-09-30 Thread Stephen Frost

Greetings,

* Torsten Förtsch (tfoertsch...@gmail.com) wrote:
> I have a table with a really small number of rows, usually about 1500,
> sometimes may be up to 5000. The usage pattern of that table is such that
> rows are inserted and kept for a while, mostly seconds or minutes but
> theoretically up to 1 year. After that they are deleted. No updates, just
> insert, delete. The table is pretty actively written, sometimes >100
> transactions per second.

The number of live tuples is never over 5000..?  Or 5000 per
transaction, meaning the upper bound is more like 500 000?

> Although the table is frequently auto-vacuumed, its size is growing over
> time. The size of the table corresponds to the remaining number of pages in
> the autovacuum log.
> 
>  pages: 0 removed, 30069 remain, 0 skipped due to pins, 29985 skipped
> frozen
>  tuples: 136 removed, 1643 remain, 15 are dead but not yet removable
>  buffer usage: 44327 hits, 0 misses, 21 dirtied
>  avg read rate: 0.000 MB/s, avg write rate: 0.036 MB/s
>  system usage: CPU 0.06s/0.05u sec elapsed 4.59 sec
> 
> What does "29985 skipped frozen" mean? I presume these skipped buffers is
> the table bloat.

No, the pages skipped because they're frozen just means that there's
nothing for vacuum to do on those pages.

To look at bloat, use pgstattuple.

> My way to get rid of the bloat is to cluster the table. That takes less
> than half a second and the next autovacuum log then looks like this:
> 
>  pages: 0 removed, 3493 remain, 0 skipped due to pins, 0 skipped frozen
>  tuples: 0 removed, 58873 remain, 57470 are dead but not yet removable
>  buffer usage: 9425 hits, 0 misses, 0 dirtied
>  avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
>  system usage: CPU 0.02s/0.03u sec elapsed 1.01 sec
> 
> For a number of autovacuum cycles "skipped frozen" remains 0 until
> eventually:
> 
>  pages: 0 removed, 4822 remain, 0 skipped due to pins, 4433 skipped frozen
>  tuples: 227 removed, 2609 remain, 13 are dead but not yet removable
>  buffer usage: 5269 hits, 0 misses, 315 dirtied
>  avg read rate: 0.000 MB/s, avg write rate: 2.065 MB/s
>  system usage: CPU 0.03s/0.01u sec elapsed 1.19 sec
> 
> Is there anything I can do to prevent that bloat from accumulating in the
> first place?

What problem is the bloat causing?  If you can keep all of your
transactions short and autovacuum the table fast enough then the table
size should stablize.

> Another thing that seems odd is the number of dead but not removable tuples
> in the record in the middle. Sifting through the log, I can see numbers up
> to 8. One of our replicas is configured with hot_standby_feedback. Can
> that be the culprit?

Yes, if you have hot_standby_feedback on then a long running transaction
on the replica would have the same effect as on the primary- PG is
unable to mark rows as reusable until the oldest transaction in the
system is after the commit where the rows were deleted.

If you need to be able to perform long running transactions on the
primary or replica w/ hot standby feedback enabled then there's a few
other approaches which can be used but they're more complicated and
involve things which aren't MVCC-safe (which is why they work to avoid
bloat, but does mean that those long-running transactions won't see rows
that maybe they should have if they look at this table).  Consider
having two tables where you flip between them periodically and empty the
prior one and then TRUNCATE it, then flip again, etc.  The TRUNCATE will
clear out all of the bloat, but will remove rows that a long running
transaction maybe should have been able to see (and which a routine
VACUUM would have kept, just in case).

Thanks!

Stephen

signature.asc
Description: PGP signature

How to improve sql query to achieve the better plan

2018-09-30 Thread Arup Rakshit

I have the below query which is taking 1873 ms. How can I improve this?

explain analyze select
sum(coalesce(price_cents, 0)::bigint * coalesce(quantity, 0) * (1 - 
coalesce(workitems.discount, 0)/ 100)) as total_budget_cents,
sum(coalesce(price_cents, 0)::bigint * coalesce(quantity, 0) * (1 - 
coalesce(workitems.discount, 0)/ 100) + coalesce(additional_cost_cents, 0) - 
coalesce(cost_reduction_cents, 0)) as final_budget_cents,
projects.id as project_id
from
projects
left join workitems on
workitems.project_id = projects.id
where
workitems.deleted_at is null
group by
projects.id
order by
project_id asc


And explain output is:

Sort  (cost=62851.33..62856.07 rows=1897 width=35) (actual 
time=1872.867..1873.003 rows=1229 loops=1)
  Sort Key: projects.id
  Sort Method: quicksort  Memory: 145kB
  ->  HashAggregate  (cost=62719.59..62748.04 rows=1897 width=35) (actual 
time=1871.281..1872.104 rows=1229 loops=1)
Group Key: projects.id
->  Hash Right Join  (cost=159.68..45386.32 rows=364911 width=35) 
(actual time=2.226..637.936 rows=365784 loops=1)
  Hash Cond: (workitems.project_id = projects.id)
  Filter: (workitems.deleted_at IS NULL)
  Rows Removed by Filter: 257457
  ->  Seq Scan on workitems  (cost=0.00..36655.53 rows=623353 
width=43) (actual time=0.020..220.215 rows=623175 loops=1)
  ->  Hash  (cost=135.97..135.97 rows=1897 width=16) (actual 
time=2.177..2.177 rows=1897 loops=1)
Buckets: 2048  Batches: 1  Memory Usage: 105kB
->  Seq Scan on projects  (cost=0.00..135.97 rows=1897 
width=16) (actual time=0.013..1.451 rows=1897 loops=1)
Planning time: 2.775 ms
Execution time: 1873.308 ms


Projects table has the index:

Indexes:
"projects_pkey" PRIMARY KEY, btree (id)
"index_projects_on_company_id" btree (company_id)
"index_projects_on_deleted_at" btree (deleted_at)
"index_projects_on_inspector_id" btree (inspector_id)
"index_projects_on_managed_offline_by_user_id" btree 
(managed_offline_by_user_id)
"index_projects_on_project_status_id" btree (project_status_id)
"index_projects_on_shipyard_id" btree (shipyard_id)
"index_projects_on_vessel_id" btree (vessel_id)

Workitems table has the index:

Indexes:
"workitems_pkey" PRIMARY KEY, btree (id)
"index_workitems_on_company_id" btree (company_id)
"index_workitems_on_deleted_at" btree (deleted_at)
"index_workitems_on_parent_workitem_id" btree (parent_workitem_id)
"index_workitems_on_project_id" btree (project_id)
"index_workitems_on_standard_workitem_id" btree (standard_workitem_id)
"index_workitems_on_workitem_category_id" btree (workitem_category_id)


Thanks,

Arup Rakshit
a...@zeit.io

Re: How to improve sql query to achieve the better plan

2018-09-30 Thread Pavel Stehule

Hi

ne 30. 9. 2018 v 18:23 odesílatel Arup Rakshit  napsal:

> I have the below query which is taking 1873 ms. How can I improve this?
>
> explain analyze select
> sum(coalesce(price_cents, 0)::bigint * coalesce(quantity, 0) * (1 -
> coalesce(workitems.discount, 0)/ 100)) as total_budget_cents,
> sum(coalesce(price_cents, 0)::bigint * coalesce(quantity, 0) * (1 -
> coalesce(workitems.discount, 0)/ 100) + coalesce(additional_cost_cents, 0)
> - coalesce(cost_reduction_cents, 0)) as final_budget_cents,
> projects.id as project_id
> from
> projects
> left join workitems on
> workitems.project_id = projects.id
> where
> workitems.deleted_at is null
> group by
> projects.id
> order by
> project_id asc
>
>
> And explain output is:
>
> Sort  (cost=62851.33..62856.07 rows=1897 width=35) (actual
> time=1872.867..1873.003 rows=1229 loops=1)
>   Sort Key: projects.id
>   Sort Method: quicksort  Memory: 145kB
>   ->  HashAggregate  (cost=62719.59..62748.04 rows=1897 width=35) (actual
> time=1871.281..1872.104 rows=1229 loops=1)
> Group Key: projects.id
> ->  Hash Right Join  (cost=159.68..45386.32 rows=364911 width=35)
> (actual time=2.226..637.936 rows=365784 loops=1)
>   Hash Cond: (workitems.project_id = projects.id)
>   Filter: (workitems.deleted_at IS NULL)
>   Rows Removed by Filter: 257457
>   ->  Seq Scan on workitems  (cost=0.00..36655.53 rows=623353
> width=43) (actual time=0.020..220.215 rows=623175 loops=1)
>   ->  Hash  (cost=135.97..135.97 rows=1897 width=16) (actual
> time=2.177..2.177 rows=1897 loops=1)
> Buckets: 2048  Batches: 1  Memory Usage: 105kB
> ->  Seq Scan on projects  (cost=0.00..135.97 rows=1897
> width=16) (actual time=0.013..1.451 rows=1897 loops=1)
> Planning time: 2.775 ms
> Execution time: 1873.308 ms
>
>
maybe conditional index can help

CREATE INDEX ON workitems(project_id) WHERE deleted_at is null

Regards

Pavel


> Projects table has the index:
>
> Indexes:
> "projects_pkey" PRIMARY KEY, btree (id)
> "index_projects_on_company_id" btree (company_id)
> "index_projects_on_deleted_at" btree (deleted_at)
> "index_projects_on_inspector_id" btree (inspector_id)
> "index_projects_on_managed_offline_by_user_id" btree
> (managed_offline_by_user_id)
> "index_projects_on_project_status_id" btree (project_status_id)
> "index_projects_on_shipyard_id" btree (shipyard_id)
> "index_projects_on_vessel_id" btree (vessel_id)
>
> Workitems table has the index:
>
> Indexes:
> "workitems_pkey" PRIMARY KEY, btree (id)
> "index_workitems_on_company_id" btree (company_id)
> "index_workitems_on_deleted_at" btree (deleted_at)
> "index_workitems_on_parent_workitem_id" btree (parent_workitem_id)
> "index_workitems_on_project_id" btree (project_id)
> "index_workitems_on_standard_workitem_id" btree (standard_workitem_id)
> "index_workitems_on_workitem_category_id" btree (workitem_category_id)
>
>
> Thanks,
>
> Arup Rakshit
> a...@zeit.io
>
>
>
>

Re: How to improve sql query to achieve the better plan

2018-09-30 Thread Arup Rakshit

I just added it as you said, but I am getting same plan.


Sort  (cost=62842.16..62846.91 rows=1897 width=35) (actual 
time=1845.831..1845.950 rows=1229 loops=1)
  Sort Key: projects.id
  Sort Method: quicksort  Memory: 145kB
  ->  HashAggregate  (cost=62710.42..62738.88 rows=1897 width=35) (actual 
time=1844.178..1845.060 rows=1229 loops=1)
Group Key: projects.id
->  Hash Right Join  (cost=159.68..45382.09 rows=364807 width=35) 
(actual time=1.534..618.717 rows=365784 loops=1)
  Hash Cond: (workitems.project_id = projects.id)
  Filter: (workitems.deleted_at IS NULL)
  Rows Removed by Filter: 257457
  ->  Seq Scan on workitems  (cost=0.00..36653.75 rows=623175 
width=43) (actual time=0.047..213.842 rows=623175 loops=1)
  ->  Hash  (cost=135.97..135.97 rows=1897 width=16) (actual 
time=1.478..1.478 rows=1897 loops=1)
Buckets: 2048  Batches: 1  Memory Usage: 105kB
->  Seq Scan on projects  (cost=0.00..135.97 rows=1897 
width=16) (actual time=0.006..0.914 rows=1897 loops=1)
Planning time: 0.498 ms
Execution time: 1846.100 ms

——

Indexes:
"workitems_pkey" PRIMARY KEY, btree (id)
"index_workitems_on_company_id" btree (company_id)
"index_workitems_on_deleted_at" btree (deleted_at)
"index_workitems_on_parent_workitem_id" btree (parent_workitem_id)
"index_workitems_on_project_id" btree (project_id)
"index_workitems_on_standard_workitem_id" btree (standard_workitem_id)
"index_workitems_on_workitem_category_id" btree (workitem_category_id)
"patrial_index_workitems_200_1" btree (project_id) WHERE deleted_at IS NULL


Thanks,

Arup Rakshit
a...@zeit.io



> On 30-Sep-2018, at 10:15 PM, Pavel Stehule  wrote:
> 
> CREATE INDEX ON workitems(project_id) WHERE deleted_at is null

Re: How to improve sql query to achieve the better plan

2018-09-30 Thread Pavel Stehule

ne 30. 9. 2018 v 18:49 odesílatel Arup Rakshit  napsal:

> I just added it as you said, but I am getting same plan.
>
>
> Sort  (cost=62842.16..62846.91 rows=1897 width=35) (actual
> time=1845.831..1845.950 rows=1229 loops=1)
>   Sort Key: projects.id
>   Sort Method: quicksort  Memory: 145kB
>   ->  HashAggregate  (cost=62710.42..62738.88 rows=1897 width=35) (actual
> time=1844.178..1845.060 rows=1229 loops=1)
> Group Key: projects.id
> ->  Hash Right Join  (cost=159.68..45382.09 rows=364807 width=35)
> (actual time=1.534..618.717 rows=365784 loops=1)
>   Hash Cond: (workitems.project_id = projects.id)
>   Filter: (workitems.deleted_at IS NULL)
>   Rows Removed by Filter: 257457
>   ->  Seq Scan on workitems  (cost=0.00..36653.75 rows=623175
> width=43) (actual time=0.047..213.842 rows=623175 loops=1)
>   ->  Hash  (cost=135.97..135.97 rows=1897 width=16) (actual
> time=1.478..1.478 rows=1897 loops=1)
> Buckets: 2048  Batches: 1  Memory Usage: 105kB
> ->  Seq Scan on projects  (cost=0.00..135.97 rows=1897
> width=16) (actual time=0.006..0.914 rows=1897 loops=1)
> Planning time: 0.498 ms
> Execution time: 1846.100 ms
>
>
Then there is not too much what can be done better - maybe you can try
PostgreSQL 11 with paralel hash join -- it is process about 6M rows, the
time about 2 sec is good


> ——
>
> Indexes:
> "workitems_pkey" PRIMARY KEY, btree (id)
> "index_workitems_on_company_id" btree (company_id)
> "index_workitems_on_deleted_at" btree (deleted_at)
> "index_workitems_on_parent_workitem_id" btree (parent_workitem_id)
> "index_workitems_on_project_id" btree (project_id)
> "index_workitems_on_standard_workitem_id" btree (standard_workitem_id)
> "index_workitems_on_workitem_category_id" btree (workitem_category_id)
> "patrial_index_workitems_200_1" btree (project_id) WHERE deleted_at IS
> NULL
>
>
> Thanks,
>
> Arup Rakshit
> a...@zeit.io
>
>
>
> On 30-Sep-2018, at 10:15 PM, Pavel Stehule 
> wrote:
>
> CREATE INDEX ON workitems(project_id) WHERE deleted_at is null
>
>
>

Re: Why my query not using index to sort?

2018-09-30 Thread Arup Rakshit

Hi Adrian,

I am on psql (10.5, server 9.5.14).

I am still investigating the Rails side. I found a blog 
(https://schneems.com/2015/10/27/sql-in-rails-logs.html 
) , where a Rails core 
team member said that Load time is basically SQL execution time.

I also had done a little test on psql. Here I also see the explain output and 
actual execution time far apart and it matches what I see in the Rails log.

docking_dev=# explain analyze select

"vessels" .*


from

"vessels"   


where   


"vessels"."deleted_at" is null  

and "vessels"."company_id" in 
('6f56abfe-a355-44b9-b728-a642f661a8e7',

  'c82d08d7-bec1-4413-b627-63e1898c568c',   

  
'5404cd95-29d9-44c3-8ca3-261be373bf31', 


'19d5224a-ff2b-43c4-b756-4e82c29a4712', 


'6fed40b7-fdd7-4efb-a163-c2b42e6486ae', 


'a0793a69-330c-4a6b-8a55-ee6daf574371', 


'3e936613-04e6-4db0-8595-5b8ae9cc2a40', 


'059275e2-933d-4543-8eef-91a5af87849b', 


'c64a658a-6ff1-4e76-8551-dfa62383bac3', 


'ce634593-555a-4f3c-af79-d7a7cf3796fb', 


'e18c4dab-f536-46f8-9f85-8bf51dbe989e', 


'cfe6a534-b33b-4493-862e-6aa4f0b4a790', 


'58d628c1-af0d-4e64-b672-87c77b677c7b', 


'36cb8ab0-5e8a-40db-a296-5e4b5dc666f3', 


'0d15288d-c042-461e-b4cb-0b2ad47ead29', 


'e8cd498d-9da5-4d54-ac07-e4fc48c84a67',

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

2018-09-30 Thread Adrian Klaver


On 9/30/18 1:13 PM, Carl Sverre wrote:
Thanks for the initial results. Can you check that you are not using 
super permissions and are enabling row security when running the test? 
Super ignores row security.


Yeah, big oops on my part, I was running as superuser. Running as 
non-superuser resulted in the failure you see. I tried to get around 
this with no success. My suspicion is that the new row in b is not 
visible to the returning(SELECT) query in a until after the transaction 
completes. Someone with more knowledge on this then I will have to 
confirm/deny my suspicion.





Also yes, I forgot to add the policy names, sorry about that.
On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG) 
mailto:clavadetsc...@swisspug.org>> wrote:


Hello


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Why my query not using index to sort?

2018-09-30 Thread Adrian Klaver


On 9/30/18 1:21 PM, Arup Rakshit wrote:

Hi Adrian,

I am on psql (10.5, server 9.5.14).

I am still investigating the Rails side. I found a blog 
(https://schneems.com/2015/10/27/sql-in-rails-logs.html) , where a Rails 
core team member said that Load time is basically SQL execution time.


From what I read he said it is the SQL call, with no indication of 
whether that includes the the ORM/framwork overhead to make the call or not.




I also had done a little test on psql. Here I also see the explain 
output and actual execution time far apart and it matches what I see in 
the Rails log.


No it does not. In a previous post you said:

"It takes now between 1.7 to 1.9 ms in production."

Which was confirmed by the information here:

https://gist.github.com/aruprakshit/a6bd7ca221c9a13cd583e0827aa24ad6

Below you show 5.046 ms and then 14.377 ms. The second number is not 
really relevant as it includes the time to write out to a file, so is 
different operation all together.




docking_dev=# explain analyze select
 
                                                             "vessels" 
.*  
 
                                                   from  
 
 
                                       "vessels" 
 
 
                           where 
 
 
               "vessels"."deleted_at" is null
 
 
   and "vessels"."company_id" in 
('6f56abfe-a355-44b9-b728-a642f661a8e7',
 
                               'c82d08d7-bec1-4413-b627-63e1898c568c',   
 
 
                   '5404cd95-29d9-44c3-8ca3-261be373bf31',   
 
 
       '19d5224a-ff2b-43c4-b756-4e82c29a4712',   
 
   
'6fed40b7-fdd7-4efb-a163-c2b42e6486ae', 
 
 
'a0793a69-330c-4a6b-8a55-ee6daf574371', 
 
 
'3e936613-04e6-4db0-8595-5b8ae9cc2a40', 
 
 
'059275e2-933d-4543-8eef-91a5af87849b', 
 
 
'c64a658a-6ff1-4e76-8551-dfa62383bac3', 
 
 
'ce634593-555a-4f3c-af79-d7a7cf3796fb', 
 
 
'e18c4dab-f536-46f8-9f85-8bf51dbe989e', 
 
 
'cfe6a534-b33b-4493-862e-6aa4f0b4a790', 
 
 
'58d628c1-af0d-4e64-b672-87c77b677c7b', 
 
 
'36cb8ab0-5e8a-40db-a296-5e4

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

2018-09-30 Thread Charles Clavadetscher (SwissPUG)

Hello

On 30.09.2018 23:31:32, Adrian Klaver  wrote:
On 9/30/18 1:13 PM, Carl Sverre wrote:
> Thanks for the initial results. Can you check that you are not using
> super permissions and are enabling row security when running the test?
> Super ignores row security.

Yeah, big oops on my part, I was running as superuser. Running as
non-superuser resulted in the failure you see. I tried to get around
this with no success. My suspicion is that the new row in b is not
visible to the returning(SELECT) query in a until after the transaction
completes. Someone with more knowledge on this then I will have to
confirm/deny my suspicion.


>
> Also yes, I forgot to add the policy names, sorry about that.
> On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG)
> > wrote:
>
> Hello

--
Adrian Klaver
adrian.kla...@aklaver.com

[Charles] : I also made the first test as super. However I still don't get any 
errors when executing the test query as non superuser.

The user is not superuser:

testuser@charles.localhost=> SELECT CURRENT_USER;
 current_user
--
 testuser
(1 row)

testuser@charles.localhost=> \du testuser
           List of roles
 Role name | Attributes | Member of
---++---
 testuser  |            | {}

The table privileges show that RLS is enabled and that testuser has SELECT and 
INSERT privilege on both tables. This is not related to RLS but simple 
precondition for the test:

testuser@charles.localhost=> \d a
                Table "public.a"
 Column | Type | Collation | Nullable | Default
+--+---+--+-
 id     | text |           |          |
Policies (forced row security enabled):
    POLICY "a_insert" FOR INSERT
      WITH CHECK (true)
    POLICY "a_select" FOR SELECT
      USING ((EXISTS ( SELECT b.id
   FROM b
  WHERE (a.id = b.id
Triggers:
    reprotrigger BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE 
reprohandler()

testuser@charles.localhost=> \dp a
                                         Access privileges
 Schema | Name | Type  |    Access privileges    | Column privileges |          
 Policies
+--+---+-+---+--
 public | a    | table | charles=arwdDxt/charles+|                   | a_select 
(r):               +
        |      |       | testuser=ar/charles     |                   |   (u): 
(EXISTS ( SELECT b.id+
        |      |       |                         |                   |    FROM 
b                   +
        |      |       |                         |                   |   WHERE 
(a.id = b.id)))     +
        |      |       |                         |                   | a_insert 
(a):               +
        |      |       |                         |                   |   (c): 
true

testuser@charles.localhost=> \d b
                Table "public.b"
 Column | Type | Collation | Nullable | Default
+--+---+--+-
 id     | text |           |          |

testuser@charles.localhost=> \dp b
                               Access privileges
 Schema | Name | Type  |    Access privileges    | Column privileges | Policies
+--+---+-+---+--
 public | b    | table | charles=arwdDxt/charles+|                   |
        |      |       | testuser=ar/charles     |                   |

And now the test:

testuser@charles.localhost=> SELECT * FROM a;
 id

(0 rows)

testuser@charles.localhost=> SELECT * FROM b;
 id

(0 rows)

testuser@charles.localhost=> INSERT INTO a VALUES ('fails');
NOTICE:  inside trigger handler
INSERT 0 1
testuser@charles.localhost=> SELECT * FROM a;
  id
---
 fails
(1 row)

testuser@charles.localhost=> SELECT * FROM b;
  id
---
 fails
(1 row)

Version of PG:
testuser@charles.localhost=> SELECT version();
                          version

 PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit
(1 row)

Regards
Charles

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

2018-09-30 Thread Charles Clavadetscher (SwissPUG)

Hello

On 01.10.2018 05:00:02, Carl Sverre  wrote:
Thank you for the detailed report Charles. I think you may be missing the 
“returning id” clause in the insert. Can you verify it works when you use 
“returning id”? Thanks!
[Charles] : You are right:

testuser@charles.localhost=> INSERT INTO a VALUES ('fails2') RETURNING id;
NOTICE:  inside trigger handler
ERROR:  new row violates row-level security policy for table "a"

This implies that the returning_id is evaluated before the trigger executes, 
i.e. Adrian's assumption is probably correct.

Regards
Charles

On Sun, Sep 30, 2018 at 7:57 PM Charles Clavadetscher (SwissPUG) 
mailto:clavadetsc...@swisspug.org]> wrote:

Hello

On 30.09.2018 23:31:32, Adrian Klaver mailto:adrian.kla...@aklaver.com]> wrote:
On 9/30/18 1:13 PM, Carl Sverre wrote:
> Thanks for the initial results. Can you check that you are not using
> super permissions and are enabling row security when running the test?
> Super ignores row security.

Yeah, big oops on my part, I was running as superuser. Running as
non-superuser resulted in the failure you see. I tried to get around
this with no success. My suspicion is that the new row in b is not
visible to the returning(SELECT) query in a until after the transaction
completes. Someone with more knowledge on this then I will have to
confirm/deny my suspicion.


>
> Also yes, I forgot to add the policy names, sorry about that.
> On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG)
> > wrote:
>
> Hello

--
Adrian Klaver
adrian.kla...@aklaver.com [mailto:adrian.kla...@aklaver.com]

[Charles] : I also made the first test as super. However I still don't get any 
errors when executing the test query as non superuser.

The user is not superuser:

testuser@charles.localhost=> SELECT CURRENT_USER;
 current_user
--
 testuser
(1 row)

testuser@charles.localhost=> \du testuser
           List of roles
 Role name | Attributes | Member of
---++---
 testuser  |            | {}

The table privileges show that RLS is enabled and that testuser has SELECT and 
INSERT privilege on both tables. This is not related to RLS but simple 
precondition for the test:

testuser@charles.localhost=> \d a
                Table "public.a"
 Column | Type | Collation | Nullable | Default
+--+---+--+-
 id     | text |           |          |
Policies (forced row security enabled):
    POLICY "a_insert" FOR INSERT
      WITH CHECK (true)
    POLICY "a_select" FOR SELECT
      USING ((EXISTS ( SELECT b.id [http://b.id]
   FROM b
  WHERE (a.id [http://a.id] = b.id [http://b.id]
Triggers:
    reprotrigger BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE 
reprohandler()

testuser@charles.localhost=> \dp a
                                         Access privileges
 Schema | Name | Type  |    Access privileges    | Column privileges |          
 Policies
+--+---+-+---+--
 public | a    | table | charles=arwdDxt/charles+|                   | a_select 
(r):               +
        |      |       | testuser=ar/charles     |                   |   (u): 
(EXISTS ( SELECT b.id [http://b.id]+
        |      |       |                         |                   |    FROM 
b                   +
        |      |       |                         |                   |   WHERE 
(a.id [http://a.id] = b.id [http://b.id])))     +
        |      |       |                         |                   | a_insert 
(a):               +
        |      |       |                         |                   |   (c): 
true

testuser@charles.localhost=> \d b
                Table "public.b"
 Column | Type | Collation | Nullable | Default
+--+---+--+-
 id     | text |           |          |

testuser@charles.localhost=> \dp b
                               Access privileges
 Schema | Name | Type  |    Access privileges    | Column privileges | Policies
+--+---+-+---+--
 public | b    | table | charles=arwdDxt/charles+|                   |
        |      |       | testuser=ar/charles     |                   |

And now the test:

testuser@charles.localhost=> SELECT * FROM a;
 id

(0 rows)

testuser@charles.localhost=> SELECT * FROM b;
 id

(0 rows)

testuser@charles.localhost=> INSERT INTO a VALUES ('fails');
NOTICE:  inside trigger handler
INSERT 0 1
testuser@charles.localhost=> SELECT * FROM a;
  id
---
 fails
(1 row)

testuser@charles.localhost=> SELECT * FROM b;
  id
---
 fails
(1 row)

Version of PG:
testuser@charles.localhost=> SELECT version();
                          version

 PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit
(1 row)

Regards
Charles

--

Carl Sverre

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

2018-09-30 Thread Charles Clavadetscher (SwissPUG)

An addition


On 01.10.2018 05:18:15, Charles Clavadetscher (SwissPUG) 
 wrote:
Hello

On 01.10.2018 05:00:02, Carl Sverre  wrote:
Thank you for the detailed report Charles. I think you may be missing the 
“returning id” clause in the insert. Can you verify it works when you use 
“returning id”? Thanks!
[Charles] : You are right:

testuser@charles.localhost=> INSERT INTO a VALUES ('fails2') RETURNING id;
NOTICE:  inside trigger handler
ERROR:  new row violates row-level security policy for table "a"

This implies that the returning_id is evaluated before the trigger executes, 
i.e. Adrian's assumption is probably correct.

Regards
Charles
[Charles] : In the RLS documentation 
(https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html):

"To specify which rows are visible or modifiable according to a policy, an 
expression is required that returns a Boolean result. This expression will be 
evaluated for each row prior to any conditions or functions coming from the 
user's query."

I assume that the returning clause is essentially a condition or function from 
the user query or it is implemented as such. Therefore the policy is applied 
prior to it.
The most knowlegdable person on this list on the RLS topic is Stephen Frost. He 
may shed light on the matter.

Regards
Charles

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

Re: How to maintain the csv log files in pg_log directory only for past 30 days

vacuum question

Re: vacuum question

How to improve sql query to achieve the better plan

Re: How to improve sql query to achieve the better plan

Re: How to improve sql query to achieve the better plan

Re: How to improve sql query to achieve the better plan

Re: Why my query not using index to sort?

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

Re: Why my query not using index to sort?

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

14 matches

Site Navigation

Mail list logo

Footer information