Views Security (or insecurity)

[Abstrct]

I read an article today that basically proved, with a single temporary function, that all my row-level security in the Schemaverse is completely helpless to full information leakage. Everybody has the ability to run queries that will return them all details from the underlying table(s) regardless of if they should be included in the views results. 

Really neat article which you can read over here: http://rhaas.blogspot.com/2012/03/security-barrier-views.html

Luckily I did figure out a fix, which I discuss on this page: http://schemaverse.tumblr.com/post/19355554777/security-barrier-views-without-waiting-for-9-2

I am still working on fixing everything. It is a bit of a time consuming change but I will get through all the views over the next couple days. 

I am going to try using that site for random dev stories like this. It isn't replacing this group at all since I like the discussion we have here, this just allows me to do some nicer formatting and hopefully help other developer folk looking for help on odd technical challenges like this. 

Had anybody figured this pretty simple but powerful hack yet? Derpfish, I'm looking your way here :P

[derpfish]

Neat trick. I hadn't figured this out yet (I'm definitely still
picking up PostgreSQL's quirks as I go along, vulnerabilities and all)
though I appreciate being thought of as so devious. Although speaking
of information leakage, I did find the following leak the other day,
which possibly gives you some useful information (perhaps it's nice to
know how long your enemies' fleets scripts are running) but which I
haven't found any particular use for:

schemaverse=> select x,get_player_username(u),get_fleet_runtime(x,
get_player_username(u)) from generate_series(1,300) as x,
generate_series(2000,2100) as u where get_fleet_runtime(x,
get_player_username(u)) is not null;
...
142 | massconfusion | 00:06:00
...
(15 rows)

On Mar 15, 6:16 pm, Abstrct <j...@saucetel.com> wrote:
> I read an article today that basically proved, with a single temporary
> function, that all my row-level security in the Schemaverse is completely
> helpless to full information leakage. Everybody has the ability to run
> queries that will return them all details from the underlying table(s)
> regardless of if they should be included in the views results.
>
> Really neat article which you can read over
> here:http://rhaas.blogspot.com/2012/03/security-barrier-views.html
>
> Luckily I did figure out a fix, which I discuss on this

> page:http://schemaverse.tumblr.com/post/19355554777/security-barrier-views...

[Christopher Browne]

On Thursday, 15 March 2012 21:16:06 UTC-4, Abstrct wrote:

I read an article today that basically proved, with a single temporary function, that all my row-level security in the Schemaverse is completely helpless to full information leakage. Everybody has the ability to run queries that will return them all details from the underlying table(s) regardless of if they should be included in the views results. 

Really neat article which you can read over here: http://rhaas.blogspot.com/2012/03/security-barrier-views.html

Luckily I did figure out a fix, which I discuss on this page: http://schemaverse.tumblr.com/post/19355554777/security-barrier-views-without-waiting-for-9-2

I am still working on fixing everything. It is a bit of a time consuming change but I will get through all the views over the next couple days.

I think I need to give a firm "thumbs down" on this change, as it effectively destroys the capability to use indexes when accessing data about your fleet.

The set-returning function essentially turns every user query against ships into a Seq Scan against their set of ships.  So when you query your ships, you're processing all of them, every time.  That seems like something about as horrible as the security problem.

Furthermore, it's plausible to treat this leakage as "not a bug, but a feature."  You do have a trophy category concerning getting in an SQL injection...

I much, much prefer the "security barrier" answer; I suggest that you hold off fixing this bug until 9.2, and implement my_ships as more like:

   create or replace view my_ships with (security_barrier) as select s.* from ship s, my_player m where m.id = s.player_id;

Hermetically sealing everything behind functions wasn't what we wanted, was it?

[derpfish]

If putting everything behind functions kills the performance (and I
would believe that it does) then that's probably not a great solution.
I think this is an excellent work around for my_fleets since privacy
of fleet scripts is critical and I don't do any joining/querying
against my_fleets in my fleet script anyway. For everything else
though, probably the best thing to do is wait for a 9.2 release (the
9.2 beta was originally tentatively scheduled for April;
http://www.postgresql.org/about/news/1319/). If we/you are feeling
impatient you could always try giving a dev snapshot a whirl (
http://www.postgresql.org/download/snapshots/ ); this game is all
about trying to break things anyway, right? ; )

On Mar 16, 4:07 pm, Christopher Browne <cbbro...@gmail.com> wrote:
> On Thursday, 15 March 2012 21:16:06 UTC-4, Abstrct wrote:
>
> > I read an article today that basically proved, with a single temporary
> > function, that all my row-level security in the Schemaverse is completely
> > helpless to full information leakage. Everybody has the ability to run
> > queries that will return them all details from the underlying table(s)
> > regardless of if they should be included in the views results.
>
> > Really neat article which you can read over here:
> >http://rhaas.blogspot.com/2012/03/security-barrier-views.html
>
> > Luckily I did figure out a fix, which I discuss on this page:

> >http://schemaverse.tumblr.com/post/19355554777/security-barrier-views...

> > I am still working on fixing everything. It is a bit of a time consuming
> > change but I will get through all the views over the next couple days.
>
> I think I need to give a firm "thumbs down" on this change, as it
> effectively destroys the capability to use indexes when accessing data
> about your fleet.
>

> The set-returning function essentially turns *every* user query against

> ships into a Seq Scan against their set of ships.  So when you query your

> ships, you're processing *all* of them, every time.  That seems like

[Abstrct]

Alright, I admit, my mild obsession with security got the best of me here. I didn't even think about the index issues but you are definitely right here. Thanks for calling me out on this. 

I am definitely going to keep the new method on for the fleets because I still think those should be safe from prying eyes and I cannot imagine this will cause a huge performance bottle-neck. Their only index is likely player_id anyways so that will still be used. I may also try it out on the my_players and public_variables view as well but we will see how that goes. Everything else will wait for 9.2. 

So, until then, enjoy the hack on ally he ship and planet tables :P

As for Derpfish's suggestion of running the current dev build, this would be awesome but I just don't have the time unfortunately. It took me like four months just to upgrade to 9.1 :( Maybe one day when the game starts to become more stable I can invest more time in administrative tasks.

 

-Abstrct

[Christopher Browne]

On Tuesday, 20 March 2012 13:45:30 UTC-4, Abstrct wrote:

Alright, I admit, my mild obsession with security got the best of me here. I didn't even think about the index issues but you are definitely right here. Thanks for calling me out on this. 

I am definitely going to keep the new method on for the fleets because I still think those should be safe from prying eyes and I cannot imagine this will cause a huge performance bottle-neck. Their only index is likely player_id anyways so that will still be used. I may also try it out on the my_players and public_variables view as well but we will see how that goes. Everything else will wait for 9.2. 

I put a bit of instrumentation into my "AI" script, and this piece is the one hyper-expensive part.

I got an *ENORMOUS* performance improvement by starting my function with a single scan through my_ships:

 --- Load ship data into a temporary table as we wind up using this data *heavily*
drop table if exists my_ship_data;
create temp table my_ship_data (
 id                integer,
 fleet_id          integer           ,
 player_id         integer           ,
 name              character varying ,
 last_action_tic   integer           ,
 last_move_tic     integer           ,
 last_living_tic   integer           ,
 current_health    integer           ,
 max_health        integer           ,
 current_fuel      integer           ,
 max_fuel          integer           ,
 max_speed         integer           ,
 range             integer           ,
 attack            integer           ,
 defense           integer           ,
 engineering       integer           ,
 prospecting       integer           ,
 location_x        integer           ,
 location_y        integer           ,
 direction         integer           ,
 speed             integer           ,
 destination_x     integer           ,
 destination_y     integer           ,
 repair_priority   integer           ,
 action            text     ,
 action_target_id  integer           ,
 location          point             ,
 destination       point             ,
 target_speed      integer           ,
 target_direction  integer);

insert into my_ship_data (id , fleet_id , player_id , name ,
 last_action_tic , last_move_tic , last_living_tic , current_health ,
 max_health , current_fuel , max_fuel , max_speed , range , attack ,
 defense , engineering , prospecting , location_x , location_y ,
 direction , speed , destination_x , destination_y , repair_priority ,
 action , action_target_id , location , destination , target_speed ,
 target_direction)
select id , fleet_id , player_id , name ,
 last_action_tic , last_move_tic , last_living_tic , current_health ,
 max_health , current_fuel , max_fuel , max_speed , range , attack ,
 defense , engineering , prospecting , location_x , location_y ,
 direction , speed , destination_x , destination_y , repair_priority ,
 action , action_target_id , location , destination , target_speed ,
 target_direction from my_ships;

create index msd_id on my_ship_data(id);
create index msd_fleet on my_ship_data(fleet_id);
create index msd_loc on my_ship_data using gist(location);
create index msd_dest on my_ship_data using gist(destination);

I'll observe that this call to my_ships typically takes 15-20 seconds, whereas the other steps tend to take fractions of a second apiece.

Here's typical, in production:

schem...@db.schemaverse.com->  explain analyze select * from my_ships;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=5517.19..5897.33 rows=762 width=188) (actual time=16290.601..16977.745 rows=108 loops=1)
   Hash Cond: (ship_control.ship_id = ship.id)
   ->  Seq Scan on ship_control  (cost=0.00..320.83 rows=13783 width=83) (actual time=0.038..678.540 rows=13761 loops=1)
   ->  Hash  (cost=5507.66..5507.66 rows=762 width=109) (actual time=16290.435..16290.435 rows=108 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 13kB
         ->  Seq Scan on ship  (cost=0.00..5507.66 rows=762 width=109) (actual time=12914.614..16289.944 rows=108 loops=1)
               Filter: ((NOT destroyed) AND (player_id = get_player_id("session_user"())))
 Total runtime: 16977.971 ms
(8 rows)

[MassConfusion]

I'm doing something similar here but using a RECORD variable.
I'm not sure if players other than 'schemaverse' can actually create
temporary tables though, I could be completely wrong (I often am) but
I *THINK* I tried this back in the earlier days when I was playing and
that's why I ended up just going with RECORD

> schemave...@db.schemaverse.com->  explain analyze select * from my_ships;

[Christopher Browne]

On Thursday, 22 March 2012 16:39:08 UTC-4, MassConfusion wrote:

I'm not sure if players other than 'schemaverse' can actually create
temporary tables though

I'm sure...

 schem...@db.schemaverse.com->  select fleet_script_231();                                                                                                                                                                                                                   NOTICE:  Starting up @ 2012-03-22 23:07:16.810929+00
NOTICE:  table "my_ship_data" does not exist, skipping
CONTEXT:  SQL statement "drop table if exists my_ship_data"
PL/pgSQL function "fleet_script_231" line 26 at SQL statement
NOTICE:  Prep of my_ship_data took [00:00:19.845537] (@2012-03-22 23:07:36.656523+00)
... secret stuff omitted ... :-)

[Christopher Browne]

What's sad in this game is that after building ~100 ships, I'm steadily penniless.

What's quite counter-intuitive is that capturing planets doesn't forcibly help; that requires, first, that I spend a bunch of money on prospectors, and it takes quite a long time for them to pay for themselves.  :-(

It makes for much more interesting trade-offs than were the case when I seemed to have a boatload of money.

Evidently a workable fleet requires a LOT more prospectors than was obvious.

Also, it looks like getting a ship across the galaxy is a remarkably expensive endeavor.  That costs more than *several* prospectors, and doesn't offer any rapid return on investment.  Which seems somehow surprising.

[Christopher Browne]

On Thursday, 22 March 2012 15:52:44 UTC-4, Christopher Browne wrote:

I put a bit of instrumentation into my "AI" script, and this piece is the one hyper-expensive part.

I got an *ENORMOUS* performance improvement by starting my function with a single scan through my_ships

Instrumentation of these scripts is HUGELY useful.

Despite having dramatically increased the amount of work my script is doing, it keeps getting *faster*, the sole bottleneck being the single scan through my_ships.

In effect, all the other pieces wind up, combined, running in a fraction of a second.  Sadly, as the game goes onwards, scans of my_ships get longer and longer :-(

BTW, I have had to give up on writing anything scanning data from my_events - that winds up being way too slow.  I'm not quite sure what's wrong there.

Well, actually, I think I do...

schem...@db.schemaverse.com->  explain  select * from my_events order by id desc limit 200;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.01..3717.75 rows=200 width=98)
   InitPlan 1 (returns $0)
     ->  Seq Scan on tic_seq  (cost=0.00..1.01 rows=1 width=8)
   ->  Index Scan Backward using event_pkey on event  (cost=0.00..3071587.46 rows=165284 width=98)
         Filter: ((tic < $0) AND ((get_player_id("session_user"()) = player_id_1) OR (get_player_id("session_user"()) = player_id_2) OR public))
(5 rows)

This is unable to make use of indexes on player IDs due to the OR clauses leaving there with several choices for visibility for each tuple.

Several possibilities for improvement to this:
a) Accept that you'll need to record events for both players; events involving 2 players will need to be recorded twice.  That eliminates one OR.
b) Separate "public" events to a separate table.  That eliminates the other OR.

I think that, as a user, I'd rather that this leads to two views:
a) public_events, listing the public bits
b) my_events, listing things that are private.

If the schemas are sufficiently compatible, I can meld them together if I want via a UNION/UNION ALL.

[Abstrct]

Upgrading prospecting make a drastic difference in how much you will gain from that planet. Also keep in mind, if you get to a planet that nobody else in mining, it only takes one mine action to conquer that planet. So, while it may be expensive to send a fleet of ships to a planet, you only really need to send one and then, once you conquer the planet, build up the mining fleet right on that planet.

 

Prospecting is definitely a large aspect of the game since there is no other way to make money. I have considered countless times what else to add to slightly diversify gameplay but I haven't come up with anything that actually would make it more interesting yet (at least without making the game too complicated). 

[Christopher Browne]

On Monday, 26 March 2012 14:37:28 UTC-4, Abstrct wrote:

Upgrading prospecting make a drastic difference in how much you will gain from that planet. Also keep in mind, if you get to a planet that nobody else in mining, it only takes one mine action to conquer that planet. So, while it may be expensive to send a fleet of ships to a planet, you only really need to send one and then, once you conquer the planet, build up the mining fleet right on that planet.

 

Prospecting is definitely a large aspect of the game since there is no other way to make money. I have considered countless times what else to add to slightly diversify gameplay but I haven't come up with anything that actually would make it more interesting yet (at least without making the game too complicated). 

I suppose I didn't entirely "get" the economics of it, initially.  You need a bunch of miners in order to meaningfully support one ship that's out "on the prowl."  During the last game, I had about a 1:1 ratio of miners:scouts, and that left my scouts extremely starved for resources, and little ability to grow the fleet, to boot.  I did "get" the part about only needing to send one.

Ah, and I now see a mistake I was making in terms of the size of the fleet of prospectors.  The "max" on extraction is that each planet can only support a certain number of prospectors.  D'oh.