Connecting to postgres as ubuntu standard user

Question

I want to connect to my postgresql when running my python program. The issue i face is that my username is dave, but i can only access my database with the user postgres on linux. This constellation never failed me on my mac, because i could start postgres from my "dave" user account. With linux (ubuntu) i can only connect to the database with psql, when switching my user with *sudo -su postgres to the postgres user.

How am i able to start my program from my user dave while accessing the database?

stackoverflow.com/questions/15301826/…

David
– David

2020-06-09 00:42:18 +00:00
Commented Jun 9, 2020 at 0:42 — David
– David, Commented Jun 9, 2020 at 0:42

zedfoxus · Accepted Answer · 2020-06-09 01:18:42Z

1

You'll have to create a user called dave:

Log on using sudo -u postgres
create database davedb;
create user dave with encrypted password 'testing';
grant all privileges on database davedb to dave;

Then, you can log in from your username itself like so:

psql dave davedb
--or--
psql -U dave -d davedb

If it asks you for your password, type it. You should be in then.

answered Jun 9, 2020 at 1:18

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

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

Comments

Konard · Accepted Answer · 2023-09-29 16:23:19Z

In my case, I needed to connect to PostgreSQL from inside its docker container.

So I opened the terminal of the docker container:

docker exec -it deep-postgres bash

Then I changed the user to postgres:

su postgres

And then I started the client:

psql

It all should look like this:

deep@deep:~$ docker exec -it deep-postgres bash
root@7b02446de835:/# su postgres
postgres@7b02446de835:/$ psql
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
psql (12.11 (Debian 12.11-1.pgdg110+1))
Type "help" for help.

postgres=#

Then, if you need to be able to log in with another user, you can create database and user like this:

CREATE database db;
CREATE USER dave WITH encrypted password 'test';
GRANT ALL privileges ON database db TO dave;

Then exit from psql and login using another user:

psql -U dave -d db

It will look like this:

postgres@7b02446de835:/$ psql -U dave -d db
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
psql (12.11 (Debian 12.11-1.pgdg110+1))
Type "help" for help.

db=>

Collectives™ on Stack Overflow

Connecting to postgres as ubuntu standard user

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related