Re: Something I don't understand with the use of schemas

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> On Mon, Dec 12, 2005 at 05:43:47PM -0500, Rod Taylor wrote:
>> A sudo equivalent would be a version of psql that always connected to
>> the database using super-user and allowed command execution based on a
>> regular expression. Bit of a hack to say the least.

> How is that at all what you're describing?
> sudo gives you the ability to run a command as root, plain and simple.

Perhaps you should read the sudo documentation sometime ;-). sudo
allows fairly fine-grained control over who can become which userid
and what commands they can issue. (At least the Linux version does.)

I'm not sure that a psql sudo would have to have all that, since to
some extent it'd duplicate the existing SQL permissions machinery,
but at the very least it needs to allow specification of the target
userid. There isn't any universal equivalent to "root" that we could
sensibly default to in Postgres. So you're really talking about
sudo postgres create user joe ... ;
versus
set role postgres;
create user joe ... ;
reset role;
which is not *that* amazing a savings in typing, and becomes very
rapidly less so when you need to execute multiple commands as the
more-privileged user.

Implementing sudo on the psql side would be a bit of a PITA, because
of the problem of "how do you reset role if the called command fails
(and thereby aborts your open transaction)?" On the backend side I
think it could use the same reset mechanism that already exists for
security-definer functions...

regards, tom lane