patternsqlMinor
Grant postgres superuser to linux account
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.
How can I alter my own OS account to have the OS-level privilages of postgres, so I will not need to su ?
When I want to perform any postgres activities, create/drop databases etc, I must first su to the postgres account.
$ sudo su postgresHow 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
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
The value for your install might be available in the environment variable
if you make any changes, reloading postgres will be necessary
Or as postgres
Now connect to psql as postgres
Test the connection
# Identify yourself as root
su -
# Create the user who will have access to a postgres database
useradd mypostgresuser
# Add a password
passwd mypostgresuserGive 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 allif you make any changes, reloading postgres will be necessary
/etc/init.d/postgresql reloadOr 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;
GRANTTest the connection
# Identify yourself as mypostgresuser
su - mypostgresuser
# Connect to the database
psql -d mytestdbCode 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 reloadpg_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;
GRANTContext
StackExchange Database Administrators Q#54241, answer score: 6
Revisions (0)
No revisions yet.