HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Grant postgres superuser to linux account

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresgrantaccountlinuxsuperuser

Problem

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 ?

Solution

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

Code Snippets

# Identify yourself as root
su - 

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

# Add a password
passwd mypostgresuser
# 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
/etc/init.d/postgresql reload
pg_ctl reload -D ${PGDATA}
# 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

Context

StackExchange Database Administrators Q#54241, answer score: 6

Revisions (0)

No revisions yet.