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.
- Set up the user in the database
- Configure Postgres access methods
- 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:
and then do:
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:
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
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.
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!
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.
pg_hba.conf file after line
# local is for Unix domain socket connections only you’ll need to add following:
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):
If you want to open remote access to ALL users from that specific IP address you can omit the username:
You can open it up to any IP connection using the following:
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.
Finally, restart Postgres to apply the changes.
Now to connect to DB using readonly user, – simply do
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.