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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
