-- Drop user
DROP USER bob;
-- Drop privileges
DROP OWNED BY bob;


-- Create a group
CREATE ROLE readonly;

-- Create a final user with password
CREATE USER bob WITH ENCRYPTED PASSWORD 'secret';
GRANT readonly TO bob;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- repeat code below for each database:
GRANT CONNECT ON DATABASE db_name to readonly;
\c db_name

--- this grants privileges on new tables generated in new database "db_name"
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO readonly;

GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;