Thread: Regarding timezone

Dev Kumkar <devdas.kumkar@gmail.com> writes:
> I want to change the timezone to Europe/Moscow. Moscow timezone is changing
> from +4 to +3

You'd want to get a new version of the IANA timezone database files for
that.  Depending on what packaging you're using, this might be an
operating-system update not a Postgres update.  If you are relying
on the Postgres copies, you'd have to update src/timezone/data/ and
then do a "make install" in src/timezone.

            regards, tom lane

On 09/11/2014 10:59 PM, Dev Kumkar wrote:
>
> On Wed, Sep 10, 2014 at 8:43 PM, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     You'd want to get a new version of the IANA timezone database files for
>     that.  Depending on what packaging you're using, this might be an
>     operating-system update not a Postgres update.  If you are relying
>     on the Postgres copies, you'd have to update src/timezone/data/ and
>     then do a "make install" in src/timezone.
>
>                              regards, tom lane
>
>
> Thanks Tom !
> Actually am using the postgres binaries and not building from sources.
> Is there any solution if using the binaries and want to update timezone
> settings?

What OS and what packaging?

>
> Regards...


--
Adrian Klaver
adrian.klaver@aklaver.com

On 09/16/2014 11:43 PM, Dev Kumkar wrote:
> On Tue, Sep 16, 2014 at 6:16 PM, Dev Kumkar <devdas.kumkar@gmail.com
> <mailto:devdas.kumkar@gmail.com>> wrote:
>
>     On Fri, Sep 12, 2014 at 7:31 PM, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>
>         What OS and what packaging?
>
>     For both windows-64-bit and Linux-64-bit.
>     PostgreSQL version - 9.3.4
>
>     I believe the file "pgsql/share/postgresql/timezone/Europe/Moscow"
>     will require changes.
>     As the above changes will then be reflected in the function
>     *pg_timezone_names()* which will modify the output as follows:
>
>     << "Europe/Moscow";"MSK";"04:00:00";f
>      >> "Europe/Moscow";"MSK";"03:00:00";f
>
>     Can I get a modified version of this file?
>
>     Regards...
>
>
> Any inputs?

Tom already answered this upstream:

http://www.postgresql.org/message-id/4192.1410362010@sss.pgh.pa.us

It is possible that the binary package(s) you are using do not use the
Postgres built in timezone information. They can be compiled to use the
OS timezone database:

http://www.postgresql.org/docs/9.3/interactive/install-procedure.html

--with-system-tzdata=DIRECTORY

     PostgreSQL includes its own time zone database, which it requires
for date and time operations. This time zone database is in fact
compatible with the "zoneinfo" time zone database provided by many
operating systems such as FreeBSD, Linux, and Solaris, so it would be
redundant to install it again. When this option is used, the
system-supplied time zone database in DIRECTORY is used instead of the
one included in the PostgreSQL source distribution. DIRECTORY must be
specified as an absolute path. /usr/share/zoneinfo is a likely directory
on some operating systems. Note that the installation routine will not
detect mismatching or erroneous time zone data. If you use this option,
you are advised to run the regression tests to verify that the time zone
data you have pointed to works correctly with PostgreSQL.

     This option is mainly aimed at binary package distributors who know
their target operating system well. The main advantage of using this
option is that the PostgreSQL package won't need to be upgraded whenever
any of the many local daylight-saving time rules change. Another
advantage is that PostgreSQL can be cross-compiled more
straightforwardly if the time zone database files do not need to be
built during the installation.


So you will need to determine what method you binaries use. If they are
using the system timezone data, you will need to update that.

>
> Regards...


--
Adrian Klaver
adrian.klaver@aklaver.com

Hi list,
I have a strange problem in postgres (PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 64-bit), there are granted
privilegeson schemas, tables, columns for roles that don't exist. 


Example:
In pgAdmin for schema user_data the follwing wrong grants are reported:
...
GRANT ALL ON SCHEMA user_data TO "482499";
GRANT ALL ON SCHEMA user_data TO "17708";
...


Problem is:
- these roles don't exist,
- they can't be dropped (DROP ROLE "482499"; => FEHLER:  Rolle „482499“ existiert
nicht)
- grants can't be revoked (REVOKE ALL ON SCHEMA "user_data" FROM "482499"; => FEHLER: 
Rolle„482499“ existiert nicht) 
- ROLES can be recreated and dropped afterwards, but the grants persists:
    CREATE ROLE "482499";
    DROP OWNED BY "482499";
    REVOKE CONNECT ON DATABASE "wver_ims" FROM "482499";
    REVOKE ALL ON SCHEMA "user_data" FROM "482499";
    DROP ROLE "482499";
- new tables can't be created in schemas with these grants
    CREATE TABLE user_data.test
    (
       id serial,
       PRIMARY KEY (id)
    );
    => FEHLER:  Rolle 17708 wurde gleichzeitig gelöscht
    
    
The roles are not listed in any catalog
    SELECT * FROM information_schema.xxxxxxx WHERE grantee in
('243683','243666','243689','482499','482499','17708');


Only in pg_auth_members there is a set for each of these roles:
    SELECT * FROM pg_catalog.pg_auth_members WHERE member in
('243683','243666','243689','482499','482499','17708');

 

What can I do to get rid of these roles and grants?

 

Ludwig

On 09/17/2014 07:53 AM, Dev Kumkar wrote:
> On Wed, Sep 17, 2014 at 7:24 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     So you will need to determine what method you binaries use. If they
>     are using the system timezone data, you will need to update that.
>
>
> Thanks!
>
> Just a hack here, how about copying
> "pgsql/share/postgresql/timezone/Etc/GMT-3" as
> "pgsql/share/postgresql/timezone/Europe/Moscow"
> This will make the *pg_timezone_names()* function to return output as
> follows:
>  >> "Europe/Moscow";"GMT-3";"03:00:00";f
>
> With this "now()" function returns expected data for Moscow timezone.
> Please let me know your comments here.

I am not that versed in the timezone handling to make a definitive
statement. I would say it should work until you upgrade. At that point
the new Moscow timezone should be correct and the change will not
matter. I would verify on the upgrade though.

>
> Regards..


--
Adrian Klaver
adrian.klaver@aklaver.com

On 09/17/2014 08:08 AM, ludwig@kni-online.de wrote:
> Hi list,
> I have a strange problem in postgres (PostgreSQL 9.3.1, compiled by
> Visual C++ build 1600, 64-bit), there are granted privileges on schemas,
> tables, columns for roles that don't exist.

So how did the data get into the database?

>
> Example:
> In pgAdmin for schema user_data the follwing wrong grants are reported:
> ...
> GRANT ALL ON SCHEMA user_data TO "482499";
> GRANT ALL ON SCHEMA user_data TO "17708";

Where these actual roles at some point in time?

> ...
>
> Problem is:
> - these roles don't exist,
> - they can't be dropped (DROP ROLE "482499"; => FEHLER:  Rolle „482499“
> existiert nicht)
> - grants can't be revoked (REVOKE ALL ON SCHEMA "user_data" FROM
> "482499"; => FEHLER:  Rolle „482499“ existiert nicht)
> - ROLES can be recreated and dropped afterwards, but the grants persists:
>      CREATE ROLE "482499";
>      DROP OWNED BY "482499";
>      REVOKE CONNECT ON DATABASE "wver_ims" FROM "482499";
>      REVOKE ALL ON SCHEMA "user_data" FROM "482499";
>      DROP ROLE "482499";
> - new tables can't be created in schemas with these grants
>      CREATE TABLE user_data.test
>      (
>         id serial,
>         PRIMARY KEY (id)
>      );
>      => FEHLER:  Rolle 17708 wurde gleichzeitig gelöscht
>
>
> The roles are not listed in any catalog
>      SELECT * FROM information_schema.xxxxxxx WHERE grantee in
> ('243683','243666','243689','482499','482499','17708');
>
> Only in pg_auth_members there is a set for each of these roles:
>      SELECT * FROM pg_catalog.pg_auth_members WHERE member in
> ('243683','243666','243689','482499','482499','17708');

What does pg_auth_members show for the problem roles?

> What can I do to get rid of these roles and grants?
> Ludwig


--
Adrian Klaver
adrian.klaver@aklaver.com

Dev Kumkar <devdas.kumkar@gmail.com> writes:
> Which upgraded postgres version will have the corrected Moscow timezone?

The next set of minor releases, whenever those are (and no, there's
no schedule).

> Tom, can you too provide your comments on the mentioned hack here.

It would break timezone reporting for historical Moscow timestamps, ie
they'd be printed as GMT-3 whether or not that was applicable at the time.
Don't know whether you care about that.

            regards, tom lane

On 09/17/2014 12:26 PM, Dev Kumkar wrote:
> On Thu, Sep 18, 2014 at 12:01 AM, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     The next set of minor releases, whenever those are (and no, there's
>     no schedule).
>
>
> I hope the binaries archive containing ""pgsql/share/postgresql/
> timezone/Europe/Moscow" uploaded at
> http://www.enterprisedb.com/products-services-training/pgbindownload
> will also get corrected.

They will.

>
>     It would break timezone reporting for historical Moscow timestamps, ie
>     they'd be printed as GMT-3 whether or not that was applicable at the
>     time.
>     Don't know whether you care about that.
>
>
> The column storing datetime is of type "timestamp" and timezone is not
> being stored. So should not be an issue for this particular use-case.
> For "timestamp with time zone" that was definitely an issue.
> By the way won't that be issue anyways once the Moscow timezone is
> corrected in upgraded postgres version?

No, because a timezone(versus an offset) definition includes a
historical record of the changes in the timezone. For all the gory
details see:

http://en.wikipedia.org/wiki/Tz_database

>
> Regards...


--
Adrian Klaver
adrian.klaver@aklaver.com

On 09/17/2014 01:14 PM, Dev Kumkar wrote:
> On Thu, Sep 18, 2014 at 1:22 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 09/17/2014 12:26 PM, Dev Kumkar wrote:
>
>         I hope the binaries archive containing ""pgsql/share/postgresql/
>         timezone/Europe/Moscow" uploaded at
>         http://www.enterprisedb.com/__products-services-training/__pgbindownload
>         <http://www.enterprisedb.com/products-services-training/pgbindownload>
>         will also get corrected.
>
>
>     They will.
>
>
> Thanks, on 25 October 2014 the timezone will change permanently.
> Currently binaries are at 9.3.5.1 level, will keep an watch on the update.
>
>     No, because a timezone(versus an offset) definition includes a
>     historical record of the changes in the timezone. For all the gory
>     details see:
>
>     http://en.wikipedia.org/wiki/Tz_database
>
>
> Great info! So the historical Moscow timestamps will still reflect GMT+4
> based on the datetime.

Or GMT+3 depending on the time of the year and what year:

http://en.wikipedia.org/wiki/Moscow_Time

A timezone definition does not necessarily mean a single offset. For
instance I live on the US West coast(Washington state) so I am in the
America/Los_Angeles(or US/Pacific ) timezone. This means the actual
offset changes over the course of a year and by year:

test=> show timezone;
   TimeZone


------------


  US/Pacific


(1 row)





test=> select now();
               now


-------------------------------


  2014-09-17 13:43:22.546162-07


(1 row)





test=> select '2014-11-03'::timestamptz;
       timestamptz
------------------------
  2014-11-03 00:00:00-08
(1 row)


test=> select '2000-10-30'::timestamptz;
       timestamptz
------------------------
  2000-10-30 00:00:00-08
(1 row)


> Sure, awaiting fix and will cross-check the behavior.


--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

data got into the database with normal update/insert-queries from logged-in database-users using "normal"
PG-Users/roles,

the "ghost-roles" (with these unusual numerical role-names) were never created by me, I don't know where
theycome from. 

 

The query

[SNIP]

SELECT * FROM pg_catalog.pg_auth_members WHERE member in
('243683','243666','243689','482499','482499','17708');

[/SNIP]

 

has the following result:

[SNIP]

roleid;member;grantor;admin_option;


17699;17708;10;f
17699;482499;17687;f
17701;243666;17687;f
17699;243683;17687;f
17710;243689;17687;f


[/SNIP]

 

Yust a thought:

In some schemas the public user has full default-privileges (it's for uploading GIS-data from Shapefiles, each
uploadedfile generates a new table). 

[SNIP]

ALTER DEFAULT PRIVILEGES IN SCHEMA user_data
    GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES
    TO public;


[/SNIP]

 

Another thought:

Each "normal" DB-user has *one* granted role, but some of theses roles themselves can have mutliple granted
subroles.

 

Perhaps a reason for my problems?

 

Ludwig


Gesendet: Mittwoch, 17. September 2014 um 17:33 Uhr
Von: "Adrian Klaver" <adrian.klaver@aklaver.com>
An: "ludwig@kni-online.de" <ludwig@kni-online.de>, pgsql-general@postgresql.org
Betreff: Re: [GENERAL] strange problem with not existing roles

On 09/17/2014 08:08 AM, ludwig@kni-online.de wrote:
> Hi list,
> I have a strange problem in postgres (PostgreSQL 9.3.1, compiled by
> Visual C++ build 1600, 64-bit), there are granted privileges on schemas,
> tables, columns for roles that don't exist.

So how did the data get into the database?

>
> Example:
> In pgAdmin for schema user_data the follwing wrong grants are reported:
> ...
> GRANT ALL ON SCHEMA user_data TO "482499";
> GRANT ALL ON SCHEMA user_data TO "17708";

Where these actual roles at some point in time?

> ...
>
> Problem is:
> - these roles don't exist,
> - they can't be dropped (DROP ROLE "482499"; => FEHLER: Rolle „482499“
> existiert nicht)
> - grants can't be revoked (REVOKE ALL ON SCHEMA "user_data" FROM
> "482499"; => FEHLER: Rolle „482499“ existiert nicht)
> - ROLES can be recreated and dropped afterwards, but the grants persists:
> CREATE ROLE "482499";
> DROP OWNED BY "482499";
> REVOKE CONNECT ON DATABASE "wver_ims" FROM "482499";
> REVOKE ALL ON SCHEMA "user_data" FROM "482499";
> DROP ROLE "482499";
> - new tables can't be created in schemas with these grants
> CREATE TABLE user_data.test
> (
> id serial,
> PRIMARY KEY (id)
> );
> => FEHLER: Rolle 17708 wurde gleichzeitig gelöscht
>
>
> The roles are not listed in any catalog
> SELECT * FROM information_schema.xxxxxxx WHERE grantee in
> ('243683','243666','243689','482499','482499','17708');
>
> Only in pg_auth_members there is a set for each of these roles:
> SELECT * FROM pg_catalog.pg_auth_members WHERE member in
> ('243683','243666','243689','482499','482499','17708');

What does pg_auth_members show for the problem roles?

> What can I do to get rid of these roles and grants?
> Ludwig


--
Adrian Klaver
adrian.klaver@aklaver.com


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

On 09/18/2014 04:12 AM, ludwig@kni-online.de wrote:
> Hi Adrian,
> data got into the database with normal update/insert-queries from
> logged-in database-users using "normal" PG-Users/roles,
> the "ghost-roles" (with these unusual numerical role-names) were never
> created by me, I don't know where they come from.

I should have been more specific.

Did the database get created by restoring a dump file from somewhere, or
via pg_upgrade or just by creating the schema and adding data over time?

The numeric part, at least as shown below, is the oid of the role and
all roles have that. The question is whether pgAdmin is showing the oid
or the actual role name? See below for a queries to help determine that.

> The query
> [SNIP]
> SELECT * FROM pg_catalog.pg_auth_members WHERE member in
> ('243683','243666','243689','482499','482499','17708');
> [/SNIP]
> has the following result:
> [SNIP]
> roleid;member;grantor;admin_option;
> 17699;17708;10;f
> 17699;482499;17687;f
> 17701;243666;17687;f
> 17699;243683;17687;f
> 17710;243689;17687;f
> [/SNIP]

So what is the result if you do?:

select * from pg_roles  where oid in(10, 482499, 17708, 17687);

select * from pg_roles  where rolname in('482499', '17708');

10 should be the postgres role, it is the others that are of interest.

> Yust a thought:
> In some schemas the public user has full default-privileges (it's for
> uploading GIS-data from Shapefiles, each uploaded file generates a new
> table).

So who originaly created the schema?

> [SNIP]
> ALTER DEFAULT PRIVILEGES IN SCHEMA user_data
>      GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
> ON TABLES
>      TO public;
> [/SNIP]
> Another thought:
> Each "normal" DB-user has *one* granted role, but some of theses roles
> themselves can have mutliple granted subroles.
> Perhaps a reason for my problems?

Not sure. At this point just trying to establish the current state.

> Ludwig

--
Adrian Klaver
adrian.klaver@aklaver.com

<div style="font-family: Verdana;font-size: 12.0px;"><div><div>Hi Adrian,</div><div>this database runs as
develop-versionon my PC and was created by hand, no dumps or pg_upgrade.</div><div> </div><div>The same database runs
asproduction-version on another server (PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 32-bit), so far without
theseproblems.</div><div> </div><div>pgAdmin shows a mix of the normal roles and these "ghost-roles", I don't know the
queriesrunning in pgAdmins-background for that result.</div><div> </div><div>[SNIP]</div><div>select * from pg_roles
whereoid in(10, 482499, 17708,
17687);</div><div>=></div><div>[SNIP]</div><div><div>kniprath;t;t;t;t;t;t;f;-1;********;infinity;;17687<br/>
postgres;t;t;t;t;t;t;t;-1;********;infinity;;10</div><div>[/SNIP]</div><div> </div><div>[SNIP]</div><div>select* from
pg_roleswhere rolname in('482499', '17708');</div><div>[/SNIP]</div><div>=> empty result</div><div> </div><div>One
testedworkaround was to dump the schema-contents (tables, sequences, functions etc.), drop and recreate the schema and
restorethe dumped contents.</div><div>But I'm curious about what has caused the problems and how to avoid
them...</div><div> </div><div>Ludwig</div></div><div> <div name="quote" style="margin:10px 5px 5px 10px; padding: 10px
010px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break:
after-white-space;"><divstyle="margin:0 0 10px 0;"><b>Gesendet:</b> Donnerstag, 18. September 2014 um 15:53 Uhr<br
/><b>Von:</b> "AdrianKlaver" <adrian.klaver@aklaver.com><br /><b>An:</b> "ludwig@kni-online.de"
<ludwig@kni-online.de>,pgsql-general@postgresql.org<br /><b>Betreff:</b> Re: [GENERAL] strange problem with not
existingroles</div><div name="quoted-content">On 09/18/2014 04:12 AM, ludwig@kni-online.de wrote:<br /> > Hi
Adrian,<br/> > data got into the database with normal update/insert-queries from<br /> > logged-in database-users
using"normal" PG-Users/roles,<br /> > the "ghost-roles" (with these unusual numerical role-names) were never<br />
>created by me, I don't know where they come from.<br /><br /> I should have been more specific.<br /><br /> Did the
databaseget created by restoring a dump file from somewhere, or<br /> via pg_upgrade or just by creating the schema and
addingdata over time?<br /><br /> The numeric part, at least as shown below, is the oid of the role and<br /> all roles
havethat. The question is whether pgAdmin is showing the oid<br /> or the actual role name? See below for a queries to
helpdetermine that.<br /><br /> > The query<br /> > [SNIP]<br /> > SELECT * FROM pg_catalog.pg_auth_members
WHEREmember in<br /> > ('243683','243666','243689','482499','482499','17708');<br /> > [/SNIP]<br /> > has the
followingresult:<br /> > [SNIP]<br /> > roleid;member;grantor;admin_option;<br /> > 17699;17708;10;f<br />
>17699;482499;17687;f<br /> > 17701;243666;17687;f<br /> > 17699;243683;17687;f<br /> >
17710;243689;17687;f<br/> > [/SNIP]<br /><br /> So what is the result if you do?:<br /><br /> select * from pg_roles
whereoid in(10, 482499, 17708, 17687);<br /><br /> select * from pg_roles where rolname in('482499', '17708');<br /><br
/>10 should be the postgres role, it is the others that are of interest.<br /><br /> > Yust a thought:<br /> > In
someschemas the public user has full default-privileges (it's for<br /> > uploading GIS-data from Shapefiles, each
uploadedfile generates a new<br /> > table).<br /><br /> So who originaly created the schema?<br /><br /> >
[SNIP]<br/> > ALTER DEFAULT PRIVILEGES IN SCHEMA user_data<br /> > GRANT INSERT, SELECT, UPDATE, DELETE,
TRUNCATE,REFERENCES, TRIGGER<br /> > ON TABLES<br /> > TO public;<br /> > [/SNIP]<br /> > Another
thought:<br/> > Each "normal" DB-user has *one* granted role, but some of theses roles<br /> > themselves can
havemutliple granted subroles.<br /> > Perhaps a reason for my problems?<br /><br /> Not sure. At this point just
tryingto establish the current state.<br /><br /> > Ludwig<br /><br /> --<br /> Adrian Klaver<br />
adrian.klaver@aklaver.com<br/><br /><br /> --<br /> Sent via pgsql-general mailing list
(pgsql-general@postgresql.org)<br/> To make changes to your subscription:<br /><a
href="http://www.postgresql.org/mailpref/pgsql-general"
target="_blank">http://www.postgresql.org/mailpref/pgsql-general</a></div></div></div></div></div>

On 09/18/2014 09:44 AM, ludwig@kni-online.de wrote:
> Hi Adrian,
> this database runs as develop-version on my PC and was created by hand,
> no dumps or pg_upgrade.
> The same database runs as production-version on another server
> (PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 32-bit), so far
> without these problems.
> pgAdmin shows a mix of the normal roles and these "ghost-roles", I don't
> know the queries running in pgAdmins-background for that result.

I would say pgAdmin could not find a rolname in pg_roles so it just used
the role oid as the role 'name'.

> [SNIP]
> select * from pg_roles where oid in(10, 482499, 17708, 17687);
> =>
> [SNIP]
> kniprath;t;t;t;t;t;t;f;-1;********;infinity;;17687
> postgres;t;t;t;t;t;t;t;-1;********;infinity;;10
> [/SNIP]
> [SNIP]
> select * from pg_roles where rolname in('482499', '17708');
> [/SNIP]
> => empty result
> One tested workaround was to dump the schema-contents (tables,
> sequences, functions etc.), drop and recreate the schema and restore the
> dumped contents.
> But I'm curious about what has caused the problems and how to avoid them...

Hard to say at this point. The only thing I can point out is the
postgres role granted membership to the 17708 'role' to whatever role
has the oid of 17699 and the kniprath role did the same for the 482499
'role'. Maybe looking up what is the role with an oid of 17699 might jog
the memory, so:

select * from pg_roles where oid = 17699;

The only other thing I can think to do is troll the Postgres logs over
the time period in question for the oids, GRANT, REVOKE, the schema
user_data and see if anything stands out.

> Ludwig



--
Adrian Klaver
adrian.klaver@aklaver.com

Hi List,

a workaround for my own problems as described below:

In the following system-table-colums (type aclitem[]) I replaced all entries with these non-existing Group-Roles with
somethinglike {postgres=arwdDxt/postgres,kniprath=arwdDxt/kniprath}, this resets the privileges to just these two
(Admin-)users. 

   pg_namespace.nspacl

   pg_class.relacl

   pg_default_acl.defaclacl

I dont't think it's good practice to update systemtables manually, but apparently I previously did something in
mydatabase, that messed the contents. 

Ludwig

Gesendet: Mittwoch, 17. September 2014 um 17:08 Uhr
Von: "ludwig@kni-online.de" <ludwig@kni-online.de>
An: pgsql-general@postgresql.org
Betreff: strange problem with not existing roles





Hi list,
I have a strange problem in postgres (PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 64-bit), there are granted
privilegeson schemas, tables, columns for roles that don't exist. 


Example:
In pgAdmin for schema user_data the follwing wrong grants are reported:
...
GRANT ALL ON SCHEMA user_data TO "482499";
GRANT ALL ON SCHEMA user_data TO "17708";
...


Problem is:
- these roles don't exist,
- they can't be dropped (DROP ROLE "482499"; => FEHLER:  Rolle „482499“ existiert
nicht)
- grants can't be revoked (REVOKE ALL ON SCHEMA "user_data" FROM "482499"; => FEHLER: 
Rolle„482499“ existiert nicht) 
- ROLES can be recreated and dropped afterwards, but the grants persists:
    CREATE ROLE "482499";
    DROP OWNED BY "482499";
    REVOKE CONNECT ON DATABASE "wver_ims" FROM "482499";
    REVOKE ALL ON SCHEMA "user_data" FROM "482499";
    DROP ROLE "482499";
- new tables can't be created in schemas with these grants
    CREATE TABLE user_data.test
    (
       id serial,
       PRIMARY KEY (id)
    );
    => FEHLER:  Rolle 17708 wurde gleichzeitig gelöscht
    
    
The roles are not listed in any catalog
    SELECT * FROM information_schema.xxxxxxx WHERE grantee in
('243683','243666','243689','482499','482499','17708');


Only in pg_auth_members there is a set for each of these roles:
    SELECT * FROM pg_catalog.pg_auth_members WHERE member in
('243683','243666','243689','482499','482499','17708');

 

What can I do to get rid of these roles and grants?

 

Ludwig