MODE=PostgreSQL[create role with password]

[gilbertoca]

OS: opensuse 11.2
java: openjdk 1.6.0
h2: 1.2.131

Sql statement:
[code]
CREATE ROLE park LOGIN
ENCRYPTED PASSWORD 'md5cc67fcf394136f13be5b9615a8ff6310'
NOSUPERUSER NOINHERIT CREATEDB CREATEROLE;
[/code]

The error:
[quote]
Error: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement
"CREATE ROLE PARK LOGIN[*]
ENCRYPTED PASSWORD 'md5cc67fcf394136f13be5b9615a8ff6310'
NOSUPERUSER NOINHERIT CREATEDB CREATEROLE "; SQL statement:
CREATE ROLE park LOGIN
ENCRYPTED PASSWORD 'md5cc67fcf394136f13be5b9615a8ff6310'
NOSUPERUSER NOINHERIT CREATEDB CREATEROLE [42000-131], SQL State:
42000, Error Code: 42000
[/quote]

Can anyone show more role creation example?
If the "CREATE ROLE" has just this syntax (http://www.h2database.com/
html/grammar.html#create_role), so I can't neither assign password nor
grants to the role. Is that right?
I'm trying to convert a postgresql database to h2.


Regards,

Gilberto Caetano de Andrade
www.secad.to.gov.br
blog.gilbertoca.com

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[gilbertoca]

I forgot to mention another error while creating one SCHEMA:
[code]
CREATE SCHEMA park
AUTHORIZATION park;
GRANT ALL ON SCHEMA park TO park;
[/code]

error:
[quote]
Error: org.h2.jdbc.JdbcSQLException: Table "SCHEMA" not found; SQL
statement:

GRANT ALL ON SCHEMA park TO park [42102-131], SQL State: 42S02, Error
Code: 42102
Error occured in:

GRANT ALL ON SCHEMA park TO park
[/quote]

But at the end the SCHEMA park was created. See my build log:
[code]
[INFO] [sql:execute {execution: create-schema}]
[INFO] Executing file: /home/gilberto/dev/netbeans-config/park-jpa/
core/src/test/resources/sql/park.usr.sql
[ERROR] Failed to execute: CREATE ROLE park LOGIN ENCRYPTED

PASSWORD 'md5cc67fcf394136f13be5b9615a8ff6310' NOSUPERUSER NOINHERIT
CREATEDB CREATEROLE

[ERROR] org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement

" CREATE ROLE PARK LOGIN[*] ENCRYPTED PASSWORD
'md5cc67fcf394136f13be5b9615a8ff6310' NOSUPERUSER NOINHERIT CREATEDB
CREATEROLE "; SQL statement:
CREATE ROLE park LOGIN ENCRYPTED PASSWORD
'md5cc67fcf394136f13be5b9615a8ff6310' NOSUPERUSER NOINHERIT CREATEDB
CREATEROLE [42000-131]

[ERROR] Failed to execute: GRANT ALL ON SCHEMA park TO park
[ERROR] org.h2.jdbc.JdbcSQLException: Table "SCHEMA" not found; SQL
statement:
GRANT ALL ON SCHEMA park TO park [42102-131]
[INFO] 1 of 3 SQL statements executed successfully
[INFO] [compiler:compile {execution: default-compile}]
[/code]

Thanks,

Gilberto

[Thomas Mueller]

Hi,

> If the "CREATE ROLE" has just this syntax (http://www.h2database.com/
> html/grammar.html#create_role), so I can't neither assign password nor
> grants to the role. Is that right?

Yes. In H2, only users can log in. Roles can not log in.

H2 doesn't support all features of PostgreSQL. I suggest to read the
H2 documentation to find out what features are supported.

It's still good if you send a mail for feature requests of course. In
that case, it's very good to know why exactly you need the feature. In
your case, does the role log in? If yes, why?

Regards,
Thomas

[gilbertoca]

Hi, Thomas!
Sorry for delay, I've to disable email notification because this group
has high traffic. :)

On May 11, 4:31 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> > If the "CREATE ROLE" has just this syntax (http://www.h2database.com/
> > html/grammar.html#create_role), so I can't neither assign password nor
> > grants to the role. Is that right?
>
> Yes. In H2, only users can log in. Roles can not log in.

Since 8.1 version PostgreSQl uses users and groups as ROLE. That was
the point!

But don't worry, I'm just starting to study JPA and trying to
initialize the project using maven. I would like to use just one init
file for that, but I've discovered that isn't easy.
So I've created the init file for all the databases I'm going test
(http://code.google.com/p/construtor/source/browse/#svn/trunk/park-jpa/
core/src/test/resources/sql).

Thanks,

Gilberto

[Thomas Mueller]

Hi,

> PostgreSQL uses users and groups as ROLE

Thanks! I didn't know about that... I sounds like a good idea, it's
simpler. I will add a feature request, but not implement it right now.

Regards,
Thomas