Outils pour utilisateurs

Outils du site


pgsql-create-db-user:postgresql_create_database_user

Postgresql create database user

Autoriser les connections authentifiées en local pour posgresql

export PGSQL_DB_NAME=db_mydbname
export PGSQL_USERNAME=myusername
export PGSQL_USERPWD=mypassword
 
 
cp -a /etc/postgresql/9.6/main/pg_hba.conf /etc/postgresql/9.6/main/pg_hba.conf_
sed -i "s/local   all             all                                     peer/local   all             all                                     md5/g" /etc/postgresql/9.6/main/pg_hba.conf
service postgresql stop
service postgresql start
 
cd /
/bin/su postgres --command "/usr/bin/createuser --no-superuser --no-createdb --no-createrole --login ${PGSQL_USERNAME}"
/bin/su postgres --command "/usr/bin/psql --command=\"ALTER USER ${PGSQL_USERNAME} PASSWORD '${PGSQL_USERPWD}'\""
/bin/su postgres --command "/usr/bin/createdb --encoding UTF8 --owner ${PGSQL_USERNAME} ${PGSQL_DB_NAME}"
/bin/su postgres --command "/usr/bin/psql --command \"GRANT ALL PRIVILEGES ON DATABASE ${PGSQL_DB_NAME} to ${PGSQL_USERNAME}\""

Test

/bin/su postgres --command "psql --command=\"\d\" --username=${PGSQL_USERNAME} --dbname=${PGSQL_DB_NAME}"

MEMO

  • drop database : /bin/su postgres –command “/usr/bin/dropdb ${PGSQL_DB_NAME}”
  • drop user : /bin/su postgres –command “/usr/bin/dropuser ${PGSQL_USERNAME}
  • list databases : /bin/su postgres –command “/usr/bin/psql –command \”\list\“”
  • list tables : /bin/su postgres –command “/usr/bin/psql –command \”\connect ${PGSQL_DB_NAME};\d;\“”
  • list users : /bin/su postgres –command “/usr/bin/psql –command \”\du\“”
  • list privileges : /bin/su postgres –command “/usr/bin/psql –command \”\l\“”

DROP DB

su - postgres
psql -U postgres
\l
DROP DATABASE xys;
\q

Cheat Sheets

pgsql-create-db-user/postgresql_create_database_user.txt · Dernière modification: 2019/05/27 10:52 par ghusson