Thread: What am I doing wrong in here?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
I'm tyring to create a new table/database/user; and I get some errors.
I'm running:
postgres@[local]:template1=# SELECT version(); version
- -------------------------------------------------------------------------------------------------------PostgreSQL 7.4
oni686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
20031022 (Red Hat Linux 3.3.2-1)
which has been installed using Lamar's RPMS.
Here is set of commands:
=========================================================
CREATE USER tdmsoftmailserveruser WITH ENCRYPTED PASSWORD 'test'NOCREATEDB NOCREATEUSER;
CREATE DATABASE tdmsoftmailserverWITH ENCODING 'LATIN5' OWNER=tdmsoftmailserveruser;
\c tdmsoftmailserver tdmsoftmailserveruser
CREATE TABLE public.tdmalias ( mid serial, address varchar(255) NOT NULL default '' PRIMARY KEY, goto text NOT NULL,
domainvarchar(255) NOT NULL default '', create_date timestamp NOT NULL DEFAULT 'NOW', change_date timestamp NOT NULL
DEFAULT'NOW', active int2 NOT NULL default '1');
============================
And I get:
NOTICE: CREATE TABLE will create implicit sequence "tdmalias_mid_seq" for
"serial" column "tdmalias.mid"
ERROR: permission denied for schema pg_catalog
Google'd a bit; but could not find a solution for that.
Is is something with schemas?
Regards,
- --
Devrim GUNDUZ
devrim@gunduz.org devrim.gunduz@linux.org.tr http://www.TDMSoft.com
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/7aF+tl86P3SPfQ4RAtC7AJ974H/j5rWlTYP32De+LYLgEb2GmgCePZFW
QHbdSSw9OKvY0cF/nYbUM5g=
=3jq0
-----END PGP SIGNATURE-----
Devrim GUNDUZ (Saturday 27 December 2003 10:12)
> create_date timestamp NOT NULL DEFAULT 'NOW',
> change_date timestamp NOT NULL DEFAULT 'NOW',
Do these actually work? I've always used 'default now()'...
> And I get:
>
> NOTICE: CREATE TABLE will create implicit sequence "tdmalias_mid_seq" for
> "serial" column "tdmalias.mid"
If you want to avoid this, then create your sequences manually instead of
using 'serial'.
\echo '* datasources'
create sequence "datasources_id_seq"
start 1
increment 1
maxvalue 9223372036854775807
minvalue 1
cache 1;
comment on sequence "datasources_id_seq" is 'Datasources ID';
create table "datasources" ( "id" integer not null unique default nextval
('datasources_id_seq'), "name" varchar(32) not null unique, "type_id" varchar(16) not null,
primarykey ("id") );
comment on table "datasources" is 'Datasource Definition';
This also gives you more flexibility since you can change the parameters of
the sequence (oftentimes I use start 0 minvalue 0, instead), and you can use
a name of your own choice. The settings shown are the default when you use
'serial'.
> ERROR: permission denied for schema pg_catalog
The user you create the user as needs to have createuser permission.
select * from "pg_catalog"."pg_user";
...will show you all that you need to know.
alter user "foo" with createuser;
...(run as an appropriate user) will grant the user such permission.
Vertu sæll,
--
Sigþór Björn Jarðarson (Casey Allen Shobe)
cshobe@softhome.net / http://rivyn.livejournal.com
Jabber: sigthor@jabber.org; ICQ: 1494523; AIM/Yahoo: SomeLinuxGuy
Free development contributor of:
> KDE toolbar icons
> Kopete user interface, usability, and testing
> X11 Icelandic Dvorak keymaps
> Reporting of over 100 Kopete bugs
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, On Sat, 27 Dec 2003, Casey Allen Shobe wrote: <snip> > > ERROR: permission denied for schema pg_catalog > > The user you create the user as needs to have createuser permission. > alter user "foo" with createuser; > > ...(run as an appropriate user) will grant the user such permission. Hmm, that solved the problem, thanks. But I still could not understand why the lack of createuser permission caused the error above... Regards, - -- Devrim GUNDUZ devrim@gunduz.org devrim.gunduz@linux.org.tr http://www.TDMSoft.com http://www.gunduz.org -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/7akUtl86P3SPfQ4RAkBRAJ9gHSdmypPYYJRFEkLjgwbcQwB1ugCg1iE2 goLf9LvTeQkblKho+e5yUk8= =Y7Vg -----END PGP SIGNATURE-----
Devrim GUNDUZ (Saturday 27 December 2003 10:45) > > > ERROR: permission denied for schema pg_catalog > > > > The user you create the user as needs to have createuser permission. > > alter user "foo" with createuser; > > > > ...(run as an appropriate user) will grant the user such permission. > > Hmm, that solved the problem, thanks. > > But I still could not understand why the lack of createuser permission > caused the error above... Because database users (and lots of other database information) is stored in the pg_catalog schema. When you create, alter, or drop a user, you are performing an insert, update, or delete on pg_catalog.pg_shadow. I'm not familiar enough with the internals to say exactly how createuser=t in the same table grants update permission to the user, but that is the effect. Vertu sæll, -- Sigþór Björn Jarðarson (Casey Allen Shobe) cshobe@softhome.net / http://rivyn.livejournal.com Jabber: sigthor@jabber.org; ICQ: 1494523; AIM/Yahoo: SomeLinuxGuy Free development contributor of: > KDE toolbar icons > Kopete user interface, usability, and testing > X11 Icelandic Dvorak keymaps > Reporting of over 100 Kopete bugs
Devrim GUNDUZ (Saturday 27 December 2003 10:45) > But I still could not understand why the lack of createuser permission > caused the error above... Though I do think a more clear error in this case would be helpful (*hint hint*). Vertu sæll, -- Sigþór Björn Jarðarson (Casey Allen Shobe) cshobe@softhome.net / http://rivyn.livejournal.com Jabber: sigthor@jabber.org; ICQ: 1494523; AIM/Yahoo: SomeLinuxGuy Free development contributor of: > KDE toolbar icons > Kopete user interface, usability, and testing > X11 Icelandic Dvorak keymaps > Reporting of over 100 Kopete bugs
Devrim GUNDUZ <devrim@gunduz.org> writes:
> And I get:
> NOTICE: CREATE TABLE will create implicit sequence "tdmalias_mid_seq" for
> "serial" column "tdmalias.mid"
> ERROR: permission denied for schema pg_catalog
I can't replicate that here: I get
NOTICE: CREATE TABLE will create implicit sequence "tdmalias_mid_seq" for "serial" column "tdmalias.mid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tdmalias_pkey" for table "tdmalias"
CREATE TABLE
tdmsoftmailserver=>
The nearby comments about needing to be superuser to create a user seem
to me to be well wide of the mark, since you'd have failed much earlier
than this in the script if that were the issue.
What do you get from "select * from pg_namespace" in the database where
this happens?
regards, tom lane
Casey Allen Shobe <cshobe@softhome.net> writes:
> Devrim GUNDUZ (Saturday 27 December 2003 10:45)
>> Hmm, that solved the problem, thanks.
>>
>> But I still could not understand why the lack of createuser permission
>> caused the error above...
> Because database users (and lots of other database information) is stored in
> the pg_catalog schema. When you create, alter, or drop a user, you are
> performing an insert, update, or delete on pg_catalog.pg_shadow.
This analysis is nonsense ... system catalog operations do not do the
same kinds of permission checks as user queries do. Furthermore, if
he'd not had permissions to create users, the initial CREATE USER
command would have failed, and so would CREATE DATABASE (since it would
then be specifying a nonexistent owner name).
My guess is that Devrim interpreted your suggestion as telling him to
make the created user (tdmsoftmailserveruser) a superuser, which would
naturally suppress any and all permissions failures for operations
executed by that user. That's hardly a reasonable answer to his problem
though. As to what his real problem is, I dunno, but I'd like to find
out.
regards, tom lane
Saturday 27 December 2003 13:50 > This analysis is nonsense ... system catalog operations do not do the > same kinds of permission checks as user queries do. Furthermore, if > he'd not had permissions to create users, the initial CREATE USER > command would have failed, and so would CREATE DATABASE (since it would > then be specifying a nonexistent owner name). The CREATE USER *did* fail, didn't it? I'm sorry if I was inaccurate in my response, but I don't believe I was - I think maybe we're just interpreting the E-Mail two different ways. Devrim, if you could copy and paste exactly what you're doing and exactly where the errors appear in a psql session, that would be most helpful. I did nat say that the user had to be a superuser, only that the user had to have createuser permission in order to CREATE USER. And the last sentence of the first paragraph may not be syntactically accurate because I am not familiar with PostgreSQL's internals, but that is the effect of creating a user, as I said in the second paragraph. > My guess is that Devrim interpreted your suggestion as telling him to > make the created user (tdmsoftmailserveruser) a superuser, which would > naturally suppress any and all permissions failures for operations > executed by that user. That's hardly a reasonable answer to his problem > though. That is *NOT* what I suggested at all. > As to what his real problem is, I dunno, but I'd like to find out. Sure. Vertu sæll, -- Sigþór Björn Jarðarson (Casey Allen Shobe) cshobe@softhome.net / http://rivyn.livejournal.com Jabber: sigthor@jabber.org; ICQ: 1494523; AIM/Yahoo: SomeLinuxGuy Free development contributor of: > KDE toolbar icons > Kopete user interface, usability, and testing > X11 Icelandic Dvorak keymaps > Reporting of over 100 Kopete bugs
Casey Allen Shobe <cshobe@softhome.net> writes:
>> My guess is that Devrim interpreted your suggestion as telling him to
>> make the created user (tdmsoftmailserveruser) a superuser, which would
>> naturally suppress any and all permissions failures for operations
>> executed by that user.
> That is *NOT* what I suggested at all.
No, of course not, but that's the only way I can make sense of his
response that said your suggestion had fixed the problem.
regards, tom lane