On my development (Ubuntu linux) laptop I have a postgres OS account, which owns the Postgres installation.

When I want to perform any postgres activities, create/drop databases etc, I must first su to the postgres account.

$ sudo su postgres

How can I alter my own OS account to have the OS-level privilages of postgres, so I will not need to su ?

2 Answers 2


Create the user on the OS

# Identify yourself as root
su - 

# Create the user who will have access to a postgres database
useradd mypostgresuser

# Add a password
passwd mypostgresuser

Give local users access to postgres

You need to locate the data directory for your postgresql install, i.e. where you have created the database files. They are typically located in /var/lib/pgsql/data. The value for your install might be available in the environment variable $PGDATA

# Make sure that local users can access postgres
cat /${PGDATA}/pg_hba.conf

# this was the default setting on my 8.4 install

# "local" is for Unix domain socket connections only
local   all         all

if you make any changes, reloading postgres will be necessary

/etc/init.d/postgresql reload

Or as postgres

pg_ctl reload -D ${PGDATA}

Now connect to psql as postgres

# Create the user in postgres
postgres=# create user mypostgresuser;

# Give that user access to a database
postgres=# grant all privileges on database mytestdb to mypostgresuser;

Test the connection

# Identify yourself as mypostgresuser
su - mypostgresuser

# Connect to the database
psql -d mytestdb 
  • Thanks for the comprehensive answer. I followed the steps, but is it correct I will need to re-grant access every time a database is created? This is done frequently. I could add it to the import/ grant scripts if necessary.
    – port5432
    Dec 2, 2013 at 15:00
  • You must GRANT rights to every new database for normal users. Only the postgres user has access to all objects by default. Dec 2, 2013 at 15:11
  • I see. Ok thanks Craig. I guess I'll have to keep sudo'ing as I am constantly creating and importing datbases from a remote server. Cheers.
    – port5432
    Dec 2, 2013 at 15:20
  • 1
    Just for info, if you need to give a user access to an existing database, you can grant your new user the role that already has access. GRANT ROLE nameofexistingrole TO newrole; Dec 2, 2013 at 15:22

You can also use pg_ident.conf to map a system user to a postgresql user.

1. Edit pg_ident.conf

For example, to allow the "root" user to run psql as the "postgres" user (psql -U postgres ...), map it in pg_ident.conf by adding a line like

root2pg  root  postgres

2. Edit pg_hba.conf

Then in pg_hba.conf, change this line:

local  all  postgres  peer

to add "map=root2pg" at the end:

local  all  postgres  peer map=root2pg

3. Restart postgresql

systemctl restart postgresql

You can use any string you like for the map name (here "root2pg"), and allow other users than root (the 2nd column in pg_ident.conf)

To make this very generic and easy to copy paste to different machines which may have several different postgresql versions, you could do it with something like:

allow=root; as=postgres;

export mapname=${allow}2$as 
printf '%-16s%-24s%s\n' $mapname $allow $as >> /etc/postgresql/*/main/pg_ident.conf
perl -i.bak -pe 's/(^local\s+all\s+postgres\s+peer)\s*$/$1 map=$ENV{mapname}\n/' /etc/postgresql/*/main/pg_hba.conf

(The use of printf instead of echo is only to align with the existing columns in the default pg_ident.conf)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.