Обсуждение: how to create a role with no privileges?

Kynn Jones <kynnjo@gmail.com> writes:

> How does one define the most limited role/user possible in PostgreSQL?
>
> Ideally, this role would not be able to do *anything* at all.  In particular, this role would not be able to query
meta-informationabout existing tables, functions, 
> etc. with backslash commands such as \dt, \df.

Some new role created and not granted anything has only public rights
which by default is the lowest level of privilege but as you probably
are aware does permit creating objects in public schema and viewing
certain system info.

But you can revoke usage on schemas; public, pg_catalog,
information_schema to create the illusion of even tighter than default
perms.

You would then need to grant usage on those schemas to some other role
and give this role to  real new roles/users who are permitted to do
those things.

>
> (Of course, in practice such a role would not correspond to any real role.  Its purpose, rather, is to serve as the
startingpoint for defining more realistic roles by 
> selectively adding the fewest privileges possible).
>
> TIA for any pointers!
>
> kynn
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

On Tue, Jul 1, 2014 at 6:13 PM, Kynn Jones <kynnjo@gmail.com> wrote:
> Actually, AFAICT, revoking usage on the schemas you listed seems to have no
> effect at all on the "minimal role"'s ability to use \l, \d, \dt, etc.
>
> In particular, the minimal role still has access to the shell through \!.
> YIKES!!!

Without seeing your actual commands, it's difficult to know about the
schema stuff, but one thing for sure, \! has nothing to do with
databases permissions, it is related to local process permisions (
database is not contacted, it is a local shell access ), you have to
restrict it with whichever tools your operating system has.

It seems you are confussing the postgresql database with psql, which
is just a C program linked with the libpq library to access a
postgresql database server. You can easily rewrite it in, say, java
using jdbc, or, if you read enough docs, in any language which allows
socket access.
Database permissions will apply to any of them.

Francisco Olarte.

Kynn Jones <kynnjo@gmail.com> writes:
> In particular, the minimal role still has access to the shell through \!.
> YIKES!!!

What exactly do you find to be "yikes" about that?  It's a shell under the
user's own account on the client machine, ie, exactly like the shell
account he invoked psql from.  It doesn't really have anything to do with
Postgres at all.

            regards, tom lane

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.