Kynn Jones wrote
> On Tue, Jul 1, 2014 at 12:36 PM, Francisco Olarte <
> folarte@
> >
> wrote:
>
>> Without seeing your actual commands, it's difficult to know about the
>> schema stuff...
>>
>
> Well, the "actual commands" is what the original question was asking for,
> since I really don't know how to do any of this (I find the documentation
> very unclear on the matter). At any rate, what I tried was
>
> REVOKE ALL ON SCHEMA x FROM nopriv;
> REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA x FROM nopriv;
>
> ...replacing x by public, pg_catalog, and information_schema.
>
> kynn
The global PUBLIC pseudo-role, which all roles must inherit from, is giving
permission on system catalogs. If you really want to create a role without
such permissions - which is not advisable - you would have to REVOKE those
permissions from PUBLIC so that they cannot be inherited.
The first rule regarding PostgreSQL permissions is that everything is
forbidden unless allowed - via GRANT. REVOKE simply undoes whatever has
been granted; it does not put up a block to prevent inheritance of granted
permissions.
As Tom and Francisco noted the client tool "psql" provides pretty much zero
access control capabilities for itself; the slash-commands will simply fail
with a database error if the relevant permissions have not been granted to
the user. The shell-access command \! allows the user to do anything could
have done directly from the shell in which they launched psql. That said,
if you allow them to run psql with elevated permissions then they will be
able to do shell stuff via psql that they might be forbidden to do
otherwise. This is not different than other programs (vim, emacs) that
provide embedded shell access.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-create-a-role-with-no-privileges-tp5809861p5810029.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.