postgresql – Postgressql role drop

I am having the same issue dropping a role. Did so much research on google but no help.

SQL Error [2BP01]: ERROR: role “readonly” cannot be dropped because some objects depend on it Detail: privileges for default privileges on new relations belonging to role rohit in schema public

Here is what I did to drop role readonly.

REVOKE USAGE ON SCHEMA public FROM readonly;
REVOKE ALL ON SCHEMA public FROM readonly;


REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM readonly;

  
REVOKE CONNECT ON DATABASE kozuchi_db FROM readonly;
REVOKE CONNECT ON DATABASE parc_prod FROM readonly;
REVOKE CONNECT ON DATABASE postgres FROM readonly;


DROP USER readonly;

postgresql – Error en el IF EXISTS en postgresSQL: “ERROR: la consulta no tiene un destino para los datos de resultado”

Me dice que las consultas dentro del if necesitan Return, pero solo quiero verificar si existe un valor similar dentro de una tabla en la BD, los valores que quiero retornar ya están con su return query en la parte inferior y ya lo intenté usando el INTO asignándole una variable.

He estado tratando de validar si existen registros similares en algunas de las tablas, dado que mi consulta lo que hace es insertar en mi tabla paciente en mi aplicación y retornar un valor para actualizar mi tabla de pacientes, pero me da el error en los IF, pues necesita que retorne un valor después del select, lo había intentado con los IF EXISTS pero me da el mismo error.
Además intenté declarar 2 valores, pero al parecer debo retornar algo después de la consulta en el IF

create function fnNuevRegistro
(input_xml xml)
returns table(validX text)
as
$$
DECLARE root_xpath TEXT = '/MascotaREG/';
begin 

IF (select count(*) from especie_paciente where 
nombrevulgarespecie = ((xpath(root_xpath||'especieM/text()', input_xml))(1))::text) >0
THEN select '1';
else                             
insert into especie_paciente (especiepacienteid, nombrevulgarespecie) 
values ((select count(*) from especie_paciente)::int +1, 
((xpath(root_xpath||'especieM/text()', input_xml))(1)::text));
                  end IF;


        
if(select count(*) from raza_especie where nombreraza =
((xpath(root_xpath||'raza/text()', input_xml))(1))::text) >0
      THEN select '1';
       else
       insert into raza_especie (razaespecieid, nombreraza) values
       ((select count(*) from raza_especie)::int +1,
((xpath(root_xpath||'raza/text()', input_xml))(1))::text);
        end IF;
insert into paciente values 
((select count(*) from paciente)::int +1,
((xpath(root_xpath||'nombreM/text()',input_xml))(1))::text,
case when ((xpath(root_xpath||'generoM/text()',input_xml))(1))::text= 'Masculino' then 'M' else 'F' end,
cast( ((xpath(root_xpath||'edadM/text()',input_xml))(1))::text as integer ) ,
(select cl.clienteid from persona p inner join cliente cl on cl.personaid = p.personaid
where p.email = ((xpath(root_xpath||'usuarioLg/text()', input_xml))(1))::text),
(select especiepacienteid from especie_paciente where nombrevulgarespecie = 
 ((xpath(root_xpath||'especieM/text()', input_xml))(1))::text),
(select razaespecieid from raza_especie where nombreraza = 
 ((xpath(root_xpath||'raza/text()',input_xml))(1))::text
), 'FOTO'
);
return query
select case when nombrepaciente =
((xpath(root_xpath||'nombreM/text()', input_xml))(1))::text then 'Bien' 
else 'Mal' end as ValidadorX from paciente
where nombrepaciente =((xpath(root_xpath||'nombreM/text()', input_xml))(1));          
end;
$$
LANGUAGE 'plpgsql'

Salida
ERROR: la consulta no tiene un destino para los datos de resultado.
Hint: Si quiere descartar los resultados de un SELECT, utilice PERFORM.

Read the XML field in PostgresSQL

The code

SELECT xmltable. *
FROM development.tbl_x tx,
XMLTABLE (& # 39; / Proof / Concepts / Concept & # 39; PASSING x
COLUMNS
id FOR ORDINALITY,
ClaveProdServ Text PATH & # 39; @ ClaveProdServ & # 39; NOT zero,
Quantity text path & # 39; @ Quantity & # 39 ;,
NoIdentification text PATH & # 39; @ NoIdentification & # 39; NOT zero,
UnitKey-Text PATH & # 39; @ UnityKey & # 39; NOT zero,
Description text PATH & # 39; @ Description & # 39; NOT zero,
UnitValue text PATH & # 39; @ UnitValue & # 39; NOT null,
Import text PATH & # 39; @ Import & # 39; NOT zero,
Discount text PATH & # 39; @ Discount & # 39; NOT zero

            );

XML

    
        
        
        
            
            
            
            
            
            
            
            
        

    

The result

I should return the full table

postgresql – PostgresSQL – Limit list access when using dbeaver or pgadmin

I have a scheme in Postgres called – public

If I log in via dbeaver or pgadmin, I can go database > schema > public > tables and it shows me the list of all the tables that are in the public schema.

Is there any way to create a user named bob and grant access to the public schema but only 5 tables in it?

When Bob logs in to postgres with his password, he can go database > schema > public > tables and display ONLY 5 tables. If he looks in information_schema or other Postgres-related tables and finds that there are other tables, that's fine. In dbeaver or pgadmin, however, he should only be able to display 5 tables.

If Bob selects on another table, that's fine too.

postgresql – Restrict access to my PostgresSQL / Post-GIS database used through QGIS

We have a small network of users who access our QGIS data, which is stored in a PostGIS database (in 3 schemes) located on my local drive in my computer. This drive is then shared with the others. Since the other users are beginners in the GIS world, I created a series of layer files (.qlr) for them that refer to the data in my PostGIS database. You can only see these layer files.

But I just found that if you use these layer files, you can still edit, add, and remove the data.

How do I restrict this?

Is there an easy way in QGIS or even Windows? (Setting shared folder permissions doesn't seem to work.)

The database was named & # 39; pgAdmin 4 & # 39; I tried to add another user / role with different permissions but I don't seem to be able to make this work. Access appears to be completely denied or everything is still open.

The rights that I can change seem to be too high in the hierarchy. All I have to limit is the ability to write and delete the schemas and tables, with the editing rights.

I have no idea how to write in SQL, I'm a geographer and not IT. There is no database administrator here, just me and I created this database by simply following the steps on the pgAdmin help pages and having some luck. So I'm looking for foolproof help.

postgresql – To move a PostgresSQL / PostGIS to another drive

First, I want to say that I know little about databases (GIS background) and that I may have bluffed everything so far.
However, we wanted to set up a database for use with QGIS.
My understanding was that PostgresSQL created the server and PostGIS add-on the databases on this server.
I do not work with a network or dedicated servers. I have a C drive and a Q drive. We have installed PostgresSQL (Server?) And PostGIS (Database?) On my Q drive, where I have created and edited all my data.
But now we want to move everything to a portable drive (E) so others can access the database by plugging it into the drive. Is this possible?

and if so, how does anyone do that understands neither databases nor SQL?

cheers
Matthew