Différences
Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentes Révision précédente Prochaine révision | Révision précédente | ||
postgresql [Le 09/01/2023, 15:50] bcag2 [Guide de survie pour le client psql] les droits |
postgresql [Le 05/03/2024, 11:27] (Version actuelle) bcag2 [Guide de survie pour le client psql] lister les schémas |
||
---|---|---|---|
Ligne 128: | Ligne 128: | ||
^ \dn | liste tout le schéma de la base courante| | ^ \dn | liste tout le schéma de la base courante| | ||
^ \h ALTER TABLE | affiche l'aide pour la commande ALTER TABLE| | ^ \h ALTER TABLE | affiche l'aide pour la commande ALTER TABLE| | ||
+ | |||
+ | Pour lister les schémas : | ||
+ | <code sql> | ||
+ | select *from pg_namespace; | ||
+ | </code> | ||
Pour lister les tables d'un schéma : | Pour lister les tables d'un schéma : | ||
Ligne 152: | Ligne 157: | ||
</code> | </code> | ||
- | Lister les droits (\du est très limité !) : | + | Lister les droits (//\du// est très limité !) : |
+ | <code> | ||
+ | select grantee, table_catalog, table_schema, table_name, string_agg(privilege_type, ', ' order by privilege_type) as privileges from information_schema.role_table_grants where grantee != 'postgres' group by grantee, table_catalog, table_schema, table_name; | ||
+ | </code> | ||
+ | |||
+ | et si besoin : | ||
<code sql> | <code sql> | ||
- | SELECT grantee, table_catalog, table_schema, table_name, string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges | + | REVOKE ALL ON SCHEMA mon_schema FROM mon_role; |
- | FROM information_schema.role_table_grants | + | |
- | WHERE grantee != 'postgres' | + | |
- | GROUP BY grantee, table_catalog, table_schema, table_name; | + | |
</code> | </code> | ||
====Supprimer l'utilisateur Postgres du greeter sous xubuntu 14.04.1==== | ====Supprimer l'utilisateur Postgres du greeter sous xubuntu 14.04.1==== | ||
Ligne 342: | Ligne 349: | ||
Il faut d'abord modifier le fichier de configuration pour autoriser les connexions via mot de passe chiffré (sur les versions récentes, au moins la 12, ça ne semble pas nécessaires) : | Il faut d'abord modifier le fichier de configuration pour autoriser les connexions via mot de passe chiffré (sur les versions récentes, au moins la 12, ça ne semble pas nécessaires) : | ||
- | <code> | + | <code bash> |
sudo nano /etc/postgresql/x.x/main/pg_hba.conf | sudo nano /etc/postgresql/x.x/main/pg_hba.conf | ||
</code> | </code> | ||
Ligne 354: | Ligne 361: | ||
Modifier le fichier en remplaçant **ident sameuser** par **md5** afin d'obtenir les lignes suivantes : | Modifier le fichier en remplaçant **ident sameuser** par **md5** afin d'obtenir les lignes suivantes : | ||
- | <code> | + | <code bash> |
... | ... | ||
# Database administrative login by Unix domain socket | # Database administrative login by Unix domain socket | ||
Ligne 372: | Ligne 379: | ||
La modification des paramètres de connexion au serveur nécessite son redémarrage pour que ces derniers soient pris en compte. | La modification des paramètres de connexion au serveur nécessite son redémarrage pour que ces derniers soient pris en compte. | ||
- | <code> | + | <code bash> |
$ sudo service postgresql restart | $ sudo service postgresql restart | ||
</code> | </code> | ||
ou | ou | ||
- | <code> | + | <code bash> |
$ sudo pg_ctlcluster 9.5 main restart | $ sudo pg_ctlcluster 9.5 main restart | ||
</code> | </code> | ||
Ligne 382: | Ligne 389: | ||
On peut ensuite créer notre utilisateur : | On peut ensuite créer notre utilisateur : | ||
- | <code> | + | <code bash> |
$ sudo -i -u postgres | $ sudo -i -u postgres | ||
$ createuser -P --interactive <nom_utilisateur> | $ createuser -P --interactive <nom_utilisateur> | ||
Ligne 393: | Ligne 400: | ||
Puis, on lui crée une base de données dédiée : | Puis, on lui crée une base de données dédiée : | ||
- | <code> | + | <code bash> |
$ createdb -O <nom_utilisateur> -E UTF8 <ma_database> | $ createdb -O <nom_utilisateur> -E UTF8 <ma_database> | ||
</code> | </code> | ||
Ligne 402: | Ligne 409: | ||
L'option //-O <nom_utilisateur>// est là pour préciser l'identité du propriétaire de la base <ma_database>. | L'option //-O <nom_utilisateur>// est là pour préciser l'identité du propriétaire de la base <ma_database>. | ||
</note> | </note> | ||
- | |||
Ensuite, on peut se connecter via : | Ensuite, on peut se connecter via : | ||
- | <code> | + | <code bash> |
$ psql -U <nom_utilisateur> | $ psql -U <nom_utilisateur> | ||
- | Mot de passe pour l'utilisateur <nom_utilisateur> : | + | Mot de passe pour l’utilisateur <nom_utilisateur> : |
</code> | </code> | ||
ou, si le nom de la base de l'utilisateur n'est pas le même que celui de l'utilisateur : | ou, si le nom de la base de l'utilisateur n'est pas le même que celui de l'utilisateur : | ||
- | <code> | + | <code bash> |
$ psql -U <nom_utilisateur> <nom_database_de_l_utilisateur> | $ psql -U <nom_utilisateur> <nom_database_de_l_utilisateur> | ||
- | Mot de passe pour l'utilisateur <nom_utilisateur> : | + | Mot de passe pour l’utilisateur <nom_utilisateur> : |
</code> | </code> | ||
Ligne 421: | Ligne 427: | ||
== Les variables d'environnement == | == Les variables d'environnement == | ||
Les [[http://docs.postgresqlfr.org/9.0/libpq-envars.html|variables d'environnement]] sont utilisées pour sélectionner des valeurs par défaut pour les paramètres de connexion. | Les [[http://docs.postgresqlfr.org/9.0/libpq-envars.html|variables d'environnement]] sont utilisées pour sélectionner des valeurs par défaut pour les paramètres de connexion. | ||
- | Les plus intéressantes à utiliser son PGUSER et PGHOST (notamment pour cette dernière si vous administrez une base qui n'est pas sur un serveur //localhost//). | + | Les plus intéressantes à utiliser son //PGUSER// et //PGHOST// (notamment pour cette dernière si vous administrez une base qui n'est pas sur un serveur //localhost//). |
Pour que ces variables soient systématiquement disponibles dans votre shell de commandes, il faut les ajouter dans le fichier ~/.profile correspondant à votre utilisateur. | Pour que ces variables soient systématiquement disponibles dans votre shell de commandes, il faut les ajouter dans le fichier ~/.profile correspondant à votre utilisateur. | ||
- | Les lignes à ajouter sont (à vous de défionir les variables à ajouter): | + | Les lignes à ajouter sont (à vous de définir les variables à ajouter): |
+ | <code bash> | ||
export PGHOST=<nom_hôte> | export PGHOST=<nom_hôte> | ||
export PGUSER=<nom_utilisateur> | export PGUSER=<nom_utilisateur> | ||
... | ... | ||
- | + | </code> | |
- | La variable PGUSER étant ainsi définie, votre connexion peut alors se résumer à | + | |
+ | La variable //PGUSER// étant ainsi définie, votre connexion peut alors se résumer à | ||
+ | <code bash> | ||
psql <nom_database> | psql <nom_database> | ||
+ | </code> | ||
et si vous définissez PGDATABASE, votre connexion express sera simplement | et si vous définissez PGDATABASE, votre connexion express sera simplement | ||
+ | <code bash> | ||
psql | psql | ||
+ | </code> | ||
<note> | <note> | ||
il faut toujours saisir le mot de passe correspondant à l'utilisateur. Une variable PGPASSWORD existe, mais pour des raisons de sécurité, il est vivement recommandé de ne pas en faire usage. | il faut toujours saisir le mot de passe correspondant à l'utilisateur. Une variable PGPASSWORD existe, mais pour des raisons de sécurité, il est vivement recommandé de ne pas en faire usage. | ||
</note> | </note> | ||
- | |||
== Le fichier .pgpass == | == Le fichier .pgpass == | ||
Ligne 451: | Ligne 462: | ||
Et la sécurité ? | Et la sécurité ? | ||
si vous tentez une connexion | si vous tentez une connexion | ||
+ | <code bash> | ||
psql <nom_database> | psql <nom_database> | ||
+ | |||
WARNING: password file "/home/<user>/.pgpass" has group or world access; permissions should be u=rw (0600) or less | WARNING: password file "/home/<user>/.pgpass" has group or world access; permissions should be u=rw (0600) or less | ||
+ | </code> | ||
La limitation des droits d'accès à ce fichier est recommandée | La limitation des droits d'accès à ce fichier est recommandée | ||
+ | <code bash> | ||
chmod 0600 ~/.pgpass. | chmod 0600 ~/.pgpass. | ||
+ | </code> | ||
=== Personnalisation === | === Personnalisation === | ||
Ligne 469: | Ligne 484: | ||
Ces quelques paramètres doivent suffire dans la majorité des cas. Si vous souhaitez aller plus loin dans le tuning de PostgreSQL, je vous conseille d'aller voir là [[http://docs.postgresqlfr.org|documentation PostgreSQL]] (qui existe en français) | Ces quelques paramètres doivent suffire dans la majorité des cas. Si vous souhaitez aller plus loin dans le tuning de PostgreSQL, je vous conseille d'aller voir là [[http://docs.postgresqlfr.org|documentation PostgreSQL]] (qui existe en français) | ||
- | |||
- | |||
=== Rôles et droits === | === Rôles et droits === | ||
Ligne 493: | Ligne 506: | ||
Différentes contributions sont disponibles (cf la [[http://docs.postgresqlfr.org/9.1/contrib.html|documentation PostgreSQL]]). Leur installation (et désinstallation) est facilitée par l'existence de paquets dédiés | Différentes contributions sont disponibles (cf la [[http://docs.postgresqlfr.org/9.1/contrib.html|documentation PostgreSQL]]). Leur installation (et désinstallation) est facilitée par l'existence de paquets dédiés | ||
- | <code> | + | <code bash> |
sudo apt-get install postgresql-contrib | sudo apt-get install postgresql-contrib | ||
Les paquets supplémentaires suivants seront installés : | Les paquets supplémentaires suivants seront installés : | ||
Ligne 501: | Ligne 514: | ||
Les scripts permettant l'application de ces extensions sont disponibles dans **/usr/share/postgresql/9.1/extension/*.sql** | Les scripts permettant l'application de ces extensions sont disponibles dans **/usr/share/postgresql/9.1/extension/*.sql** | ||
L'ajout se fait par exécution du script concerné sur la base de données souhaitée. | L'ajout se fait par exécution du script concerné sur la base de données souhaitée. | ||
- | <code> | + | <code bash> |
$ psql -U <nom_utilisateur> <nom_database> -f /usr/share/postgresql/9.1/extension/<nom_extension>.sql | $ psql -U <nom_utilisateur> <nom_database> -f /usr/share/postgresql/9.1/extension/<nom_extension>.sql | ||
</code> | </code> | ||
Ligne 508: | Ligne 521: | ||
Pour ne permettre seulement une consultation des données par un utilisateur sans droit de modification, il faut exécuter la requête suivante: | Pour ne permettre seulement une consultation des données par un utilisateur sans droit de modification, il faut exécuter la requête suivante: | ||
- | <code> | + | <code sql> |
ALTER USER <nom_utilisateur> set default_transaction_read_only = on; | ALTER USER <nom_utilisateur> set default_transaction_read_only = on; | ||
</code> | </code> |