New SQL to pull from pwalk-populated table

[bmcgough]

select find_owner(filename) as owner,
   filename,
   to_timestamp(atime) as atime,
   to_timestamp(mtime) as mtime,
   to_timestamp(ctime) as ctime,
   st_size/1024.0/1024.0/1024.0 as size
from file_metadata
where
   st_size > 3.0*1024*1024*1024
   and (
     mtime > (extract(epoch from now()) - 604800)
     or
     ctime > (extract(epoch from now()) - 604800)
   )

If you want epoch time omit the 'to_timestamp' and 'as' and clause - "to_timestamp(atime) as atime" becomes "atime".

Or you can omit any fields you do not are about.

[dirkpetersen]

I tried that as ro user and am getting this error message:

root@lf3:~# vi meta.sql root@lf3:~# psql < meta.sql ERROR: permission denied for relation folder_owners CONTEXT: SQL statement "SELECT fo.owner FROM folder_owners fo WHERE fo.path = trimmed_path" PL/pgSQL function find_owner(text) line 12 at SQL statement root@lf3:~#

[bmcgough]

Two issues

First, I didn't mark the SQL query as code in markdown, so it got corrupted; if you copy and pasted from github in a browser, the query is wrong, but valid SQL so it will not error. I have fixed this, so please check that your query matches the one in the issue now.

Second, I hadn't granted access to the folder owners table to the storcrawl_ro user. This is one of the reasons I used a schema before (and we may want to again); you can grant access to the entire schema to a user, and it will be inherited by existing and newly created tables. I don't think granting access to the public schema to another user is the best idea as that also grants write access to some places in that schema. The scripts are granting access to the folder owners table when it is created now.

[jmkatcher]

Added new table to lfbackup replacing old one, changed parameters to not accept passed sql table name and updated documentation accordingly.