Differences

This shows you the differences between two versions of the page.

--- tips:db:postgres [2017/11/15 12:09] – [pgmodeler] scipio
+++ tips:db:postgres [2019/03/13 09:55] (current) – [Basic operations] scipio
@@ Line 21: / Line 21: @@
 ===== Basic operations =====
-create role (user)
 <code>
--- User: gianoauth
+# list databases
-DROP USER gianoauth;
+\l
+# connect to database
+\c giano
+# list schema
+\dn
+# list tables in schema
+\dt auth.*
+# describe table
+\d+ auth.users
+# list of installed extensions
+\dx
+# enable expanded display
+\x
+</code>
+set search path for database
+<code>
+psql <database>
+ALTER DATABASE database_name SET search_path TO schema_name,public;
+</code>
+drop role gianoauth
+<code>
+REASSIGN OWNED BY gianoauth TO postgres;
+DROP OWNED BY gianoauth;
+DROP USER IF EXISTS gianoauth;
+</code>
+create role gianoauth
+<code>
 CREATE USER gianoauth WITH
+  ENCRYPTED PASSWORD 'xxx'
   LOGIN
   NOSUPERUSER
@@ Line 33: / Line 68: @@
   NOCREATEROLE
   NOREPLICATION;
+</code>
-ALTER USER gianoauth SET search_path TO gianoauth;
+... create database and schemas ...
-ALTER ROLE gianoauth IN DATABASE giano SET search_path TO gianoauth;
-GRANT ALL PRIVILEGES ON  SCHEMA gianoauth TO gianoauth;
+grant operations on schema auth to role gianoauth
-GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA gianoauth TO gianoauth;
+<code>
-GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA gianoauth TO gianoauth;
+GRANT ALL PRIVILEGES ON  SCHEMA auth TO gianoauth;
+ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA auth GRANT SELECT ON TABLES TO gianoauth;
+ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA auth GRANT SELECT, USAGE ON SEQUENCES TO gianoauth;
+</code>
+on database giano set search path on user gianoauth to search first in schema auth
+<code>
+ALTER USER gianoauth SET search_path TO auth;
+ALTER ROLE gianoauth IN DATABASE giano SET search_path TO auth;
+</code>
+create superuser <admin> with password
+<code>
+createuser -P -s -e admin
+</code>
+create user and database for sentry
+<code>
+CREATE ROLE sentry WITH PASSWORD 'blabla' LOGIN;
+CREATE DATABASE sentry ENCODING 'UTF8' OWNER sentry;
 </code>
 ==== developer containers =====
@@ Line 69: / Line 124: @@
 apt install wget python3-pip
 pip3 install -U pip
-cd /tmp
+pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.2/pip/pgadmin4-4.2-py2.py3-none-any.whl
-wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v2.0/pip/pgadmin4-2.0-py2.py3-none-any.whl
-pip install ./pgadmin4-2.0-py2.py3-none-any.whl
 # define ip binding
@@ Line 110: / Line 163: @@
 <code>
-PG_VERSION=0.9.1-alpha
+PG_VERSION=0.9.1-beta1
 # for building, can remove after install
-sudo apt install make g++ qt5-qmake libxml2-dev libpq-dev pkg-config libqt5svg5-dev
+sudo apt install -y make g++ qt5-qmake libxml2-dev libpq-dev pkg-config libqt5svg5-dev
 # runtime dependencies
-sudo apt install qt5-default libqt5svg5 postgresql-server-dev-*
+sudo apt install -y qt5-default libqt5svg5 postgresql-server-dev-all
-cd /user/local/src
+cd /usr/local/src
-sudo apt install wget
+sudo apt install -y wget
 wget https://github.com/pgmodeler/pgmodeler/archive/v${PG_VERSION}.tar.gz
-sudo apt install qt5-default libqt5svg5 postgresql-server-dev-all
 tar xvzf v${PG_VERSION}.tar.gz
-cd pgmodeler-0.9.1-alpha/
+cd pgmodeler-${PG_VERSION}/
 qmake pgmodeler.pro
-make
+make -j 4
 make install
 sudo mkdir /usr/local/lib/pgmodeler/plugins
@@ Line 132: / Line 184: @@
 </code>
+desktop launcher
+<code>
+mkdir -p ~/.local/share/applications/
+cat > ~/.local/share/applications/pgmodeler.desktop <<EOF
+#!/usr/bin/env xdg-open
+[Desktop Entry]
+Type=Application
+Terminal=false
+Exec=/usr/local/bin/pgmodeler
+Name=pgmodeler
+EOF
+</code>
 if in container enable remote connection on host
 <file ini /etc/gdm3/custom.conf>
@@ Line 139: / Line 205: @@
 [xdmcp]
 Enable=true
-</code>
+</file>
 run pgmodeler in container