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 able to execute OS c ommands onlypostgres'> \r onlypostgres'># THANKS. ==========================END======================= -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
