How to grant permissions on database to user?

Question

How to grant permissions on database to user?

Mykhailo Levandovskiy 20

Hi,

I have a Flexible Postgesql server with several databases, i.e. db01.

And I want to grant INSERT,UPDATE,DELETE privileges for all tables in public schema on database db01 to user myuser01.

I connected as psqladmin user and tried to run these commands:

\c db01
grant insert on all tables in schema public to myuser01;
WARNING:  no privileges were granted for "table01"

grant all privileges on database db01 to myuser01

alter schema public owner to myuser01;
ERROR:  must be able to SET ROLE "myuser01"

So my question is: how to give user write permissions for all tables in database?

Thanks.

Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-05-26T11:30:09.05+00:00
Hi @ Mykhailo Levandovskiy

To grant INSERT, UPDATE, and DELETE privileges to myuser01 on all tables in the public schema of the database db01, follow these steps:

To allow INSERT, UPDATE, and DELETE on existing tables in the public schema:

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser01;

For new tables created in the future, apply default privileges using:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO myuser01;

To allow myuser01 to connect to the database and create temporary tables, use:

GRANT CONNECT, TEMPORARY ON DATABASE db01 TO myuser01;

If ownership needs to be changed:

ALTER SCHEMA public OWNER TO myuser01;

If still an error appears ("must be able to SET ROLE myuser01"), your current user might lack the necessary permissions.

To verify existing permissions for myuser01:

SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'myuser01';

Please go through this Documentation that might help you:

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-create-users

Hope this helps. Do let us know if you any further queries.
Mykhailo Levandovskiy 20 Reputation points

2025-05-26T13:56:41.5066667+00:00
This query

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser01;

returns

WARNING: no privileges were granted for "table01"

I'm running psqladmin user, it has azure_pg_admin role.
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-05-26T14:34:44.46+00:00
Hi @ Mykhailo Levandovskiy

Thank you for your question. It looks like the issue may be related to the azure_pg_admin role, which does not have full superuser privileges in Azure Database for PostgreSQL. Because of this, certain permissions may not be granted as expected.

Run the following query to check what privileges myuser01 currently has:

SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'myuser01';

If the tables were created by another user, you might need to grant permissions explicitly:

GRANT INSERT, UPDATE, DELETE ON table01 TO myuser01;

If new tables are created, ensure they automatically have the correct permissions:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO myuser01;

If psqladmin does not own the tables, ownership may need to be changed or granted by the actual table owner:

ALTER TABLE table01 OWNER TO myuser01;

Since azure_pg_admin lacks full superuser rights, you may need to work with a user who has sufficient privileges or explore creating a new role with higher access levels.

Please go through this Parallel thread that might help you:

https://stackoverflow.com/questions/76272100/azure-postgresql-how-to-create-admin-user
Mykhailo Levandovskiy 20 Reputation points

2025-05-26T15:51:55.0033333+00:00
Database db01 owned by psqladmin

I want to give user myuser01 write access to database db01 too.

This query

SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'myuser01';

returns 0 rows.

This server was created by using Terraform and I don't have any other user than psqladmin with admin permissions:

resource "azurerm_postgresql_flexible_server" "db_postgres" { administrator_login = "psqladmin"

So how can I grant to user myuser01 write permissions to table in public schema in database db01?
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-05-27T08:45:32.57+00:00

Hi Mykhailo Levandovskiy

Could you please share the details asked in Private message?
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-05-28T08:54:50.0666667+00:00

Hi Mykhailo Levandovskiy

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution

please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Accepted answer

0 additional answers

Your answer

Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-05-26T11:30:09.05+00:00

Hi @ Mykhailo Levandovskiy

To grant INSERT, UPDATE, and DELETE privileges to myuser01 on all tables in the public schema of the database db01, follow these steps:

To allow INSERT, UPDATE, and DELETE on existing tables in the public schema:

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser01;

For new tables created in the future, apply default privileges using:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO myuser01;

To allow myuser01 to connect to the database and create temporary tables, use:

GRANT CONNECT, TEMPORARY ON DATABASE db01 TO myuser01;

If ownership needs to be changed:

ALTER SCHEMA public OWNER TO myuser01;

If still an error appears ("must be able to SET ROLE myuser01"), your current user might lack the necessary permissions.

To verify existing permissions for myuser01:

SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'myuser01';

Please go through this Documentation that might help you:

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-create-users

Hope this helps. Do let us know if you any further queries.
Mykhailo Levandovskiy 20 Reputation points

2025-05-26T13:56:41.5066667+00:00

This query

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser01;

returns

WARNING: no privileges were granted for "table01"

I'm running psqladmin user, it has azure_pg_admin role.
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-05-26T14:34:44.46+00:00

Hi @ Mykhailo Levandovskiy

Thank you for your question. It looks like the issue may be related to the azure_pg_admin role, which does not have full superuser privileges in Azure Database for PostgreSQL. Because of this, certain permissions may not be granted as expected.

Run the following query to check what privileges myuser01 currently has:

SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'myuser01';

If the tables were created by another user, you might need to grant permissions explicitly:

GRANT INSERT, UPDATE, DELETE ON table01 TO myuser01;

If new tables are created, ensure they automatically have the correct permissions:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO myuser01;

If psqladmin does not own the tables, ownership may need to be changed or granted by the actual table owner:

ALTER TABLE table01 OWNER TO myuser01;

Since azure_pg_admin lacks full superuser rights, you may need to work with a user who has sufficient privileges or explore creating a new role with higher access levels.

Please go through this Parallel thread that might help you:

https://stackoverflow.com/questions/76272100/azure-postgresql-how-to-create-admin-user
Mykhailo Levandovskiy 20 Reputation points

2025-05-26T15:51:55.0033333+00:00

Database db01 owned by psqladmin

I want to give user myuser01 write access to database db01 too.

This query

SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'myuser01';

returns 0 rows.

This server was created by using Terraform and I don't have any other user than psqladmin with admin permissions:

resource "azurerm_postgresql_flexible_server" "db_postgres" { administrator_login = "psqladmin"

So how can I grant to user myuser01 write permissions to table in public schema in database db01?
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-05-27T08:45:32.57+00:00

Hi Mykhailo Levandovskiy

Could you please share the details asked in Private message?
Sai Raghunadh M 4,635 Reputation points Microsoft External Staff Moderator

2025-05-28T08:54:50.0666667+00:00

Hi Mykhailo Levandovskiy

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution

please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

PratikLad 1,825 Microsoft External Staff Moderator

Hi Mykhailo Levandovskiy

no privileges were granted for "table01"

It usually means that the user running the GRANT command (in your case, psqladmin) doesn't actually own the table. In PostgreSQL, only the table owner or a superuser can grant permissions on that table.

For example, when you run:


GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser01;

it will only apply to tables owned by the user you're currently connected as. If other users own some of the tables, those won't be affected by this command.

To grant permissions on those tables, you'll need to either:

Run the command as the user who owns the tables, or
Use a superuser account to apply the privileges.

Mykhailo Levandovskiy 20 Reputation points

2025-05-30T08:13:03.11+00:00

Yass, this solved the issue, thanks!

Share via

How to grant permissions on database to user?

0 additional answers

Your answer