How to Create a Read-Only Database User in Postgres for Analytics and Reporting

By Matthew Rathbone on October 07, 2016

postgres analytics database dba

This article was co-authored by Anton Antonenko

space Image by NASA

If you’re running Postgres in production, odds are that someone is going to need read-only access at some point to analyze the data and produce reports. Granting read-only access is actually non-intuitive, so here is a quick guide on how to do it.

With a fresh install of Postgres you’ll need to do three things.

  1. Set up the user in the database
  2. Configure Postgres access methods
  3. Make sure Postgres accepts external connections

Firstly, Who’s the Boss

First of all, we need to run commands on Postgres as a DB owner or administrator user. If you have access to the Postgres instance already you can figure out who the owner is:

psql -d database_name

and then do:

database_name=# \l
                              List of databases
   Name    | Owner    | Encoding |   Collate   |    Ctype    | Access privileges
-----------+----------+----------+-------------+-------------+-------------------
 db_name   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

In this case we can see that our DB owner is the user ‘postgres’. Thus, we need to run commands using the Postgres account in Linux.

I’m going to assume you have sudo privileges and can execute commands as the postgres Linux user, otherwise you’ll have to get the admin credentials from someone else.

Creating the User

Run the following command in order to figure out where psql is located on your machine:

which psql

In my case it is /usr/local/bin/psql.

We can now create a user using the createuser utility bundled with Postgres. The binary is in the same location as the psql binary.

sudo -u postgres /usr/local/bin/createuser -U postgres -D -R -S readonly_user

This command will create a database user with limited permissions.

Key -D means that this user cannot create db’s; -R means that this user cannot create any roles; -S means that this user won’t be superuser.

Okay, so we have ourselves a DB user, now let’s give him some select permissions and a login password.

sudo -u postgres /usr/local/bin/psql -U postgres database_name -c "ALTER USER readonly_user WITH ENCRYPTED PASSWORD 'super_strong_password'"

sudo -u postgres /usr/local/bin/psql -U postgres database_name -c "GRANT CONNECT ON DATABASE database_name TO readonly_user"

sudo -u postgres /usr/local/bin/psql -U postgres database_name -c "GRANT USAGE ON SCHEMA public TO readonly_user"

sudo -u postgres /usr/local/bin/psql -U postgres database_name -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user"

This block of commands will give the user a password, then connection rights, and finally usage & select access on our DB.

But wait, we’re not done!

Configuring Postgres

This is the part of this process that most people forget, we need to update two configuration files.

There’s a file called pg_hba.conf that you’ll need to edit, its location can vary, but you can find it on your machine by running SHOW hba_file; in the PSQL shell.

We can use this file to force our user to use a password when connecting to the DB.

One can connect to Postgres in two ways, socket connections, and TCP connections (disabled by default). We’ll want to configure both of these mechanisms. Most third party software will probably connect over TCP.

Socket Connections

In the pg_hba.conf file after line # local is for Unix domain socket connections only you’ll need to add following:

local database_name readonly_user md5

TCP Connections

If you want to open remote access to your database for a specific IP, you’ll need to add the following line to your pg_hba.conf (where 10.10.10.10 is the user’s IP):

host database_name readonly_user 10.10.10.10/32 md5

If you want to open remote access to ALL users from that specific IP address you can omit the username:

host database_name all 10.10.10.10/32 md5

You can open it up to any IP connection using the following:

host database_name all 0.0.0.0/0 md5

Finally, you need to tell Postgres to listen on multiple IP addresses. By default it listens on ‘localhost’, so you can’t connect to it from any other machines. Update the listen_address value inside your postgresql.conf file.

You can find the location of this file by running SHOW config_file; in the shell.

listen_addresses='*'

Finally, restart Postgres to apply the changes.

Now to connect to DB using readonly user, – simply do

psql -d database_name -U readonly_user

## or for TCP:

psql -d database_name -U readonly_user -h 192.168.2.10

By setting up a password protected read-only user we ensure that we’re not giving too many people dangerous access to our production data. Do this for every user wanting read-only access.

Still Writing SQL in a Terminal?

Beekeeper is a free and full featured SQL IDE with query autocomplete, error detection, dataset previews, and result visualization. We support SparkSQL, Hive, Postgres and more!

Create your account »

Matthew Rathbone bio photo

Matthew Rathbone

CEO of Beekeeper Data. British. Data nerd.

Big Data Blog Email Twitter Github Stackoverflow