Granting and Revoking Privileges

GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_writer;

GRANT SELECT (id, email) ON customers TO support;
GRANT UPDATE (status) ON orders TO ops;

GRANT ALL PRIVILEGES ON orders TO owner_role;

GRANT SELECT ON ALL TABLES    IN SCHEMA public TO app_reader;
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO app_writer;

REVOKE INSERT ON orders FROM app_writer;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

GRANT USAGE, CREATE ON SCHEMA app TO app_writer;

GRANT CONNECT, TEMP, CREATE ON DATABASE shop TO app_writer;

GRANT EXECUTE ON FUNCTION transfer(int,int,numeric) TO app_writer;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA api TO app_writer;

GRANT USAGE, SELECT, UPDATE ON SEQUENCE orders_id_seq TO app_writer;

GRANT SELECT ON customers TO support WITH GRANT OPTION;

REVOKE SELECT ON customers FROM support CASCADE;
-- also revokes from anyone support granted to

ALTER DEFAULT PRIVILEGES IN SCHEMA app
   GRANT SELECT ON TABLES TO app_reader;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
   GRANT USAGE ON SEQUENCES TO app_writer;

SELECT has_table_privilege('alice', 'orders', 'SELECT');
\dp orders
SELECT grantee, privilege_type FROM information_schema.role_table_grants
 WHERE table_name='orders';