Gist to test postgres permissions so multiple apps can connect and use the same DB in a namespaced way

permissions.sql

--- cleanup
drop database test;
drop user piston;
drop user crankshaft; 
drop user identified;

--- setup
create database test;

\c test

--- Application
create user identified;
alter schema public owner to identified;




--- Setup Piston Namespace
create user piston inherit login in group identified;
create schema piston authorization piston;
ALTER USER piston SET search_path TO piston,crankshaft,public;

-- basic schema usage
GRANT usage ON SCHEMA piston TO group identified;

-- access for CURRENT tables/sequences/functions 
GRANT all ON ALL TABLES    IN SCHEMA piston TO GROUP identified;
GRANT all ON ALL SEQUENCES IN SCHEMA piston TO GROUP identified;
GRANT all ON ALL FUNCTIONS IN SCHEMA piston TO GROUP identified;

-- access for FUTURE tables/sequences/functions
ALTER DEFAULT PRIVILEGES FOR ROLE piston IN SCHEMA piston GRANT all ON TABLES    TO GROUP identified;
ALTER DEFAULT PRIVILEGES FOR ROLE piston IN SCHEMA piston GRANT all ON SEQUENCES TO GROUP identified;
ALTER DEFAULT PRIVILEGES FOR ROLE piston IN SCHEMA piston GRANT all ON FUNCTIONS TO GROUP identified;






--- Setup Crankshaft Namespace
create user crankshaft inherit login in group identified;
create schema crankshaft authorization crankshaft;
ALTER USER crankshaft SET search_path TO crankshaft,piston,public;

-- basic schema usage
GRANT usage ON SCHEMA crankshaft TO group identified;

-- access for CURRENT tables/sequences/functions 
GRANT all ON ALL TABLES    IN SCHEMA crankshaft TO GROUP identified;
GRANT all ON ALL SEQUENCES IN SCHEMA crankshaft TO GROUP identified;
GRANT all ON ALL FUNCTIONS IN SCHEMA crankshaft TO GROUP identified;

-- access for FUTURE tables/sequences/functions
ALTER DEFAULT PRIVILEGES FOR ROLE crankshaft IN SCHEMA crankshaft GRANT all ON TABLES    TO GROUP identified;
ALTER DEFAULT PRIVILEGES FOR ROLE crankshaft IN SCHEMA crankshaft GRANT all ON SEQUENCES TO GROUP identified;
ALTER DEFAULT PRIVILEGES FOR ROLE crankshaft IN SCHEMA crankshaft GRANT all ON FUNCTIONS TO GROUP identified;











\c test piston
create table foo ( 
  id    serial PRIMARY KEY,
  n     integer
);
insert into foo(n) values (1);


\c test crankshaft
create table bar ( 
  id    serial PRIMARY KEY,
  n     integer
);
insert into bar(n) values (2);
select * from foo;

\c test piston
select * from bar;



--- pg_dump -s -n crankshaft -n piston -U piston test