Re: How to set default owner of objects in Postgresql

This is a tangent to your question, but can at least be filed under "good to know." Postgres has a nice feature called DEFAULT PRIVILEGES. I set up schemas for different categories of users, etc. and then set access defaults in advance of new object creation. So, something like this:

-- Strip existing settings because, well, science. ALTER DEFAULT alone may not remove all settings, I think it's additive.

ALTER DEFAULT PRIVILEGES IN SCHEMA api REVOKE ALL PRIVILEGES ON TABLES FROM PUBLIC;

ALTER DEFAULT PRIVILEGES IN SCHEMA api REVOKE ALL PRIVILEGES ON TABLES FROM group_admins;

ALTER DEFAULT PRIVILEGES IN SCHEMA api REVOKE ALL PRIVILEGES ON TABLES FROM group_api_users;

-- Add in the defaults you want.

ALTER DEFAULT PRIVILEGES IN SCHEMA api REVOKE ALL PRIVILEGES ON FUNCTIONS FROM group_server_bots;

ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, TRUNCATE ON TABLES TO group_admins;

ALTER DEFAULT PRIVILEGES IN SCHEMA api GRANT SELECT ON TABLES TO group_api_users;

For new object creation, I tend to connect as the desired owner, or log in as a higher-access user and then use ALTER TABLE/FUNCTION/etc. to set the owner correctly.

Note that if you're ever deploying on Postgres on RDS, grants are a bit different.