Do we need to fix this before the Nov. run?

Depend on how scared you are of anyone running "DROP DATABASE OPDB;". I am actually more scared of "DROP DATABASE ARCHIVER;"

We replicate opdb to Hilo and Princeton, but not the archiver. We were planning to have a summit backup machine for replication, but that has not yet happened.

I think this needs to be done before the next run. If I understand correctly, currently everyone has superuser access. So anyone could inadvertently modify the database contents.

OK, I will think about a plan to change permissions (consulting with experts). BTW, we replicated the entire database cluster at summit to Hilo, so is `archiver` I guess.

I'm thinking about the following SQL commands:

• CREATE USER admin WITH PASSWORD 'usual password' Superuser Createrole CreateDB Replication;
• CREATE USER public_user WITH PASSWORD 'public user password';
• ALTER ROLE pfs WITH NOSUPERUSER NOCREATEDB  NOCREATEROLE NOREPLICATION;
• admin@opdb (repeat followings for archiver)
  • REASSIGN OWNED BY pfs TO admin;
  • REVOKE ALL ON SCHEMA public FROM pfs;
  • REVOKE ALL ON ALL TABLES IN SCHEMA public FROM pfs;
  • GRANT USAGE ON SCHEMA public TO pfs;
  • 
    GRANT USAGE ON ALL SEQUENCES IN SCHEMA public to pfs;
  • GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO pfs;
  • ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT ON TABLES TO pfs;
  • 
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO pfs;
  • REVOKE ALL ON SCHEMA public FROM public_user;
  • REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public_user;
  • GRANT USAGE ON SCHEMA public TO public_user;
  • GRANT SELECT ON ALL TABLES IN SCHEMA public TO public_user;
  • ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO public_user;
  • ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO public_user;

These seem to work fine in my test environment, but perhaps not in the real environment. So, any comments are very welcome. Maybe there exists a smarter way...

Notes:

• `public` is a reserved word so will use `public_user` for a public user
• Do we need `UPDATE` for `pfs`?
• Probably we need to change this monitoring `opDB` & `archiver` access. Maybe downtime?

Planned commands are something like this:

[done] CREATE USER admin WITH PASSWORD 'usual password' Superuser Createrole CreateDB Replication;
[done] CREATE USER public_user WITH PASSWORD 'ask me';

(admin@archiver)
ALTER USER pfs WITH NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;

REASSIGN OWNED BY pfs TO admin;

GRANT USAGE ON SCHEMA public TO pfs;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO pfs; 
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO pfs;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT ON TABLES TO pfs;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO pfs;

(admin@opdb)
GRANT USAGE ON SCHEMA public TO pfs;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO pfs; 
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO pfs;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT ON TABLES TO pfs;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO pfs;

GRANT USAGE ON SCHEMA public TO public_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO public_user; 
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO public_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO public_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO public_user;
 

Am happy trying the archiver changes at JHU.

Wait, typo?

We do not want to grant anything but SELECT to public_user, so not:

GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO public_user;

FYI the REASSIGN OWNED BY pfs TO admin; command takes longer than you might guess. Several minutes at JHU. Worked fine in the end. I did swap the order of the ALTER USER pfs and REASSIGN OWNED BY commands since I was slightly worried about cutting my legs off at the knees.

After removing a blocking process, the following SQL commands to change permissions have been sent. Indeed, I did not use `REASSIGN OWNED` but made SQL commands to change for each table detected by the following command:

SELECT    'ALTER TABLE ' || schemaname    || '.' || tablename ||    ' OWNER TO admin;'FROM pg_tables WHERE tableowner ='pfs';

change_opdb.sql

change_archiver.sql

I just realized that `obslog` needed to delete records of some tables. I just add the following permissions for `obslog_*_note` tables.

BEGIN; 
GRANT UPDATE, DELETE ON obslog_visit_note TO pfs; 
GRANT UPDATE, DELETE ON obslog_mcs_exposure_note TO pfs; 
GRANT UPDATE, DELETE ON obslog_visit_set_note TO pfs; 
COMMIT;

So far, I don't see (or hear) any problems caused by the change, so I close this ticket. File a new one, if we get some troubles.

(Just a record) I also added the followings:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO pfs;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO pfs;