Re: Grant problem and how to prevent users to execute OS commands?

On 08/21/2012 02:34 AM, Evil wrote:
> After issusing that revoke from public my postgres user still able to
> connect to any database.

Looking at your logs, you tested to see if they could connect to a
database named "onlypostgres", but I didn't see any sign that you had
REVOKEd connect from public on that database.

Try:

REVOKE CONNECT ON DATABASE onlypostgres FROM public;

then try to see if you can connect with your test user.

> More over
> when executing \l user is able to see complete database names.

As far as I know you can't prevent that, it's never been a design goal
to limit which databases a user can see, only to stop them connecting to
them.

Since you want to limit what DBs others can see, I'm guessing you want
to set up a multi-tenanted PostgreSQL install. If so, there are some
limitations on that right now. I strongly suggest that you search the
mailing list archives to learn more.

An option to hide rows in pg_database if the user can't connect to them
sounds simple, but I suspect it'd actually be quite complicated - it'd
effectively require row-level security, something PostgreSQL doesn't
support yet.

You can `REVOKE` `SELECT` rights on the information_schema and some
parts of the system catalog, but that'll probably break `psql`, PgJDBC's
metadata queries, and more.

> 1 ) How i can grant my user(s) to connect only to *granted* database not
> *any*

When you create a database, `REVOKE CONNECT ON DATABASE thedbname FROM
public` on it if you don't want anyone to be able to connect to it.

If you want to make that the default for new databases, connect to
`template1` and revoke connect from public on it. New DBs will inherit
that setting unless they're created with a different template database.

> 2 ) Users still able to execute OS (operation system) commands on system.

Er, WTF?

... ok, looking through that log, you seem to mean this:

> onlypostgres=> \! ping google.com
>
> Обмен пакетами с google.com [173.194.71.113] по 32 байт:

That command is run by the `psql` client. Not the server. Since they're
running `psql` they can already run OS commands, so there's nothing to
prevent.

If they connect remotely over `psql`, the \! commands they run will run
on *their* computer, not the server. Since they can run psql, they can
already run OS commands on their computer, so that doesn't matter.

If they connect remotely over another client like PgAdmin-III, PgJDBC,
psqlODBC, or whatever, they can't run OS commands at all.

--
Craig Ringer