Accessing postgres without being root users

Question

I have created a postgres database in postgres named "databaseName". Now I can access this data2database through

 su - postgres

and then typing my password

Then I enter into the database through: psql databaseName

I have created users of this database through:

   createuser -P userName1

Now I dont want the users of the database to access the database as root user. Now when the users try to login into postgres as

 su - postgres -u userName1 
        or through
 psql databaseName -u userName1

I get error...can someone guide me as to how the users can get access to postgres and database without being root user?

Please never write "I get error". What error? Exactly? Copy and paste the exact error text into your question. — Craig Ringer
– Craig Ringer, Commented Mar 4, 2013 at 13:04

Craig Ringer · Accepted Answer · 2013-03-04 13:01:44Z

7

Configure pg_hba.conf to accept peer connections over unix sockets or ident connections over host (tcp) connections. If you prefer you can use md5 to use password authentication instead.

By default psql will connect with the same username as your local OS username; this can be overridden by the -U flag, eg:

psql -U myusername thedatabase

Note that it's -U not -u (it's upper case).

This is all covered in detail in the PostgreSQL documentation; see:

BTW, if you want to run commands as the postgres user, rather than su'ing you can just write:

sudo -u postgres psql

answered Mar 4, 2013 at 13:01

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1778824 Over a year ago

Thanks a lot for replying. I am new to postgres therefore I am not getting how to configure ph_hba.conf. I'll be highly thankful if you can guide me in configuring it..I am working on a linux server through command line interface. Hearty thanks again for replying

Craig Ringer Over a year ago

@user1778824 Read the "client authentication" chapter of the documentation, it sound explain everything you need. I can't just tell you how to configure it; if there were one right way to set it up we wouldn't make it a config file at all, we'd just have the server automatically work that way. It's a config file because different people want Pg to work different ways for different situations.

Craig Ringer Over a year ago

@user1778824 Without knowing your exact needs and situation it's hard to say what you need - especially as you haven't mentioned your distro, shown your current pg_hba.conf, said how you want to authenticate users, etc.

Collectives™ on Stack Overflow

Accessing postgres without being root users

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related