how to create a role with no privileges?
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-information about
existing tables, functions, etc. with backslash commands such as \dt, \df.
(Of course, in practice such a role would not correspond to any real role.
Its purpose, rather, is to serve as the starting point for defining more
realistic roles by selectively adding the fewest privileges possible).
TIA for any pointers!
kynn
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-information about 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 starting point 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks for your reply.
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!!!
kynn
On Mon, Jun 30, 2014 at 5:37 PM, Jerry Sievers <gsievers19@comcast.net>
wrote:
Show quoted text
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-information about
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 starting point for defining
more realistic roles byselectively 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Jul 1, 2014 at 12:36 PM, Francisco Olarte <folarte@peoplecall.com>
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
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 wasREVOKE 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Jul 1, 2014 at 1:28 PM, David G Johnston <david.g.johnston@gmail.com
wrote:
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.
Thanks!