search

mccgr / edgar

Code to manage data related to SEC EDGAR
31 stars 15 forks source link

Check permissions #65

Closed iangow closed 4 years ago

iangow commented 4 years ago

For tables cusip_cik and filing_details_13d in particular.

bdcallen commented 4 years ago

@iangow 

crsp=> SELECT * FROM pg_tables
WHERE tablename IN ('cusip_cik', 'cusip_cik_old',
'filing_details_13d');
 schemaname |     tablename      | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+--------------------+------------+------------+------------+----------+-------------+-------------
 edgar      | filing_details_13d | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | cusip_cik          | bdcallen   |            | f          | f        | f           | f
 edgar      | cusip_cik_old      | edgar      | big_hdd    | f          | f        | f           | f
 filings    | cusip_cik          | igow       | big_hdd    | t          | f        | f           | f
(4 rows)

crsp=> SELECT table_schema, table_name, grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name IN ('cusip_cik', 'cusip_cik_old',
'filing_details_13d');
 table_schema |     table_name     |   grantee    | privilege_type 
--------------+--------------------+--------------+----------------
 edgar        | filing_details_13d | activism     | SELECT
 edgar        | filing_details_13d | crsp_basic   | SELECT
 edgar        | cusip_cik          | bdcallen     | INSERT
 edgar        | cusip_cik          | bdcallen     | SELECT
 edgar        | cusip_cik          | bdcallen     | UPDATE
 edgar        | cusip_cik          | bdcallen     | DELETE
 edgar        | cusip_cik          | bdcallen     | TRUNCATE
 edgar        | cusip_cik          | bdcallen     | REFERENCES
 edgar        | cusip_cik          | bdcallen     | TRIGGER
 edgar        | cusip_cik_old      | edgar        | INSERT
 edgar        | cusip_cik_old      | edgar        | SELECT
 edgar        | cusip_cik_old      | edgar        | UPDATE
 edgar        | cusip_cik_old      | edgar        | DELETE
 edgar        | cusip_cik_old      | edgar        | TRUNCATE
 edgar        | cusip_cik_old      | edgar        | REFERENCES
 edgar        | cusip_cik_old      | edgar        | TRIGGER
 edgar        | filing_details_13d | edgar        | INSERT
 edgar        | filing_details_13d | edgar        | SELECT
 edgar        | filing_details_13d | edgar        | UPDATE
 edgar        | filing_details_13d | edgar        | DELETE
 edgar        | filing_details_13d | edgar        | TRUNCATE
 edgar        | filing_details_13d | edgar        | REFERENCES
 edgar        | filing_details_13d | edgar        | TRIGGER
 edgar        | cusip_cik_old      | edgar_access | SELECT
 filings      | cusip_cik          | filings      | SELECT
 edgar        | filing_details_13d | filings      | SELECT
(26 rows)

It seems I need to set access and ownership for the new cusip_cik, and set access for filing_details_13d.

bdcallen commented 4 years ago

@iangow 

crsp=> ALTER TABLE edgar.cusip_cik OWNER TO edgar;
ALTER TABLE
crsp=> GRANT SELECT ON TABLE edgar.cusip_cik TO edgar_access;
GRANT
crsp=> GRANT SELECT ON TABLE edgar.filing_details_13d TO edgar_access;
GRANT

Just did this now

bdcallen commented 4 years ago

@iangow Seems all the tables in edgar, with the exception of a couple of minor test tables done by me (which I should probably delete), are owned by edgar

crsp-> WHERE schemaname = 'edgar';
 schemaname |          tablename           | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+------------------------------+------------+------------+------------+----------+-------------+-------------
 edgar      | cusip_cik_old                | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | filing_docs_processed        | edgar      | big_hdd    | t          | f        | f           | f
 edgar      | filing_docs_alt              | edgar      | big_hdd    | t          | f        | f           | f
 edgar      | filer_ciks                   | edgar      | big_hdd    | t          | f        | f           | f
 edgar      | filing_details_13d_errors    | edgar      | big_hdd    | t          | f        | f           | f
 edgar      | filing_docs                  | edgar      | big_hdd    | t          | f        | f           | f
 edgar      | forms345_footnote_indices    | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | filing_docs_processed_alt    | edgar      | big_hdd    | t          | f        | f           | f
 edgar      | forms345_footnotes           | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | filing_docs_test             | bdcallen   | big_hdd    | f          | f        | f           | f
 edgar      | accession_numbers            | edgar      | big_hdd    | t          | f        | f           | f
 edgar      | forms345_reporting_owners    | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | forms345_signatures          | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | forms345_xml_process_table   | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | forms345_table1              | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | cusip_cik                    | edgar      |            | f          | f        | f           | f
 edgar      | forms345_table2              | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | forms345_xml_fully_processed | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | item_no                      | edgar      | big_hdd    | t          | f        | f           | f
 edgar      | server_log_processed         | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | problems                     | bdcallen   | big_hdd    | t          | f        | f           | f
 edgar      | forms345_header              | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | item_no_desc                 | edgar      |            | f          | f        | f           | f
 edgar      | filings                      | edgar      | big_hdd    | t          | f        | f           | f
 edgar      | index_last_modified          | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | filing_details_13d           | edgar      | big_hdd    | f          | f        | f           | f
 edgar      | server_log                   | edgar      | big_hdd    | t          | f        | f           | f
(27 rows)
bdcallen commented 4 years ago

@iangow Just did this

crsp=> SELECT table_schema, table_name, grantee, privilege_type 
crsp-> FROM information_schema.role_table_grants
crsp-> WHERE grantee = 'edgar_access';
 table_schema |          table_name          |   grantee    | privilege_type 
--------------+------------------------------+--------------+----------------
 edgar        | cusip_cik_old                | edgar_access | SELECT
 edgar        | filing_docs_processed        | edgar_access | SELECT
 edgar        | filing_docs_alt              | edgar_access | SELECT
 edgar        | filer_ciks                   | edgar_access | SELECT
 edgar        | filing_details_13d_errors    | edgar_access | SELECT
 edgar        | filing_docs                  | edgar_access | SELECT
 edgar        | forms345_footnote_indices    | edgar_access | SELECT
 edgar        | filing_docs_processed_alt    | edgar_access | SELECT
 edgar        | forms345_footnotes           | edgar_access | SELECT
 edgar        | accession_numbers            | edgar_access | SELECT
 edgar        | forms345_reporting_owners    | edgar_access | SELECT
 edgar        | forms345_signatures          | edgar_access | SELECT
 edgar        | forms345_xml_process_table   | edgar_access | SELECT
 edgar        | forms345_table1              | edgar_access | SELECT
 edgar        | cusip_cik                    | edgar_access | SELECT
 edgar        | forms345_table2              | edgar_access | SELECT
 edgar        | forms345_xml_fully_processed | edgar_access | SELECT
 edgar        | item_no                      | edgar_access | SELECT
 edgar        | server_log_processed         | edgar_access | SELECT
 edgar        | forms345_header              | edgar_access | SELECT
 edgar        | item_no_desc                 | edgar_access | SELECT
 edgar        | filings                      | edgar_access | SELECT
 edgar        | index_last_modified          | edgar_access | SELECT
 edgar        | filing_details_13d           | edgar_access | SELECT
 edgar        | server_log                   | edgar_access | SELECT
 public       | filer_ciks                   | edgar_access | SELECT
(26 rows)

Having looked at the set of tables in the edgar schema, the only two missing from here are the two test tables that I own (problems and filing_docs_test). Curiously, there is a public.filer_ciks

iangow commented 4 years ago

Assuming it was some old created in development, I dropped the public.filer_ciks table:

crsp=# DROP TABLE public.filer_ciks ;
DROP TABLE
crsp=# DROP TABLE public.filings_new ;
DROP TABLE
bdcallen commented 4 years ago

@iangow Can we close this? Seems this has been handled as the ownership and access for cusip_cik and filing_details_13d has been fixed.

iangow commented 4 years ago

I think so. Maybe look into the ones with bdcallen below.

crsp=# \dt+ edgar.
                                                                 List of relations
 Schema |             Name             | Type  |  Owner   |    Size    |                                Description                                
--------+------------------------------+-------+----------+------------+---------------------------------------------------------------------------
 edgar  | accession_numbers            | table | edgar    | 1825 MB    | 
 edgar  | cusip_cik                    | table | edgar    | 162 MB     | 
 edgar  | cusip_cik_old                | table | edgar    | 294 MB     | 
 edgar  | filer_ciks                   | table | edgar    | 36 MB      | 
 edgar  | filing_details_13d           | table | edgar    | 44 MB      | 
 edgar  | filing_details_13d_errors    | table | edgar    | 16 kB      | 
 edgar  | filing_docs                  | table | edgar    | 10167 MB   | 
 edgar  | filing_docs_alt              | table | edgar    | 280 kB     | 
 edgar  | filing_docs_processed        | table | edgar    | 290 MB     | 
 edgar  | filing_docs_processed_alt    | table | edgar    | 96 kB      | 
 edgar  | filing_docs_test             | table | bdcallen | 1144 kB    | 
 edgar  | filings                      | table | edgar    | 2925 MB    | CREATED USING get_filings.R IN iangow-public/edgar ON 2019-12-18 21:02:27
 edgar  | forms345_footnote_indices    | table | edgar    | 4133 MB    | Created/Updated by process_345_xml_documents.R on 2019-12-19 00:03:48
 edgar  | forms345_footnotes           | table | edgar    | 5408 MB    | Created/Updated by process_345_xml_documents.R on 2019-12-19 00:03:48
 edgar  | forms345_header              | table | edgar    | 1281 MB    | Created/Updated by process_345_xml_documents.R on 2019-12-19 00:03:48
 edgar  | forms345_reporting_owners    | table | edgar    | 1919 MB    | Created/Updated by process_345_xml_documents.R on 2019-12-19 00:03:48
 edgar  | forms345_signatures          | table | edgar    | 1323 MB    | Created/Updated by process_345_xml_documents.R on 2019-12-19 00:03:48
 edgar  | forms345_table1              | table | edgar    | 3364 MB    | Created/Updated by process_345_xml_documents.R on 2019-12-19 00:03:48
 edgar  | forms345_table2              | table | edgar    | 1941 MB    | Created/Updated by process_345_xml_documents.R on 2019-12-19 00:03:48
 edgar  | forms345_xml_fully_processed | table | edgar    | 675 MB     | Created/Updated by process_345_xml_documents.R on 2019-12-19 00:03:48
 edgar  | forms345_xml_process_table   | table | edgar    | 1421 MB    | Created/Updated by process_345_xml_documents.R on 2019-12-19 00:03:48
 edgar  | index_last_modified          | table | edgar    | 8192 bytes | 
 edgar  | item_no                      | table | edgar    | 285 MB     | 
 edgar  | item_no_desc                 | table | edgar    | 16 kB      | 
 edgar  | problems                     | table | bdcallen | 16 MB      | 
 edgar  | server_log                   | table | edgar    | 61 GB      | 
 edgar  | server_log_processed         | table | edgar    | 568 kB     | 
(27 rows)