Thread: Permission Problems:-)?
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
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
Jerome Lyles <susemail@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
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
On Tuesday 27 January 2004 06:41 am, Tom Lane wrote: > Jerome Lyles <susemail@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
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)
Jerome Lyles <susemail@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
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