löschen von usern die nicht benötigt werden

M

mickyturbo

Fleissiger Benutzer
Beiträge
54
Hi,

möchte gerne einen User names odb löschen.

Bekomme die Fehlermeldung:

Could not drop the role. FEHLER: kann Rolle „odb“ nicht löschen, weil andere Objekte davon abhängen DETAIL: 2 Objekte in Datenbank bdk

wenn ich mir allerdings die Tabellen anschaue von DB bdk sieht man das owner immer Postgres ist und nicht odb

bdk# \dt

Liste der Relationen

Schema | Name | Typ | Eigentümer

--------+---------------------------+---------+------------

public | __software_licenses_BAK | Tabelle | posters
public | _def_adaptertypes | Tabelle | postgres
public | _def_equipmenttypes | Tabelle | postgres
public | _def_softwaretypes | Tabelle | postgres
public | _std_sw | Tabelle | postgres
public | com | Tabelle | postgres
public | com_equipment | Tabelle | postgres
public | com_smartphone | Tabelle | postgres
public | comdata_simcard | Tabelle | postgres
public | equipment | Tabelle | postgres
public | equipment_net_old | Tabelle | postgres
public | equipmentdata_biospw | Tabelle | postgres
public | equipmentdata_inv | Tabelle | postgres
public | equipmentdata_imei | Tabelle | postgres
public | equipmentdata_login | Tabelle | postgres
public | equipmentdata_name | Tabelle | postgres
public | equipmentdata_priv_name | Tabelle | postgres
public | equipmentdata_room | Tabelle | postgres
public | equipmentdata_screenspecs | Tabelle | posters
public | equipmentdata_storage | Tabelle | postgres
public | exchainge_login | Tabelle | postgres
public | net_ipaddresses | Tabelle | postgres
public | net_macaddresses | Tabelle | postgres
public | net_routes | Tabelle | postgres
public | office_locations | Tabelle | postgres
public | office_netsockets | Tabelle | postgres
public | office_rooms | Tabelle | postgres
public | patchpanel_equipment | Tabelle | postgres
public | software_licenses | Tabelle | postgres
public | software_licenses_av | Tabelle | postgres
public | software_licenses_bp | Tabelle | postgres
public | software_licenses_ca | Tabelle | postgres
public | software_licenses_cl | Tabelle | postgres
public | software_licenses_hp | Tabelle | postgres
public | software_licenses_ma | Tabelle | postgres
public | software_licenses_me | Tabelle | postgres
public | software_licenses_mv | Tabelle | postgres
public | software_licenses_of | Tabelle | postgres
public | software_licenses_os | Tabelle | postgres
public | software_licenses_pe | Tabelle | postgres
public | software_licenses_sm | Tabelle | postgres
public | software_licenses_vp | Tabelle | postgres
public | software_licenses_zp | Tabelle | postgres
public | software_main | Tabelle | postgres
public | user_software_licenses | Tabelle | postgres
public | users | Tabelle | postgres
public | users_equipment | Tabelle | postgres
(47 Zeilen)

dank im voraus.
 
Werbung:
mickyturbo schrieb:
wenn ich mir allerdings die Tabellen anschaue von DB bdk sieht man das owner immer Postgres ist und nicht odb

bdk# \dt
Zum Vergrößern anklicken....

\dt arbeitet intern so:

Code: 
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Man beachte das "AND pg_catalog.pg_table_is_visible(c.oid)" in der Where-Condition. pg_table_is_visible() prüft, ob eine Tabelle dem aktuellen search_path entsprechend sichtbar ist. Du könntest also diese Abfrage oben ohne diese eine Condition noch mal ausführen und würdest dann vermutlich sehen, welche Tabellen Dich interessieren ;-)
 
Code: 
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND n.nspname !~ '^pg_toast'
ORDER BY 1,2;

habe die conditon raus genommen, "AND pg_catalog.pg_table_is_visible(c.oid)"

allerdings kommt die gleiche Ausgabe wie oben.

oder habe ich was falsch verstanden ?

danke
 
Hattest Du schon richtig verstanden. Bist Du in der richtigen Datenbank?

Was sagt ein:

Code: 
select * from pg_class where relowner = (select usesysid from pg_user where usename = 'odb');
 
Sie sind jetzt verbunden mit der Datenbank „bdk“ als Benutzer „postgres“.

select * from pg_class where relowner = (select usesysid from pg_user where usename = 'odb');

bdk =# relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relispopulated | relreplident | relfrozenxid | relminmxid | relacl | reloptions
bdk-# ---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------------+--------------+--------------+------------+--------+------------
 
Werbung:
Zurück
Oben