Pg Role
main tips stats roles efm_failover

GRANTS di una tabella

\c <database>
\dp <tabella>
\c MNP  
\prompt 'Table name? ' tabella
select a.tablename,b.usename,HAS_TABLE_PRIVILEGE(usename,tablename, 'select') as select,
  HAS_TABLE_PRIVILEGE(usename,tablename, 'insert') as insert,
  HAS_TABLE_PRIVILEGE(usename,tablename, 'update') as update,
  HAS_TABLE_PRIVILEGE(usename,tablename, 'delete') as delete, 
  HAS_TABLE_PRIVILEGE(usename,tablename, 'references') as references
from pg_tables a , pg_user b 
where a.tablename= :'tabella';

DDL

Ruoli (utenti)

pg_dumpall  -U enterprisedb -r -f roles.ddl

GRANTS di tuttte le tabelle

(mi sembra di sistema)

SELECT  rolname, relname, relnamespace, relkind, tbspace.spcname,
        has_table_privilege(rolname, pg_class.oid, 'SELECT') as SELECT, 
        has_table_privilege(rolname, pg_class.oid, 'INSERT') as INSERT, 
        has_table_privilege(rolname, pg_class.oid, 'UPDATE') as UPDATE, 
        has_table_privilege(rolname, pg_class.oid, 'DELETE') as DElETE 
  FROM pg_roles 
  CROSS JOIN pg_class
  INNER JOIN pg_tablespace as tbspace ON pg_class.reltablespace = tbspace.oid
 WHERE pg_has_role(rolname, rolname, 'MEMBER') 
 AND relkind IN ('r', 'v', 'm', 't', 'f')
 ORDER BY rolname, relname;

altra query sulle GRANTS

Attenzione, il 10 potrebbe cambiare รจ il susperuser

SELECT rolname, relname, relowner,    
        has_table_privilege(rolname, pg_class.oid, 'SELECT') as SELECT, 
        has_table_privilege(rolname, pg_class.oid, 'INSERT') as INSERT, 
        has_table_privilege(rolname, pg_class.oid, 'UPDATE') as UPDATE, 
        has_table_privilege(rolname, pg_class.oid, 'DELETE') as DElETE 
  FROM pg_roles 
  CROSS JOIN pg_class
  INNER JOIN pg_type as c ON pg_class.reltype = c.oid
WHERE pg_has_role(rolname, rolname, 'MEMBER')
and relowner <> 10
ORDER BY rolname, relname;

Associare un ruolo ad un user (esistente/nuovo)

alter role prova with login password 'prova123' in role "DBMNP";
 
create role prova with login password 'prova123' in role "DBMNP";

Droppare un ruolo

\c <database>
reassign owned by <rolename> to <other_rolename>;
revoke all privileges on all tables in schema public from <rolename>;
drop role <rolename>;

Privilegio SELECT nel db di un ROLE

SELECT 
        schemaname||'.'||tablename obj
    FROM 
        pg_tables 
    WHERE 
        has_table_privilege (
            'DBMNP',
            schemaname||'.'||tablename, 
            'select'
         ) 
    AND 
        schemaname NOT IN ( 
            'pg_catalog',
            'information_schema'
         );

TABLE get users and their privs

\c MNP  
\prompt 'Table name? ' tabella
select a.tablename,b.usename,HAS_TABLE_PRIVILEGE(usename,tablename, 'select') as select,
  HAS_TABLE_PRIVILEGE(usename,tablename, 'insert') as insert,
  HAS_TABLE_PRIVILEGE(usename,tablename, 'update') as update,
  HAS_TABLE_PRIVILEGE(usename,tablename, 'delete') as delete, 
  HAS_TABLE_PRIVILEGE(usename,tablename, 'references') as references
from pg_tables a , pg_user b 
where a.tablename= :'tabella';
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License