Basic Linux user and PostgreSQL setup commands
Linux commands
User
Add new user
sudo adduser devuser
Sudo privileges
Add sudo privileges
adduser devuser sudo
Remove sudo privileges from user
sudo deluser devuser sudo
Change password on user[1]
sudo passwd devuser
Delete user [2]
userdel -r devuser
if error
sudo killall -u devuser
or
userdel -f devuser
PostgreSQL commands
Setup
Create database
sudo su - postgres
createdb devdb
Create database user
sudo su - postgres
createuser -P devuser
or [3]
sudo su - postgres
psql
CREATE USER devuser WITH PASSWORD 'devpassword';
\q
Grant privileges to database user
sudo su - postgres
psql
GRANT ALL PRIVILEGES ON DATABASE devdb TO devuser;
\q
Backup
sudo su - postgres
pg_dump --no-owner -x devdb > backup.psql
exit
Encrypt backup
gpg -c backup.psql
Decrypt backup
gpg --output backup.psql --decrypt backup.psql.gpg
Restore backup
sudo su - postgres
psql devdb < backup.psql
Restore PRIVILEGES to devuser
Login to the devdb
psql devdb
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO devuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO devuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO devuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO devuser;
