PostgreSQL

Les bases

Les roles

PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

# psql
CREATE ROLE name;
DROP ROLE name;

# shell
createuser name
dropuser name

On peut créer des roles qui ont le droit de se logguer ou pas, via l'option LOGIN:

CREATE ROLE name LOGIN;
CREATE USER name;
On obtient donc un utilisateur. Pour lister les roles:
\du
# ou
SELECT rolname, rolcanlogin, rolsuper  FROM pg_roles;
Donc, avec PostgreSQL, les roles sont la meme chose que les utilisateurs, mis à part que l'utilisateur à le droit de se logguer. Pour se connecter avec le client psql:
psql -U $role_name
# En précisant la base
psql -U $role_name -d $database

Pour modifier un role ou un utilisateur, il faut voir ALTER ROLE

Old

Pour créer un utilisateur: http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/

Tester une connection d'un user à un BDD:
psql -h HOST -d BASE -U USER -W

L'authentification sous postgreSQL

Qui n'en a jamais chié d'ajouter un utilisateur postgres? Voici les trucs à savoir. Il y'a deux mechanismes:

  • Méthode d'authentification, c'est une sorte de protection, l'équivalent du <host> dans les noms d'utilisateur MySQL, mais en baucoup plus complet. On va pouvoir y définir comment l'utilisateur peut se connecter, depuis ou, s'il a besoin d'un compte UNIX, ou s'il a besoin de mettre un MdP. Tout ça est renseigné dans le fichier pg_hba.conf, et l'ordre des directives a de l'importance (first match)
  • Les roles SQL, là on peut faire le parralele avec les grants de mysql.

Réplication

Pour vérifier que la réplication est bien en place, faitre depuis le master:
select * from pg_stat_replication;

PgPool

Pour le password de pgpool: http://pentestmonkey.net/blog/cracking-postgres-hashes

Parcequ'il fallait bien créer cette page un jour ou l'autre ...

Pour connaitre le status des backends, il faut se connecter à pgpool (pas directement sur les postgres) et lancer la commande suivante:
show pool_nodes;
 node_id |    hostname    | port | status | lb_weight |  role   
---------+----------------+------+--------+-----------+---------
 0       | 172.16.220.238 | 5432 | 3      | 0.500000  | standby
 1       | 172.16.220.239 | 5432 | 2      | 0.500000  | standby

Ici, on voit que les deux backend sont en standby, ce qui n'est pas bon.

J'ai repris un petit script pour gérer la réplication. C'est dispo dans mon github. Au final, on peut avoir un outils pour monitorer tout ça relativement simplement:
1: root@bdd1-dev:~# /usr/local/bin/pgprepmgr2 show
PgPool-II Status:
=====================
Node    Host            Port    Alive   StandBy Status
0       192.168.121.176 5432    1       0       1
1       192.168.121.144 5432    1       1       1
2       192.168.121.89  5432    1       1       1

Repmgr Status:
=====================
Host: 192.168.121.176
Role      | Connection String
* master  | host=192.168.121.176 dbname=repmgr user=repmgr
  standby | host=192.168.121.89 dbname=repmgr user=repmgr
  standby | host=192.168.121.144 dbname=repmgr user=repmgr

Host: 192.168.121.144
Role      | Connection String
* master  | host=192.168.121.176 dbname=repmgr user=repmgr
  standby | host=192.168.121.89 dbname=repmgr user=repmgr
  standby | host=192.168.121.144 dbname=repmgr user=repmgr

Host: 192.168.121.89
Role      | Connection String
* master  | host=192.168.121.176 dbname=repmgr user=repmgr
  standby | host=192.168.121.89 dbname=repmgr user=repmgr
  standby | host=192.168.121.144 dbname=repmgr user=repmgr

Scripts pgpool

Si vous voulez jouer avec les scripts pgpool, voici une bonne base, qui permettra au débutant de pas trop galérer:
# PG-Pool-II scripts

######################################################################
### recovery_1st_stage_command = command
######################################################################

#!/bin/bash

# Called with 3 arguments:
# path to master(primary) database cluster
# PostgreSQL host name to be recovered
# path to database cluster to be recovered

# Argument management
PG_MASTER_DIR=${1}
HOST_NAME=${2}
PG_SLAVE_DIR=${3}

# Debug function
debug(){
	echo "PG_MASTER_DIR=${PG_MASTER_DIR}"
	echo "HOST_NAME=${HOST_NAME}"
	echo "PG_SLAVE_DIR=${PG_SLAVE_DIR}"
}

# Debug
echo "$(date) recovery_1st_stage_command" >> /tmp/pg_scripts
debug >> /tmp/pg_scripts


######################################################################
### recovery_2nd_stage_command 
######################################################################

#!/bin/bash

# Called with 3 arguments:
# path to master(primary) database cluster
# PostgreSQL host name to be recovered
# path to database cluster to be recovered

# Argument management
PG_MASTER_DIR=${1}
HOST_NAME=${2}
PG_SLAVE_DIR=${3}

# Debug function
debug(){
	echo "PG_MASTER_DIR=${PG_MASTER_DIR}"
	echo "HOST_NAME=${HOST_NAME}"
	echo "PG_SLAVE_DIR=${PG_SLAVE_DIR}"
}

# Debug
echo "$(date) recovery_2nd_stage_command" >> /tmp/pg_scripts
debug >> /tmp/pg_scripts


######################################################################
### failover_command = command "%d" "%h" "%p" "%D" "%m" "%M" "%H" "%P"
######################################################################

#!/bin/bash

# Argument management
DETACHED_NODE_ID=${1}          # %d	Backend ID of a detached node.
DETACHED_HOST_NAME=${2}        # %h	Hostname of a detached node.
DETACHED_PORT=${3}             # %p	Port number of a detached node.
DETACHED_DB_CLUSTER=${4}       # %D	Database cluster directory of a detached node.
NEW_MASTER_ID=${5}             # %m	New master node ID.
OLD_MASTER_ID=${6}             # %M	Old master node ID.
NEW_MASTER_HOST_NAME=${7}      # %H	Hostname of the new master node.
OLD_PRIMARY_NODE_ID=${8}       # %P	Old primary node ID.


# Debug function
debug(){
	echo "DETACHED_NODE_ID=${DETACHED_NODE_ID}"
	echo "DETACHED_HOST_NAME=${DETACHED_HOST_NAME}"
	echo "DETACHED_PORT=${DETACHED_PORT}"
	echo "DETACHED_DB_CLUSTER=${DETACHED_DB_CLUSTER}"
	echo "NEW_MASTER_ID=${NEW_MASTER_ID}"
	echo "OLD_MASTER_ID=${OLD_MASTER_ID}"
	echo "NEW_MASTER_HOST_NAME=${NEW_MASTER_HOST_NAME}"
	echo "OLD_PRIMARY_NODE_ID=${OLD_PRIMARY_NODE_ID}"
}

# Debug
echo "$(date) failover_command" >> /tmp/pg_scripts
debug >> /tmp/pg_scripts


######################################################################
### failback_command = command "%d" "%h" "%p" "%D" "%m" "%M" "%H" "%P"
######################################################################

#!/bin/bash

# Argument management
ATTACHED_NODE_ID=${1}          # %d	Backend ID of an attached node.
ATTACHED_HOST_NAME=${2}        # %h	Hostname of an attached node.
ATTACHED_PORT=${3}             # %p	Port number of an attached node.
ATTACHED_DB_CLUSTER=${4}       # %D	Database cluster path of an attached node.
NEW_MASTER_ID=${5}             # %m	New master node ID.
OLD_MASTER_ID=${6}             # %M	Old master node ID.
NEW_MASTER_HOST_NAME=${7}      # %H	Hostname of the new master node.
OLD_PRIMARY_NODE_ID=${8}       # %P	Old primary node ID.


# Debug function
debug(){
	echo "ATTACHED_NODE_ID=${ATTACHED_NODE_ID}"
	echo "ATTACHED_HOST_NAME=${ATTACHED_HOST_NAME}"
	echo "ATTACHED_PORT=${ATTACHED_PORT}"
	echo "ATTACHED_DB_CLUSTER=${ATTACHED_DB_CLUSTER}"
	echo "NEW_MASTER_ID=${NEW_MASTER_ID}"
	echo "OLD_MASTER_ID=${OLD_MASTER_ID}"
	echo "NEW_MASTER_HOST_NAME=${NEW_MASTER_HOST_NAME}"
	echo "OLD_PRIMARY_NODE_ID=${OLD_PRIMARY_NODE_ID}"
}

# Debug
echo "$(date) failback_command" >> /tmp/pg_scripts
debug >> /tmp/pg_scripts


######################################################################
### follow_master_command = command "%d" "%h" "%p" "%D" "%m" "%M" "%H" "%P"
######################################################################

#!/bin/bash

# Argument management
DETACHED_NODE_ID=${1}          # %d	Backend ID of a detached node.
DETACHED_HOST_NAME=${2}        # %h	Hostname of a detached node.
DETACHED_PORT=${3}             # %p	Port number of a detached node.
DETACHED_DB_CLUSTER=${4}       # %D	Database cluster directory of a detached node.
NEW_MASTER_ID=${5}             # %m	New master node ID.
OLD_MASTER_ID=${6}             # %M	Old master node ID.
NEW_MASTER_HOST_NAME=${7}      # %H	Hostname of the new master node.
OLD_PRIMARY_NODE_ID=${8}       # %P	Old primary node ID.


# Debug function
debug(){
	echo "DETACHED_NODE_ID=${DETACHED_NODE_ID}"
	echo "DETACHED_HOST_NAME=${DETACHED_HOST_NAME}"
	echo "DETACHED_PORT=${DETACHED_PORT}"
	echo "DETACHED_DB_CLUSTER=${DETACHED_DB_CLUSTER}"
	echo "NEW_MASTER_ID=${NEW_MASTER_ID}"
	echo "OLD_MASTER_ID=${OLD_MASTER_ID}"
	echo "NEW_MASTER_HOST_NAME=${NEW_MASTER_HOST_NAME}"
	echo "OLD_PRIMARY_NODE_ID=${OLD_PRIMARY_NODE_ID}"
}

# Debug
echo "$(date) follow_command" >> /tmp/pg_scripts
debug >> /tmp/pg_scripts

Quick stuffs

Lister les bases de données:
su - postgres -c "psql -l"
Supprimer une base:
su - postgres -c "dropdb ma_base"
Faire un dump d'une base:
su - postgres -c "ma_bdd > /var/backup/dump.pgsql"
Se connecter avec un utilisateur donné à une BDD:
psql -U user -W -h hostname -d dbname
Changer de base de donnée:
\c database;

Comment activer le réseau sur PostgreSQL.

Informations

Les plus utilisés!
\du+  [PATTERN]      list roles
\l[+]   [PATTERN]      list databases
\dt[S+] [PATTERN]      list tables

\dp     [PATTERN]      list table, view, and sequence access privileges
\drds [PATRN1 [PATRN2]] list per-database role settings

Créer

Créer un utilisateur:
CREATE USER name WITH SUPERUSER CREATEDB LOGIN UNENCRYPTED PASSWORD 'pass' 
Créer une base:
CREATE DATABASE nom WITH OWNER = user ;

Modifier

Changer le mot de passe d'un utilisateur:
\password [USERNAME]   --securely change the password for a user
ALTER USER <user> WITH UNENCRYPTED PASSWORD '<pass>';
Changer le nom d'un user:
ALTER USER name RENAME TO new_name
Changer le nom d'une base (ici on force la déconexion des utilisateurs):
select pg_terminate_backend(pid) from pg_stat_activity where datname='old';
ALTER DATABASE old  RENAME TO new ;

Vrac

Montrer les replication slots: SELECT * FROM pg_replication_slots;

Creer un replication slot: SELECT * FROM pg_create_physical_replication_slot('name');

Montrer l'etat de la replication: select * from pg_stat_replication;

Montrer le delai de replication: select now() - pg_last_xact_replay_timestamp() AS replication_delay;

Recupérer toute la conf: SELECT * FROM pg_settings; SELECT * FROM pg_settings where name='wal_level';

Afficher les r2sultat verticalement: \x

Pour changer des variables de conf à chaud: alter system set wal_level = hot_standby; alter system set option = value;

Pour afficher le status d'une variable: show max_wal_senders;

Page last modified on November 10, 2015, at 07:02 PM EST