How to revoke administrator functions for specific user while create postgres user

Question

I want to revoke the administrator or executive functions to a specific user in Postgres in user creation in java.

for example, let's have a user with read-only privileges "readonlyuser" and I don't want him to execute these functions. So I tried below to revoke, first all functions and then pg_sleep alone.

REVOKE EXECUTE ON FUNCTIONS FROM readonlyuser, public;

REVOKE EXECUTE ON FUNCTION pg_sleep FROM readonlyuser;

like below,

String sql = "CREATE USER 'readonlyuser' WITH ENCRYPTED PASSWORD 'pass';";
Statement stmt = connection.createStatement();
stmt.execute(sql);
stmt.execute("REVOKE EXECUTE ON FUNCTIONS FROM 'readonlyuser', public");

or

stmt.execute("REVOKE EXECUTE ON FUNCTION pg_sleep FROM readonlyuser;");

But still, I can able to execute functions from readonlyuser.

please, help me out to know how can I achieve this.

Which user do you use to execute that statement? What response do you get from the database? How did you check whether you can still invoke them? — Bergi, Dec 18 '22 at 18:07
I used the user who has readonly privileges. If I use pg_sleep(10) the DB stopped for 10s but It wouldn't be. I confirmed it by using pg_sleep functions @Bergi — Sakthis, Dec 19 '22 at 05:09

score 1 · Accepted Answer · answered Dec 19 '22 at 07:13

REVOKE EXECUTE ON FUNCTIONS is not correct SQL, so let me take pg_sleep as an example.

pg_sleep has the default permissions, since

SELECT proacl FROM pg_proc WHERE proname = 'pg_sleep';

returns NULL. The documentation has that

the default privileges granted to PUBLIC are as follows: [...] EXECUTE privilege for functions and procedures

So the privilege is granted to PUBLIC. Now you can only revoke a privilege that was granted, so the attempt to revoke the privilege from readonlyuser achieves nothing. You'd have to revoke the privilege from PUBLIC.

Be warned that you should not change the privileges of system functions. Such changes will be lost during an upgrade. Also, it may break client tools that expect to be able to use these functions. Note that functions that are considered dangerous are restricted to privileged users anyway.

If your goal is to keep users that can run SQL statements on your database from performing denial-of-service attacks, forget it. There is no way you can do that. Restricting access to certain functions is your least worry in that case. Don't let untrustworthy users run their own SQL statements on your database.

How to revoke administrator functions for specific user while create postgres user

1 Answers1