Remove default privilege from DB

Hi

From: Durumdara [mailto:durumdara@gmail.com]
Sent: Montag, 12. Februar 2018 09:32
To: Postgres General <pgsql-general@postgresql.org>
Subject: Remove default privilege from DB

Hello!

I need to remove default privileges from a Database.

After that some of them remains.

Default access privileges

Owner | Schema | Type | Access privileges

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

postgres | | function | =X/postgres

postgres | | sequence |

postgres | | table |

postgres | | type | =U/postgres

suser | | function | =X/suser

suser | | sequence |

suser | | table |

suser | | type | =U/suser

How to completely remove the last items?

Could you send me one example?

I assume

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE USAGE ON TYPE FROM PUBLIC;

Bye

Charles

Thank you for it!

Best regards

DD

Dear Charles!

2018-02-12 10:03 GMT+01:00 Charles Clavadetscher <clavadetscher@swisspug.org

:

Hi

*From:* Durumdara [mailto:durumdara@gmail.com]
*Sent:* Montag, 12. Februar 2018 09:32
*To:* Postgres General <pgsql-general@postgresql.org>
*Subject:* Remove default privilege from DB

Hello!

I need to remove default privileges from a Database.

After that some of them remains.

Default access privileges

Owner | Schema | Type | Access privileges

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

postgres | | function | =X/postgres

postgres | | sequence |

postgres | | table |

postgres | | type | =U/postgres

*suser | | function | =X/suser*

* suser | | sequence |*

* suser | | table |*

* suser | | type | =U/suser*

How to completely remove the last items?

Could you send me one example?

I assume

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE EXECUTE ON FUNCTIONS FROM
PUBLIC;

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE USAGE ON TYPE FROM PUBLIC;

Bye

Charles

After that:

Default access privileges
Owner | Schema | Type | Access privileges
------------+--------+----------+-------------------
suser | | function |
suser | | sequence |
suser | | table |
suser | | type |
(4 rows)

What are they?

select * from pg_default_acl

24629;0;"r";"{}"
24629;0;"S";"{}"
24629;0;"f";"{}"
24629;0;"T";"{}"

24629 = suser | namespace 0 = none in document
Hmmm... It's very strange for me. I don't find any point which links this
user to this database.

Do you have any idea?

Thanks
dd

Hi

From: Durumdara [mailto:durumdara@gmail.com]
Sent: Donnerstag, 15. Februar 2018 12:41
To: Charles Clavadetscher <clavadetscher@swisspug.org>
Cc: Postgres General <pgsql-general@postgresql.org>
Subject: Re: Remove default privilege from DB

Dear Charles!

2018-02-12 10:03 GMT+01:00 Charles Clavadetscher <clavadetscher@swisspug.org <mailto:clavadetscher@swisspug.org> >:

Hi

From: Durumdara [mailto:durumdara@gmail.com <mailto:durumdara@gmail.com> ]
Sent: Montag, 12. Februar 2018 09:32
To: Postgres General <pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org> >
Subject: Remove default privilege from DB

Hello!

I need to remove default privileges from a Database.

After that some of them remains.

Default access privileges

Owner | Schema | Type | Access privileges

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

postgres | | function | =X/postgres

postgres | | sequence |

postgres | | table |

postgres | | type | =U/postgres

suser | | function | =X/suser

suser | | sequence |

suser | | table |

suser | | type | =U/suser

How to completely remove the last items?

Could you send me one example?

I assume

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE USAGE ON TYPE FROM PUBLIC;

Bye

Charles

After that:

Default access privileges

Owner | Schema | Type | Access privileges

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

suser | | function |

suser | | sequence |

suser | | table |

suser | | type |

(4 rows)

What are they?

I am a bit puzzled. I checked the documentation and execute on function is the hard wired default privilege.

https://www.postgresql.org/docs/current/static/sql-grant.html

PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, or tablespaces. For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions; and USAGE privilege for languages and data types (including domains). The object owner can, of course, REVOKE both default and expressly granted privileges.

So after revoking it from public you should actually get an ACL like {suser=X/suser} and the entry for the grants should make it disapper.

Example:

charles@db.localhost=# \ddp
Default access privileges

Owner | Schema | Type | Access privileges

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

(0 rows)

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

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

(0 rows)

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc REVOKE EXECUTE ON FUNCTIONS FROM public;

ALTER DEFAULT PRIVILEGES

charles@db.localhost=# \ddp

Default access privileges

Owner | Schema | Type | Access privileges

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

charlesc | | function | charlesc=X/charlesc

(1 row)

Now only user charlesc can execute (new) functions created by himself. This is the most typical use case when restricting access to self-made functions.

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

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

25269137 | 0 | f | {charlesc=X/charlesc}

(1 row)

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE charlesc GRANT EXECUTE ON FUNCTIONS TO public;

ALTER DEFAULT PRIVILEGES

charles@db.localhost=# \ddp

Default access privileges

Owner | Schema | Type | Access privileges

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

(0 rows)

charles@db.localhost=# select * from pg_default_acl;

defaclrole | defaclnamespace | defaclobjtype | defaclacl

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

(0 rows)

Now again. everybody can execute functions created by charlesc.

What version of PostgreSQL are you using?
And how did you get those first entries at all?

What happens if you issue

ALTER DEFAULT PRIVILEGES FOR ROLE suser GRANT EXECUTE ON FUNCTIONS TO PUBLIC;

again?

Regards
Charles

select * from pg_default_acl

24629;0;"r";"{}"

24629;0;"S";"{}"

24629;0;"f";"{}"

24629;0;"T";"{}"

24629 = suser | namespace 0 = none in document

Hmmm... It's very strange for me. I don't find any point which links this user to this database.

Do you have any idea?

Thanks

dd

Hello!

2018-02-15 14:19 GMT+01:00 Charles Clavadetscher <clavadetscher@swisspug.org

:

What version of PostgreSQL are you using?

And how did you get those first entries at all?

What happens if you issue

ALTER DEFAULT PRIVILEGES FOR ROLE suser GRANT EXECUTE ON FUNCTIONS TO
PUBLIC;

again?

After that:

24629;0;"S";"{}"
24629;0;"T";"{}"
24629;0;"r";"{}"
24629;0;"f";"{=X/suser}"

ALTER DEFAULT PRIVILEGES FOR ROLE suser revoke EXECUTE ON FUNCTIONS from
PUBLIC;

24629;0;"S";"{}"
24629;0;"T";"{}"
24629;0;"r";"{}"
24629;0;"f";"{}"

PGSQL 9.4, 9.6 on Debian (diff. servers).

The source of the problem would be complicated.

The suser is not superuser, but all db owner was member of him.

The databases sometimes created from template0, sometimes copied from prev.
basic database (create database from another database), or from
backup/restore.
We created suser to see every data in database. But objects are created by
him denied from db owner.
Because of that we added default privileges (suser -> db owner).
The time passed away and many things changes. So we want to remove def.
privileges, but these orphan records remain here.
I removed the actual DB owner from suser's member list, but it have no
effect.

I will try to make backup/restore on an example db to see what happen. I
will try to recreate this strange thing in my test server.

Thanks
dd

Dear Charles - Prof. Xavier? :-)

I made a restore to my local Windows PG.
Here I also see these anomalies.

4594262;0;"S";"{}"
4594262;0;"T";"{}"
4594262;0;"f";"{}"
4594262;0;"r";"{}"

In restore SQL I saw:
ALTER DEFAULT PRIVILEGES FOR ROLE suser REVOKE ALL ON TABLES FROM suser;

Because of that I tried a reverse statement:
ALTER DEFAULT PRIVILEGES FOR ROLE suser grant ALL ON TABLES to suser;

And then one row eliminated.
4594262;0;"S";"{}"
4594262;0;"T";"{}"
4594262;0;"f";"{}"

Then:
ALTER DEFAULT PRIVILEGES FOR ROLE suser grant ALL ON sequences to suser;
4594262;0;"T";"{}"
4594262;0;"f";"{}"

Now I need to find a way to eliminate two last lines somehow.

But it's interesting: the revoking role is makes Black Hole (Sun - poor
Soundgarden...)

dd

Dear Charles!

I did search on backup SQL, and I found 4 lines. All of them needed. I
reversed them (Revoke to Grant):

ALTER DEFAULT PRIVILEGES FOR ROLE suser grant ALL ON TYPES to PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE suser grant ALL ON TYPES to suser;

ALTER DEFAULT PRIVILEGES FOR ROLE suser grant ALL ON functions to PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE suser grant ALL ON functions to suser;

And then all lines gone from query, and from "\ddp".

Ok, it's done.

But the question is: WHY?

When I tried to do same thing but only with one line:

This:
ALTER DEFAULT PRIVILEGES FOR ROLE suser grant ALL ON TYPES to PUBLIC;
Or just this:
ALTER DEFAULT PRIVILEGES FOR ROLE suser grant ALL ON TYPES to suser;

The result was wrong.

I want to know what happened in the background.
I will make "negative" state if I revoke DefACL without prior grant?

Thank you!

dd

On Fri, Feb 16, 2018 at 7:56 AM, Durumdara <durumdara@gmail.com> wrote:

I want to know what happened in the background.
I will make "negative" state if I revoke DefACL without prior grant?

​Not really following the whole thread but figured I'm comment on this
point that confused me in the past as well.​

​Not sure if this is what you mean but there is no concept of "negative
state" in the permissions system. Everything starts out with no
permissions. Grant adds permissions and revoke un-adds granted
permissions.​ Revoking something that doesn't exist is either a no-op or a
warning depending on the context - either way its doesn't setup a
"forbidden" state for the permission.

Revoking/granting on default ACLs never affects already existing objects.

David J.

Greetings,

* David G. Johnston (david.g.johnston@gmail.com) wrote:

On Fri, Feb 16, 2018 at 7:56 AM, Durumdara <durumdara@gmail.com> wrote:

I want to know what happened in the background.
I will make "negative" state if I revoke DefACL without prior grant?

​Not really following the whole thread but figured I'm comment on this
point that confused me in the past as well.​

​Not sure if this is what you mean but there is no concept of "negative
state" in the permissions system. Everything starts out with no
permissions. Grant adds permissions and revoke un-adds granted
permissions.​ Revoking something that doesn't exist is either a no-op or a
warning depending on the context - either way its doesn't setup a
"forbidden" state for the permission.

This isn't entirely correct. Functions are the classic example where
EXECUTE to PUBLIC is part of the default and the "negative" state of
having a function where EXECUTE is REVOKE'd from PUBLIC is entirely
reasonable and even common.

Further, object owners also have a default set of privileges which can
be revoked from them, and that's true of basically all objects.

Revoking/granting on default ACLs never affects already existing objects.

Right, to change existing ACLs one would use GRANT ON ALL or individual
GRANT statements.

Thanks!

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* David G. Johnston (david.g.johnston@gmail.com) wrote:

​Not sure if this is what you mean but there is no concept of "negative
state" in the permissions system. Everything starts out with no
permissions. Grant adds permissions and revoke un-adds granted
permissions.​ Revoking something that doesn't exist is either a no-op or a
warning depending on the context - either way its doesn't setup a
"forbidden" state for the permission.

This isn't entirely correct. Functions are the classic example where
EXECUTE to PUBLIC is part of the default and the "negative" state of
having a function where EXECUTE is REVOKE'd from PUBLIC is entirely
reasonable and even common.

FWIW, I thought David's description was fine. The fact that the initial
state of an object typically includes some positive grants doesn't change
the fact that there's no such thing as a negative grant. In particular,
if there is a GRANT TO PUBLIC, no amount of revoking that privilege from
individual users will have any effect, because the public grant is still
there.

regards, tom lane

Tom,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

* David G. Johnston (david.g.johnston@gmail.com) wrote:

​Not sure if this is what you mean but there is no concept of "negative
state" in the permissions system. Everything starts out with no
permissions. Grant adds permissions and revoke un-adds granted
permissions.​ Revoking something that doesn't exist is either a no-op or a
warning depending on the context - either way its doesn't setup a
"forbidden" state for the permission.

This isn't entirely correct. Functions are the classic example where
EXECUTE to PUBLIC is part of the default and the "negative" state of
having a function where EXECUTE is REVOKE'd from PUBLIC is entirely
reasonable and even common.

FWIW, I thought David's description was fine. The fact that the initial
state of an object typically includes some positive grants doesn't change
the fact that there's no such thing as a negative grant. In particular,
if there is a GRANT TO PUBLIC, no amount of revoking that privilege from
individual users will have any effect, because the public grant is still
there.

What I was particularly picking up on was the comment that "Everything
starts out with no permissions" which implied, at least to me, the idea
that no one has any rights on an object until those rights are GRANT'd,
which isn't correct, as I described.

Thanks!

Stephen

On Fri, Feb 16, 2018 at 8:59 AM, Stephen Frost <sfrost@snowman.net> wrote:

Tom,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

* David G. Johnston (david.g.johnston@gmail.com) wrote:

​Not sure if this is what you mean but there is no concept of

"negative

state" in the permissions system. Everything starts out with no
permissions. Grant adds permissions and revoke un-adds granted
permissions.​ Revoking something that doesn't exist is either a

no-op or a

warning depending on the context - either way its doesn't setup a
"forbidden" state for the permission.

This isn't entirely correct. Functions are the classic example where
EXECUTE to PUBLIC is part of the default and the "negative" state of
having a function where EXECUTE is REVOKE'd from PUBLIC is entirely
reasonable and even common.

FWIW, I thought David's description was fine. The fact that the initial
state of an object typically includes some positive grants doesn't change
the fact that there's no such thing as a negative grant. In particular,
if there is a GRANT TO PUBLIC, no amount of revoking that privilege from
individual users will have any effect, because the public grant is still
there.

What I was particularly picking up on was the comment that "Everything
starts out with no permissions" which implied, at least to me, the idea
that no one has any rights on an object until those rights are GRANT'd,
which isn't correct, as I described.

I think its more helpful to say "there are no inherent permissions" and
have someone ask "then why can I execute this function" and respond
"because the system places every user in to a group called PUBLIC which
(like all roles/groups) has a set of granted inheritable permissions; and
by default it gets ... on all newly created ...;". And, no, you
unfortunately cannot remove a user from the PUBLIC group. You can revoke
the permissions granted to PUBLIC by executing using REVOKE ... FROM PUBLIC.

Basically, you force the learner to confront/incorporate how the
default/PUBLIC privilege defaults system is configured to enhance the
usability of a "deny all first" based permission system. Starting from a
point of "users have these defaults" obscures that fact and leads to
questions like this - which as I said I had myself way back when. Starting
from the deny-all beginning was an "ah-ha! moment for me".

David J.