Creating user groups in PostgreSQL

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>;

Posted in Instructional Information.