Warning console code page 866 differs from windows code page 1251

Hello List,
First time here also beginner to Postgres.So please forgive me for any mistakes.
I'm pretty sure i have same problem.=> http://archives.postgresql.org/pgsql-admin/2012-03/msg00105.php
(After searching it i found it)
However it is not solution for me.:( *I'm pretty sure i'm doing something in wrong manner*.
After issusing that revoke from public my postgres user still able to connect to any database.
More over
  when executing \l user is able to see complete database names.

So i have 2 questions:
1 ) How i can grant my user(s) to connect only to *granted* database not *any*
2 ) Users still able to execute OS (operation system) commands on system.
This is a big security risk.How i can prevent it too.

Any recommendations,manuals,helps,hints,RTFM :P are welcome;)

Thanks in advance.

OS: Windows XP sp2 32 bit+Cygwin.
And here is what i'm doing (For *picture* my situation)






===================BEGIN============================
$ psql -h localhost -U postgres -p 5432
Password for user postgres:
psql (9.1.4)
WARNING: Console code page (866) differs from Windows code page (1251)
          8-bit characters might not work correctly. See psql reference
          page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \dt
No relations found.
postgres=# \l
                                            List of databases
      Name     |  Owner   | Encoding |       Collate       |        Ctype        |   Access privileges
--------------+----------+----------+---------------------+---------------------+-----------------------
  mytestdb     | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 |
  onlypostgres | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 |
  postgres     | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 |
  template0    | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 | =c/postgres          +
               |          |          |                     |                     | postgres=CTc/postgres
  template1    | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 | =c/postgres          +
               |          |          |                     |                     | postgres=CTc/postgres
(5 rows)


postgres=# \dg
                              List of roles
  Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
  postgres  | Superuser, Create role, Create DB, Replication | {}


postgres=# select version() \g
                            version
-------------------------------------------------------------
  PostgreSQL 9.1.4, compiled by Visual C++ build 1500, 32-bit
(1 row)

postgres=# create user testusr with password 'testpwd' \g
CREATE ROLE
postgres=# \dg
                              List of roles
  Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
  postgres  | Superuser, Create role, Create DB, Replication | {}
  testusr   |                                                | {}


postgres=# create database testdb \g
CREATE DATABASE
postgres=# \timing
Timing is on.
postgres=# \c testdb
WARNING: Console code page (866) differs from Windows code page (1251)
          8-bit characters might not work correctly. See psql reference
          page "Notes for Windows users" for details.
You are now connected to database "testdb" as user "postgres".
testdb=# \d
No relations found.
testdb=# create table test_tbl(id serial,somecol text) \g
NOTICE:  CREATE TABLE will create implicit sequence "test_tbl_id_seq" for serial column "test_tbl.id"
CREATE TABLE
Time: 102,137 ms
testdb=# \c
WARNING: Console code page (866) differs from Windows code page (1251)
          8-bit characters might not work correctly. See psql reference
          page "Notes for Windows users" for details.
You are now connected to database "testdb" as user "postgres".
testdb=# \d
                List of relations
  Schema |      Name       |   Type   |  Owner
--------+-----------------+----------+----------
  public | test_tbl        | table    | postgres
  public | test_tbl_id_seq | sequence | postgres
(2 rows)


testdb=# grant all on database testdb to testusr \g
GRANT
Time: 3,638 ms
testdb=# \dg
                              List of roles
  Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
  postgres  | Superuser, Create role, Create DB, Replication | {}
  testusr   |                                                | {}


testdb=# \dp
                                  Access privileges
  Schema |      Name       |   Type   | Access privileges | Column access privileges
--------+-----------------+----------+-------------------+--------------------------
  public | test_tbl        | table    |                   |
  public | test_tbl_id_seq | sequence |                   |
(2 rows)


testdb=# \q

user@SHELL ~
$ psql -h localhost -U testusr -p 5432 -d testdb
Password for user testusr:
psql (9.1.4)
WARNING: Console code page (866) differs from Windows code page (1251)
          8-bit characters might not work correctly. See psql reference
          page "Notes for Windows users" for details.
Type "help" for help.

testdb=> \l
                                            List of databases
      Name     |  Owner   | Encoding |       Collate       |        Ctype        |   Access privileges
--------------+----------+----------+---------------------+---------------------+-----------------------
  mytestdb     | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 |
  onlypostgres | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 |
  postgres     | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 |
  template0    | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 | =c/postgres          +
               |          |          |                     |                     | postgres=CTc/postgres
  template1    | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 | =c/postgres          +
               |          |          |                     |                     | postgres=CTc/postgres
  testdb       | postgres | UTF8     | Russian_Russia.1251 | Russian_Russia.1251 | =Tc/postgres         +
               |          |          |                     |                     | postgres=CTc/postgres+
               |          |          |                     |                     | testusr=CTc/postgres
(6 rows)


testdb=> \c onlypostgres
WARNING: Console code page (866) differs from Windows code page (1251)
          8-bit characters might not work correctly. See psql reference
          page "Notes for Windows users" for details.
You are now connected to database "onlypostgres" as user "testusr".
onlypostgres=> \d
              List of relations
  Schema |    Name    |   Type   |  Owner
--------+------------+----------+----------
  public | wtf        | table    | postgres
  public | wtf_id_seq | sequence | postgres
(2 rows)


onlypostgres=> # WHY THIS testusr is able to connect to all databases? In fact it is not granted for this.:(
onlypostgres(> \r
Query buffer reset (cleared).
onlypostgres=>

onlypostgres=> \! ping google.com

Обмен пакетами с google.com [173.194.71.113] по 32 байт:

Ответ от 173.194.71.113: число байт=32 время=432мс TTL=44
Ответ от 173.194.71.113: число байт=32 время=437мс TTL=44
Ответ от 173.194.71.113: число байт=32 время=437мс TTL=44
Ответ от 173.194.71.113: число байт=32 время=497мс TTL=44

Статистика Ping для 173.194.71.113:
     Пакетов: отправлено = 4, получено = 4, потеряно = 0 (0% потерь),
Приблизительное время приема-передачи в мс:
     Минимальное = 432мсек, Максимальное = 497 мсек, Среднее = 450 мсек
onlypostgres=> # I have also second problem.How i can prevent users to execute OS command?(I don't want users will be
ableto execute OS c 
ommands
onlypostgres'> \r
onlypostgres'># THANKS.
==========================END=======================

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

On 20 August 2012 19:34, Evil <evilofrevenge@hotmail.com> wrote:
> Hello List,
> First time here also beginner to Postgres.So please forgive me for any
> mistakes.
> I'm pretty sure i have same problem.=>
> http://archives.postgresql.org/pgsql-admin/2012-03/msg00105.php
> (After searching it i found it)
> However it is not solution for me.:( *I'm pretty sure i'm doing something in
> wrong manner*.
> After issusing that revoke from public my postgres user still able to
> connect to any database.
> More over
>  when executing \l user is able to see complete database names.
>
> So i have 2 questions:
> 1 ) How i can grant my user(s) to connect only to *granted* database not
> *any*
> 2 ) Users still able to execute OS (operation system) commands on system.
> This is a big security risk.How i can prevent it too.
>
> Any recommendations,manuals,helps,hints,RTFM :P are welcome;)

The postgres user is a database superuser.  Trying to prevent it from
connecting to databases is understandably impossible using the GRANT
and REVOKE system, but no end-user should ever connect to the database
cluster as a superuser.  Any operating system commands issued via
"unsafe" procedural languages are only run as the user the database
instance is running as, typically the user "postgres" on the OS, so it
has limited permissions by default.

But here's an example of how to prevent a typical user from connecting
to a database:

postgres=# create database test;
CREATE DATABASE
postgres=# create user test;
CREATE ROLE
postgres=# \c test test
You are now connected to database "test" as user "test".
test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# revoke connect on database test from public, test;
REVOKE
postgres=# \c test test
FATAL:  permission denied for database "test"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept

You can also set up further connection rules in pg_hba.conf:
http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

It will even allow you to prevent database superusers from logging in.

Regards

Thom

You appear to have replied directly to me rather than the list, so I've
cc'd the list.

On 08/21/2012 10:11 PM, Evil wrote:
> Dear Craig Ringer  And Dear Thom!
>
> THANK YOU VERY MUCH for such Great and easy explanation!
> Now everything seems is kk with grants.From now i think i understand
> how to separate grants on Postgres.
>
> @Craig Ringer
>
> Forgot about my
>  \! ping google.com
> thing) It is my Epic Fault xD
>
>
> Thanks to you both again for everything.
>