postgres install extensions (pgcrypto) without giving the application superuser rights

In this example, I’m going to install the pgcrypto extension to be able to generate UUID data type.

Procedure

1. Access as Postgres superuser. By default, it is the postgres user.

2. Access to the database using the super user (postgres).

3. Lista all the databases to identify the database we want to install the extension, (Optional). user the command \l.

 

4. The extensions need to be enabled in each database one by one. In this example, we’re going to enable pgcrypto in to the databse  dbaurlendev. Connect to the database we want to enable the extension, using the command \c databasename.

5.  Execute the specific command to enable the extension. For pgcrypto, the command is CREATE EXTENSION IF NOT EXISTS “pgcrypto”;

6. Test the extension, using an application user. To generate UUID with pgcrypto the command is SELECT gen_random_uuid();