In larger work environments it becomes essential to be able to assign similar permissions to groups of people for the database environment. PostgreSQL has a built in authentication system that easily allows for this.
As with the rest of this documentation, this section assumes a PostgreSQL installation on a Debian based Linux distribution. We use Ubuntu 18.10 but the same should work for any version of Ubuntu, Debian or even Raspbian for a Raspberry Pi.
Logging into the PostgreSQL administrator account
Assuming the default “postgres” user account log into that user within your Linux environment by typing:
sudo -i -u postgres
Then enter the database cluster by typing “psql” into the terminal.
Listing all available databases
In the event you don’t know the name of the database you need to enter you can list all databases present on the server by typing “\l” into the psql command prompt.
The databases are listed on the left column under “Name“.
Connecting to the database and creating a user group
Once you have figured out the name of the database you wish to connect to use
\connect <dbname>
to connect to it.
Once you are connected to the database, you can execute SQL queries directly against the database. Remember, all queries must end with a semicolon.
Create the group by typing:
CREATE GROUP <group_name>;
Or, create the group and add users at the same time by typing;
CREATE GROUP <group_name> WITH USER <name_one>, <name_two>;
