DROP TABLE cat CASCADE;
DROP TABLE owner CASCADE;
DROP TABLE cat_owner CASCADE;

CREATE TABLE cat
(
	id serial,
	name text,
	constraint cat_pk primary key (id)
);
INSERT INTO cat(name) VALUES('Muffin');
INSERT INTO cat(name) VALUES('Mittens');
INSERT INTO cat(name) VALUES('Tabitha');

CREATE TABLE owner
(
	id serial,
	name text,
	constraint owner_pk primary key (id)
);
INSERT INTO owner(name) VALUES('Bob');
INSERT INTO owner(name) VALUES('Agnes');

CREATE TABLE cat_owner
(
	id serial,
	cat_id integer,
	owner_id integer,
	constraint cat_owner_pk primary key (id),
	foreign key (cat_id) references cat(id),
	foreign key (owner_id) references owner(id)
);
INSERT INTO cat_owner(cat_id, owner_id) VALUES((SELECT id FROM cat WHERE name = 'Muffin'),(SELECT id FROM owner WHERE name = 'Bob'));
INSERT INTO cat_owner(cat_id, owner_id) VALUES((SELECT id FROM cat WHERE name = 'Mittens'),(SELECT id FROM owner WHERE name = 'Agnes'));
INSERT INTO cat_owner(cat_id, owner_id) VALUES((SELECT id FROM cat WHERE name = 'Tabitha'),(SELECT id FROM owner WHERE name = 'Agnes'));

GRANT SELECT ON cat TO cats_user;
GRANT SELECT ON owner TO cats_user;
GRANT SELECT ON cat_owner TO cats_user;
