By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,691 Members | 2,041 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,691 IT Pros & Developers. It's quick & easy.

Permission Problems:-)?

P: n/a
I'm trying to build a database. But none of the users I try to use works.
I as normal user doesn't work:

:~> createdb mydb
createdb: could not connect to database template1: FATAL: user "adriel" does
not exist

I as postgresql user doesn't work:
:~> createdb mydb
createdb: could not connect to database template1: FATAL: user "mypostgre"
does not exist

I as root user doesn't work:

# createdb mydb
createdb: could not connect to database template1: FATAL: user "root" does
not exist

I can only start postgresql as root. What am I doing wrong here? How do I do
it right?

Thanks,
Jerome
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On Tuesday 27 January 2004 09:31, Jerome Lyles wrote:
I'm trying to build a database. But none of the users I try to use works.

I as normal user doesn't work:
:~> createdb mydb

createdb: could not connect to database template1: FATAL: user "adriel"
does not exist

I as postgresql user doesn't work:
:~> createdb mydb

createdb: could not connect to database template1: FATAL: user "mypostgre"
does not exist


OK - it's complaining it can't connect to template1. This is the database that
contains all the default tables/types/functions etc. that will appear in your
new database.

First thing to check is that PG is running. Does it show up in "ps auxw | grep
postgres"?

If it's running you should check if you can connect to the template1 database
normally. As the postgres user try "psql template1" and see if you can
connect to the DB. If not, you need to look at your pg_hba.conf file
(probably in /var/lib/pgsql/data or similar) and also read the chapter on
client authentication in the manuals (tip - start with trust for localhost
and work outwards).

If PG is running and you can connect to the template1 database, try the
command again (as user postgres). If it still doesn't work log into template1
and try a "CREATE DATABASE" command (see manual for details). Then, come back
to the list and let us know what happened.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #2

P: n/a
Jerome Lyles <su******@hawaii.rr.com> writes:
I'm trying to build a database. But none of the users I try to use works.
I think you are confused about the distinction between Postgres users
and Unix users. The set of user names known to the database doesn't
necessarily have anything to do with the set of login names available on
the local system. (If you think about cases where the database is used
by remote users, you'll see why this is a good idea.) If you want to
use database user names that match up with local user names, you have to
specifically create those database user names with the createuser
script. Initially the only user name known to the database is the
"superuser", who has the right to create more database users.

Now in order to use createuser you need to connect as the database
superuser, which seems to be a tad messed up in itself:
I as postgresql user doesn't work:
:~> createdb mydb
createdb: could not connect to database template1: FATAL: user "mypostgre"
does not exist
The name given to the initial database superuser is the name of the
account that ran initdb. It kinda looks like you changed your mind
about the name of the account owning the postgres installation --- you
seem now to be trying to connect as "mypostgre" but the database has no
such username. If you remember what you used before, you can try
createuser -U oldnamehere mypostgre
and be sure to give mypostgre all permissions that createuser asks
about. Then mypostgre will be a second superuser and you can use that
account to create more users. If you don't remember what it was before,
you may be reduced to blowing away the old installation and making a new
one (stop postmaster, rm -rf old data dir, initdb, start postmaster).
I can only start postgresql as root.


I don't think so --- the postmaster will actively refuse to start if you
try to run it as root. Better take another look at exactly what's
happening.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #3

P: n/a
On Tuesday 27 January 2004 12:05 am, Richard Huxton wrote:

OK - it's complaining it can't connect to template1. This is the database
that contains all the default tables/types/functions etc. that will appear
in your new database.

First thing to check is that PG is running. Does it show up in "ps auxw |
grep postgres"?

If it's running you should check if you can connect to the template1
database normally. As the postgres user try "psql template1" and see if you
can connect to the DB. If not, you need to look at your pg_hba.conf file
(probably in /var/lib/pgsql/data or similar) and also read the chapter on
client authentication in the manuals (tip - start with trust for localhost
and work outwards).

If PG is running and you can connect to the template1 database, try the
command again (as user postgres). If it still doesn't work log into
template1 and try a "CREATE DATABASE" command (see manual for details).
Then, come back to the list and let us know what happened.


I have now created a database as user postgre. Thank you!

However I'm still having problems using it as a normal user. I added myself
to the postgres user group but it didn't work.
This is my default configuration:

# CAUTION: The default configuration allows any local user to connect
# using any PostgreSQL user name, including the superuser, over either
# Unix-domain sockets or TCP/IP.

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD

#local all all trust
# IPv4-style local connections:
#host all all 127.0.0.1 255.255.255.255 trust
# IPv6-style local connections:
#host all all ::1
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust
local all all ident
sameuser
"/var/lib/pgsql/data/pg_hba.conf" 63L, 3374C 63,1
Bot

When I check to see if PG is running as a normal user I get this:

:~> ps auxw | grep postgres
postgres 11956 0.0 0.2 17960 2296 pts/3 S Jan26
0:00 /usr/bin/postmaster -D /var/lib/pgsql/data
postgres 11959 0.0 0.2 8760 2072 pts/3 S Jan26 0:00 postgres: stats
buffer process
postgres 11960 0.0 0.2 7768 2120 pts/3 S Jan26 0:00 postgres: stats
collector process
adriel 14819 0.0 0.0 1828 604 pts/2 R 11:26 0:00 grep postgres

And when I try to create the db as a normal user I get this:

:~> createdb mydb1
createdb: could not connect to database template1: FATAL: user "adriel" does
not exist

So...whats next?
Jerome
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #4

P: n/a
On Tuesday 27 January 2004 06:41 am, Tom Lane wrote:
Jerome Lyles <su******@hawaii.rr.com> writes:
I'm trying to build a database. But none of the users I try to use
works.
I think you are confused about the distinction between Postgres users
and Unix users. The set of user names known to the database doesn't
necessarily have anything to do with the set of login names available on
the local system. (If you think about cases where the database is used
by remote users, you'll see why this is a good idea.) If you want to
use database user names that match up with local user names, you have to
specifically create those database user names with the createuser
script. Initially the only user name known to the database is the
"superuser", who has the right to create more database users.


You're correct, I was confused about this point. As soon as I finish here I
will use the createuser script. I wish I had read your email before I sent
my last one, I think your explanation and createuser are the answer to my
question in that email.
Now in order to use createuser you need to connect as the database

superuser, which seems to be a tad messed up in itself:
I as postgresql user doesn't work:
:~> createdb mydb

createdb: could not connect to database template1: FATAL: user
"mypostgre" does not exist
The name given to the initial database superuser is the name of the
account that ran initdb. It kinda looks like you changed your mind
about the name of the account owning the postgres installation --- you
seem now to be trying to connect as "mypostgre" but the database has no
such username.


Correct again. I tried to set up and use the initial database superuser
(mypostgre). I didn't realise that apt had set up the database superuser
(postgres) when it installed postgresql. When I did realize it I was able to
use it to set up a database.
I can only start postgresql as root.


I don't think so --- the postmaster will actively refuse to start if you
try to run it as root. Better take another look at exactly what's
happening.


Well two out of three isn't bad:-). I'll let you be the judge:
Running the postmaster as root:

adriel@linux:~> su
Password:
linux:/home/adriel # /etc/init.d/postgresql start
Starting PostgreSQL
done
linux:/home/adriel #

I then created a database as the database superuser:

postgres@linux:~> createdb mydb2
CREATE DATABASE

Here is some additional information:

postgres@linux:~> ps auxw | grep postgres
postgres 15646 0.0 0.1 2664 1136 pts/1 S 12:13 0:00 su - postgres
postgres 15647 0.0 0.1 3216 2040 pts/1 S 12:13 0:00 -bash
postgres 18973 0.0 0.2 17960 2296 pts/3 S 14:47
0:00 /usr/bin/postmaster -D /var/lib/pgsql/data
postgres 18975 0.0 0.2 8760 2072 pts/3 S 14:47 0:00 postgres: stats
buffer process
postgres 18976 0.0 0.2 7768 2120 pts/3 S 14:47 0:00 postgres: stats
collector process
postgres 18986 0.0 0.0 2668 708 pts/1 R 14:49 0:00 ps auxw
postgres 18987 0.0 0.0 1828 608 pts/1 R 14:49 0:00 grep postgres
postgres@linux:~>

The only difference that I noticed between the root postmaster and the
postgres postmaster is that the postgres postmaster demanded a password
before opening.
Now to create some users:-).
Thanks,
Jerome


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #5

P: n/a
On Tue, Jan 27, 2004 at 03:08:23PM -1000, Jerome Lyles wrote:
I don't think so --- the postmaster will actively refuse to start if you
try to run it as root. Better take another look at exactly what's
happening.
Well two out of three isn't bad:-). I'll let you be the judge:
Running the postmaster as root:

adriel@linux:~> su
Password:
linux:/home/adriel # /etc/init.d/postgresql start
Starting PostgreSQL
done


If you look closely at the script, you'll see that the postmaster is not
run as root, but rather under "su - postgres". (You should be aware
that the start script is very different and separate from the postmaster
program.) So Tom gets 3/3, and you will know sooner or later that he
scores 100% most of the time.
postgres@linux:~> ps auxw | grep postgres
postgres 18973 0.0 0.2 17960 2296 pts/3 S 14:47 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data


See, this is not running as root.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"¿Cómo puedes confiar en algo que pagas y que no ves,
y no confiar en algo que te dan y te lo muestran?" (Germán Poo)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #6

P: n/a
Jerome Lyles <su******@hawaii.rr.com> writes:
On Tuesday 27 January 2004 06:41 am, Tom Lane wrote:
I don't think so --- the postmaster will actively refuse to start if you
try to run it as root. Better take another look at exactly what's
happening.
Running the postmaster as root: adriel@linux:~> su
Password:
linux:/home/adriel # /etc/init.d/postgresql start
Starting PostgreSQL If you look into that startup script, you'll find it does a su to
postgres before trying to start the postmaster. The postmaster will
barf if you try to invoke it directly as root (this is a security
measure).
I then created a database as the database superuser: postgres@linux:~> createdb mydb2
CREATE DATABASE


Okay, looks like you are set: postgres is the DB superuser, and
should be able to create all the other database users you want.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #7

P: n/a
On Tuesday 27 January 2004 03:51 pm, Alvaro Herrera wrote:
On Tue, Jan 27, 2004 at 03:08:23PM -1000, Jerome Lyles wrote:
I don't think so --- the postmaster will actively refuse to start if
you try to run it as root. Better take another look at exactly what's
happening.


Well two out of three isn't bad:-). I'll let you be the judge:
Running the postmaster as root:

adriel@linux:~> su
Password:
linux:/home/adriel # /etc/init.d/postgresql start
Starting PostgreSQL
done


If you look closely at the script, you'll see that the postmaster is not
run as root, but rather under "su - postgres". (You should be aware
that the start script is very different and separate from the postmaster
program.) So Tom gets 3/3, and you will know sooner or later that he
scores 100% most of the time.
postgres@linux:~> ps auxw | grep postgres
postgres 18973 0.0 0.2 17960 2296 pts/3 S 14:47 0:00
/usr/bin/postmaster -D /var/lib/pgsql/data


See, this is not running as root.


I stand corrected and it feels good:-).
Thanks for your excellent help Tom and Richard.
Sincerely,
Jerome
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.