First connect to the PG server using psql and as your DB owner account or the superuser account.

$> psql

Create the user and set a password.  Here I am creating user rouser with a password of R34D0NLY.

CREATE ROLE rouser WITH ENCRYPTED PASSWORD 'R34D0NLY';

Next, switch to your database context (here my database is named mydatabase, and my superuser is named postgres)

postgres=# \connect mydatabase;

The prompt will switch to:

mydatabase=#

No execute the following (be sure to swap out your username with my rouser username):

Now your user has readonly access to the schema public.  If you are creating tables and other database objects in another schema you will need to do the same thing but replace public with your other schema name.

Allow a user to use a sequence:

 

I have been using JetBrains new 0XDBE to do some database development on PostgreSQL.  I installed PostgreSQL myself on my linux VPS and immediately got frustrated with the following error:

java.sql.SQLException: SSL error: sun.security.validator.ValidatorException: PKIK path building failed:
sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.

Seriously?

Data Sources and Drivers

 

The error is confusing but luckily the fix is simple.

In the Data Source settings add: org.postgresql.ssl.NonValidatingFactory to the sslfactory setting in the Advanced tab.

settings

 

After adding that setting click apply and then test the connection again and you should be good to go.

success