How to create a database from psql user (not linux user)

Question

I' ve studied some PostgreSQL. In the principle i' ve been teach to create a postgress's user with same name of my linux's user.

Now i want to try to make an application, and for this reason i would like to create a new user associated to the database of the application. How to do that?

I tried this:

1. sudo -u postgres psql postgres
2. postgres=# create user myuser with password 'myuserpassword' createdb;
   CREATE ROLE

Then i try to access the database template1 with the user just created using:

psql template1 myuser

When i type psql template1 and then tab, i don't get the user "myuser" and don't know why.

Thanks for help

Answer 1

When i type psql template1 and then tab, i don't get the user "myuser" and don't know why.

It's bash programmable completion feature which is at play here. It appears that for psql, tab does not suggest a list of user names on the command line when the cursor is after a database name. If does produces that list however, when the cursor is after -U.

It's not clear how it bothers you to create a database, though.

Assuming you can get inside psql with psql template1 myuser, just issue the SQL command

CREATE DATABASE dbname;

If that psql invocation fails, it's probably because the policy of pg_hba.conf for Unix domain socket connection is peer. There will be an explicit error message saying so.

In this case, use psql -U username -h localhost -d template1

Another method is to create the database from a superuser account (typically postgres) just like you created the user, and leave its ownership this user (OWNER option of CREATE DATABASE).