LINUX.ORG.RU
Форум — Development  

Отзыв прав пользователю postgresql на чтение системных таблиц

 


0

1

Всем привет!

Выдаю права на чтение системных таблиц для пользователя postgresql так: 

psql -U postgres -d userdb -c \"GRANT SELECT ON pg_settings TO user;\"

В результате получилась такая таблица выданных прав: 

userdb=> SELECT * FROM information_schema.role_table_grants;
 grantor  | grantee  | table_catalog | table_schema | table_name  | privilege_type | is_grantable | with_hierarchy 
----------+----------+---------------+--------------+-------------+----------------+--------------+----------------
 postgres | user | userdb    | pg_catalog   | pg_settings | SELECT         | NO           | YES
 postgres | user | userdb    | pg_catalog   | pg_shadow   | SELECT         | NO           | YES
 postgres | user | userdb    | pg_catalog   | pg_authid   | SELECT         | NO           | YES
 postgres | user | userdb    | pg_catalog   | pg_database | SELECT         | NO           | YES
(4 rows)

Как правильно отозвать права пользователю postgresql на чтение системных таблиц ?

userdb=> REVOKE SELECT FROM information_schema.role_table_grants WHERE table_name='pg_settings' ;
ERROR:  syntax error at or near "."
LINE 1: REVOKE SELECT FROM information_schema.role_table_grants WHER...
                                             ^
userdb=> REVOKE SELECT ON information_schema.role_table_grants WHERE table_name='pg_settings' FROM user ;
ERROR:  syntax error at or near "WHERE"
LINE 1: ...KE SELECT ON information_schema.role_table_grants WHERE tabl...
                                                             ^
userdb=> REVOKE SELECT ON information_schema.role_table_grants WHERE table_name='pg_settings' FROM 'user' ;
ERROR:  syntax error at or near "WHERE"
LINE 1: ...KE SELECT ON information_schema.role_table_grants WHERE tabl...
                                                             ^
userdb=> REVOKE SELECT FROM information_schema.role_table_grants WHERE table_name='pg_settings' ;
ERROR:  syntax error at or near "."
LINE 1: REVOKE SELECT FROM information_schema.role_table_grants WHER...
← python прикрутить enconv
Busybox, linuxrc reboot →

Так вы даёте права на таблицу pg_setting, а отнять пытаетесь у role_table_grants почему-то.

bff7755a
()
Ответ на: комментарий от Deleted

Это нужно делать от юзера postgres? или нет? Потому что от обычного юзера не получается. 


userdb=> REVOKE SELECT ON pg_settings FROM user;
WARNING:  no privileges could be revoked for "pg_settings"
WARNING:  no privileges could be revoked for column "name" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "setting" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "unit" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "category" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "short_desc" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "extra_desc" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "context" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "vartype" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "source" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "min_val" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "max_val" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "enumvals" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "boot_val" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "reset_val" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "sourcefile" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "sourceline" of relation "pg_settings"
WARNING:  no privileges could be revoked for column "pending_restart" of relation "pg_settings"
REVOKE
userdb=> SELECT * FROM information_schema.role_table_grants;
 grantor  | grantee  | table_catalog | table_schema | table_name  | privilege_type | is_grantable | with_hierarchy 
----------+----------+---------------+--------------+-------------+----------------+--------------+----------------
 postgres | user | userdb    | pg_catalog   | pg_settings | SELECT         | NO           | YES
 postgres | user | userdb    | pg_catalog   | pg_shadow   | SELECT         | NO           | YES
 postgres | user | userdb    | pg_catalog   | pg_authid   | SELECT         | NO           | YES
 postgres | user | userdb    | pg_catalog   | pg_database | SELECT         | NO           | YES
(4 rows)

chemtech ★
() автор топика
Ответ на: комментарий от Deleted 
 ➤ psql://postgres@postgresql-96.local:5432/postgres 

# SELECT * FROM information_schema.role_table_grants WHERE grantee='user';
 grantor  | grantee  | table_catalog | table_schema | table_name  | privilege_type | is_grantable | with_hierarchy 
----------+----------+---------------+--------------+-------------+----------------+--------------+----------------
 postgres | user | postgres      | pg_catalog   | pg_roles    | SELECT         | NO           | YES
 postgres | user | postgres      | pg_catalog   | pg_user     | SELECT         | NO           | YES
 postgres | user | postgres      | pg_catalog   | pg_database | SELECT         | NO           | YES
 postgres | user | postgres      | pg_catalog   | pg_shadow   | SELECT         | NO           | YES
 postgres | user | postgres      | pg_catalog   | pg_class    | SELECT         | NO           | YES
 postgres | user | postgres      | pg_catalog   | pg_authid   | SELECT         | NO           | YES
(6 rows)

Time: 7.902 ms

 ➤ psql://postgres@postgresql-96.local:5432/postgres 

# REVOKE SELECT ON pg_roles FROM user;
REVOKE
Time: 4.793 ms

 ➤ psql://postgres@postgresql-96.local:5432/postgres 

# SELECT * FROM information_schema.role_table_grants WHERE grantee='user';
 grantor  | grantee  | table_catalog | table_schema | table_name  | privilege_type | is_grantable | with_hierarchy 
----------+----------+---------------+--------------+-------------+----------------+--------------+----------------
 postgres | user | postgres      | pg_catalog   | pg_user     | SELECT         | NO           | YES
 postgres | user | postgres      | pg_catalog   | pg_database | SELECT         | NO           | YES
 postgres | user | postgres      | pg_catalog   | pg_shadow   | SELECT         | NO           | YES
 postgres | user | postgres      | pg_catalog   | pg_class    | SELECT         | NO           | YES
 postgres | user | postgres      | pg_catalog   | pg_authid   | SELECT         | NO           | YES
(5 rows)

Time: 4.098 ms

 ➤ psql://postgres@postgresql-96.local:5432/postgres 

# REVOKE SELECT ON pg_user FROM user;
REVOKE
Time: 5.617 ms

 ➤ psql://postgres@postgresql-96.local:5432/postgres 

# REVOKE SELECT ON pg_database FROM user;
REVOKE
Time: 4.882 ms

 ➤ psql://postgres@postgresql-96.local:5432/postgres 

# REVOKE SELECT ON pg_shadow FROM user;
REVOKE
Time: 5.852 ms

 ➤ psql://postgres@postgresql-96.local:5432/postgres 

# REVOKE SELECT ON pg_class FROM user;
REVOKE
Time: 5.685 ms

 ➤ psql://postgres@postgresql-96.local:5432/postgres 

# REVOKE SELECT ON pg_authid FROM user;
REVOKE
Time: 7.061 ms

 ➤ psql://postgres@postgresql-96.local:5432/postgres 

# SELECT * FROM information_schema.role_table_grants WHERE grantee='user';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)

Time: 4.865 ms

 ➤ psql://postgres@postgresql-96.local:5432/postgres
chemtech ★
() автор топика
Ответ на: комментарий от chemtech 
psql -h postgresql-96.local -p 5432 -U user -d userdb
Password for user user: 
psql (9.6.6)
Type "help" for help.

userdb=> SELECT * FROM information_schema.role_table_grants;
 grantor  | grantee  | table_catalog | table_schema | table_name  | privilege_type | is_grantable | with_hierarchy 
----------+----------+---------------+--------------+-------------+----------------+--------------+----------------
 postgres | user | userdb    | pg_catalog   | pg_settings | SELECT         | NO           | YES
 postgres | user | userdb    | pg_catalog   | pg_shadow   | SELECT         | NO           | YES
 postgres | user | userdb    | pg_catalog   | pg_authid   | SELECT         | NO           | YES
 postgres | user | userdb    | pg_catalog   | pg_database | SELECT         | NO           | YES
(4 rows)
chemtech ★
() автор топика
Ответ на: комментарий от chemtech

Т.е. от пользователя postgres сделал отзыв прав.

Но эти права я вижу если зайти от пользователя user

И от юзера удалить их не могу

chemtech ★
() автор топика
Ответ на: комментарий от Deleted

Права выдавал сначала вот так:

postgres -c \"GRANT SELECT ON pg_settings TO user;

А потом вот так:

postgres -d userdb -c \"GRANT SELECT ON pg_settings TO user;
chemtech ★
() автор топика
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.
← python прикрутить enconv
Development
Busybox, linuxrc reboot →