A user atribute question

[stan]

Just starting to expore setting up roles & useres. I ran this statement:

GRANT CONNECT ON DATABASE stan TO employee;

But yet \du still reports:

 employee| Cannot login 

 What am I doing wrong?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: A user atribute question

[Rob Sargent]

> On Aug 17, 2019, at 9:45 AM, stan  wrote:
> 
> Just starting to expore setting up roles & useres. I ran this statement:
> 
> GRANT CONNECT ON DATABASE stan TO employee;
> 
> But yet \du still reports:
> 
> employee| Cannot login 
> 
> What am I doing wrong?
> 
> -- 
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
>-- Benjamin Franklin
> 
> 
Does the role have the login option set?

Re: A user atribute question

[Tom Lane]

stan  writes:
> Just starting to expore setting up roles & useres. I ran this statement:
> GRANT CONNECT ON DATABASE stan TO employee;

> But yet \du still reports:
>  employee| Cannot login 
>  What am I doing wrong?

That's not a permissions issue, it's a role-property issue; the
role is marked as not being allowed to be used as a login role.
Probably because you said CREATE ROLE not CREATE USER.

You can fix it with ALTER ROLE ... LOGIN or something along
that line, check the ALTER ROLE page.

regards, tom lane

Re: Question on pgwatch

[Luca Ferrari]

On Fri, Aug 16, 2019 at 11:57 PM Bikram MAJUMDAR
 wrote:
> When I go to the URL link for pgwatch that you have given I see the following 
> :  How do I get to pgwatch docker download and install on my linux server?

I don't want to be harsh, but you should start doing your homework and
report about problems.
Since I'm not a pgwatch user, as already stated, I believe that you
should get the image with the following:

docker pull cybertec/pgwatch2

as reported here .

Luca

Re: Transaction state on connection Idle/Open/Failed

[Adrian Klaver]

On 8/16/19 12:57 PM, David Wall wrote:
In JDBC (latest version), I'm using the 
org.postgresql.jdbc.PgConnection.getTransactionState() that returns an 
enumeration of IDLE, OPEN or FAILED.


I am familiar with IDLE, meaning the connection has a new transaction 
started, but isn't doing anything.  We think of this as the "normal" 
state for our connections.


How does OPEN compare to IDLE?  If we stop using a connection that's in 
the OPEN state, is something wrong?  That is, does it have pending 
updates that require a commit/rollback?


Same for FAILED.  If we no longer need a connection in the FAILED state, 
is something wrong?  Does it have pending updates that require a 
commit/rollback (assuming in this state rollback makes more sense).


I'm really trying to find out what it means for the connection to be in 
OPEN or FAILED states compared to IDLE.


https://jdbc.postgresql.org/development/privateapi/org/postgresql/core/ProtocolConnection.html



Thanks,
David







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Unexpected "canceling statement due to user request" error

[Adrian Klaver]

On 8/16/19 2:02 PM, Will Storey wrote:

Hi!

I have a query that fails due to this error and I'm trying to understand
why.

My understanding is I should only see this error if I cancel a query
manually, such as with kill -INT or with pg_cancel_backend(). However I
can't find anything doing that.

The query looks like this:

 SELECT *
 FROM (
   SELECT
 c1, c2, [...],
 EXTRACT(EPOCH FROM time) AS epoch,
 to_rfc3339_us(time) AS time_rfc3339
   FROM t1
   WHERE
 NOT EXISTS (SELECT 1 FROM t2 WHERE c2 = ?) AND
 c2 = ? AND
 time < ? AND
 time > ?::timestamptz - ? * interval '1 day'
   LIMIT ?
 ) AS s
 ORDER BY epoch DESC

t1 is partitioned on time, by month.

I run it using Perl's DBI with DBD::Pg. I wrap it in a transaction where I
first run:

 SET LOCAL statement_timeout TO 1000

I know this query can time out, and it does, resulting in the error I
expect: "canceling statement due to statement timeout". The problem is
occasionally I see this other error: "canceling statement due to user
request".

Looking at the query logs, when the query fails with the user request
error, the query reached the statement timeout (at least in cases I
checked). In one instance the duration shows as 1283ms for example.

Could there be any situation where a query getting cancelled due to a
statement timeout be reported as cancelled due to user request? Or do you
have any ideas about what might be going on?


Here is the relevant source:

https://doxygen.postgresql.org/postgres_8c.html#ab7af3ee6ad40efb8454c6f2f790d9588

Start at around line 3078.



This is on PostgreSQL 9.6.14 on Ubuntu Xenial.

Thank you!






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Unexpected "canceling statement due to user request" error

[Will Storey]

On Sat 2019-08-17 10:32:40 -0700, Adrian Klaver wrote:
> > I know this query can time out, and it does, resulting in the error I
> > expect: "canceling statement due to statement timeout". The problem is
> > occasionally I see this other error: "canceling statement due to user
> > request".
> > 
> > Looking at the query logs, when the query fails with the user request
> > error, the query reached the statement timeout (at least in cases I
> > checked). In one instance the duration shows as 1283ms for example.
> > 
> > Could there be any situation where a query getting cancelled due to a
> > statement timeout be reported as cancelled due to user request? Or do you
> > have any ideas about what might be going on?
> 
> Here is the relevant source:
> 
> https://doxygen.postgresql.org/postgres_8c.html#ab7af3ee6ad40efb8454c6f2f790d9588
> 
> Start at around line 3078.

Thanks for the pointer!

It looks like we'd have to be entering that section and finding the
statement timeout not set, otherwise we'd not fall through to the "user
request" case.

But as far as I can tell, the only reason my query would be cancelled is
because of the statement timeout. I wonder if there is some way the
statement timeout flag is being lost/cleared. I see there's situations
where we clear timeout flags.

Something I forgot to mention: The queries are against a hot standby
server.

RE: Error message restarting a database

[Begin Daniel]

From: Adrian Klaver 
Sent: Sunday, January 27, 2019 8:18:08 PM
To: Begin Daniel
Cc: pgsql-general
Subject: Re: Error message restarting a database

On 1/27/19 2:45 PM, Begin Daniel wrote:

>> If you go to PGDATA.pg_tblspc  do you have links to the tablespaces?
>>
>> I only installed one instance of Postgres on my PC, which I use to manage 
>> two databases (postgres and osmdump). osmdump is the database that complains.
>>
>> Regarding the links to the tablespaces, I first ran the following request.
>> SELECT spcname FROM pg_tablespace; and got the following list.
>> "pg_default"
>> "pg_global"
>> "workspace"
>> "datadrive1"
>> "datadrive2"
>> "datadrive3"
>> "datadrive0"
>>
>> I went to E:\pgsqlData\pg_tblspc and found the links to the five last 
>> tablespaces above (I manually created them, the first two were created when 
>> I installed Postgres).
>> E:\pgsqlData\pg_tblspc \113608\PG_9.3_201306121\18364 link to the 888 items 
>> mentioned previously (physically stored in K:\pgsqlData\pg_tblspc...)
>
> Hmm, K:\pgsqlData\pg_tblspc looks suspiciously like something
> masquerading as another PGDATA directory. Does a directory listing show
> what is shown in?:
>
> https://www.postgresql.org/docs/10/storage-file-layout.html
>
>> I also found a PG_VERSION file in E:\pgsqlData. The file contains the value 
>> 9.3
>>
>> Daniel
>>
>
> Sorry, bad copy paste, you should have read 
> K:\pgsqlData\PG_9.3_201306121\18364
> The suggested content is found only in E:\pgsqlData, including PG_VERSION 
> file and pg_tblspc subdirectory
>

Your original post had:

FATAL: *"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data
directory

PG. 9.3_ 201306121/18364 does not look like PG_9.3_201306121\18364.
To me it looks like a case of corrupted symlink(or whatever it is called
on Windows).

--
Adrian Klaver
adrian.kla...@aklaver.com

You were right. I was able to correct them and restart the database.
Thank

Daniel

How to determine what is preventing deleting a rule

[stan]

I created some roles that I think are badly named, as my understanding of
them increases.

I have deleted,  what i thought were all the objects referenced by these roles,
but I still get an error saying there is one object they reference.

How can I find out what this object is?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: How to determine what is preventing deleting a rule

[Tom Lane]

stan  writes:
> I have deleted,  what i thought were all the objects referenced by these 
> roles,
> but I still get an error saying there is one object they reference.
> How can I find out what this object is?

The error should tell you, if you're connected to the database where
that object is.

regression=# create database d1;
CREATE DATABASE
regression=# create database d2;
CREATE DATABASE
regression=# create user joe;
CREATE ROLE
regression=# \c d2 joe
You are now connected to database "d2" as user "joe".
d2=> create table tt(d1 int);
CREATE TABLE
d2=> \c d1 postgres
You are now connected to database "d1" as user "postgres".
d1=# drop user joe;
ERROR:  role "joe" cannot be dropped because some objects depend on it
DETAIL:  1 object in database d2
d1=# \c d2
You are now connected to database "d2" as user "postgres".
d2=# drop user joe;
ERROR:  role "joe" cannot be dropped because some objects depend on it
DETAIL:  owner of table tt

Unfortunately, we can't see into the catalogs of a different database
to explain about dependencies there ...

regards, tom lane

Roles versus users

[stan]

I am creating an application that will need to have access control. There
will basically be the groups (roles ?):

* normal user (can do insert on a limited sate of tables, and select on a
slightly larger set

* project manager will have some increased insert and select capabilities

* sysadmin will be able to do select and insert on all tables in the schema

There will be more than one person in each of these groups. My original
intent was to create roles, and assign users to appropriate roles, using
inheritance to add increasingly greater capabilities. That is the inheritance
would look like this

normal user <- project manager <- sysadmin

But, I have run up ion a note in the documentation that says that create user
is actually a synonym for create role.

So, should I just create roles for each user?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Roles versus users

[Adrian Klaver]

On 8/17/19 4:56 PM, stan wrote:

I am creating an application that will need to have access control. There
will basically be the groups (roles ?):

* normal user (can do insert on a limited sate of tables, and select on a
slightly larger set

* project manager will have some increased insert and select capabilities

* sysadmin will be able to do select and insert on all tables in the schema

There will be more than one person in each of these groups. My original
intent was to create roles, and assign users to appropriate roles, using
inheritance to add increasingly greater capabilities. That is the inheritance
would look like this

normal user <- project manager <- sysadmin

But, I have run up ion a note in the documentation that says that create user
is actually a synonym for create role.


You need to read the rest of the paragraph:

"The only difference is that when the command is spelled CREATE USER, 
LOGIN is assumed by default, whereas NOLOGIN is assumed when the command 
is spelled CREATE ROLE."


https://www.postgresql.org/docs/11/sql-createrole.html

"CREATE ROLE adds a new role to a PostgreSQL database cluster. A role is 
an entity that can own database objects and have database privileges; a 
role can be considered a “user”, a “group”, or both depending on how it 
is used. ..."





So, should I just create roles for each user?





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to determine what is preventing deleting a rule

[Melvin Davidson]

> How can I find out what this object is?
This query might help if the object is a table:

SELECT c.relname as table,
   a.rolname as owner,
   c.relacl as permits
  FROM pg_class c
  JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE relname NOT LIKE 'pg_%'
  AND relname NOT LIKE 'information%'
  AND relname NOT LIKE 'sql_%'
  AND relkind = 'r'
  AND a.rolname = ''
ORDER BY relname;

Since you have not stated the PostgreSQL version, I can only
assure you this will work for 9.6 and below.

On Sat, Aug 17, 2019 at 7:49 PM Tom Lane  wrote:

> stan  writes:
> > I have deleted,  what i thought were all the objects referenced by these
> roles,
> > but I still get an error saying there is one object they reference.
> > How can I find out what this object is?
>
> The error should tell you, if you're connected to the database where
> that object is.
>
> regression=# create database d1;
> CREATE DATABASE
> regression=# create database d2;
> CREATE DATABASE
> regression=# create user joe;
> CREATE ROLE
> regression=# \c d2 joe
> You are now connected to database "d2" as user "joe".
> d2=> create table tt(d1 int);
> CREATE TABLE
> d2=> \c d1 postgres
> You are now connected to database "d1" as user "postgres".
> d1=# drop user joe;
> ERROR:  role "joe" cannot be dropped because some objects depend on it
> DETAIL:  1 object in database d2
> d1=# \c d2
> You are now connected to database "d2" as user "postgres".
> d2=# drop user joe;
> ERROR:  role "joe" cannot be dropped because some objects depend on it
> DETAIL:  owner of table tt
>
> Unfortunately, we can't see into the catalogs of a different database
> to explain about dependencies there ...
>
> regards, tom lane
>
>
>

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!