PostGreSQL: Unterschied zwischen den Versionen
Zur Navigation springen
Zur Suche springen
(→Rollen) |
|||
Zeile 67: | Zeile 67: | ||
# Zugriff für DB erlauben: | # Zugriff für DB erlauben: | ||
GRANT CREATE,TEMPORARY,TEMP ON DATABASE dbname TO joe; | GRANT CREATE,TEMPORARY,TEMP ON DATABASE dbname TO joe; | ||
GRANT ALL ON | GRANT ALL PRIVILEGES ON DATABASE vanadium TO joe; | ||
</pre> | </pre> | ||
<pre> | <pre> |
Version vom 22. Januar 2023, 10:06 Uhr
Links
- https://gis-ops.com/postgrest-postgis-api-tutorial-geospatial-api-in-5-minutes/
- https://postgis.net/docs/reference.html
SQL-Tipps
Tabellenänderungen
alter table mytable rename table_length to tablesize;
alter table mytable alter table_group type varchar(128);
alter table mytable alter table_reference type int using asset_no::integer;
Reguläre Ausdrücke
select * from polygons where group similar to '[0-9]+';
select * from polygons where group similar to '(Gruppe|Group)[0-9]{2,4}%';
- '.' ist kein Metazeichen, benutze '%' und '_'
- '%' ist Ersatz für .*, '_' für '.'
- Keine Metazeichen für Anfang und Ende nötig, da Muster immer am Anfang gesucht wird und immer komplett verglichen wird.
DB anlegen
createdb dbtest
Mit DB arbeiten
- Programm: /usr/bin/psql dbtest
- Oder: sudo -u postgres psql postgres
CREATE DATABASE geodb OWNER = forum; # Datenbanken auflisten: \list ODER \l # Relationen auflisten: \dt # Relationen mit mehr Infos: \dt+ # Definition der Table xxx: \d xxx # Mit DB yyy verbinden: \c yyy # Alternative: Tabellen auflisten: SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'; # Benutzer auflisten: \du ODER \du+
Rollen
- Eine Rolle kann als "Gruppe" oder als "Benutzer" verwendet werden.
- Ein Benutzer ist eine Rolle mit Attribut LOGIN.
- "CREATE USER" ist ein Alias von "CREATE ROLE" mit gesetztem "LOGIN"-Attribut.
sudo -u postgres psql postgres
SELECT rolname FROM pg_roles; CREATE ROLE root WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD 'NoOneKnows'; CREATE ROLE dbadm WITH CREATEDB CREATEROLE LOGIN PASSWORD 'UnknownPw'; CREATE ROLE forum WITH CREATEDB LOGIN PASSWORD 'ThinkAbout'; CREATE ROLE dbusr WITH CREATEDB; # Der Gruppe dbusr zuordnen: GRANT dbusr TO dbadm, forum, postgres; # Zugriff in Datenbank erlauben GRANT SELECT,INSERT,UPDATE,DELETE,RULE,REFERENCES,TRIGGER,CREATE,TEMPORARY,EXECUTE,USAGE ON mytable TO joe; # Zugriff für DB erlauben: GRANT CREATE,TEMPORARY,TEMP ON DATABASE dbname TO joe; GRANT ALL PRIVILEGES ON DATABASE vanadium TO joe;
# Benutzer auflisten: sudo -u postgres psql postgres <<EOS \du+ EOS # Superuser dba und Gruppe admins anlegen. USER=dba GROUP=admins CODE=TopSecret sudo -u postgres psql postgres <<EOS CREATE ROLE $GROUP WITH SUPERUSER CREATEDB CREATEROLE; CREATE ROLE $USER WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD '$CODE'; GRANT $GROUP TO $USER; EOS # Readonly-Admin dbbup mit Gruppe roadmins anlegen: USER=dbbup GROUP=roadmins CODE=TopSecret sudo -u postgres psql postgres <<EOS CREATE ROLE $GROUP; CREATE ROLE $USER WITH LOGIN PASSWORD '$CODE'; GRANT $GROUP TO $USER; GRANT USAGE ON SCHEMA public TO $GROUP; EOS # DB pgdb anlegen mit Benutzer dbuser und Gruppe pgdb: DB=pgdb USER=dbuser GROUP=g_$DB CODE=TopSecret sudo -u postgres psql postgres <<EOS CREATE ROLE $GROUP; CREATE ROLE $USER WITH LOGIN PASSWORD '$CODE'; GRANT $GROUP TO $USER, admins; CREATE DATABASE $DB OWNER = $GROUP; GRANT ALL ON $DB TO $GROUP; GRANT SELECT ON $DB TO roadmins; GRANT CONNECT ON DATABASE $DB TO roadmins; EOS
Eigentümer ändern
ALTER database xy OWNER TO dbusr; ALTER scheme public OWNER TO dbusr; ALTER TABLE table1 OWNER TO dbusr;
Benutzer anlegen
- pgadmin3 aufrufen
- Login-Rollen: neue Rolle eintragen
- SQL:
- CREATE ROLE jonny LOGIN ENCRYPTED PASSWORD 'md582dfb88f80303e0d0e2ffdbdc9e82b64' SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
Geometry-Variante
- EPSG Projection 4326 - WGS 84
- https://spatialreference.org/ref/epsg/4326/
- Verwendung: Horizontal component of 3D system. Used by the GPS satellite navigation system and for NATO military geodetic surveying.
- Bereich: -180.0000, -90.0000, 180.0000, 90.0000
- EPSG:3857
- https://spatialreference.org/ref/sr-org/6864/
- Verwendung: Openstreetmap
- Bereich: 0, 10E7
Tabelle mit GIS-Daten anlegen
CREATE EXTENSION IF NOT EXISTS plpgsql; CREATE EXTENSION postgis; CREATE TABLE public.geodb ( id SERIAL PRIMARY KEY, name varchar(64), count INT, changed TIMESTAMP NULL, geom geography(POLYGON,3857) ); CREATE INDEX geodb_geom_idx ON geodb USING GIST (geom); ALTER TABLE public.geodb OWNER TO dbusr; select distinct(st_isvalid(geom)) from geodb; INSERT into geodb (name, geom) VALUES ('3-Eck', ST_GeomFromEWKT('SRID=4326;POLYGON((10.4267776 47.8347264, 10.4277776 47.8357264,10.4287776 47.8367264,10.4287776 47.8357264,10.4267776 47.8347264))'), ('4-Eck', ST_GeomFromEWKT('SRID=4326;POLYGON((10.4267776 47.8347264, 10.4277776 47.8357264,10.4287776 47.8367264,10.4287776 47.8367264,10.4297776 47.8357264,10.4267776 47.8347264))') ;
- Hinweis: Polygon mit 1000 Ecken erfolgreich eingetragen.
SRID für Spalte ändern
ALTER TABLE roads ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 3857) USING ST_Transform(ST_SetSRID(geom,4326),3857) ;
Abfragen der Geometry
# Objekte mit maximalem Abstand von 1000000: SELECT name FROM geodb WHERE ST_DWithin(geom, 'SRID=4326;POINT(-110 29)'::geography, 1000000); -- Distance calculation using GEOGRAPHY SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)'::geography); select ST_AsText(geom) froom geodb; -- Display with maximal float precision of 2 (digits behind the dot) select ST_AsText(polygon_gis, 1) from polygons where polygon_id=83; # POLYGON((727382.1 5432550.3,727389.8 5432536.6,727403.4...)) select ST_AsEWKT(polygon_gis) from polygons where polygon_id=83; # SRID=3035;POLYGON((727382.11 5432550.29,727389.78 ....))
Spaltentyp GEOMETRYCOLLECTION
GEOMETRYCOLLECTION(POINT(4 6),LINESTRING(4 6,7 10)) POINT ZM (1 1 5 60) POINT M (1 1 80) POINT EMPTY MULTIPOLYGON EMPTY TRIANGLE((0 0 0,0 1 0,1 1 0,0 0 0)) TIN (((0 0 0, 0 0 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 0 0 0))) POLYHEDRALSURFACE Z ( PATCHES ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 0 1 0, 0 1 1, 0 0 1, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)), ((1 1 1, 1 0 1, 0 0 1, 0 1 1, 1 1 1)), ((1 1 1, 1 0 1, 1 0 0, 1 1 0, 1 1 1)), ((1 1 1, 1 1 0, 0 1 0, 0 1 1, 1 1 1)) )
Sequenz (Primary Key) ändern
ALTER SEQUENCE sysflags_sysflag_id_seq RESTART WITH 1100;
Installation
Interface ändern
- vi /etc/postgresql/13/main/postgresql.conf
listen_addresses = '10.10.100.100,localhost'
Zugriff von anderen Hosts erlauben
- vi /etc/postgresql/13/main/pg_hba.conf
host all all 127.0.0.1/32 trust host all all 10.10.100.151/32 trust
Debian
adduser -uid 251 postgres
PG_VERSION=13
apt install postgresql-$PG_VERSION postgresql-$PG_VERSION-postgis-3
passwd postgres
# Cluster festlegen:
DB_BASE=/data/postgresql
POST_BIN=/usr/lib/postgresql/$PG_VERSION/bin
LOGFILE=/var/log/local/postgres.log
test -d $DB_BASE || mkdir $DB_BASE
chown postgres $DB_BASE
sudo -u postgres $POST_BIN/initdb -D $DB_BASE
- Änderungen in /etc/postgresql/13/main/postgresql.conf
data_directory=/data/postgresql ... log_destination = 'syslog' ... log_directory = '/var/log/local' ... full_page_writes = off
systemctl list-units | grep post
systemctl restart postgresql@13-main.service
Ohne Admin-Passwort arbeiten (z.B. temporär)
- /etc/postgresql/9.5/main/pg_hba.conf
- host all all 127.0.0.1/32 trust
- host all all ::1/128 trust
Passwort-Authentifizierung erlauben
Linux-User werden zu PostgreSql-Rollen
vi /var/lib/pgsql/data/pg_hba.conf # change host all all 127.0.0.1/32 ident host all all ::1/128 ident #to host all all 127.0.0.1/32 md5 host all all ::1/128 md5 systemctl restart postgresql
DBMS-Zugriff von außen ermöglichen
- /etc/postgresql/13/main/postgresql.conf
listen_addresses = '*' oder listen_addresses = '10.10.10.1'
- in /etc/postgresql/13/main/pg_hba.conf
# Passwort vom User notwendig: host all all 10.10.10.202/32 md5 # Kein Passwort notwendig host all all 10.10.10.204/32 trust
Rolle einrichten
sudo -u postgres -i createuser --interactive # z.B. hm einrichten # oder createuser hm # Passwort setzen als user postgres: psql \password hm
DB erzeugen
- als User hm:
createdb -e -O $owner -h $host -p $port sale * oder als User postgres: psql create database owner hm;
Datensicherung
# Sichern: pg_dump dbname > dumpfile pg_dump -h $HOST -p $PORT dbname | gzip > dbname.sql.gz # Restaurieren: psql dbname < dumpfile psql --set ON_ERROR_STOP=on dbname < dumpfile # Duplizieren einer DB: FN=/tmp/current.db.dump sudo -u postgres pg_dump -h dragon -F c -f $FN pggreenlab ls -ld $FN sudo -u postgres pg_restore -h dragon --dbname=pggrlabtest $FN