Schema multitennancy

General
1

Here is what I use to configure schemas for apps to connect to and use.

-- as a neondb user
revoke all on schema public from public;
create user "app-name" with password 'kUXqtms4bvWtUxSU7H9Ve9WzMK7E';
alter user "app-name" set search_path = 'app-name',public;
grant create on database neondb to "app-name";

-- as the app's user
alter user "app-name" with password 'rsnnZVpCpYh5yQKM5vpiph4pPNzb';
create schema authorization "app-name";

-- as a neondb user
revoke create on database neondb from "app-name";

I’m not a postgres expert or anything. Here are the concerns the this currently addresses:

  • If you have the console create the role currently it makes a role that can create other roles.
  • They also don’t hand out superuser priviledges to anyroles they create so I need to log in as the newly created user in order to make the schema have the right owner.
  • liquibase currently errors if you are using a schema with a dash and only one item in the search_path
  • The first password entered into the console will get saved
  • those aren’t my real passwords

I’m mostly posting this here for the benefit of future me.
Let me know if there is anything that could be improved here.

1 Like