5

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 ?

4

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
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "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;
CREATE ROLE

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

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. – ardochhigh Dec 2 '13 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. – Craig Efrein Dec 2 '13 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. – ardochhigh Dec 2 '13 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; – Craig Efrein Dec 2 '13 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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