Thread: Grant problem and how to prevent users to execute OS commands?
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. >