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

From: Evil <evilofrevenge(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Grant problem and how to prevent users to execute OS commands?
Date: 2012-08-20 18:34:14
Message-ID: BLU0-SMTP17797E7C6A07F0307CE7F96B8B90@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)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 able to execute OS c
ommands
onlypostgres'> \r
onlypostgres'># THANKS.
==========================END=======================

Responses

Browse pgsql-general by date

  From Date Subject
Next Message elliott 2012-08-20 18:46:24 Re: Database Bloat
Previous Message John R Pierce 2012-08-20 18:10:06 Re: Database Bloat