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.

>