[GENERAL] Reset privileges to builtin defaults
Hi, I've been charged with the administration of a couple of big databases in an old 8.1 cluster. The databases have many administrative issues, privileges being the most prominent for my job. All objects have privileges for specific personal end-user roles (dozens and dozens of privileges each object) instead of using profile roles with the correct privileges and membership granted to specific personal roles. I have identified the profiles and granted them to the personal roles. Now I need to reset all privileges on all objects to the builtin defaults and grant the correct privileges to the profile roles. Part of the job is also migrating to version 9.6 (current stable as of today), but as far as I can see in the documentation there's no clean way of achieving my task not even in that version, so postponing it will not help. So I decided to take a risk and modify system catalogs in a test 8.1 environment with success up to what I can see. What I did is to create three functions that take an OID: one for databases, one for functions and the last for relations (ordinary table, sequence, view with extended capability for materialized view, composite type or foreign table only for the case I'd use these functions in a more recent cluster some time in the future). They all perform three basic things: 1) Set the ACL field of the object to NULL in the correct catalog. 2) Delete the ACL dependencies of the object on any role by deleting from "pg_shdepend". 3) Returning a useful thing: TRUE when privileges are set to the builtin defaults (wether or not the function actually had to do something) and FALSE when something goes wrong. They're declared STRICT, so NULL is returned for NULL input. These functions could be improved by marking them SECURITY DEFINER and letting a role reset the privileges of it's owned objects. Also, other functions should be added for other kind of objects like foreign servers and such. Certainly, many things might also be useful for general use but for my current purposes they suffice (although I wouldn't bother to make these changes as an exercice if someone is interested and, of course, if I'm on the riht way). I added two kind of "properties" to the functions defined by "-- @PROTECT: OWNERSHIP PRIVILEGES" in the code. That makes these functions immune to the privilege reset function for functions (funny English). You might also guess I have another function to change the ownership, but that's another story. Also, superuser owned SECURITY DEFINER functions are untouched and managed manually for now and only because of my particular needs. I tried these functions in the test environment and they seem to work properly in every use case I could think of, although I would be more comfortable if an experienced someone gives me it's impressions on these before I go into production. Please, do! Here goes the code: /* BEGIN CODE */ CREATE OR REPLACE FUNCTION reset_privileges_on_database(oid) RETURNS BOOLEAN AS $BODY$ -- @PROTECT: OWNERSHIP PRIVILEGES -- Reset privileges on the database UPDATE pg_database SET datacl = NULL WHERE datacl IS NOT NULL AND -- Only change when necessary oid = $1; -- Change the requested database -- Delete all ACL dependencies that might have been introduced DELETE FROM pg_shdepend AS s USING pg_class AS c -- USING list item #1 WHERE ( -- Join conditions for the USING list items s.classid = c.oid -- When introduced USING list item #1 ) AND ( -- Specific conditions that normally make up the whole WHERE clause in a SELECT statement s.dbid = 0AND -- We're deleting a shared object's (databases) dependencies s.deptype = 'a' AND -- The dependencies we're deleting are ACLs c.relname = 'pg_database' AND -- The shared object we're deleting dependencies of is a database s.objid = $1 -- Only delete dependencies of the requested database ); -- Return value SELECT EXISTS ( SELECT 1 FROM pg_database WHERE oid = $1 -- Only the requested database ); $BODY$ LANGUAGE sql VOLATILE STRICT; ALTER FUNCTION reset_privileges_on_database(oid) OWNER TO postgres; REVOKE ALL ON FUNCTION reset_privileges_on_database(oid) FROM public; COMMENT ON FUNCTION reset_privileges_on_database(oid) IS 'Diego Molina (20170427): Reset all privileges on a database given by its OID to the builtin defaults. Return values: * NULL: if NULL argument. * TRUE: Privileges have been reset. * FALSE: Privileges have not been reset. Reason: the given database does not exist.'; CREATE OR REPLACE FUNCTION reset_privileges_on_function(oid) RETURNS BOOLEAN AS $BODY$ -- @PROTECT: OWNERSHIP PRIVILEGES -- Reset privileges on the function UPDATE pg_proc AS p SET proacl = NULL FROM pg_authid AS o -- FROM list item #1 (function's owner) WHERE ( -- Join conditions p.proowner =
Re: [GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers
Some errata and round up comments. First of all, I think it's relevant to give some info about the system I'm running: OS: Linux Distro: Gentoo Perl: dev-lang/perl-5.12.3-r1 USE=-* berkdb gdbm Gentoo Base System release: 2.0.2 PORTDIR/metadata/timestamp.chk: Tue, 12 Jul 2011 00:45:01 + 2011/10/2, Diego Augusto Molina diegoaugustomol...@gmail.com: solved if we use no composite at all! Instead of a field with an array of a composite of three instrinsics, use three fields, each of an intrinsic type. See your proposal: Errata: use three fields, each *one being an array of an intrinsic type.* mentioned earlier (to reduce space: 2 bytes of type smallint against variable size of type text; and to keep track of names been used too). You can also set up something like this if you like dimensions: Errata: and to keep track of names *being* used too) Implying that the first dimension is the before value and the second one is for the after value. Any of these prevents us from using composites and makes the box a little wider and simpler. Even further, I would like to keep the logging on demand: Comment on I would like to keep the logging on demand: With this I mean to log only those columns wich are part of the primary key or whose values have changed -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers
2011/9/28, Merlin Moncure mmonc...@gmail.com:
I disagree. unnest() and array_agg() (or, even better, array()
constructor syntax) are an absolute joy to work with and thinking in a
more functional way, which is usually the key to making things run
quickly. Also both functions are trivial to emulate in userland for
compatibility. Arrays of composites IIRC only go back to 8.3 so that
would be a true stopper for any solution in that vein.
Ok, tastes are tastes: I hate to make two or three more levels of
subqueries. Regarding arrays of composites, that would be perfectly
solved if we use no composite at all! Instead of a field with an array
of a composite of three instrinsics, use three fields, each of an
intrinsic type. See your proposal:
create type audit_field_t as (field text, old_value text, new_value text);
Instad, in the audit table you may use:
..., field smallint[], before text[], after text[],...
Note the smallint in field, that means I really want to keep the
reference to the field table. That is for the two reasons I had
mentioned earlier (to reduce space: 2 bytes of type smallint against
variable size of type text; and to keep track of names been used
too). You can also set up something like this if you like dimensions:
..., field smallint[], values text[][],...
Implying that the first dimension is the before value and the second
one is for the after value. Any of these prevents us from using
composites and makes the box a little wider and simpler. Even further,
I would like to keep the logging on demand:
..., field smallint[], is_pk boolean[], { before text[], after text[]
| values text[][] },...
You know what are the braces and pipe for...
So, at the end, we have the entire audet table inside the audit
table, as a series of arrays. We got a real compact table with only
enough data to fully log the changes which triggered the event. No
less, no more.
At this point we know querying this table will be much more slow and
rotation will have to be done more frequently. If we dumprestore the
table somewhere else we will still be able to split the table in the
original two ones, and make indexes, cluster them, and query as
desired. But this can get so complicated that maybe I should implement
a function doing all this. In an event, we are getting less
responsiveness because of this. But a couple of mins more may not be a
problem for most cases. I'm just trying to summarize.
As a rule of thumb, you may need to run a cron job every night or so
to check if 'select count(*) from audit' is bigger than X then rotate
the table (or maybe each X days/weeks/etc.). The smaller the X, the
bigger responsiveness _in_ some cases: if we know an interval in time
we will just have to dumprestore those logs. In other cases this
would not be of much help: if you need to track a tupple to the very
beggining of the times, you'll have a lot of work to do
dumpingrestoring (and so forth... remember to split the table,
indexing...). Still, rotation seems to be a good practice, and you can
include in the cron job the dump/restore part into another server and
then delete the old table. That would save a lot of space in your
production environment.
As for the rest of it, I'd be looking to try and come up with an all
sql implementation. Also you should give an honest comparison between
what you've come up with vs. this:
http://pgfoundry.org/projects/tablelog/.
merlin
All SQL implementation? Didn't we agree that's not possible in
pg=8.4? then what do you mean by that?
About project tablelog, I didn't really try it, but read it's
documentation and seems not appropiate at all for my case. First of
all, it's propose seems to be to log everything in a table to be able
to restore it later as of any time in the past. My propose is to log
to run analysis. Also, it needs to create one table per logged table,
consisting of the same structure of the logged table (without
constraints) plus three, four or five columns for control (depending
on usage, four or five recommended). I have a lot of tables to log
(hundreds!) with small changes to each of them; that means to
duplicate the amount of tables for a few changes. Speaking of
compactness... It also logs everything, not only changed values.
It is written in C, so I assume it runs much, much faster (specially
needed for highly transactional DBs). But it's not proven to be binary
safe (which I don't remember what that is). Bugs: nothing known.
So, if you need to be able to restore your table as of any time, use
tablelog. If you need to run analysis on who did what, use my option.
Finally attaching the code!
Cheers.
--
Diego Augusto Molina
diegoaugustomol...@gmail.com
ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html
/* Created by Diego Augusto Molina in 2011 for Tucuman Government
Re: [GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers
a maximum time for that ;). If instead we would have made a string from the record, we wouldn't have been able to easily track the tupple. Note also the rotate(character) function. It hot-rotates the audit/audet tables similarly to a logrotate program in *nix. At the same time, you never stop logging, and you can dump the old table before dropping it (to save space) and restore it somewhere else to exploit it. We would usually rotate each 6 months or so, but that is shortening each time with the growing system. The *real* system is very discreet with I/U/D operations: it's not usual to have more than one of those operations per second. For higher transactional systems I think this logging system would be more noticeable (right now things go smooth). As I said before, if you have test cases they're very welcome. -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers
2011/9/28, Merlin Moncure mmonc...@gmail.com: Yup -- I get it now. Well, one point about this is that it seems directed towards your personal requirements. This is a very 'heavy' audit system that will not be suitable for high performance transactional systems. That said, it looks well thought out. Storing only the changed fields is pretty clever, but I can't help but wonder if you're better off using arrays for that purpose: create type audit_field_t as (field text, old_value text, new_value text); and inside the audit table itself having fields audit_field_t, and, if at all possible, constructing the array of audit fields in a single expression. This will be much more compact than one record per field -- normally, arrays in table definitions tend to be bad mojo but this is one case they could be useful. Audit records are WORM, 'Write Once Read Maybe', so compactness is important. Obviously, for 9.0+, I would be rigging a solution around hstore for an 'all sql' solution which is usually better if you can get away with it. merlin Well that sounds pretty fair to me. But that flow would not allow me to make partial indexes on primary key fields. As you can see in the audet table, there's a column named is_pk which tells if that column was considered a primary key at the moment of the logging. Normally there's no indexes, but when I have to make some audits I do the following: 1) Dump the audits. 2) Restore somewhere else. 3) Generate some indexes on: timestamp, schema|table, field|is_pk and id (I think, I've got the procedure annotated too, but not here hehe). This indexing is a pain sometimes but even adding it to the time it takes to run one query it is really cheap. Making the indexes gets far more necessary if you run more than one query (which is probably the case). I had considered the solution you're posting, but it would get a _real_ pain to run a query with 'unnest's and 'array_agg's. Also, note that some of these may not be available in versions of PostgreSQL prior to 8.4 (I think), so if you're planning to track the tupple you won't be able to do it in clear (maybe using temp tables). But! all those arguments above get beat by only one you asserted: that WORM thing. You are defintly right about that. Logging in the majority of the cases should be meaningful, light to run, compact/compressed, and rotated so that it doesn't take up all your space with time. Having said that, I'm going to take your advice for the next version, which I hope that also checks some TODO's in the list. When I get home I'll send the current code attached and when I get some fresh air at work I'll make the changes and post the new version. Any other ideas for the new version? (get some previews in the TODO list at the top of the perl trigger function in the attachment of the next mail). -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers
/* Created by Diego Augusto Molina in 2011 for Tucuman
Government,
Argentina. */
/*
-- Execute the following accordingly to your needs.
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plperl';
*/
CREATE ROLE auditor NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
CREATE ROLE audit LOGIN ENCRYPTED PASSWORD 'test.1234' NOSUPERUSER
NOINHERIT NOCREATEDB NOCREATEROLE;
CREATE SCHEMA audit AUTHORIZATION audit;
ALTER ROLE auditor SET search_path=audit;
ALTER ROLE audit SET search_path=audit;
SET search_path=audit;
SET SESSION AUTHORIZATION audit;
CREATE SEQUENCE seq_audit
INCREMENT 1
MINVALUE -9223372036854775808
MAXVALUE 9223372036854775807
START 0
CACHE 1
CYCLE;
ALTER TABLE seq_audit OWNER TO audit;
CREATE SEQUENCE seq_elems
INCREMENT 1
MINVALUE -32768
MAXVALUE 32767
START 0
CACHE 1
CYCLE;
ALTER TABLE seq_elems OWNER TO audit;
CREATE TABLE field
(
id smallint NOT NULL DEFAULT nextval('seq_elems'::regclass),
value name NOT NULL,
CONSTRAINT field_pk PRIMARY KEY (id)
WITH (FILLFACTOR=100),
CONSTRAINT field_uq_value UNIQUE (value)
WITH (FILLFACTOR=100)
)
WITH (
OIDS=FALSE
);
ALTER TABLE field OWNER TO audit;
GRANT ALL ON TABLE field TO audit;
GRANT SELECT ON TABLE field TO auditor;
CREATE TABLE client_inet
(
id smallint NOT NULL DEFAULT nextval('seq_elems'::regclass),
value inet NOT NULL DEFAULT inet_client_addr(),
CONSTRAINT dir_inet_pk PRIMARY KEY (id )
WITH (FILLFACTOR=100),
CONSTRAINT dir_inet_uq_value UNIQUE (value)
WITH (FILLFACTOR=95)
)
WITH (
OIDS=FALSE
);
ALTER TABLE client_inet
OWNER TO audit;
GRANT ALL ON TABLE client_inet TO audit;
GRANT SELECT ON TABLE client_inet TO auditor;
CREATE TABLE schema
(
id smallint NOT NULL DEFAULT nextval('seq_elems'::regclass),
value name NOT NULL,
CONSTRAINT schema_pk PRIMARY KEY (id )
WITH (FILLFACTOR=100),
CONSTRAINT schema_uq_value UNIQUE (value )
WITH (FILLFACTOR=100)
)
WITH (
OIDS=FALSE
);
ALTER TABLE schema
OWNER TO audit;
GRANT ALL ON TABLE schema TO audit;
GRANT SELECT ON TABLE schema TO auditor;
CREATE TABLE table
(
id smallint NOT NULL DEFAULT nextval('seq_elems'::regclass),
value name NOT NULL,
CONSTRAINT table_pk PRIMARY KEY (id )
WITH (FILLFACTOR=100),
CONSTRAINT table_uq_value UNIQUE (value )
WITH (FILLFACTOR=100)
)
WITH (
OIDS=FALSE
);
ALTER TABLE table
OWNER TO audit;
GRANT ALL ON TABLE table TO audit;
GRANT SELECT ON TABLE table TO auditor;
CREATE TABLE user
(
id smallint NOT NULL DEFAULT nextval('seq_elems'::regclass),
value name NOT NULL DEFAULT current_user(),
CONSTRAINT user_pk PRIMARY KEY (id )
WITH (FILLFACTOR=100),
CONSTRAINT user_uq_value UNIQUE (value )
WITH (FILLFACTOR=95)
)
WITH (
OIDS=FALSE
);
ALTER TABLE user
OWNER TO audit;
GRANT ALL ON TABLE user TO audit;
GRANT SELECT ON TABLE user TO auditor;
CREATE TABLE audit
(
id bigint,
type character(1),
tstmp timestamp with time zone DEFAULT now(),
schema smallint,
table smallint,
user smallint,
client_inet smallint,
client_port integer DEFAULT inet_client_port(),
pid integer DEFAULT pg_backend_pid()
)
WITH (
OIDS=FALSE
);
ALTER TABLE audit OWNER TO audit;
GRANT ALL ON TABLE audit TO audit;
GRANT SELECT ON TABLE audit TO auditor;
CREATE TABLE audet
(
id bigint,
field smallint,
is_pk boolean,
before text,
after text
)
WITH (
OIDS=FALSE
);
ALTER TABLE audet OWNER TO audit;
GRANT ALL ON TABLE audet TO audit;
GRANT SELECT ON TABLE audet TO auditor;
CREATE OR REPLACE FUNCTION tgf_ins_audet()
RETURNS trigger AS
$BODY$
begin
execute E'insert into audet_' || tg_argv[0] || E'
(
id,
field,
is_pk,
before,
after
) values
(
'||coalesce(new.id::text,'NULL')||E',
'||coalesce(new.field::text,'NULL')||E',
'||coalesce(new.is_pk::text,'NULL')||E',
'||coalesce(quote_literal(new.before),'NULL')||E',
'||coalesce(quote_literal(new.after),'NULL')||E'
)';
return null;
end;$BODY$ LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION tgf_ins_audet() SET search_path=auditoria;
ALTER FUNCTION tgf_ins_audet() OWNER TO audit;
GRANT EXECUTE ON FUNCTION tgf_ins_audet() TO audit;
CREATE OR REPLACE FUNCTION tgf_ins_audit()
RETURNS trigger AS
$BODY$
begin
execute E'insert into audit_' || tg_argv[0] || E'
(
id,
type,
tstmp,
schema,
table,
user,
client_inet,
client_port,
pid
) values
(
'||coalesce(new.id::text,'NULL')||E',
'||coalesce(quote_literal(new.type),'NULL')||E',
'||coalesce(quote_literal(new.tstmp),'NULL')||E',
'||coalesce(new.schema::text,'NULL')||E',
'||coalesce(new.table::text,'NULL')||E',
'||coalesce(new.user::text,'NULL')||E',
'||coalesce(new.client_inet::text,'NULL')||E
Re: [GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers
Sh*#@, the code was wrapped! if you can suggest me an external web to host the code I can put it there and send the link. Maybe the wiki Nevertheless, I can't seem to publish in the wiki. I think I've followed all the steps but still can't. Anyway, there aren't many long-long lines, except for those whoch are comments. Honestly, I don't remember why I used triggers instead of rules in the audit and audet tables. -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers
2011/9/27, Diego Augusto Molina diegoaugustomol...@gmail.com: Honestly, I don't remember why I used triggers instead of rules in the audit and audet tables. I remember now, that's because in my case, operations over tuples are done very lightly (one or two in the same sentence at a time). So, for a case as such, rules end up beeing more expensive than triggers (right?). -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [Solved] Generic logging system for pre-hstore using plperl triggers
Hi, I had to implement a logging system for some DBs in work. It's generic and implemented using plperl. I've seen tons of mails on the list from newbies asking for something like this using plpgsql, but no specific solution is pointed for them. I think this may discourage some of them. The system is implemented using a separate schema with whatever name you want, and has some really nice features: relevant tables can be rotated to facilitate auditing, each logged action refers to the modified tuple by pk, which you don't even have to (but can) specify when reating the trigger, and some more. Performance is very acceptable (test cases and suggestions are welcome), and never had a problem since the about 8 months it's been working. In the wiki some points are mentioned, but all is _too_ general and for a vanilla pg-8.4 you won't have the hstore facility (note that upgrading is not always a choice). Will it worth pasting the little code in here or that'll be ignored? -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clean way to insert records if they don't exist, update if they do
Hi, first of all, I still haven't tried PG further that 8.4 2011/9/18, Mike Christensen m...@kitchenpc.com: CREATE RULE Pages_Upsert AS ON INSERT TO Pages WHERE EXISTS (SELECT 1 from Pages P where NEW.Url = P.Url) DO INSTEAD UPDATE Pages SET LastCrawled = NOW(), Html = NEW.Html WHERE Url = NEW.Url; This seems to actually work great. It probably loses some points on the code readability standpoint, as someone looking at my code for the first time would have to magically know about this rule, but I guess that could be solved with good code commenting and documentation. I was in a situation like yours and did exactly what you are saying. The insert rule and the documentation (after a week I forgot about the matter and was surprise by the resolution I had taken when I reviewed the comment :). Are there any other drawbacks to this idea, or maybe a your idea sucks, you should do it /this/ way instead comment? I'm on PG 9.0 if that matters. BTW, add my name to the long list of people who would love to see UPSERT and/or MERGE commands in the next version of PG. I used that code for quiet a long time with no drawbacks; it worked perfectly fine for me (tested it nice with a heavy load, in production things were more quiet). What's more, I find this approach very appropiate from the programming point of view. Correct me if I'm wrong. -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Grouping by timestamp, how to return 0 when there's no record
Hi, this may be a start:
-- This will make our day better :)
with base_query (tstmp) as (
select DATE_TRUNC('hour',timestamp) as tstmp
FROM record
WHERE
record.timestamp BETWEEN ( CURRENT_TIMESTAMP + INTERVAL '-7 day') and
(CURRENT_TIMESTAMP) -- this I don't understand
)
-- The following will make the same as your query
select count(*) as qt, tstmp as dfilter from base_query
-- And this adds the zero's
union
select 0 as qt, min(tstmp) + interval generate_series( 0, max(tstmp) -
min(tstmp) ) || ' hours' as dfilter
from base_query
where dfilter not in tstmp; -- Only for those who don't!
Don't forget to tell us how you ended up!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How can I merge two tables?
Mmm... maybe if you can dump them as inserts you'll be able to restore them in one DB only. Important: make the restore connection autocommit (i.e. don't put the --single-transaction flag). That way individual INSERTs will fail without affecting the other data if that INSERT violates the PK. This is slow! specially with large data sets. Once you've got the data in one DB, you can use some replication system if you want to have that DB in all your machines. One of that systems is Slony-I, though I haven't tried it. -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Dump database roles for full restoration
Short description: * Need: migration of database, with roles relevant to it. * From server: pg 8.1; no superuser access. * To server: pg 8.4; full access. * Database in question: depends on few roles, mainly group roles used to set permission; there are other roles (login roles) which are members of these and are intended for the users of the system. * Proposed solution: migrate the database and reset users passwords. Long description: I've been searching a way to achieve this. Having not found it, I proposed the following solution: 1) Get the roles which are explicitly part of the database through ownership or acl. 2) Get the roles which descend into the membership graph (members of roles in (1) plus the members of these and so on). 3) Dump them all (which includes the statements CREATE ROLE, ALTER ROLE and GRANT's referred to role membership). Why not include the upper part of the graph (parent roles)? well, unless any of these are superusers any other permission is irrelevant for the purpose of that single database. In fact, that's a TODO which is not needed for my particular case. I wrote two functions for this: the first one is a privileged one and the second is not. I did the second because I can't access to the passwords of the users in the server I'm migrating and asking the admins to do this is such a pain. After the restoration I will reset the application users' passwords and securely transmit them to them. Additionally, the functions can dump the roles of any number of databases in the cluster. In the attachment you'll find two queries which are later combined and slightly modified to compose the functions. One of the queries is a WITH RECURSIVE ... query, so the PostgreSQL version should be 8.3 or above I guess. Given the case that the cluster which I'm migrating from is 8.1, I had to take a workaround in order for this to work: 1) Dump the needed catalog tables: pg_auth_members, pg_authid for privileged role OR pg_roles for unprivileged role (which is in fact a view), pg_database and pg_shdepend. 2) Restore the tables in some user-schema of some database in the 8.4 cluster. 3) Modify the functions to query the specified schema instead of pg_catalog. I provide this code in the hope that it will be useful to someone, but I also expect some comments and/or corrections. NOTICE: the privileged function was already successfully tested in a production environment between two 8.4 servers. -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- ES: Consulta que lista los oid de los roles de los cuales depende la base de datos, así como los roles que dependen de éstos primeros. -- EN: Query that lists the oids of the roles in which the database deppends on as well as those roles which descend from them. WITH RECURSIVE deps(rol) AS ( SELECT DISTINCT roles.oid FROM pg_catalog.pg_database db INNER JOIN pg_catalog.pg_shdepend dep ON (db.oid = dep.dbid) INNER JOIN pg_catalog.pg_roles roles ON (dep.refobjid = roles.oid) WHERE dep.refclassid = 'pg_catalog.pg_authid'::REGCLASS AND ARRAY[db.datname] @ $1 UNION ALL SELECT am.member AS rol FROM pg_catalog.pg_auth_members am INNER JOIN deps ON (am.roleid = deps.rol) ) SELECT * FROM deps -- ES: La siguiente consulta genera el dump de los roles. La columna 'orden' establece el orden en que deben ejecutarse las sentencias. -- EN: This query generates the dump of the roles. The column 'orden' sets the order in which the sentencies are to be executed. SELECT 1::SMALLINT AS orden, 'CREATE ROLE ' || rolname || ' ' CASE WHEN NOT rolsuper THEN 'NO' ELSE '' END || 'SUPERUSER ' ||-- 'NOSUPERUSER ' || CASE WHEN NOT rolinheritTHEN 'NO' ELSE '' END || 'INHERIT ' || CASE WHEN NOT rolcreaterole THEN 'NO' ELSE '' END || 'CREATEROLE ' || -- 'NOCREATEROLE ' || CASE WHEN NOT rolcreatedb THEN 'NO' ELSE '' END || 'CREATEDB ' || -- 'NOCREATEDB ' || CASE WHEN NOT rolcanlogin THEN 'NO' ELSE '' END || 'LOGIN ' || 'CONNECTION LIMIT ' || rolconnlimit || CASE WHEN rolvaliduntil IS NULL THEN '' ELSE E' VALID UNTIL \'' || rolvaliduntil::text || E'\'' END || ';' AS sentencia FROM pg_catalog.pg_roles --WHERE ARRAY[oid] @ $1 --WHERE oid IN (LISTA_OID_ROLES) UNION SELECT 2::SMALLINT AS orden, 'ALTER ROLE ' || rolname || ' SET ' || array_to_string( rolconfig, ';ALTER ROLE ' || rolname || ' SET ' ) || ';' AS sentencia FROM pg_catalog.pg_roles WHERE rolconfig IS NOT NULL --AND ARRAY[oid] @ $1 --AND oid IN (LISTA_OID_ROLES) UNION SELECT 2::SMALLINT AS orden, 'GRANT ' || rol.rolname || ' TO ' || member.rolname || '' || CASE WHEN am.admin_option THEN 'WITH ADMIN OPTION' ELSE '' END || ';' AS sentencia FROM
Re: [GENERAL] is max connections in a database table somewhere
2011/8/10, Greg Smith g...@2ndquadrant.com:
On 08/10/2011 02:46 PM, Geoffrey Myers wrote:
Is the max connections value in a system table somewhere?
Yes, it is in the table pg_catalog.pg_database. The column is named
datconnlimit and is of type int4. See this:
http://www.postgresql.org/docs/9.0/interactive/catalog-pg-database.html.
But this would be the appropiate way of getting the value:
SELECT CAST(current_setting('max_connections') AS integer);
You would rather use that form instead of messing up with the catalogs.
--
Diego Augusto Molina
diegoaugustomol...@gmail.com
ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indicating DEFAULT values in INSERT statement
Hi, when posting SQL it may be a good practice to post your PG
version so that answers may be more accurate and better fit your
needs. In this case, I don't think you'll be able to do what you are
trying to, because as of my understanding the DEFAULT is not part of
an expression but a keyword itself which is to be written *instead* of
an expression. I get your point and seems pretty fair to me, but if
I'm right you don't have access to the default value of the column
from within an expression.
Assuming you're using the latest stable version of PostgreSQL (9.0
as of today), you can check the following page for an online reading
reference of the INSERT statement:
http://www.postgresql.org/docs/9.0/interactive/sql-insert.html
If you look carefully, you'll see that for the value of a column you
may write: the DEFAULT keyword [exclusive or] an expression (which
includes specific values).
The [exclusive or] is deduced because of the pipe (`|') between the
two choices which are embraced by curly braces.
So, if you were able to use the DEFAULT keyword inside an
expression, instead of having the { expression | DEFAULT } syntax
you would have the expression syntax, which would include the case
of having just DEFAULT as an expression. It's just a matter of
understanding the syntax.
Cheers!
--
Diego Augusto Molina
diegoaugustomol...@gmail.com
ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indicating DEFAULT values in INSERT statement
This is a bit hacky, but it may work (I haven't tried it yet). If it works let us know and if it doesn't then maybe we can debug it and get something useful, or move on to another solution. code INSERT INTO public.test ( userid, object_id, user_notes, object_status, created_ts ) VALUES ( p_userid, p_obj_id, p_user_notes, p_obj_status, Case When p_created_ts Is Not Null Then p_created_ts Else ( -- You may want to be sure the field has a default value. SELECT d.adsrc -- or should it be d.adbin? FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_attrdef d ON (a.attnum = d.adnum) WHERE a.attname = 'created_ts' AND a.attrelid = 'public.test'::REGCLASS AND d.adrelid = 'public.test'::REGCLASS ) End ); /code Well (thinking it thoroughly) it won't work at all as is. It will just put the source code of the default expression but you would need to *interpret* it first. Looked for a way to do this (without `EXECUTE') but couldn't find it. Sorry. -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
