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

Поиск
Список
Период
Сортировка
От Evil
Тема Grant problem and how to prevent users to execute OS commands?
Дата
Msg-id BLU0-SMTP17797E7C6A07F0307CE7F96B8B90@phx.gbl
обсуждение исходный текст
Ответы Re: Grant problem and how to prevent users to execute OS commands?  (Craig Ringer <ringerc@ringerc.id.au>)
Re: Grant problem and how to prevent users to execute OS commands?  (Thom Brown <thom@linux.com>)
Список pgsql-general
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=======================




В списке pgsql-general по дате отправления:

Предыдущее
От: Sébastien Lorion
Дата:
Сообщение: Amazon High I/O instances
Следующее
От: Thalis Kalfigkopoulos
Дата:
Сообщение: Re: Different results from view and from its defintion query [w/ windowing function]