Azure PostgreSql Flexible Server - Admin to database

Is it somehow possible to have an admin user in Azure PostgreSql who has rights over all objects - he can read write, but can also alter, delete or give rights to others etc?

It seems to me that this is not possible. It's really awkward in many situations. We use pipeline to script the database and log in with federation identity. That means all objects are of that identity. Logging in somehow manually is not very logical or very difficult. So we have given rights over all operations to our admin - but that's just select, update etc. we can't delete objects set rights to other users for this table etc..

Is it somehow possible to set the user to have rights over all objects in the db? Alternatively is it somehow possible to give full rights over objects created by someone else? So that the user can assign rights etc.

Thank you

Solution

Thank you for sharing your views @Laurenz Albe and @Frank Heikens, I totally agree with you.

In Azure Database for PostgreSQL we have a role known as "Super user" that have all the rights over the database but since this service is a managed PaaS service, so only Microsoft is part of the super user role that's why we are going for the "Custom Role" with which we can grant full rights over all objects.

Grant full rights (including delete) to ADMIN over all objects:

Grant full rights to all existing objects in the schema so the admin can modify, delete, or grant rights on those objects.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_user WITH GRANT OPTION;

To Grant full rights on future objects you can use the ALTER DEFAULT PRIVILEGES command to set permissions for the admin user on new objects created by other users.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO admin_user;

Creating a Custom Role for Full Rights over all objects:

A Custom Role would be the most suitable approach for granting full rights over all objects, including those created by other users, in Azure PostgreSQL Flexible Server.

How to create a Custom Role -

1. Create a Custom Role

To create a custom role that will have login capabilities, run the following command:

CREATE ROLE custom_role WITH LOGIN PASSWORD 'password';

2. Grant Full Access to All Objects

Then, grant the custom role full privileges on the database:

GRANT ALL PRIVILEGES ON DATABASE your_database TO custom_role;

3. Grant Full Rights Over Objects Created by Others

To ensure that the custom role has full rights over existing tables and can also grant these rights to other users:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO custom_role WITH GRANT OPTION;

4. Grant full rights to Future objects:

Finally, ensure that the custom role will have full rights to new objects created in the future:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO custom_role;

This custom role will have the necessary rights to alter, delete, and assign privileges on any object in the schema, including those created by other users.