Thread: How to set default owner of objects in Postgresql
Dear Expert's,
I wanted to know whether it is possible to set default owner of any new objects being created in Postgresql 9.6?
For eg: if user abc creates a table in database mydb then by default the owner of the object should be xyz (non superuser).
Regards,
Ashif Shaikh
Hi,
Le mer. 16 janv. 2019 à 09:40, Ashif Shaikh <sashif0@gmail.com> a écrit :
Dear Expert's,I wanted to know whether it is possible to set default owner of any new objects being created in Postgresql 9.6?For eg: if user abc creates a table in database mydb then by default the owner of the object should be xyz (non superuser).
No. The default owner will always be the user who executes the CREATE statement.
Guillaume.
Hi Ashif,
When we create any table in a database that table owner is a respective user who created the table. If user ABC creates a table in the database mydb then by default the owner of the object should be ABC (non superuser) later you can change the table owner as xyz by using alter command.
On Wed, Jan 16, 2019 at 2:10 PM Ashif Shaikh <sashif0@gmail.com> wrote:
Dear Expert's,I wanted to know whether it is possible to set default owner of any new objects being created in Postgresql 9.6?For eg: if user abc creates a table in database mydb then by default the owner of the object should be xyz (non superuser).Regards,Ashif Shaikh
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.
In addition, you can use an event trigger to set the default owner whenever creates the object.
On Wed, Jan 16, 2019 at 2:40 PM Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Ashif,When we create any table in a database that table owner is a respective user who created the table. If user ABC creates a table in the database mydb then by default the owner of the object should be ABC (non superuser) later you can change the table owner as xyz by using alter command.On Wed, Jan 16, 2019 at 2:10 PM Ashif Shaikh <sashif0@gmail.com> wrote:Dear Expert's,I wanted to know whether it is possible to set default owner of any new objects being created in Postgresql 9.6?For eg: if user abc creates a table in database mydb then by default the owner of the object should be xyz (non superuser).Regards,Ashif Shaikh
On Wed, Jan 16, 2019 at 10:24 PM Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
In addition, you can use an event trigger to set the default owner whenever creates the object.
! Yet another feature I've never heard of. Pretty interesting, thanks very much for pointing it out.
Thanks a ton.
Will test this.
Regards,
Ashif Shaikh
On Wed 16 Jan, 2019, 2:42 PM Shreeyansh Dba <shreeyansh2014@gmail.com wrote:
Hi Ashif,When we create any table in a database that table owner is a respective user who created the table. If user ABC creates a table in the database mydb then by default the owner of the object should be ABC (non superuser) later you can change the table owner as xyz by using alter command.On Wed, Jan 16, 2019 at 2:10 PM Ashif Shaikh <sashif0@gmail.com> wrote:Dear Expert's,I wanted to know whether it is possible to set default owner of any new objects being created in Postgresql 9.6?For eg: if user abc creates a table in database mydb then by default the owner of the object should be xyz (non superuser).Regards,Ashif Shaikh