ALTER DEFAULT PRIVILEGES FOR ROLE

From: "Hilbert, Karin" <ioh1(at)psu(dot)edu>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: ALTER DEFAULT PRIVILEGES FOR ROLE
Date: 2019-01-30 18:57:08
Message-ID: DM6PR02MB48761279ADE0CD89EF417E4189900@DM6PR02MB4876.namprd02.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
Email - ioh1(at)psu(dot)edu
IM - ioh1(at)chat(dot)psu(dot)edu

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-01-30 19:16:14 Re: ALTER DEFAULT PRIVILEGES FOR ROLE
Previous Message Tom Lane 2019-01-30 18:08:48 Re: Old tsearch functions