Conexio remota.sql

Fitxer /var/lib/pgsql/data/postgresql.conf :

-	listen_adresses='*'
-	port=5432
	(les dues anteriors descomentades)

Fitxer /var/lib/pgsql/data/pg_hba.conf:
	(al apartat IPv4, afegir:)
-		host 	all 	all 	192.168.3.4/32 		trust
	(amb /32 només pot entrar el propi pc)
	(amb /24 tota la red (192.168.3.*) (tota l´aula))

( CAL FER RESTART DE POSTGRES)
-	systemctl restart postgresql.service


Ja podem accedir:
-	psql -h 192.168.3.9 -p 5432 -U usuari nom_db;


-- Info extra pg_hba.conf
Last column specifies which authentication method will be used.

    md5 — client has to supply password processed with MD5 algorithm
    ident — obtain user name of connecting client from operating system and consult it with specified map
    trust — anyone who is able to connect to PostgreSQL server may act as any user without supplying password
    peer — obtains user´s name from operating system and checks if it matches database user name 

Instalacio-Postgres.sql

Entrar a root

yum -y install postgresql-server 

postgresql-setup initdb

systemctl start postgresql

systemctl enable postgresql

-------------- DESINSTALAR -------------------
Entrar a root

systemctl stop postgresql.service

yum -y remove postgresql-server postgresql

rm -rf /var/lib/pgsql/data

Transaccions.sql

BEGIN;
comanda/es;
SAVEPOINT nom_savepoint; -- Guarda un punt al que podem tornar
comanda/es;
ROLLBACK TO nom_savepoint; -- Torna a l'estat anterior de nom_savepoint
comanda/es;
COMMIT; -- per finalitzar la transacció i guardar tot el que hem fet fins ara
ROLLBACK; -- Sense especificar a on, desfá TOT fins a l'estat anterior al BEGIN
Si hi a algún error durant la transacció, es cancela tot.

Usuaris.sql

-- Creació d´usuari:
create user nom_usuari permisos; -- on permisos coloquem els permisos.
create user nom_usuari createdb createuser createrole;

-- eliminació usuari:
drop user nom_usuari;

-- Creació de rol:
create role nom_rol createdb nologin;

-- Assignar permisos concrets a rols:
grant permisos on nom_taula to nom_rol;
grant INSERT, update, delete on cliente to nom_rol;
grant select, update, delete on all tables in schema public to nom_rol; -- Assigna els permisos especificats a totes les taules de la bbdd on som.

--Assignar rol a usuari:
grant nom_rol TO nom_usuari;

-- El propi usuari (nom_usuari) pot ficar-se el rol:
set role nom_rol;
-- I treure'ls per quedar-se amb el seu inicial de usuari:
reset role;

-- Treure rol a usuari:
revoke nom_rol FROM nom_usuari;

Vistes.sql

Vista simple: -- Sense joins ni coses rares, directament un select

create or replace view nom_vista
as select repcod, nombre, edad, ofinum
from repventa 
where ofinum = 12;
-- with local check option; -> perque comprovi segons el select si es poden fer inserts, updates i deletes. (a partir de postgres 9.4)


Vista complexa: -- Amb joins i mes

create or replace view cap as
select r.repcod as codi,r.nombre as nom, r.ofinum as oficina ,count(r2.repcod) as representants
from repventa r, repventa r2
where r.repcod=r2.jefe
group by r.repcod;


-- Exemples exercicis:


/*
create view emp30
as select empno, ename, sal, deptno
from emp
where deptno=30
with local check option;

Amb la vista emp30 (amb cláusula with check option) comprova les següents operacions i indica si es poden fer o no :

    Select
    Insert
    Update
    Delete 
*/

Es pot fer tot, sempre que cumpleixi el requisits del where.

-- On s´emmagatzema la informació?

A la taula emp.

-- Com puc veure la definició de la vista?

select *
from pg_views
where viewname = 'emp30';

-- Com puc veure les vistes de l´usuari?

select *
from pg_views
where viewowner = current_user;
--where viewowner = 'nom_usuari';

--Creeu sobre la BBDD training la vista cap. La seva estructura serà codi, nom, oficina i representants (que hi contendrà el nombre d´empleats al seu càrrec)
--Els noms de la vista o els camps no es poden canviar.

create or replace view cap as
select r.repcod as codi,r.nombre as nom, r.ofinum as oficina ,count(r2.repcod) as representants
from repventa r, repventa r2
where r.repcod=r2.jefe
group by r.repcod;

-------- Altres:

create or replace view comandes_oest as
select pednum as "Codi", fecha as "Data", c.nombre "Client", r.nombre "Representat", r.ofinum "Codi oficina", o.ciudad "Ciutat"
from pedido ped, cliente c, repventa r, oficina o
where ped.cliecod = c.cliecod and ped.repcod = r.repcod and r.ofinum = o.ofinum and lower(o.region) = 'oeste';