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
