Re: [GENERAL] Grant problem and how to prevent users to execute OS commands?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Grant problem and how to prevent users to execute OS commands?
On 20 August 2012 19:34, Evil evilofreve...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
Re: [GENERAL] Grant problem and how to prevent users to execute OS commands?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
