ALTER DEFAULT PRIVILEGES FOR ROLE

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

ALTER DEFAULT PRIVILEGES FOR ROLE

KARIN SUSANNE HILBERT

After a database was updated by the application, a schema dump showed the following default privilege statements:

--
-- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema: public; Owner: gitlab_dbo
--

ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public REVOKE ALL ON SEQUENCES  FROM <dbowner>;
ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public GRANT SELECT,USAGE ON SEQUENCES  TO <appuser>;


--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: public; Owner: <dbowner>
--

ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public REVOKE ALL ON TABLES  FROM <dbowner>;
ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES  TO <appuser>;

Why would you want to revoke all privileges from the dbowner?
It actually had granted the privileges to PUBLIC, but I revoked those privileges & changed it to the app account.


What is the difference between these statements?:
ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public GRANT ...  TO <appuser>;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ... TO <appuser>;



Karin Hilbert
Database Specialist
Administrative Information Services
Pennsylvania State University
25 Shields Bldg., University Park, PA  16802
Work - 814-863-3633

Reply | Threaded
Open this post in threaded view
|

Re: ALTER DEFAULT PRIVILEGES FOR ROLE

Adrian Klaver-4
On 1/30/19 10:57 AM, Hilbert, Karin wrote:

> After a database was updated by the application, a schema dump showed
> the following default privilege statements:
>
> --
> -- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema:
> public; Owner: gitlab_dbo
> --
>
> ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public REVOKE ALL
> ON SEQUENCES  FROM <dbowner>;
> ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public GRANT
> SELECT,USAGE ON SEQUENCES  TO <appuser>;
>
>
> --
> -- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema:
> public; Owner: <dbowner>
> --
>
> ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public REVOKE ALL
> ON TABLES  FROM <dbowner>;
> ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public GRANT
> SELECT,INSERT,DELETE,UPDATE ON TABLES  TO <appuser>;
>
> Why would you want to revoke all privileges from the dbowner?

You would have to ask the application developer.

> It actually had granted the privileges to PUBLIC, but I revoked those
> privileges & changed it to the app account.

This seems to be a continuation of your previous post. It would seem you
and the application developer need to have a head to head and agree on
what the privilege/permissions policy for this application/database
needs to be.

>
>
> What is the difference between these statements?:
> ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public GRANT ...  
> TO <appuser>;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ... TO <appuser>;

The first is keyed to a ROLE, the second to a SCHEMA.

>
>
>
> Karin Hilbert
> Database Specialist
> Administrative Information Services
> Pennsylvania State University
> 25 Shields Bldg., University Park, PA  16802
> Work - 814-863-3633
> Email - [hidden email]
> IM - [hidden email]
>


--
Adrian Klaver
[hidden email]