almost-super-user problems that we haven't fixed yet

On Mon, Jan 16, 2023 at 02:29:56PM -0500, Robert Haas wrote:

4. You can reserve a small number of connections for the superuser
with superuser_reserved_connections, but there's no way to do a
similar thing for any other user. As mentioned above, a CREATEROLE
user could set connection limits for every created role such that the
sum of those limits is less than max_connections by some margin, but
that restricts each of those roles individually, not all of them in
the aggregate. Maybe we could address this by inventing a new GUC
reserved_connections and a predefined role
pg_use_reserved_connections.

I've written something like this before, and I'd be happy to put together a
patch if there is interest.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Mon, Jan 16, 2023 at 5:37 PM Nathan Bossart <nathandbossart@gmail.com> wrote:

On Mon, Jan 16, 2023 at 02:29:56PM -0500, Robert Haas wrote:

4. You can reserve a small number of connections for the superuser
with superuser_reserved_connections, but there's no way to do a
similar thing for any other user. As mentioned above, a CREATEROLE
user could set connection limits for every created role such that the
sum of those limits is less than max_connections by some margin, but
that restricts each of those roles individually, not all of them in
the aggregate. Maybe we could address this by inventing a new GUC
reserved_connections and a predefined role
pg_use_reserved_connections.

I've written something like this before, and I'd be happy to put together a
patch if there is interest.

Cool. I had been thinking of coding it up myself, but you doing it works, too.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Mon, Jan 16, 2023 at 09:06:10PM -0500, Robert Haas wrote:

On Mon, Jan 16, 2023 at 5:37 PM Nathan Bossart <nathandbossart@gmail.com> wrote:

On Mon, Jan 16, 2023 at 02:29:56PM -0500, Robert Haas wrote:

4. You can reserve a small number of connections for the superuser
with superuser_reserved_connections, but there's no way to do a
similar thing for any other user. As mentioned above, a CREATEROLE
user could set connection limits for every created role such that the
sum of those limits is less than max_connections by some margin, but
that restricts each of those roles individually, not all of them in
the aggregate. Maybe we could address this by inventing a new GUC
reserved_connections and a predefined role
pg_use_reserved_connections.

I've written something like this before, and I'd be happy to put together a
patch if there is interest.

Cool. I had been thinking of coding it up myself, but you doing it works, too.

Alright. The one design question I have is whether this should be a new
set of reserved connections or replace superuser_reserved_connections
entirely.

If we create a new batch of reserved connections, only roles with
privileges of pg_use_reserved_connections would be able to connect if the
number of remaining slots is greater than superuser_reserved_connections
but less than or equal to superuser_reserved_connections +
reserved_connections. Only superusers would be able to connect if the
number of remaining slots is less than or equal to
superuser_reserved_connections. This helps avoid blocking new superuser
connections even if you've reserved some connections for non-superusers.

Іf we replace superuser_reserved_connections, we're basically opening up
the existing functionality to non-superusers, which is simpler and probably
more in the spirit of this thread, but it doesn't provide a way to prevent
blocking new superuser connections.

My preference is the former approach. This is closest to what I've written
before, and if I read your words carefully, it seems to be what you are
proposing. WDYT?

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Tue, Jan 17, 2023 at 1:42 PM Nathan Bossart <nathandbossart@gmail.com> wrote:

Alright. The one design question I have is whether this should be a new
set of reserved connections or replace superuser_reserved_connections
entirely.

I think it should definitely be something new, not a replacement.

If we create a new batch of reserved connections, only roles with
privileges of pg_use_reserved_connections would be able to connect if the
number of remaining slots is greater than superuser_reserved_connections
but less than or equal to superuser_reserved_connections +
reserved_connections. Only superusers would be able to connect if the
number of remaining slots is less than or equal to
superuser_reserved_connections. This helps avoid blocking new superuser
connections even if you've reserved some connections for non-superusers.

This is precisely what I had in mind.

I think the documentation will need some careful wordsmithing,
including adjustments to superuser_reserved_connections. We want to
recast superuser_reserved_connections as a final reserve to be touched
after even reserved_connections has been exhausted.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Tue, Jan 17, 2023 at 02:59:31PM -0500, Robert Haas wrote:

On Tue, Jan 17, 2023 at 1:42 PM Nathan Bossart <nathandbossart@gmail.com> wrote:

If we create a new batch of reserved connections, only roles with
privileges of pg_use_reserved_connections would be able to connect if the
number of remaining slots is greater than superuser_reserved_connections
but less than or equal to superuser_reserved_connections +
reserved_connections. Only superusers would be able to connect if the
number of remaining slots is less than or equal to
superuser_reserved_connections. This helps avoid blocking new superuser
connections even if you've reserved some connections for non-superusers.

This is precisely what I had in mind.

Great. Here is a first attempt at the patch.

I think the documentation will need some careful wordsmithing,
including adjustments to superuser_reserved_connections. We want to
recast superuser_reserved_connections as a final reserve to be touched
after even reserved_connections has been exhausted.

I tried to do this, but there is probably still room for improvement,
especially for the parts that discuss the relationship between
max_connections, superuser_reserved_connections, and reserved_connections.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Tue, Jan 17, 2023 at 7:15 PM Nathan Bossart <nathandbossart@gmail.com> wrote:

Great. Here is a first attempt at the patch.

In general, looks good. I think this will often call HaveNFreeProcs
twice, though, and that would be better to avoid, e.g.

if (!am_superuser && !am_walsender && (SuperuserReservedBackends +
ReservedBackends) > 0)
&& !HaveNFreeProcs(SuperuserReservedBackends + ReservedBackends))
{
if (!HaveNFreeProcs(SuperuserReservedBackends))
remaining connection slots are reserved for non-replication
superuser connections;
if (!has_privs_of_role(GetUserId(), ROLE_PG_USE_RESERVED_CONNECTIONS))
remaining connection slots are reserved for roles with
privileges of pg_use_reserved_backends;
}

In the common case where we hit neither limit, this only counts free
connection slots once. We could do even better by making
HaveNFreeProcs have an out parameter for the number of free procs
actually found when it returns false, but that's probably not
important.

I don't think that we should default both the existing GUC and the new
one to 3, because that raises the default limit in the case where the
new feature is not used from 3 to 6. I think we should default one of
them to 0 and the other one to 3. Not sure which one should get which
value.

I think the documentation will need some careful wordsmithing,
including adjustments to superuser_reserved_connections. We want to
recast superuser_reserved_connections as a final reserve to be touched
after even reserved_connections has been exhausted.

I tried to do this, but there is probably still room for improvement,
especially for the parts that discuss the relationship between
max_connections, superuser_reserved_connections, and reserved_connections.

I think it's pretty good the way you have it. I agree that there might
be a way to make it even better, but I don't think I know what it is.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Mon, Jan 16, 2023 at 2:29 PM Robert Haas <robertmhaas@gmail.com> wrote:

1. It's still possible for a CREATEROLE user to hand out role
attributes that they don't possess. The new prohibitions in
cf5eb37c5ee0cc54c80d95c1695d7fca1f7c68cb prevent a CREATEROLE user
from handing out membership in a role on which they lack sufficient
permissions, but they don't prevent a CREATEROLE user who lacks
CREATEDB from creating a new user who does have CREATEDB. I think we
should subject the CREATEDB, REPLICATION, and BYPASSRLS attributes to
the same rule that we now use for role memberships: you've got to have
the property in order to give it to someone else. In the case of
CREATEDB, this would tighten the current rules, which allow you to
give out CREATEDB without having it. In the case of REPLICATION and
BYPASSRLS, this would liberalize the current rules: right now, a
CREATEROLE user cannot give REPLICATION or BYPASSRLS to another user
even if they possess those attributes.

This proposal doesn't address the CREATEROLE or CONNECTION LIMIT
properties. It seems possible to me that someone might want to set up
a CREATEROLE user who can't make more such users, and this proposal
doesn't manufacture any way of doing that. It also doesn't let you
constraint the ability of a CREATEROLE user to set a CONNECTION LIMIT
for some other user. I think that's OK. It might be nice to have ways
of imposing such restrictions at some point in the future, but it is
not very obvious what to do about such cases and, importantly, I don't
think there's any security impact from failing to address those cases.
If a CREATEROLE user without CREATEDB can create a new role that does
have CREATEDB, that's a privilege escalation. If they can hand out
CREATEROLE, that isn't: they already have it.

Here is a patch implementing the above proposal. Since this is fairly
closely related to already-committed work, I would like to get this
into v16. That way, all the changes to how CREATEROLE works will go
into a single release, which seems less confusing for users. It is
also fairly clear to me that this is an improvement over the status
quo. Sometimes things that seem clear to me turn out to be false, so
if this change seems like a problem to you, please let me know.

Thanks,

--
Robert Haas
EDB: http://www.enterprisedb.com

On Wed, Jan 18, 2023 at 11:28:57AM -0500, Robert Haas wrote:

In general, looks good. I think this will often call HaveNFreeProcs
twice, though, and that would be better to avoid, e.g.

I should have thought of this. This is fixed in v2.

In the common case where we hit neither limit, this only counts free
connection slots once. We could do even better by making
HaveNFreeProcs have an out parameter for the number of free procs
actually found when it returns false, but that's probably not
important.

Actually, I think it might be important. IIUC the separate calls to
HaveNFreeProcs might return different values for the same input, which
could result in incorrect error messages (e.g., you might get the
reserved_connections message despite setting reserved_connections to 0).
So, I made this change in v2, too.

I don't think that we should default both the existing GUC and the new
one to 3, because that raises the default limit in the case where the
new feature is not used from 3 to 6. I think we should default one of
them to 0 and the other one to 3. Not sure which one should get which
value.

I chose to set reserved_connections to 0 since it is new and doesn't have a
pre-existing default value.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Wed, Jan 18, 2023 at 2:00 PM Nathan Bossart <nathandbossart@gmail.com> wrote:

On Wed, Jan 18, 2023 at 11:28:57AM -0500, Robert Haas wrote:

In general, looks good. I think this will often call HaveNFreeProcs
twice, though, and that would be better to avoid, e.g.

I should have thought of this. This is fixed in v2.

Should (nfree < SuperuserReservedBackends) be using <=, or am I confused?

In the common case where we hit neither limit, this only counts free
connection slots once. We could do even better by making
HaveNFreeProcs have an out parameter for the number of free procs
actually found when it returns false, but that's probably not
important.

Actually, I think it might be important. IIUC the separate calls to
HaveNFreeProcs might return different values for the same input, which
could result in incorrect error messages (e.g., you might get the
reserved_connections message despite setting reserved_connections to 0).
So, I made this change in v2, too.

I thought of that briefly and it didn't seem that important, but the
way you did it seems fine, so let's go with that.

What's the deal with removing "and no new replication connections will
be accepted" from the documentation? Is the existing documentation
just wrong? If so, should we fix that first? And maybe delete
"non-replication" from the error message that says "remaining
connection slots are reserved for non-replication superuser
connections"? It seems like right now the comments say that
replication connections are a completely separate pool of connections,
but the documentation and the error message make it sound otherwise.
If that's true, then one of them is wrong, and I think it's the
docs/error message. Or am I just misreading it?

--
Robert Haas
EDB: http://www.enterprisedb.com

On Wed, Jan 18, 2023 at 02:51:38PM -0500, Robert Haas wrote:

Should (nfree < SuperuserReservedBackends) be using <=, or am I confused?

I believe < is correct. At this point, the new backend will have already
claimed a proc struct, so if the number of remaining free slots equals the
number of reserved slots, it is okay.

What's the deal with removing "and no new replication connections will
be accepted" from the documentation? Is the existing documentation
just wrong? If so, should we fix that first? And maybe delete
"non-replication" from the error message that says "remaining
connection slots are reserved for non-replication superuser
connections"? It seems like right now the comments say that
replication connections are a completely separate pool of connections,
but the documentation and the error message make it sound otherwise.
If that's true, then one of them is wrong, and I think it's the
docs/error message. Or am I just misreading it?

I think you are right. This seems to have been missed in ea92368. I moved
this part to a new patch that should probably be back-patched to v12.

On that note, I wonder if it's worth changing the "sorry, too many clients
already" message to make it clear that max_connections has been reached.
IME some users are confused by this error, and I think it would be less
confusing if it pointed to the parameter that governs the number of
connection slots. I'll create a new thread for this.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Wed, Jan 18, 2023 at 12:15:33PM -0500, Robert Haas wrote:

On Mon, Jan 16, 2023 at 2:29 PM Robert Haas <robertmhaas@gmail.com> wrote:

1. It's still possible for a CREATEROLE user to hand out role
attributes that they don't possess. The new prohibitions in
cf5eb37c5ee0cc54c80d95c1695d7fca1f7c68cb prevent a CREATEROLE user
from handing out membership in a role on which they lack sufficient
permissions, but they don't prevent a CREATEROLE user who lacks
CREATEDB from creating a new user who does have CREATEDB. I think we
should subject the CREATEDB, REPLICATION, and BYPASSRLS attributes to
the same rule that we now use for role memberships: you've got to have
the property in order to give it to someone else. In the case of
CREATEDB, this would tighten the current rules, which allow you to
give out CREATEDB without having it. In the case of REPLICATION and
BYPASSRLS, this would liberalize the current rules: right now, a
CREATEROLE user cannot give REPLICATION or BYPASSRLS to another user
even if they possess those attributes.

This proposal doesn't address the CREATEROLE or CONNECTION LIMIT
properties. It seems possible to me that someone might want to set up
a CREATEROLE user who can't make more such users, and this proposal
doesn't manufacture any way of doing that. It also doesn't let you
constraint the ability of a CREATEROLE user to set a CONNECTION LIMIT
for some other user. I think that's OK. It might be nice to have ways
of imposing such restrictions at some point in the future, but it is
not very obvious what to do about such cases and, importantly, I don't
think there's any security impact from failing to address those cases.
If a CREATEROLE user without CREATEDB can create a new role that does
have CREATEDB, that's a privilege escalation. If they can hand out
CREATEROLE, that isn't: they already have it.

Here is a patch implementing the above proposal. Since this is fairly
closely related to already-committed work, I would like to get this
into v16. That way, all the changes to how CREATEROLE works will go
into a single release, which seems less confusing for users. It is
also fairly clear to me that this is an improvement over the status
quo. Sometimes things that seem clear to me turn out to be false, so
if this change seems like a problem to you, please let me know.

This seems like a clear improvement to me. However, as the attribute
system becomes more sophisticated, I think we ought to improve the error
messages in user.c. IMHO messages like "permission denied" could be
greatly improved with some added context.

For example, if I want to change a role's password, I need both CREATEROLE
and ADMIN OPTION on the role, but the error message only mentions
CREATEROLE.

postgres=# create role createrole with createrole;
CREATE ROLE
postgres=# create role otherrole;
CREATE ROLE
postgres=# set role createrole;
SET
postgres=> alter role otherrole password 'test';
ERROR: must have CREATEROLE privilege to change another user's password

Similarly, if I want to allow a role to grant REPLICATION to another role,
I have to give it CREATEROLE, REPLICATION, and membership with ADMIN
OPTION. If the role is missing CREATEROLE or membership with ADMIN OPTION,
it'll only ever see a "permission denied" error.

postgres=# create role createrole with createrole;
CREATE ROLE
postgres=# create role otherrole;
CREATE ROLE
postgres=# set role createrole;
SET
postgres=> alter role otherrole with replication;
ERROR: permission denied
postgres=> reset role;
RESET
postgres=# alter role createrole with replication;
ALTER ROLE
postgres=# set role createrole;
SET
postgres=> alter role otherrole with replication;
ERROR: permission denied
postgres=> reset role;
RESET
postgres=# grant otherrole to createrole;
GRANT ROLE
postgres=# set role createrole;
SET
postgres=> alter role otherrole with replication;
ERROR: permission denied
postgres=> reset role;
RESET
postgres=# grant otherrole to createrole with admin option;
GRANT ROLE
postgres=# set role createrole;
SET
postgres=> alter role otherrole with replication;
ALTER ROLE

If it has both CREATEROLE and membership with ADMIN OPTION (but not
REPLICATION), it'll see a more helpful message.

postgres=# create role createrole with createrole;
CREATE ROLE
postgres=# create role otherrole;
CREATE ROLE
postgres=# grant otherrole to createrole with admin option;
GRANT ROLE
postgres=# set role createrole;
SET
postgres=> alter role otherrole with replication;
ERROR: must have replication privilege to change replication attribute

This probably shouldn't block your patch, but I think it's worth doing in
v16 since there are other changes in this area. I'm happy to help.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On 1/19/23 4:47 AM, Nathan Bossart wrote:

This seems like a clear improvement to me. However, as the attribute
system becomes more sophisticated, I think we ought to improve the error
messages in user.c. IMHO messages like "permission denied" could be
greatly improved with some added context.

I observed this behavior where the role is having creatrole but still
it's unable to pass it to another user.

postgres=# create role abc1 login createrole;
CREATE ROLE
postgres=# create user test1;
CREATE ROLE
postgres=# \c - abc1
You are now connected to database "postgres" as user "abc1".
postgres=> alter role test1 with createrole ;
ERROR:  permission denied
postgres=>

which was working previously without patch.

Is this an expected behavior?

--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

On 1/19/23 3:05 PM, tushar wrote:

which was working previously without patch.

My bad, I was testing against PG v15 but this issue is not
reproducible on master (without patch).

As you mentioned- "This implements the standard idea that you can't give
permissions
you don't have (but you can give the ones you do have)" but here the
role is having
createrole  privilege that he cannot pass on to another user? Is this
expected?

postgres=# create role fff with createrole;
CREATE ROLE
postgres=# create role xxx;
CREATE ROLE
postgres=# set role fff;
SET
postgres=> alter role xxx with createrole;
ERROR:  permission denied
postgres=>

--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

On 1/19/23 2:44 AM, Nathan Bossart wrote:

On Wed, Jan 18, 2023 at 02:51:38PM -0500, Robert Haas wrote:

Should (nfree < SuperuserReservedBackends) be using <=, or am I confused?

I believe < is correct. At this point, the new backend will have already
claimed a proc struct, so if the number of remaining free slots equals the
number of reserved slots, it is okay.

What's the deal with removing "and no new replication connections will
be accepted" from the documentation? Is the existing documentation
just wrong? If so, should we fix that first? And maybe delete
"non-replication" from the error message that says "remaining
connection slots are reserved for non-replication superuser
connections"? It seems like right now the comments say that
replication connections are a completely separate pool of connections,
but the documentation and the error message make it sound otherwise.
If that's true, then one of them is wrong, and I think it's the
docs/error message. Or am I just misreading it?

I think you are right. This seems to have been missed in ea92368. I moved
this part to a new patch that should probably be back-patched to v12.

On that note, I wonder if it's worth changing the "sorry, too many clients
already" message to make it clear that max_connections has been reached.
IME some users are confused by this error, and I think it would be less
confusing if it pointed to the parameter that governs the number of
connection slots. I'll create a new thread for this.

There is� one typo , for the doc changes, it is� mentioned
"pg_use_reserved_backends" but i think it supposed to be
"pg_use_reserved_connections"
under Table 22.1. Predefined Roles.

--
regards,tushar
EnterpriseDB https://www.enterprisedb.com/
The Enterprise PostgreSQL Company

On Thu, Jan 19, 2023 at 6:28 PM tushar <tushar.ahuja@enterprisedb.com>
wrote:

On 1/19/23 2:44 AM, Nathan Bossart wrote:

On Wed, Jan 18, 2023 at 02:51:38PM -0500, Robert Haas wrote:

Should (nfree < SuperuserReservedBackends) be using <=, or am I

confused?

I believe < is correct. At this point, the new backend will have already
claimed a proc struct, so if the number of remaining free slots equals

the

number of reserved slots, it is okay.

What's the deal with removing "and no new replication connections will
be accepted" from the documentation? Is the existing documentation
just wrong? If so, should we fix that first? And maybe delete
"non-replication" from the error message that says "remaining
connection slots are reserved for non-replication superuser
connections"? It seems like right now the comments say that
replication connections are a completely separate pool of connections,
but the documentation and the error message make it sound otherwise.
If that's true, then one of them is wrong, and I think it's the
docs/error message. Or am I just misreading it?

I think you are right. This seems to have been missed in ea92368. I

moved

this part to a new patch that should probably be back-patched to v12.

On that note, I wonder if it's worth changing the "sorry, too many

clients

already" message to make it clear that max_connections has been reached.
IME some users are confused by this error, and I think it would be less
confusing if it pointed to the parameter that governs the number of
connection slots. I'll create a new thread for this.

There is one typo , for the doc changes, it is mentioned
"pg_use_reserved_backends" but i think it supposed to be
"pg_use_reserved_connections"
under Table 22.1. Predefined Roles.

and in the error message too

[edb@centos7tushar bin]$ ./psql postgres -U r2

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
FATAL: remaining connection slots are reserved for roles with privileges
of pg_use_reserved_backends
[edb@centos7tushar bin]$

regards,

On Thu, Jan 19, 2023 at 6:50 PM tushar <tushar.ahuja@enterprisedb.com>
wrote:

and in the error message too

[edb@centos7tushar bin]$ ./psql postgres -U r2

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
FATAL: remaining connection slots are reserved for roles with privileges
of pg_use_reserved_backends
[edb@centos7tushar bin]$

I think there is also a need to improve the error message if non
super users are not able to connect due to slot unavailability.
--Connect to psql terminal, create a user
create user t1;

--set these GUC parameters in postgresql.conf and restart the server

max_connections = 3 # (change requires restart)

superuser_reserved_connections = 1 # (change requires restart)

reserved_connections = 1

psql terminal ( connect to superuser), ./psql postgres
psql terminal (try to connect to user t1) , ./psql postgres -U t1
Error message is

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
FATAL: remaining connection slots are reserved for roles with privileges
of pg_use_reserved_backends

that is not true because the superuser can still able to connect,

probably in this case message should be like this -

"remaining connection slots are reserved for roles with privileges of
pg_use_reserved_connections and for superusers" or something better.

regards,

On Thu, Jan 19, 2023 at 6:15 AM tushar <tushar.ahuja@enterprisedb.com> wrote:

postgres=# create role fff with createrole;
CREATE ROLE
postgres=# create role xxx;
CREATE ROLE
postgres=# set role fff;
SET
postgres=> alter role xxx with createrole;
ERROR: permission denied
postgres=>

Here fff would need ADMIN OPTION on xxx to be able to make modifications to it.

See https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=cf5eb37c5ee0cc54c80d95c1695d7fca1f7c68cb

--
Robert Haas
EDB: http://www.enterprisedb.com

On Wed, Jan 18, 2023 at 6:17 PM Nathan Bossart <nathandbossart@gmail.com> wrote:

Here is a patch implementing the above proposal. Since this is fairly
closely related to already-committed work, I would like to get this
into v16. That way, all the changes to how CREATEROLE works will go
into a single release, which seems less confusing for users. It is
also fairly clear to me that this is an improvement over the status
quo. Sometimes things that seem clear to me turn out to be false, so
if this change seems like a problem to you, please let me know.

This seems like a clear improvement to me.

Cool.

However, as the attribute
system becomes more sophisticated, I think we ought to improve the error
messages in user.c. IMHO messages like "permission denied" could be
greatly improved with some added context.

This probably shouldn't block your patch, but I think it's worth doing in
v16 since there are other changes in this area. I'm happy to help.

That would be great. I agree that it's good to try to improve the
error messages. It hasn't been entirely clear to me how to do that.
For instance, I don't think we want to say something like:

ERROR: must have CREATEROLE privilege and ADMIN OPTION on the target
role, or in lieu of both of those to be superuser, to set the
CONNECTION LIMIT for another role
ERROR: must have CREATEROLE privilege and ADMIN OPTION on the target
role, plus also CREATEDB, or in lieu of all that to be superuser, to
remove the CREATEDB property from another role

Such messages are long and we'd end up with a lot of variants. It's
possible that the messages could be multi-tier. For instance, if we
determine that you're trying to manage users and you don't have
permission to manage ANY user, we could say:

ERROR: permission to manage roles denied
DETAIL: You must have the CREATEROLE privilege or be a superuser to
manage roles.

If you could potentially manage some user, but not the one you're
trying to manage, we could say:

ERROR: permission to manage role "%s" denied
DETAIL: You need ADMIN OPTION on the target role to manage it.

If you have permission to manage the target role but not in the
requested manner, we could then say something like:

ERROR: permission to manage CREATEDB for role "%s" denied
DETAIL: You need CREATEDB to manage it.

This is just one idea, and maybe not the best one. I'm just trying to
say that I think this is basically an organizational problem. We need
a plan for how we're going to report errors that is not too
complicated to implement with reasonable effort, and that will produce
messages that users will understand. I'd be delighted if you wanted to
provide either ideas or patches...

--
Robert Haas
EDB: http://www.enterprisedb.com

On Thu, Jan 19, 2023 at 9:21 AM tushar <tushar.ahuja@enterprisedb.com> wrote:

that is not true because the superuser can still able to connect,

It is true, but because superusers have all privileges.

--
Robert Haas
EDB: http://www.enterprisedb.com