During a set up of any new PostgreSQL server, one often immediately needs to create a user and database for immediate use. After that, one has to take care of user privileges as well on the database. Here we provide a list of commands for such cases.
In the first step one has to install PostgreSQL server (the following commands are based on UBUNTU and PostgreSQL-12):
sudo apt-get install wget ca-certificates -y
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib -y
Next will be now to create a user and database.
$ sudo su postgres
$ psql
postgres=#SHOW SERVER_ENCODING; # It should be UTF8
postgres=#CREATE USER myusername WITH ENCRYPTED PASSWORD 'mypassword';
postgres=#CREATE DATABASE mydatabase;
postgres=#GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myusername;
# If certain privileges are to be granted
# First USAGE on schema
postgres=#GRANT USAGE ON SCHEMA myschema TO myusername;
# Now specific privileges
postgres=#GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myusername;
That's it ! Now we have a database with proper user access.