Home > mailing lists

Thread: permission denied for relation

permission denied for relation

From

Ovid

Date:

30 January 2014, 13:29:36

Hi all,

Struggling to figure out what I'm doing wrong with postgresql 9.1.11.

I've created a user and database like this:

CREATE USER some_user WITH ENCRYPTED PASSWORD '...';

CREATE DATABASE mydatabase ENCODING 'UTF8' OWNER some_user TEMPLATE template0;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO some_user;

I then have a shell script which rebuilds my database, but when I connect with my software, I get this:

DBD::Pg::st execute failed: ERROR: permission denied for relation users [for Statement ...

If it matters, my pg_hba.conf has this:

# Database administrative login by Unix domain socket

local all postgres peer

local all veure_user trust

And uname:

$ uname -a

Linux foo.example.com 3.2.0-4-amd64 #1 SMP Debian 3.2.39-2 x86_64 GNU/Linux

I've gotten this successfully running on my laptop and home machine (both Macs) using an identical code base, but I don't know what I'm missing on the Debian server.

In short, I have a user that requires full SELECT, INSERT, UPDATE, and DELETE privileges on the "mydatabase" database.

Can someone point me in the right direction?

Cheers,

Ovid

--
IT consulting, training, international recruiting
http://www.allaroundtheworld.fr/.
Buy my book! - http://bit.ly/beginning_perl
Live and work overseas - http://www.overseas-exile.com/

Re: permission denied for relation

From

Ovid

Date:

30 January 2014, 13:34:40

And in the above, by "veure_user" in the pg_hba.conf, I obviously meant "some_user".
 
Cheers,
Ovid
--
IT consulting, training, international recruiting
       http://www.allaroundtheworld.fr/.
Buy my book! - http://bit.ly/beginning_perl
Live and work overseas - http://www.overseas-exile.com/


 On Thursday, 30 January 2014, 14:31, Ovid <curtis_ovid_poe@yahoo.com> wrote:
Hi all,

Struggling to figure out what I'm doing wrong with postgresql 9.1.11.

I've created a user and database like this:

    CREATE USER some_user WITH ENCRYPTED PASSWORD '...';
    CREATE DATABASE mydatabase ENCODING 'UTF8' OWNER some_user TEMPLATE template0;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO some_user;
 
I then have a shell script which rebuilds my database, but when I connect with my software, I get this:

    DBD::Pg::st execute failed: ERROR:  permission denied for relation users [for Statement ...

If it matters, my pg_hba.conf has this:

    # Database administrative login by Unix domain socket
    local   all             postgres                                peer
    local   all             veure_user                              trust

And uname:

    $ uname -a
    Linux foo.example.com 3.2.0-4-amd64 #1 SMP Debian 3.2.39-2 x86_64 GNU/Linux

I've gotten this successfully running on my laptop and home machine (both Macs) using an identical code base, but I don't know what I'm missing on the Debian server.

In short, I have a user that requires full SELECT, INSERT, UPDATE, and DELETE privileges on the "mydatabase" database.

Can someone point me in the right direction?

Cheers,
Ovid
--
IT consulting, training, international recruiting
       http://www.allaroundtheworld.fr/.
Buy my book! - http://bit.ly/beginning_perl
Live and work overseas - http://www.overseas-exile.com/

Re: permission denied for relation

From

JotaComm

Date:

30 January 2014, 13:46:27

Hello,

2014-01-30 Ovid <curtis_ovid_poe@yahoo.com>

And in the above, by "veure_user" in the pg_hba.conf, I obviously meant "some_user".

Cheers,
Ovid
--
IT consulting, training, international recruiting
http://www.allaroundtheworld.fr/.
Buy my book! - http://bit.ly/beginning_perl
Live and work overseas - http://www.overseas-exile.com/

On Thursday, 30 January 2014, 14:31, Ovid <curtis_ovid_poe@yahoo.com> wrote:
Hi all,

Struggling to figure out what I'm doing wrong with postgresql 9.1.11.

I've created a user and database like this:

CREATE USER some_user WITH ENCRYPTED PASSWORD '...';
  CREATE DATABASE mydatabase ENCODING 'UTF8' OWNER some_user TEMPLATE template0;
  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO some_user;

I then have a shell script which rebuilds my database, but when I connect with my software, I get this:

  DBD::Pg::st execute failed: ERROR: permission denied for relation users [for Statement ...

If it matters, my pg_hba.conf has this:

# Database administrative login by Unix domain socket
local all postgres peer
local all veure_user trust

And uname:

$ uname -a
Linux foo.example.com 3.2.0-4-amd64 #1 SMP Debian 3.2.39-2 x86_64 GNU/Linux

I've gotten this successfully running on my laptop and home machine (both Macs) using an identical code base, but I don't know what I'm missing on the Debian server.

In short, I have a user that requires full SELECT, INSERT, UPDATE, and DELETE privileges on the "mydatabase" database.

Can someone point me in the right direction?

First: CREATE ROLE and CREATE DATABASE;

After: CREATE TABLEs;

Last: GRANT SELECT,INSERT,UPDATE and DELETE.

Cheers,
Ovid
--
IT consulting, training, international recruiting
http://www.allaroundtheworld.fr/.
Buy my book! - http://bit.ly/beginning_perl
Live and work overseas - http://www.overseas-exile.com/

Regards

--
JotaComm
http://jotacomm.wordpress.com

Re: permission denied for relation

From

Ovid

Date:

30 January 2014, 14:14:03

> First: CREATE ROLE and CREATE DATABASE;

> After: CREATE TABLEs;

> Last: GRANT SELECT,INSERT,UPDATE and DELETE.

OK, I dropped the database. Since I have the user already created, I recreated the database. Then I created all of the tables. Then I did this:

postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO veure_user;

GRANT

postgres=# GRANT INSERT ON ALL TABLES IN SCHEMA public TO veure_user;

GRANT

postgres=# GRANT UPDATE ON ALL TABLES IN SCHEMA public TO veure_user;

GRANT

postgres=# GRANT DELETE ON ALL TABLES IN SCHEMA public TO veure_user;

GRANT

Same error: permission denied for relation "users". And when I do \dt:

Schema | Name | Type | Owner

--------+-------------------+-------+------------

public | users | table | veure_user

So I'm still missing something here :)

I'm sure my password is correct because this works (password in .pgpass, though the fact that I'm connecting suggests that my password is fine):

$ psql -U veure_user -d veure

psql (9.1.11)

Type "help" for help.

Cheers,

Ovid

--
IT consulting, training, international recruiting
http://www.allaroundtheworld.fr/.
Buy my book! - http://bit.ly/beginning_perl
Live and work overseas - http://www.overseas-exile.com/

On Thursday, 30 January 2014, 14:49, JotaComm <jota.comm@gmail.com> wrote:

Hello,

2014-01-30 Ovid <curtis_ovid_poe@yahoo.com>
And in the above, by "veure_user" in the pg_hba.conf, I obviously meant "some_user".

Cheers,
Ovid
--
IT consulting, training, international recruiting
http://www.allaroundtheworld.fr/.
Buy my book! - http://bit.ly/beginning_perl
Live and work overseas - http://www.overseas-exile.com/

On Thursday, 30 January 2014, 14:31, Ovid <curtis_ovid_poe@yahoo.com> wrote:
Hi all,

Struggling to figure out what I'm doing wrong with postgresql 9.1.11.

I've created a user and database like this:

CREATE USER some_user WITH ENCRYPTED PASSWORD '...';
  CREATE DATABASE mydatabase ENCODING 'UTF8' OWNER some_user TEMPLATE template0;
  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO some_user;

I then have a shell script which rebuilds my database, but when I connect with my software, I get this:

  DBD::Pg::st execute failed: ERROR: permission denied for relation users [for Statement ...

If it matters, my pg_hba.conf has this:

# Database administrative login by Unix domain socket
local all postgres peer
local all veure_user trust

And uname:

$ uname -a
Linux foo.example.com 3.2.0-4-amd64 #1 SMP Debian 3.2.39-2 x86_64 GNU/Linux

I've gotten this successfully running on my laptop and home machine (both Macs) using an identical code base, but I don't know what I'm missing on the Debian server.

In short, I have a user that requires full SELECT, INSERT, UPDATE, and DELETE privileges on the "mydatabase" database.

Can someone point me in the right direction?

First: CREATE ROLE and CREATE DATABASE;

After: CREATE TABLEs;

Last: GRANT SELECT,INSERT,UPDATE and DELETE.

Cheers,
Ovid
--
IT consulting, training, international recruiting
http://www.allaroundtheworld.fr/.
Buy my book! - http://bit.ly/beginning_perl
Live and work overseas - http://www.overseas-exile.com/

Regards
--
JotaComm
http://jotacomm.wordpress.com

Re: permission denied for relation

From

Raymond O'Donnell

Date:

30 January 2014, 14:21:50

On 30/01/2014 14:13, Ovid wrote:
>> First: CREATE ROLE and CREATE DATABASE;
>
>> After: CREATE TABLEs;
>
>> Last: GRANT SELECT,INSERT,UPDATE and DELETE.
>
> OK, I dropped the database. Since I have the user already created, I
> recreated the database. Then I created all of the tables. Then I did this:
>
> postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
> postgres=# GRANT INSERT ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
> postgres=# GRANT UPDATE ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
> postgres=# GRANT DELETE ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
>
> Same error: permission denied for relation "users". And when I do \dt:
>
>  Schema |       Name        | Type  |   Owner
> --------+-------------------+-------+------------
>  public | users             | table | veure_user
>
> So I'm still missing something here :)
>
> I'm sure my password is correct because this works (password in .pgpass,
> though the fact that I'm connecting suggests that my password is fine):

Possibly a silly question, but are you sure that your software is
connecting as user "veure_user"?

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: permission denied for relation

From

Adrian Klaver

Date:

30 January 2014, 14:53:38

On 01/30/2014 06:13 AM, Ovid wrote:
>> First: CREATE ROLE and CREATE DATABASE;
>
>> After: CREATE TABLEs;
>
>> Last: GRANT SELECT,INSERT,UPDATE and DELETE.
>
> OK, I dropped the database. Since I have the user already created, I
> recreated the database. Then I created all of the tables. Then I did this:
>
> postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
> postgres=# GRANT INSERT ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
> postgres=# GRANT UPDATE ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
> postgres=# GRANT DELETE ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
>
> Same error: permission denied for relation "users". And when I do \dt:
>
>   Schema |       Name        | Type  |   Owner
> --------+-------------------+-------+------------
>   public | users             | table | veure_user
>
> So I'm still missing something here :)

I would tend to go with Raymond, are you sure about the user you are
connecting as?

It would be helpful to tail the Postgres log and see what the connection
info is.

>
> I'm sure my password is correct because this works (password in .pgpass,
> though the fact that I'm connecting suggests that my password is fine):
>
> $ psql -U veure_user -d veure
> psql (9.1.11)
> Type "help" for help.


Well if your pg_hba.conf is the same as before :

local   all             veure_user                              trust

than a password is not being used. So connecting does not prove a valid
password.

>
> Cheers,
> Ovid
>

--
Adrian Klaver
adrian.klaver@gmail.com

Re: permission denied for relation

From

Klaus Ita

Date:

30 January 2014, 15:32:14

select current_user;

On Thu, Jan 30, 2014 at 3:53 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 01/30/2014 06:13 AM, Ovid wrote:
First: CREATE ROLE and CREATE DATABASE;

After: CREATE TABLEs;

Last: GRANT SELECT,INSERT,UPDATE and DELETE.

OK, I dropped the database. Since I have the user already created, I
recreated the database. Then I created all of the tables. Then I did this:

postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO veure_user;
GRANT
postgres=# GRANT INSERT ON ALL TABLES IN SCHEMA public TO veure_user;
GRANT
postgres=# GRANT UPDATE ON ALL TABLES IN SCHEMA public TO veure_user;
GRANT
postgres=# GRANT DELETE ON ALL TABLES IN SCHEMA public TO veure_user;
GRANT

Same error: permission denied for relation "users". And when I do \dt:

Schema | Name | Type | Owner
--------+-------------------+-------+------------
public | users | table | veure_user

So I'm still missing something here :)

I would tend to go with Raymond, are you sure about the user you are connecting as?

It would be helpful to tail the Postgres log and see what the connection info is.

I'm sure my password is correct because this works (password in .pgpass,
though the fact that I'm connecting suggests that my password is fine):

$ psql -U veure_user -d veure
psql (9.1.11)
Type "help" for help.

Well if your pg_hba.conf is the same as before :

local all veure_user trust

than a password is not being used. So connecting does not prove a valid password.

Cheers,
Ovid

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: permission denied for relation

From

Tom Lane

Date:

30 January 2014, 15:32:55

Adrian Klaver <adrian.klaver@gmail.com> writes:
> I would tend to go with Raymond, are you sure about the user you are
> connecting as?

That's my thought as well.

> It would be helpful to tail the Postgres log and see what the connection
> info is.

Note you will need to turn on "log_connections" to have the relevant
info logged.  If the program with the problem keeps a persistent
connection, you could also look into pg_stat_activity.

            regards, tom lane

Re: permission denied for relation

From

Ovid

Date:

30 January 2014, 15:42:34

I turned on log_connections and that is indeed the problem. Looks like it's my software and not pg.

Thanks all!

Cheers,

Ovid

--
IT consulting, training, international recruiting
http://www.allaroundtheworld.fr/.
Buy my book! - http://bit.ly/beginning_perl
Live and work overseas - http://www.overseas-exile.com/

On Thursday, 30 January 2014, 15:53, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 01/30/2014 06:13 AM, Ovid wrote:
>> First: CREATE ROLE and CREATE DATABASE;
>
>> After: CREATE TABLEs;
>
>> Last: GRANT SELECT,INSERT,UPDATE and DELETE.
>
> OK, I dropped the database. Since I have the user already created, I
> recreated the database. Then I created all of the tables. Then I did this:
>
> postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
> postgres=# GRANT INSERT ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
> postgres=# GRANT UPDATE ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
> postgres=# GRANT DELETE ON ALL TABLES IN SCHEMA public TO veure_user;
> GRANT
>
> Same error: permission denied for relation "users". And when I do \dt:
>
> Schema | Name | Type | Owner
> --------+-------------------+-------+------------
> public | users | table | veure_user
>
> So I'm still missing something here :)

I would tend to go with Raymond, are you sure about the user you are
connecting as?

It would be helpful to tail the Postgres log and see what the connection
info is.

>
> I'm sure my password is correct because this works (password in .pgpass,
> though the fact that I'm connecting suggests that my password is fine):
>
> $ psql -U veure_user -d veure
> psql (9.1.11)
> Type "help" for help.

Well if your pg_hba.conf is the same as before :

local all veure_user trust

than a password is not being used. So connecting does not prove a valid

password.

>
> Cheers,
> Ovid

>

--
Adrian Klaver
adrian.klaver@gmail.com